Forum Discussion
Fabric data pipeline > copy data activity > table action
Hello,
I have a Fabric data pipeline with copy data activity > destination tab > table action = Overwrite. Importantly, I selected the radio button in the UI for Overwrite (there are 3 options - Append, Overwrite, Upsert). When the pipeline runs, the Lakehouse tables are overwritten, all is good.
I recently updated that copy data activity > destination tab > table action to use an expression (IF( equals(item().incremental_type, 'append'), 'Append', 'Overwrite' ) ).
Now, when the pipeline runs, ALL tables that are processed through that copy data activity have backup tables created in the Lakehouse. For example, an existing bronze table is called dim_proj_raw. After the pipeline runs, a new table appears called dim_proj_raw_backup_[guid], alongside the existing/refreshed one.
I've seen this in the past when I tried to use a variable library variable for table action and couldn't figure out why it was happening at that time so I just selected the Overwrite radio button from the UI, deleted the backup tables and moved on but alas, I'm back here, needing to actually use the table action dynamic expression again...
Should I use something other than 'Overwrite' in my expression? Why does selecting 'Overwrite' from the UI not create those backup tables yet using a dynamic expression does?
I briefly saw a suggestion to create a process to clean up these backup files. I would hope that is not the answer here....
Thanks in advance!
Hi mestee,
When you select Overwrite in the UI, the table refreshes without creating backup tables. However, using an expression-based Overwrite seems to generate tables named <table>_backup_<guid>. This indicates that Fabric may treat dynamic Table Action values differently from static UI selections, though this isn't officially documented. As a workaround, you can use an If Condition to direct to separate Copy Activities one set to Append and one to Overwrite to keep the Table Action statically configured.
Thank you.
2 Replies
Hi mestee,
When you select Overwrite in the UI, the table refreshes without creating backup tables. However, using an expression-based Overwrite seems to generate tables named <table>_backup_<guid>. This indicates that Fabric may treat dynamic Table Action values differently from static UI selections, though this isn't officially documented. As a workaround, you can use an If Condition to direct to separate Copy Activities one set to Append and one to Overwrite to keep the Table Action statically configured.
Thank you.
Thanks. This is exactly what I had already implemented as a workaround (adds unnecessary complexity/activities, so I'm not especially happy about that). I guess the takeaway is using dynamic expression in copy data > destination > table action isn't 100% supported, currently. While you can use it, it will result in backup tables being created in the Lakehouse which creates yet another workaround needed to remove those...
