how to replace data in azure sql database using azure data factory?

Question

I would like to find out if it's possible to replace the data in azure sql database with the latest one everytime when my pipeline is ingesting new data. I have a pipeline that takes data from azure blob storage to azure sql but the challenge that I am facing is that the pipeline runs every 5 minutes and it is concatenating new data on the existing table in the database and I want it to replace the data everytime the pipeline is running. I have tried everything on the internet and nothing worked for me and now the space of my azure sql is exhausted.

Anything that will help will be highly appreciated. thanks

Answer 1

How you update your data depends on the data lifecycle. It sounds like you want a truncate & replace, or a MERGE.

1. Create a landing table and a final table to report against.
2. The ADF copy component has a pre-script feature in the Sink. In the pre-script feature truncate the table you are bringing the data into, then bring in the data from ADL to a landing table.
3. Via stored procedure wrapped in a transaction, update the final table with a try/catch from the landing table. On a failed try, roll back the transaction and throw an error. The landing table allows you to wrap the truncate in a transaction with the insert preventing you from having an empty table on issues with the insert.

Truncate & Replace

1. Begin a transaction
2. Truncate the final table
3. BEGIN TRY
4. Insert into it from the landing table and commit transaction
5. BEGIN CATCH
6. Rollback transaction and throw error.

Merge

1. Begin a transaction
2. BEGIN TRY
3. When data found in source that is not in target THEN inert into target.
4. When data found in source that is in target but differs THEN update target.
5. When data found in target that is not in source THEN delete from target.
6. COMMIT TRANSACTION
7. BEGIN CATCH
8. Rollback transaction and throw error.