# How we test that AUTO CDC keeps the right customer record

> A Databricks CDC flow can finish green and still keep a customer's old city or bring a deleted customer back. How we test ordering and deletes before release.

Published: 2026-10-27
Author: Ihor Seleznov
Tags: Databricks, Knowledge Share
Canonical: https://www.techfabric.com/blog/how-we-test-that-auto-cdc-keeps-the-right-customer-record

---

Customer changes don't always reach Databricks in the order they happened. If an AUTO CDC flow uses arrival time as its sequence, a customer who moved twice can end up with the older city, and a late update after a delete can bring that customer back. The pipeline update still succeeds. When only the city is wrong, the row count and the schema still match, so nothing in the run says which version of the customer survived.

We built a test kit at TechFabric to catch this before a CDC flow feeds a shared table. It holds five hand-written event sequences, each with the customer rows we expect after every step, fifteen checkpoints in all, plus a Lakeflow definition and workspace helpers to run them. A small Python model of the flow matched all fifteen checkpoints, and then we broke five copies of it on purpose. The copy that ordered events by arrival kept Lille over Nantes at the third checkpoint, and at the fifth it brought deleted customer 101 back in Bordeaux.

## The source contract we test against

The kit tests SCD Type 1, which keeps one current row per customer. [SCD Type 2 keeps history](https://docs.databricks.com/aws/en/data-engineering/what-is-cdc) and needs its own assertions.

Our synthetic source uses `customer_id` as its key and `(source_lsn, event_pos)` as its logical sequence: a source log position and the order inside that position. Both are non-null, stable across redelivery and authoritative for these fixtures. An ingestion timestamp would only say when an event arrived.

Every INSERT or UPDATE carries complete `city` and `email` values, so a null city means the city was cleared. An exact redelivery repeats every field. Two events with the same key and sequence but different payloads break the contract, and our local validator rejects them before any sequence runs. The Lakeflow definition assumes that check has already happened upstream.

The [AUTO CDC sequencing guide](https://docs.databricks.com/aws/en/ldp/cdc#use-multiple-columns-for-sequencing) documents a STRUCT for ordering by more than one field. We put `event_pos` second because one log position can carry two changes to the same customer, and only the position inside it says which came first. A tie-breaker that only makes events unique still sorts them, and it can sort them wrong.

## The Lakeflow definition under test

We wrote this definition for an isolated test workspace and haven't run it there yet. `cdc.source_table` points at an append-only Delta table of normalized events, and the flow doesn't read Delta Change Data Feed.

```python
from pyspark import pipelines as dp
from pyspark.sql.functions import expr, struct

@dp.temporary_view
def customer_events():
    return spark.readStream.table(
        spark.conf.get("cdc.source_table")
    )

dp.create_streaming_table("customers_current")
dp.create_auto_cdc_flow(
    target="customers_current",
    source="customer_events",
    keys=["customer_id"],
    sequence_by=struct("source_lsn", "event_pos"),
    apply_as_deletes=expr("operation = 'DELETE'"),
    ignore_null_updates=False,
    except_column_list=["operation", "source_lsn", "event_pos"],
    stored_as_scd_type=1,
)
```

The [Python API reference](https://docs.databricks.com/aws/en/ldp/developer/ldp-python-ref-apply-changes) defines how deletes and nulls are handled. With `ignore_null_updates=False`, a null clears the city. A source that leaves unchanged fields out of its updates needs its own rule for filling the missing ones, and our fixtures don't cover that case.

## Expected states written by hand

Every expected row in `fixtures.json` is typed by hand. None of them comes from the sequencing or mapping code under test, because expectations computed with the implementation's own logic agree with whatever that logic does.

The main sequence starts with an empty target. The first batch inserts customer 101 in Paris and customer 202 in Lyon, and both emails have to match at every checkpoint too. Each step is a separate append followed by a completed pipeline update:

| Step | Event for customer 101 | Expected visible cities |
|---|---|---|
| 1 | INSERT at `(10, 0)`: Paris | 101: Paris; 202: Lyon |
| 2 | UPDATE at `(30, 0)`: Nantes | 101: Nantes; 202: Lyon |
| 3 | Late UPDATE at `(20, 0)`: Lille | 101: Nantes; 202: Lyon |
| 4 | DELETE at `(40, 0)` | 202: Lyon only |
| 5 | Late UPDATE at `(35, 0)`: Bordeaux | 202: Lyon only |

Customer 202 never changes. It's the control, so a flow that drops or rewrites an untouched customer fails on 202 even when 101 looks right.

Four shorter sequences cover the rest of the contract. In one, an update changes both city and email. In another, two events share LSN 20 and position 2 arrives before position 1. A third sets the city to null, and the last delivers the same update twice.

The comparison runs in a separate notebook or job after each update, with `expected_rows` loaded from the fixture. It checks keys, both payload columns and how many times each row appears:

```python
from collections import Counter

target = spark.table("<catalog>.<test_schema>.customers_current")
columns = ["customer_id", "city", "email"]
actual = Counter(tuple(r) for r in target.select(*columns).collect())
expected = Counter(tuple(r) for r in expected_rows)
assert actual == expected, (actual, expected)
```

A set comparison would hide a duplicated customer, and a row count would miss a city swapped with an email. Our workspace helper checks the projected column types on their own as well.

## Running one checkpoint per pipeline update

Each sequence gets its own source and target namespace. For every checkpoint we append one batch, start a triggered update, wait for `COMPLETED` and compare the target with the expected rows. The target and the flow checkpoints stay in place between steps, so each comparison tests a transition from the state the previous step left.

```mermaid
%% caption: Each checkpoint appends one batch, runs an ordinary update and compares the target with rows written by hand.
flowchart TD
  A["Fixture: events and expected rows per checkpoint"] --> B["Append one batch to the source table"]
  B --> C["Ordinary triggered pipeline update"]
  C --> D["Compare target with expected rows"]
  D -->|"match"| E["Next checkpoint, flow state kept"]
  E --> B
  D -->|"mismatch"| F["Record the differing rows"]
  class D accent
```

Steps use ordinary refreshes. Databricks documents that a [full refresh clears streaming-table data and flow checkpoints](https://docs.databricks.com/aws/en/ldp/concepts/refresh), which turns the run into a replay from scratch. A final replay from an empty target can sit next to the incremental run as a separate test.

Deletes are checked on the published target. AUTO CDC keeps tombstones to handle changes that arrive out of order, and its [delete-retention guidance](https://docs.databricks.com/aws/en/ldp/developer/ldp-python-ref-apply-changes) says `pipelines.cdc.tombstoneGCThresholdInSeconds` has to exceed the longest expected delay between an event arriving and the pipeline processing it. The delete sequence finishes well inside that window. What happens after a tombstone expires needs its own test, sized to real delivery and outage delays, and our local model can't answer that, because its delete markers never expire.

## Five broken copies and what caught them

We broke the local model one behavior at a time to see whether the assertions notice. One copy collapsed all customers onto one key and one ordered events by arrival. Another turned deletes into upserts, one swapped city and email, and the last kept the old value whenever an update sent a null. The expected rows stayed the same for all five.

| Sequence | Collapsed key | Arrival order | Ignored deletes | Swapped columns | Kept nulls |
|---|---|---|---|---|---|
| Ordering and delete | caught | caught | caught | caught | |
| Update and mapping | caught | | | caught | |
| Sequence tie | | caught | | caught | |
| Explicit null | | | | caught | caught |
| Exact redelivery | | | | caught | |

Every broken copy failed at least one checkpoint, and the rows they left behind say more than the marks. The copy that ignored deletes kept customer 101 as a row with a null city and a null email. At the first checkpoint, the copy that put every customer under one key returned only customer 101 in Paris, and customer 202 was missing. The swapped copy failed on the first insert of every sequence, so its mark on the redelivery row says nothing about redelivery.

It's the same idea as our [kill matrix for Databricks data quality rules](/blog/testing-the-tests-databricks-kill-matrix), applied to state that changes from one update to the next. A failed startup or a permission error doesn't count as a catch. Only a comparison that ran and found the wrong rows does.

## Running the fixtures in a test workspace

Our next step is the Lakeflow run: all five sequences in an isolated workspace, under the pipeline's real execution identity, with the code revision, pipeline configuration, update IDs and comparison output kept beside each result. Exact redelivery should leave the visible state unchanged under this contract. That tells us about one repeated event and says nothing about exactly-once delivery from the source system to the table.

Start with your own source's key, its sequence fields and what a null means, then write the expected rows for one late update and one delete by hand. Accept the flow when every checkpoint matches, unchanged customers included, and add a sequence whenever the source contract changes.

## Frequently asked questions

### Why compare rows when the row count already matches?

A count stays at two when the flow keeps Lille in place of Nantes, so it can't see which version survived. Comparing keys and both payload columns with their multiplicity catches the wrong city, a swapped column and a duplicated customer.

### Does a full refresh test the same thing?

No. A full refresh clears the streaming table and its flow checkpoints and replays everything, so it never tests a late event arriving after state already exists. It's still useful once at the end, as a separate replay check.

### Can an event ID act as the sequence tie-breaker?

Only when the ID follows the source's own order. An arbitrary ID makes events unique and still sorts them, possibly in the wrong order. Our sequence-tie fixture sends position 2 before position 1 to check exactly that.

### Do the fixtures cover SCD Type 2?

No. They test one current row per customer. Type 2 keeps every version of a customer, so its assertions have to cover the history rows as well as the current one.

### What does the redelivery sequence show?

It checks that an identical event sent twice leaves the visible row as it was. Exactly-once delivery from the source to the table depends on the connector and the ingestion path too, and one repeated event doesn't test those.