Load data into synapse delta lake from Azure SQL database using synapse pipeline

Question

I want to load data from azure sql database to delta lake table using synapse pipeline. I tried using copy activity but there is no target type available to load data into delta lake directly. So, using copy activity I am reading data from azure sql and writing them into csv file in Azure data lake storage. Then using notebook I am writing data from csv to delta lake table. all the steps are performing using pipeline.

I am using metadata driven approach to pull the multiple tables from source so single pipeline will be used. once metadata is read, writing to csv and writing to delta lake using notebook will be inside the for each loop. The performance is too poor for this approach.

I am not sure about the approach I am using for the requirement. I need to improve the performance of the pipeline.

Answer 1

Hi @Anonymous ,

Thank you for posting query in Microsoft Q&A Platform.
Delta format is not available in copy activity. Hence you are not able to see option.

In your case, I would suggest you use dataflows directly to take data from azure sql database to delta. Delta format is available in data flows as inline datasets. Click here to know more about it.

Please note, data flows also works on spark. In above approach we dont need to have middle layer of taking tables data to files and then loading to destination.

Below link may come handy to understand various tips to follow for better performance with data flows.
https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-performance

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Not supported in Pipelines yet

if you want to work with delta in this way you would need to use the SparkPool and code like Python to interact with the delta in adlsv2