Data type of Delta lake table in synapse gets changed after loading data using dataflow

heta desai 247 Reputation points
2022-09-23T11:29:37.467+00:00

Trying to load data from SQL Server to delta lake. As we can not connect synapse dataflow with on-premise SQL Server so I pulled data from SQL Server to CSV file in ADLS using synapse pipeline and than using dataflow reading csv file and loading data into delta lake table. I have implemented meta data driven approach where Pipeline reads metadata of tables to read from source table and write to delta lake table stored in Azure SQL server and iterates the process for each record of metadata.

In delta lake some columns are integer but when data gets inserted through synapse dataflow the column data type changes to string.

Allow schema drift is enable because auto mapping is enable. As I am following metadata driven approach mannual schema mapping is not possible as mapping will be different for each table.

Please suggest a way how I can handle the schema drift issue.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,362 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
1,916 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,525 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2022-09-26T21:06:51.767+00:00

    Hello @Anonymous ,
    Thanks for the question and using MS Q&A platform.
    As we understand the ask here is how to preserve the data type in delta tables , please do let us know if its not accurate.
    I think the issue here is that you are using CSV file as the staging file type . If you use paraquet or Azure SQL as the staging data store it should work . In the CSV world everythings is string .
    Please do let me if you have any queries.
    Thanks
    Himanshu

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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