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!