Forum Discussion
Microsoft Lakehouse Delta Table Logging
In our Microsoft Fabric Lakehouse environment, we are working with a managed Delta table named "Customer_Orders", which includes a primary key column `cust_num` and a timestamp column `RecordDate`. Change Data Feed (CDF) has been enabled using the following configuration:
ALTER TABLE Customer_Orders SET TBLPROPERTIES (delta.enableChangeDataFeed = true)
Our current ingestion process involves extracting Delta Parquet files from SQL Server based on a rolling window defined by `MAX(RecordDate) - 3 days`. This approach occasionally results in duplicate rows being reintroduced into the Lakehouse table. To handle this, we perform a `MERGE` operation using `cust_num` and `RecordDate` as composite keys, with the following logic:
- WHEN MATCHED THEN SKIP** (no updates)
- WHEN NOT MATCHED THEN INSERT** (new row)
However, the customer expects Fabric Lakehouse to automatically detect changes across all columns—without requiring explicit key definitions in the `MERGE` clause—and to perform updates or inserts accordingly. This expectation appears to be misaligned with Fabric’s native capabilities.
We are seeking guidance from Microsoft to help clarify the out-of-the-box behavior of Lakehouse-managed Delta tables, particularly around change detection and merge semantics, so we can realign the customer’s understanding and ensure the solution is both technically sound and aligned with platform best practices.
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.
3 Replies
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.
