Fabric Copy Activity Upsert Needs Native Audit Columns
š š¶š°šæš¼šš¼š³š šš®šÆšæš¶š° šš¼š½š šš°šš¶šš¶šš + šš²š¹šš® šØš½šš²šæš is honestly one of the most underrated features in Fabric for high-performance operational data loading. We can now: ā¢ Read directly from sources such as SQL Server / SQL DB ā¢ Write directly into Lakehouse Delta Tables ā¢ Use native Upsert mode ā¢ Avoid Spark notebooks completely ā¢ Avoid Parquet staging/intermediate layers ā¢ Achieve very fast sync performance with only ONE Copy Activity Iām currently using this approach to dynamically load and merge data from multiple SQL Server store databases into consolidated Delta tables in Microsoft Fabric Lakehouse. However, there is one very important feature still missing from Copy Activity Upsert. š šš¼šš¹š± š¹š¼šš² šš¼ šš²š² š»š®šš¶šš² ššš½š½š¼šæš š³š¼šæ š®ššš¼-šŗš®š»š®š“š²š± š®šš±š¶š / ššŖ š°š¼š¹ššŗš»š during Upsert operations. My requirement is: In the destination Delta table, I need these columns: ā¢ DW_DATE_INSERT ā Datetime when a NEW record is inserted through Upsert ā¢ DW_DATE_UPDATE ā Datetime when an EXISTING record is updated through Upsert ā¢ DW_LOAD_DATE ā Maximum of DW_DATE_INSERT and DW_DATE_UPDATE (meaning the latest insert/update datetime) Expected behavior: ā¢ If INSERT occurs: DW_DATE_INSERT should be populated DW_DATE_UPDATE should remain NULL ā¢ If UPDATE occurs: DW_DATE_UPDATE should be updated DW_DATE_INSERT should remain unchanged with its original value Currently, adding these columns dynamically in the Source ā Additional Columns section is NOT a solution. Why? Because these datetime values change every execution, the Upsert process detects every row as changed and re-updates all records again and again, even when business data has not changed. Of course, I know this can be handled using Spark / Notebook MERGE logic. But the whole beauty of this approach is: š”š¼ š»š¼šš²šÆš¼š¼šøš. š”š¼ š¦š½š®šæšø ššš®šæššš½ š¹š®šš²š»š°š. š”š¼ š²š ššæš® šŗš²šæš“š² š¹š®šš²šæ. šššš š¼š»š² š³š®šš šš¼š½š šš°šš¶šš¶šš. It would be amazing if Microsoft could add native insert/update audit column handling directly inside Copy Activity Upsert for Delta Lakehouse tables. This would make Copy Activity even more powerful for very fast enterprise operational sync workloads. If you think this feature would be useful, please vote/support this idea so we can hopefully get it added to Fabric š #MicrosoftFabric #DataEngineering #DeltaLake #Lakehouse #ETL #ELT #OneLake #SQLServer #Fabric #Microsoft #AnalyticsEngineering
