Skip to content

DATABRICKS 2026-08-11

Read original ↗

Taking AUTO CDC to the next level: Solving the hardest real-world use cases

Summary

A Databricks follow-up to Stop hand-coding change data capture pipelines that extends AUTO CDC in Apache Spark Declarative Pipelines (SDP) beyond standard SCD Type 1 / Type 2 / snapshot CDC to three hard real-world cases: (1) bitemporal history tracking with independent business-time and system-time axes, enabling point-in-time reconstruction along either clock; (2) partial updates, where NULLs in an incoming change mean "leave this column unchanged" rather than overwrite; and (3) reproducible ML whose as-of contract survives VACUUM because history is stored as data rows, not as reclaimable Delta file versions. The post also announces that the AUTO CDC Type 1 Python API is being contributed to open-source Apache Spark 4.2, built on Spark's streaming/table abstractions so it runs on both Delta Lake and Apache Iceberg.

Key takeaways

  • Standard SCD Type 2 tracks one timeline; bitemporal tracks two, independently. Business time (a.k.a. event / valid time) = when a fact was true in the real world; system time (a.k.a. transaction / processing time) = when the system of record learned about it. A Monday change may not land in the pipeline until Wednesday (Source: sources/2026-08-11-databricks-taking-auto-cdc-to-the-next-level-solving-the-hardest-real-world-use-cases).
  • Four system-managed columns implement the two axes: __START_AT / __END_AT for business time, __SYSTEM_START_AT / __SYSTEM_END_AT for system time. A single logical fact can occupy several physical rows — one per business-version × system-version combination — which is what makes point-in-time reconstruction along either axis possible.
  • Exact SQL clause is STORED AS BITEMPORAL (not STORED AS SCD TYPE BITEMPORAL) and it requires both SEQUENCE BY and SYSTEM SEQUENCE BY. Sequencing columns must be sortable types with no NULLs. Currently Beta — pin the pipeline to channel: PREVIEW; runs on serverless SDP or Pro/Advanced product editions.
  • Out-of-order corrections rewrite history, not just append. When a correction arrives with an earlier business or system time than something already processed, the engine rewrites the affected intervals rather than tacking a row onto the end. Worked example: Acme's reportable flag truly changes Jan 1 (business), feed receives it Jan 5 (system), a back-dated correction lands Jan 8 — a query "as of Jan 3" correctly returns nothing (the auditable answer for what the system knew then), while a query run today reflects the corrected truth. "Two clocks, two answers, both right."
  • The regulatory driver is recordkeeping, not storage. Under SEC Rule 17a-4 and FINRA rules, firms must reconstruct records as they existed at a point in time; the SEC's recordkeeping sweep alone has drawn >$2B in fines across 100+ firms since 2021. The hard part is answering months later "what did the reference data say on the reporting date, and what did our systems believe at the time" — demonstrated against FINRA CAT reference data.
  • Delta Lake time travel is NOT a permanent audit record. TIMESTAMP AS OF resolves against the table's file history; VACUUM permanently deletes data files no longer referenced by recent versions, so once past the default 7-day retention window an as-of timestamp logged at training time can quietly stop resolving. A bitemporal table stores that history as data; VACUUM and OPTIMIZE compact files but never touch the logical history, so every past business/system version remains a queryable row. See reproducibility-survives-vacuum.
  • Reproducible-ML contract = a couple of timestamps in the MLflow run. Either log two as-of instants (business + system) as MLflow params and pin the training query to that belief-state, or (if the table exposes a current view) log a single system instant and reconstruct later via a system-time query. Because history is rows, the contract holds even after VACUUM.
  • Partial Updates are now GA. Many CDC sources emit only changed fields, representing the rest as NULL; naively these NULLs overwrite existing data. With Partial Updates, NULL means "do not update" for selected columns. Target (1,'A',20) + update (1,NULL,30) → (1,'A',30) instead of (1,NULL,30). See concepts/change-data-capture.
  • Three ways to declare partial-update columns: IGNORE NULL UPDATES ON columnList; IGNORE NULL UPDATES ON * EXCEPT (columnList); or a per-row source column via COLUMNS TO UPDATE.
  • AUTO CDC Type 1 Python API is going open source in Apache Spark 4.2 as reviewed SPIP + PRs (see the SPIP and SPARK-56249), not a code drop. Correctness with out-of-order data is built in: a small auxiliary state table tracks early-arriving events (e.g. delete tombstones), retried microbatches converge rather than corrupt the target, and because it builds on Spark's streaming / table abstractions rather than a storage format, it runs on both Delta Lake and Apache Iceberg.
  • Roadmap (in the open): the SQL interface (CREATE FLOW ... AS AUTO CDC INTO) is already merged to master for the next Spark release; SCD Type 2 full-history management, native changelog inputs, and partial-update support are under development; plus apply-as- truncate capabilities and expanded test suites for out-of-order data and idempotent retries.

Operational numbers

Figure Value Note
SEC recordkeeping fines >$2B across 100+ firms since 2021 Regulatory motivation for bitemporal audit
Delta VACUUM default retention 7 days Window after which TIMESTAMP AS OF may stop resolving
Bitemporal system columns 4 __START_AT, __END_AT, __SYSTEM_START_AT, __SYSTEM_END_AT
Target open-source release Apache Spark 4.2 AUTO CDC Type 1 Python API

Caveats

  • Bitemporal is Beta (requires channel: PREVIEW); limited to serverless SDP or Pro/Advanced editions.
  • No absolute throughput / latency / storage-amplification numbers are disclosed for the multi-physical-row bitemporal layout — a single logical fact fanning into business×system versions clearly amplifies row count, but the magnitude at scale is not quantified.
  • Open-source contribution is staged: at time of writing, Type 1 Python API is landing in 4.2; SCD Type 2 full history, native changelog inputs, and partial updates are still "under development" upstream.

Source

Last updated · 766 distilled / 2,225 read