How to Auto map columns is Azure synapse mapping data flow inline sink (delta table)

Question

I need to read data from Azure SQL server and want to write to Delta lake table. I have implemented metadata driven approach where I have store select query for each source table. In dataflow source I am using this select query to pull data from data source. As approach I am following is meta-driven the schema will be different for each table. If I enable Allow drift schema it changes the column data types in delta lake table according to the data it pulls from source. I want to preserve the column data types of target delta lake table.

Please suggest me the way to persist target delta lake table schema and load the data from data source azure sql server.

Answer 1

Hi @Heta Desai ,

Thank you for posting query in Microsoft Q&A Platform.

Any specific reason why we are using Dataflows here?

You can consider using Copy activity also to load data from source to Delta table. Advantage of using copy activity is mappings between source and sink can be dynamic as well. That way you have good control on column mappings and datatypes.

Kindly check below video, where it's explained in detail that how to have dynamic column mappings in copy activity.
Dynamic Column mapping in Copy Activity in Azure Data Factory

Using similar kind of dynamic column mappings in dataflows in not possible. We can use auto mapping of sink transformation. But for that to work column names should be same.

For any reason, if your requirements have to perform some transformations hence you will be going with data flows, then I would suggest you have the data transformation done by data flow and load transformed data into some dummy storage or location. And then take that transformed data from dummy location and load to Delta using copy activity.

Hope this helps. Please let me know how it goes.

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

Please consider hitting Accept Answer button. Accepted answers help community as well.