Lakeflow AUTO CDC (APPLY CHANGES) gives wrong results: out-of-order events, SCD Type 2 duplicates, deletes that come back

#Symptoms

• The target shows an older value for some keys although newer changes were ingested.
• SCD Type 2 tables have many history rows with identical values, or overlapping validity periods.
• Deleted records reappear after a later batch.
• After a pipeline full refresh, history or deleted rows are missing.

AUTO CDC handles ordering, upserts, deletes and history for you, but only as well as the columns you give it.

#The basic pattern

Python (newer API name; older code uses dlt.apply_changes with the same arguments):

import dlt
from pyspark.sql.functions import col, expr

dlt.create_streaming_table("customers")

dlt.create_auto_cdc_flow(
    target="customers",
    source="customers_cdc_clean",
    keys=["customer_id"],
    sequence_by=col("source_lsn"),
    apply_as_deletes=expr("op = 'D'"),
    except_column_list=["op", "source_lsn", "_ingested_at"],
    stored_as_scd_type=1,
)

SQL (older but still common syntax):

CREATE OR REFRESH STREAMING TABLE customers;

APPLY CHANGES INTO customers
FROM STREAM(customers_cdc_clean)
KEYS (customer_id)
APPLY AS DELETE WHEN op = 'D'
SEQUENCE BY source_lsn
COLUMNS * EXCEPT (op, source_lsn, _ingested_at)
STORED AS SCD TYPE 1;

Check the current documentation for the exact API names in your release; the semantics below are the same.

#Problem 1: wrong sequence column (stale values)

AUTO CDC uses sequence_by to decide which change is newest. If you sequence by ingestion time (_ingested_at, file modification time), two changes landing in the same file or out of order are applied in the wrong order.

Use the source's own ordering:

If one column isn't unique per key, sequence by a struct so ties are broken deterministically:

sequence_by=expr("struct(source_lsn, source_seq)")

Ties on sequence_by for the same key make the result non-deterministic.

#Problem 2: duplicate SCD Type 2 history rows

With stored_as_scd_type=2, a new history row is created whenever tracked columns change. You get noise when:

• The feed sends "updates" where nothing meaningful changed (e.g. a last_sync timestamp column changes).
• Columns that shouldn't create history are tracked.

Track only the columns that matter:

dlt.create_auto_cdc_flow(
    target="customers_history",
    source="customers_cdc_clean",
    keys=["customer_id"],
    sequence_by=col("source_lsn"),
    apply_as_deletes=expr("op = 'D'"),
    except_column_list=["op", "source_lsn", "_ingested_at"],
    stored_as_scd_type=2,
    track_history_except_column_list=["last_sync_ts", "etl_batch_id"],
)

(track_history_column_list is the inverse option.) Also drop exact duplicate change records upstream (same key, same sequence, same values), which often come from re-delivered CDC messages.

#Problem 3: deletes that come back

• Delete without sequence: if the delete record's sequence value is lower than an earlier upsert's (wrong column, clock issues), the delete is ignored as "older".
• Late upsert after delete: AUTO CDC keeps deleted keys as tombstones for a retention period so late, older updates don't resurrect them. If a late record arrives after that retention, it can reappear. Check the tombstone retention setting in the docs if your feeds can be very late.
• Truncate: apply_as_truncates (SCD1 only) handles source truncate events; without it, a truncate in the source doesn't empty the target.

#Problem 4: full refresh destroys what the source can't replay

A full refresh of the pipeline clears the streaming table and reprocesses the source from the beginning. If the source (Kafka topic, CDC files with retention, Event Hubs) no longer has the full history, SCD2 history and old rows are lost.

Caution

Protect CDC targets from accidental full refresh. Set pipelines.reset.allowed = false on the table, and restrict who can run full refreshes on production pipelines.

dlt.create_streaming_table(
    "customers_history",
    table_properties={"pipelines.reset.allowed": "false"},
)

Keep the raw CDC feed in a bronze table with long retention, so a rebuild is possible from your own copy.

#Step-by-step diagnosis for one bad key

-- 1. All changes for the key, in the order AUTO CDC sees them
SELECT op, source_lsn, _ingested_at, *
FROM   main.bronze.customers_cdc_clean
WHERE  customer_id = 4711
ORDER  BY source_lsn;

-- 2. What the target has
SELECT * FROM main.silver.customers_history WHERE customer_id = 4711 ORDER BY __START_AT;

In SCD2 targets, __START_AT and __END_AT hold the sequence values bounding each version. Compare them with the source order to see which record was applied wrongly.

#Checklist

#Prevent it next time

• Agree with source owners which column defines order, and test with out-of-order data.
• Add expectations on the CDC source (non-null keys and sequence, valid op codes).
• Keep raw CDC in bronze long enough to rebuild silver.
• Review SCD2 row growth per key; sudden spikes mean noisy updates.