CODEWITHSUNDEEP
azureazure-data-factoryazure-synapse
Don
Don

Reputation: 31

How to Build Incremental data loading?

I found this blog (https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview) and tried it, it was very helpful in moving the data b/w sql server and azure database,but how can I build something like this to copy data over in B/w On Prem SQL Server and Azure Datawarehouse??

Are there any links which I can get started with.

I have found various links but they are not very reasonable when I talk about Delta data loading?

Is there any way I can achive this using ADF V1?? or this is only possible using ADF V2??

Upvotes: 0

Views: 1990

Answers (1)

SQLmojoe
SQLmojoe

Reputation: 2053

The change tracking part of the guidance is still relevant. You need some way to figure out what is the incremental data on the SQL Server on-premises instance that need to be pushed to your Azure SQL DW. What's different is how you can push the incremental data to SQL DW.

If you have something built using SSIS, it will work with SQL DW. Download the Azure pack for SSIS to get the SQL DW optimized workflow. ADF can do the same also if you already use it or are ok with spinning up another service.

In the simplest case you can export the incremental data into csv files, push to blob store using azcopy then insert into your SQL DW via external tables. For small data sets, this works just fine and can be automated with scripts to minimize risk and labor. Large data sets will be trickier as inserts are expensive fully logged operations. Check out Minimal logging with bulk load for hints on how INSERT.....SELECT can be minimally logged and the conditions for it to occur. While partitioning is a valid strategy, it's not a good idea for most environments for daily incremental loads mostly because there isn't enough data to build high quality row groups with clustered column store tables. That can results in poor query performance until the index is rebuilt. Optimizing for a single load operation daily instead of tens, hundreds or thousands of queries daily is usually not the right tradeoff.

Upvotes: 2

Related Questions