question

RobStelter-4904 avatar image
0 Votes"
RobStelter-4904 asked KranthiPakala-MSFT commented

Azure Lake Files Loading along with transformation to Azure SQL database

Hi Folks,
We are in the process of loading incremental files (with updates and newly inserted records) from Azure Lake Gen2 to Azure SQL with transformation (to dimension and fact tables). Please note the destination is not Synapse, but it is a Azure SQL database.
We have to approaches for it:
1. Use Data flow to get the files and transform it in the ADF and then load to the final table. In this method I fear we might get stuck if the transformation is more complex.
2. OR, load the file in staging table in Azure SQL and then use Stored Procedure to transform all the data. In this step we use extra step to create staging table on the Destination database but the transformation step is much easier to do in SP.

Is there a third way which is more feasible? If not, which one should be considered from above ?

Have a great day!



azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered KranthiPakala-MSFT commented

Hi @robstelter-4904,

Welcome to Microsoft Q&A forum and thanks for your query.

Option 1 & Option 2 are more feasible and I don't think of a better option.
To choose between Option 1 and Option 2 it depends on the how comfortable/proficient you are with SQL SP.
But when it comes to Mapping data flow, it allows users build data transformation pipelines (ELT jobs) using a graphical user interface. Essentially Data Flow mapping generates Spark code for the pipeline to be executed on Spark at scale without needing to write a line of code and with the advantage of a GUI for pipeline management, data lineage, query push down and most importantly embedding within the current ADF pipelines like any other activity.

Mapping Data flow currently support various transformations as listed in this document: Mapping data flow transformation overview
I would encourage you to have a look at these supported transformations and see if those help to meet your business requirement and if yes, then it would be better to proceed with Mapping Data flows so that end to end ETL is handled within ADF. By doing so you can skip the extra step of staging and don't have to manage any more SP's to achieve your requirement.

But if you feel like out of box Mapping data flow transformations doesn't meet your business requirement, then it is better to go with Option 2 by having a staging table and do transformation using SP's.

Hope this info helps.



Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @robstelter-4904,

Just checking in to see if the above suggestion was helpful. If this answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @robstelter-4904,

We still have not heard back from you. Just wanted to check if the above suggestion was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·