I am loading data from Azure sql Db to snowflake using data flow activity with infer schema at source and Recreate table at sink enabled , but table created at snowflake side is created with all column as string data type

Question

Answer 1

When you enable the "Infer Schema" option in ADF Data Flow, ADF attempts to infer the data types of the source columns. However, during the process of writing to Snowflake, especially when "Recreate Table" is enabled, ADF might not translate the inferred data types from the source (Azure SQL Database) to the corresponding Snowflake data types accurately. Often, ADF defaults to creating all columns as strings in Snowflake because string data types are versatile and can accommodate various types of data.

Instead of relying on "Infer Schema," manually specify the data types in the Data Flow's schema projection. This way, you can explicitly define how each column should be treated in Snowflake, ensuring that numeric columns, dates, ..., are not treated as strings.

In my opinion, for most scenarios, manually defining the schema in the Data Flow or pre-creating the table in Snowflake with the correct schema would be the best approach.

Answer 2

Hello Nidhi Kumari,

Welcome to the Microsoft Q&A forum.

On the schema mapping, can you explicitly map the data types in your data flow activity and see if it helps?

also, you can try with Copy activity and see the results.