Azure Synapse/Data Factory - Schema Drift is not writing additional columns.

Question

Hello,

I am trying to implement a dataflow that takes a parquet file, then upserts that file into a dedicated SQL pool sink. I need ADF to add any additional columns that are present in the Parquet, but are not present in the sink table. I have enabled 'Allow Schema Drift' everywhere, and make sure that 'Validate Schema' is disabled.

In my Sink, I can see data for all of my columns (data not pictured here, but is present):

My Upsert rule is just to allow all through:

My Sink:

However, when I run the flow/preview data, only the column that is present is the one that was originally in the source:

Is there something I need to do in order to get my schema to fully flow from the parquet into the table?

Answer 1

Hi @Anonymous ,

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

In your case its SQL Pool table if you would like additional column also should be created automatically from source data then in that case you should consider selection recreate table option.

If your table schema already has that additional column when also it will work.

Kindly check above points and alter your implementation accordingly.

Please also check below additional resources to understand about schema drift.\
https://techcommunity.microsoft.com/t5/azure-data-factory-blog/how-to-handle-evolving-database-schemas-in-your-etl-with-azure/ba-p/1303851
https://www.youtube.com/watch?v=LS0u7DxhpDI
https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-schema-drift

Hope this helps. Please let me know if any further queries.

---

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