Choosing destination target without worrying if the table exists or not

[pmscorca]

Hi,

respect to a dataflow gen2 when a data destination is specified, choosing a lakehouse as a destination target it occurs to indicate a new table or select an existing table.

If possible somehow, respect to a destination lakehouse I'd like to specify only a table name and so the dataflow should understand if the related table exists or not: in this way, the first dataflow run could create the table if it doesn't exist and the next executions simply run the chosen update method, append or replace.

Any suggests to me, please? Thanks

[Anonymous]

Hi @pmscorca ,

Dataflow Gen2 does not automatically check for the existence of a table before creating it. For subsequent runs, you can use scripts or stored procedures to check if the table exists, and then decide whether to append or replace the data.

You can also look at this document: Dataflow Gen2 data destinations and managed settings - Microsoft Fabric | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[frithjof_v]

I don't think this is possible in Dataflow Gen2.

I think when you assign a data destination, you need to specify if you want to use an existing table, or if you want to create a new table.

"in this way, the first dataflow run could create the table if it doesn't exist and the next executions simply run the chosen update method, append or replace." 

This is what happens when you specify to create a new table.

In a Notebook, on the other hand, you can just use saveAsTable('tableName') and it will either use an existing table if there exist a table with that name, or it will create a new table if it doesn't exist already.

[pmscorca]

Hi, do you say that a dataflow however controls if a table exists or not both for a new table case and an existing table case? If the specified table doesn't exist then the dataflow creates it, isn't it?

If so, I don't need to change the data destination settings after the first run, isn't it?

Thanks

[frithjof_v]

I think this documentation is a good source of information:

https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-data-destinations-and-managed-se...

(I haven't tested all the possible settings combinations which are listed there.)

My understanding is that if you specify a new table, then the Dataflow Gen2 creates the new table on the first refresh, and then uses this table for all subsequent refreshes. According to the docs, the Dataflow Gen2 might drop and recreate the table on each refresh, depending on which destination configurations are chosen. See more info in link above (although I'm a bit unsure if it actually gets dropped or just overwritten).

If the table gets deleted somehow (also by someone other than the dataflow gen2), the dataflow gen2 will also recreate the table on the next refresh.

It should not be necessary to edit the destination settings after creating the Dataflow Gen2.

It is my understanding that if you specify to use an existing table, it is only possible to select a table which already existed. So you cannot use the existing table option to create a new table.

If the existing table gets deleted somehow, the Dataflow Gen2 will not recreate the table. You would need to recreate the table manually.

Anyway, it should not be necessary to edit the destination settings after you have created the Dataflow Gen2.

[pmscorca]

Hi,

I've accomplished some proofs.

If I specify the "New table" option and next the name of the this table, the Dataflow gen2 seems to check the table existence: if it doesn't exist then create it, otherwise uses it, appending or replacing data basis on the related option.

I'd like this behaviour because I can create a new dataflow gen2 specifying to create a new table, run it and then run it again without changing the dataflow.

This behaviour should be better documented also directly in the Fabric service.

I've submitted this idea Managing better the New table and the Existing table options for a Data destination 

[Anonymous]

Hi  @pmscorca ,

It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

Yilong Zhou