How do I maintain data between an on-prem SQL Server and an Azure Data Lake?

Question

How do I maintain data between an on-prem SQL Server and an Azure Data Lake?

System2000 236

Hi,

I am going to be using Azure Synapse or ADF pipelines to ingest several TB of data from SQL server data sources and store them as CSV in an Azure Data Lake Gen 2.

Data is regularly appended to (and updated) in the source SQL Server tables, but I don't really want to ingest the full number of TBs of data every day to keep the data in the data lake up to date. Ideally, I would like to only ingest data from the SQL Server that is not already in the data lake, and those entries that have been updated. Can this be done?

If it's possible to achieve this, then I am also wondering how I update the CSVs in the data lake e.g. can I append data to the CSVs? can I update entries in the CSVs?

Help is appreciated.

Thanks.

PRADEEPCHEEKATLA 91,831 Reputation points

2021-10-29T07:10:18.41+00:00

Hello @System2000 ,

Thank you for the question and using MS Q&A platform.

Files in Azure data lake storage will not get append they always get override. So full load will work. But since your data is huge in TBs full will be not good option to go with as it may consume more time and resources.

Suggested approach will be have data loaded in to your data lake in partitions fashion based on dates.

For example:

Lets assume if I am trying to load sales data for today alone. It will take only newly data came from source and load in to a new file called "sales_20211028.csv".

So for every data new data will load in to new file in some data time partitions folder structure.

2021/10/sales_20211001.csv --> this will contain 1st oct data

2021/10/sales_20211002.csv --> this will contain 2st oct data

2021/10/sales_20211003.csv --> this will contain 3st oct data

Hope this helps.

Answer accepted by question author

0 additional answers

Your answer

PRADEEPCHEEKATLA 91,831 Reputation points

2021-10-29T07:10:18.41+00:00

Hello @System2000 ,

Thank you for the question and using MS Q&A platform.

Files in Azure data lake storage will not get append they always get override. So full load will work. But since your data is huge in TBs full will be not good option to go with as it may consume more time and resources.

Suggested approach will be have data loaded in to your data lake in partitions fashion based on dates.

For example:

Lets assume if I am trying to load sales data for today alone. It will take only newly data came from source and load in to a new file called "sales_20211028.csv".

So for every data new data will load in to new file in some data time partitions folder structure.

2021/10/sales_20211001.csv --> this will contain 1st oct data

2021/10/sales_20211002.csv --> this will contain 2st oct data

2021/10/sales_20211003.csv --> this will contain 3st oct data

Hope this helps.

Answer 1

System2000 236

Hi @PRADEEPCHEEKATLA ,

Thanks for your response. Since asking the question I found this article:
https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview

I think this will suit my needs. (Your suggestion is the fourth of four options.)

Thanks

Simon.

PRADEEPCHEEKATLA 91,831 Reputation points

2021-11-01T11:13:25.427+00:00

Hello @System2000 ,

Glad to know that your issue has resolved. And thanks for sharing the solution, which might be beneficial to other community members reading this thread.

Share via

How do I maintain data between an on-prem SQL Server and an Azure Data Lake?

0 additional answers

Your answer