Forum Discussion
Microsoft Lakehouse Delta Table Logging
hi, Fabric Lakehouse does not perform automatic change detection across all columns.
A robust ingestion pattern requires defining one or more keys that uniquely identify a record (cust_num + RecordDate in your case).
If the business expects column-by-column change detection, that must be implemented in your ingestion pipeline (e.g., by hashing all columns or comparing snapshots via CDF).
Best practices
- Continue using MERGE with well-defined keys.
- If you want “all column” change detection, add a row hash column (MD5/SHA of all fields) and compare hashes during ingestion.
- Use CDF for incremental extraction instead of time-window-based logic (this avoids reintroducing duplicates).
Fabric Lakehouse Delta tables don’t auto-detect all column changes.
CDF gives raw inserts/updates/deletes, but won’t auto-merge.
MERGE always needs defined keys (like cust_num) and explicit update rules.
“All-column automatic change detection” is not supported out-of-the-box—must be coded manually.
👉 Best practice: use CDF + MERGE with business keys.
We are currently implementing an ingestion pipeline in a Microsoft Fabric Lakehouse using a managed Delta table (Customer_Orders). The table contains a primary identifier cust_num and a timestamp column RecordDate, and Change Data Feed (CDF) has been enabled:
ALTER TABLE Customer_Orders SET TBLPROPERTIES (delta.enableChangeDataFeed = true)
Our ingestion process extracts Delta/Parquet files from SQL Server using a rolling window (MAX(RecordDate) - 3 days). Because of this overlap window, duplicate rows may occasionally reappear in the Lakehouse table. To address this, we currently perform a MERGE operation using cust_num and RecordDate as composite keys:
WHEN MATCHED THEN SKIP
WHEN NOT MATCHED THEN INSERT
However, the customer expects the Lakehouse Delta table to automatically detect changes across all columns and perform updates/inserts without explicitly defining matching keys in the MERGE clause.
Based on our understanding, this expectation does not align with the native behavior of Delta tables in Microsoft Fabric. Delta Lake requires explicit match conditions in MERGE operations, and it does not automatically infer row identity or detect column-level changes unless a deterministic key is defined. Additionally, enabling Change Data Feed (CDF) provides change tracking for downstream consumers, but it does not eliminate the need for explicit merge logic during ingestion.
We would appreciate confirmation from the community or Microsoft experts on the following:
Whether Fabric Lakehouse Delta tables support any automatic change detection across all columns without defining keys.
Recommended best practices for incremental ingestion and deduplication when source extraction uses rolling windows.
Whether CDF-based ingestion patterns are preferred over window-based extraction for avoiding duplicate processing.
Our goal is to align the solution with Fabric-native design patterns while clarifying platform capabilities for the customer. Any insights or recommended architectural approaches would be greatly appreciated.
Thank you.