Change data capture (CDC) keeps a downstream table in step with row-level inserts, updates, and deletes instead of reloading the whole source. Lakeflow AUTO CDC handles the state-management work, including sequencing, deletes, and SCD history. It still needs a precise source contract: which clock wins, how ties break, what NULL means, and which changes deserve history.
I built this experiment to see what happens when source data is late, duplicated, contradictory, or noisy, and to separate a pipeline that finishes from a target I would trust. The suite pushes nine hostile CDC patterns across 13 isolated source tables and 18 AUTO CDC configurations: duplicates, late events, tied sequence values, conflicting clocks, sparse NULL updates, deletes, replays, sync-noise updates, and bitemporal corrections.
All 18 configurations completed. Five green configurations failed my ship check: three had complete ordering but violated the stated business rule, and two used incomplete ordering.
Start with scenario 4. Ordering the same rows by ingestion time kept ACTIVE; source event time produced SUSPENDED, the expected business state. Both configurations finished green.
I used pipeline status to confirm execution. I used target-state assertions to decide whether I would ship.
Independent experiment. I ran this test for my own engineering work. It is not official Databricks guidance. I’m happy to discuss your results, feedback, and the failure modes you think I missed.
Run the experiment · Inspect the result matrix
Results in one minute
- 10 handled: keep the sequence rule and test it against your source.
- 3 configuration-dependent: set the option that matches the business rule.
- 3 business-semantics stops: change the chosen clock, NULL meaning, or history policy.
- 2 ambiguous-order stops: add a source-side tie-breaker.
Execution stayed green across all five stop signs. Source semantics or ordering failed my ship check.
The five stop signs
Configuration Measured result Fix before production
| 3A Sequence collision | Two business states shared one sequence value. The contract names no winner. | Reject ties or add a stable source-side tie-breaker. |
| 3B Tie-breaker ignored | The source supplied transaction_sequence, but the flow left it out of SEQUENCE BY. | Use a composite sequence. |
| 4A Ingestion-time order | The target kept ACTIVE; source time required SUSPENDED. | Use the clock that defines business recency. |
| 5A Default NULL handling | A sparse update replaced the existing email with NULL. | Define NULL semantics and use IGNORE NULL UPDATES when NULL means absent. |
| 8A Track every column | Fifty sync-timestamp updates created 51 SCD2 rows. | Exclude operational metadata from history tracking. |
The other 13 configurations matched the experiment’s business rule under a complete order and the required option.
How I tested it
The generator creates small, isolated source tables with one failure mode per scenario. One pipeline wraps each source in a streaming view and runs all 18 AUTO CDC configurations.
For late events and replays, I ran two updates. The full refresh established a baseline. The incremental update appended the withheld rows. The verifier required the two expected histories to change, the other 16 targets to remain equal, and every target to match its row-count and observed-state predicate. The final classification combined those measurements with the declared ordering and business-rule labels.
You can inspect the code in src/pipeline/pipeline.py and src/generators/dispatch.py. The result matrix and captured target rows carry the measured evidence. Official Databricks documentation used for the platform claims is indexed in docs/sources.md.
Four source decisions that change the answer
1. Pick the clock that means “newer”
The generator emits two events for the same key:
10:00 source time ACTIVE 10:05 source time SUSPENDED
Their ingestion timestamps reverse that order. Ordering by ingested_at keeps ACTIVE. Ordering by source_updated_at produces SUSPENDED, which matches the experiment’s business rule.
Two AUTO CDC targets use the same rows and schema. The sequence clock changes the answer.
Arrival time helps you analyze transport; source time can define business recency. Pick the column from the business definition before you write the flow.
2. Give ties a real winner
The collision pattern gives two states the same source_sequence:
seq=10 status=ACTIVE seq=10 status=SUSPENDED
I observed SUSPENDED, but the configured order cannot distinguish the rows, so I recorded the result as AMBIGUOUS_ORDER.
The source also provides transaction_sequence. A composite sequence such as STRUCT(source_updated_at, transaction_sequence) gives the flow a stable order. The composite configuration produced the expected SUSPENDED state, and the verifier classified it as HANDLED.
3. Decide what NULL means
The sparse-update pattern begins with email='x@example.com'. The next event updates the city and carries email=NULL.
The default configuration sets the target email to NULL. IGNORE NULL UPDATES keeps the existing email. Both behaviors can serve a valid source contract. Your producer must define whether NULL means “erase this value” or “this field was absent.”
4. Keep sync noise out of business history
The history-noise generator emits 50 updates. Among the retained target columns, only last_synced_at changes.
Tracking every included target column creates 51 SCD2 rows. Excluding last_synced_at creates one row because the business fields never change.
Operational sync metadata accounts for all 50 extra versions.
Choose the columns that represent business history before you deploy SCD2. Whether operational changes belong there is a domain and audit decision; this experiment’s business rule counted only business-field changes.
Patterns AUTO CDC handled
Pattern Measured result
| Identical duplicate and late replay | SCD1 kept one ACTIVE row. The replay left visible state unchanged. |
| Out-of-order update | SCD1 kept the newer state. SCD2 inserted the older event as a closed history row. |
| Delete followed by an older event | SCD1 kept the deletion. SCD2 inserted the late state before the delete boundary. |
| Full lifecycle replay | SCD1 and SCD2 matched their saved baselines after the replay. |
| Bitemporal correction | The target preserved business time and system time across five measured rows. |
These are visible-state observations from appended replays. They do not establish transactional deduplication or test a checkpoint-only restart with no new source rows.
Bitemporal kept both clocks
Scenario 9 uses source_updated_at for business time and ingested_at for system time. The target stores __START_AT / __END_AT beside __SYSTEM_START_AT / __SYSTEM_END_AT.
Three events produced five rows because later ingestion times revised earlier valid-time intervals.
Five rows preserve the original and revised valid-time intervals.
Databricks marks bitemporal storage as Beta. This run validates five tiny rows and does not test load. I would evaluate it when valid time differs from ingest time and consumers need as-of-system-time queries.
The full result map
All 18 measured configurations grouped by production outcome.
You can inspect the machine-readable matrix in results/normalized/summary_matrix.json and the captured rows in results/raw/target_state.json.
My production checklist
- Name the business clock. Put the column that defines recency in SEQUENCE BY.
- Search for ties per key. Add a source-side tie-breaker when two states can share one sequence value.
- Write down NULL semantics. Use IGNORE NULL UPDATES only when NULL means absent.
- Choose the state model. SCD1 keeps current state; SCD2 keeps ordered history.
- Filter history noise. Exclude sync metadata unless it represents a business event.
Add a target-state predicate for each rule: update status checks execution, while the predicate checks the expected target state.
Reproduce the run
git clone https://github.com/ivanvyd/lakeflow-auto-cdc-torture-test.git cd lakeflow-auto-cdc-torture-test python -m pip install -e ".[dev]" databricks auth login --host https://<workspace-url> --profile DEFAULT make setup make test make results
You need Python 3.10 or newer, GNU Make, jq, the Databricks CLI, and access to a Databricks workspace. The reproduction guide covers the one-time setup and evidence capture.
Sixteen targets matched their baselines after update 2. The two SCD2 targets that received late history changed as expected. The verifier checked all 18 targets and produced 10 HANDLED, 3 CONFIGURATION_DEPENDENT, 3 BUSINESS_SEMANTICS, and 2 AMBIGUOUS_ORDER results.
The checked-in result set was captured on 2026-09-01 from clean commit 01d53b4; target_state.json records the pipeline and both update IDs.
Scope
This suite uses tiny controlled datasets. I did not test throughput, backpressure, or large joins. Each flow read one isolated source table; I did not test joins or interactions across streams. The delete-and-late-event case stayed inside the configured 48-hour tombstone-retention window. Bitemporal load behavior also sits outside this run.
The evidence comes from one workspace, one SQL warehouse, one customer key, serverless Advanced-edition compute on the CURRENT channel, and tiny deterministic inputs. Use separate tests for throughput, schema evolution, and multi-stream joins.
Bring your failure mode
Clone the repository and replace one generator with an event sequence from your source. Add the expected target state, run both phases, and compare the capture.
If the suite misses your case, open an issue with the source rows, SEQUENCE BY expression, storage type, expected target, and observed target. I’m happy to turn a clear failure report into another reproducible scenario.
Measured Lakeflow AUTO CDC torture tests with reproducible Databricks evidence - ivanvyd/lakeflow-auto-cdc-torture-test
Announcements
