question

samnick-7321 avatar image
0 Votes"
samnick-7321 asked HimanshuSinha-MSFT commented

DDL and Upsert in the same flow without recreate Table

Hi,
We have a system where the source table undergoes changes (mostly adding new columns) often. Even though it has been recommended not to do that frequently, certain teams do it and I'd like to know with our incremental load in ADF , is there a way to dynamically add those new columns even when i'm not aware and also perform its regular upserts . Is this possible, without me having to drop and recreate the table everytime.

Thanks in advance.

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @samnick-7321 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

1 Answer

MarkKromer-MSFT avatar image
0 Votes"
MarkKromer-MSFT answered

If your sink is a database table, you'll need to alter the table definition first. This scenario is why many data engineers utilize NoSQL like CosmosDB where you just write the output as JSON with a flexible schema. Alternatively, you sink the data as Parquet files in the lake and then create external table definitions, like in Synapse, that point to the new files with a new schema. The more traditional method would be to recreate the table every time, treating the table as a "staging ETL table" and then use a 2nd process to either create new tables definitions from that staging table, or create views on top of it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.