Databricks AUTO CDC Adds Bitemporal History for Regulatory Compliance

Databricks shipped three AUTO CDC extensions this week that eliminate bespoke MERGE logic from CDC pipelines: bitemporal history tracking (Beta), partial updates for sparse change feeds (GA), and the AUTO CDC Type 1 Python API contributed to Apache Spark 4.2.

Bitemporal AUTO CDC tracks two independent timestamps. Business time marks when a fact was true in the real world. System time marks when the system of record learned about it. The engine adds four managed columns: __START_AT and __END_AT for business time, __SYSTEM_START_AT and __SYSTEM_END_AT for system time. When a correction arrives with an earlier timestamp, the engine rewrites affected history in place rather than appending. Declaration is declarative: STORED AS BITEMPORAL with SEQUENCE BY and SYSTEM SEQUENCE BY columns — no custom MERGE required.

The regulatory driver is direct. SEC Rule 17a-4 and FINRA recordkeeping rules require firms to reconstruct records as they existed at a specific point in time — both what the data said and what the system believed. The SEC's recordkeeping enforcement sweep has resulted in more than $2 billion in fines across 100+ firms since 2021. Standard SCD Type 2 tables cannot answer both questions simultaneously. A bitemporal table can: a query scoped to January 3 returns what the system showed that day; a query run today returns the corrected truth after a back-dated correction.

The partial updates GA closes a common failure mode. Many CDC sources emit only changed columns as NULL for everything else. Without explicit handling, those NULLs overwrite valid data silently. The fix is declarative: set ignore_null_updates or ignore_null_updates_column_list in create_auto_cdc_flow(), and the engine applies only changed fields.

The open-source contribution ships the AUTO CDC Type 1 Python API into Apache Spark 4.2 via SPARK-56249, reviewed through the standard SPIP process. The implementation handles out-of-order events through an auxiliary state table—delete tombstones and retried microbatches converge rather than corrupt the target. It runs on both Delta Lake and Iceberg. The SQL interface (CREATE FLOW ... AS AUTO CDC INTO) is merged into master for the next Spark release; SCD Type 2 full-history and partial update support are in development.

A Fortune 500 aerospace and defense engineer replaced 1,500 lines of custom CDC code with 4 lines of AUTO CDC declarations. Navy Federal Credit Union runs AUTO CDC in Lakeflow Spark Declarative Pipelines to process billions of application events. The pattern reduction: 6–10 lines of declarative pipeline vs. 40–200+ lines of hand-rolled MERGE logic.

Constraints: Bitemporal AUTO CDC is Beta and requires channel: PREVIEW. It runs only on serverless SDP or Pro/Advanced editions. DML on AUTO CDC target tables requires Unity Catalog and Databricks Runtime 13.3 LTS or above; reading change feeds requires DBR 15.2 or above. Bitemporal history survives VACUUM (which deletes Delta file versions past 7 days), making it a viable alternative to TIMESTAMP AS OF for long-lived training reproducibility.

If your CDC pipelines carry regulatory, audit, or ML reproducibility requirements, evaluate bitemporal now. The operational cost is two extra sequencing columns and a preview channel pin, not a pipeline rewrite.