From bitemporal compliance to partial record updates: delivering robust, audit-ready change data capture without custom code
by Josh Seidel, Shanelle Roman and Sudhanva Huruli
Change data capture is one of the most common things data engineers build on Spark, and one of the most tedious to get right by hand. In our previous post, Stop hand-coding change data capture pipelines, we introduced how AUTO CDC in Apache™ Spark Declarative Pipelines (SDP) automates SCD Type 1, SCD Type 2, and Snapshot CDC by replacing hundreds of lines of fragile MERGE logic with a few simple declarations.
As pipeline requirements evolve, engineers run into situations that those standard CDC patterns struggle to solve:
Today, we’re taking AUTO CDC to the next level to solve these exact real-world challenges, and expanding these capabilities into open-source Apache Spark 4.2.
Standard SCD Type 2 tables can tell you when a fact changed in the real world, but they cannot tell you what your system believed at any given point in time.
Under SEC Rule 17a-4 and FINRA recordkeeping rules, firms must be able to reconstruct records as they existed at a point in time; the SEC's recordkeeping sweep alone has drawn more than $2 billion in fines across 100+ firms since 2021. The hard part is rarely storing today's value. It's answering, months later, what did the reference data say on the reporting date, and what did our systems believe at the time.
Standard SCD Type 2 tracks one timeline: when a fact changed. Bitemporal AUTO CDC tracks two, independently:
Each target table gets four system-managed columns: __START_AT and __END_AT for business time, __SYSTEM_START_AT and __SYSTEM_END_AT for system time. A single logical fact can have several physical rows, one per business-version/system-version combination, which is what makes point-in-time reconstruction along either axis possible. The key behavioral guarantee: events can arrive in any order on either timeline.
When a correction shows up with an earlier business time or system time than something already processed, the engine rewrites the affected history instead of just appending to the end. No hand-written logic, just declare the two sequencing columns and the engine maintains both intervals. This works equally well for dimension tables, like symbol masters, and for fact tables, like trade history or sensor readings, that need strict auditability. Here's what it looks like against FINRA CAT reference data:
Note the exact SQL clause is STORED AS BITEMPORAL, not STORED AS SCD TYPE BITEMPORAL, and it requires both SEQUENCE BY and SYSTEM SEQUENCE BY. Say Acme's reportable flag changes on January 1 (business time), but the feed doesn't receive it until January 5 (system time). A back-dated correction then arrives on January 8 saying the real change was January 1 but with a different value. Bitemporal AUTO CDC can answer both questions:
On January 3, the first query returns nothing, the correct, auditable answer for what the system showed at the time. The second query, run today, reflects the corrected truth. Two clocks, two answers, both right. Sequencing columns must be sortable types, with no NULL sequencing values. The feature runs on serverless SDP or the Pro/Advanced product editions, and is currently in Beta, so pin the pipeline to channel: PREVIEW.
When a model is trained on reference or feature data, reproducibility means being able to reconstruct the exact dataset the model used, months later, during a review or an audit. The instinct is to reach for Delta Lake time travel, but that's a property of the table's file history, not a permanent record. VACUUM permanently deletes data files no longer referenced by recent versions; once past the default 7-day retention window, a TIMESTAMP AS OF logged at training time can quietly stop resolving. A bitemporal table stores that history as data, not as file versions. VACUUM and OPTIMIZE compact files but never touch the logical history, so every past business or system version is still a queryable row. There are two ways to get reproducibility out of this: Log two as-of instants (business and system time) 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 at training time and reconstruct later with a system-time query at that timestamp:
Either way, the reproducibility contract is a couple of timestamps in the MLflow run, and because bitemporal history is stored as rows, that contract holds even after VACUUM has cleaned up the underlying files.
Not all change data capture (CDC) sources emit complete rows for updates. Instead, many only send the fields that changed, representing all other columns as NULL. Without special handling, these NULL values can unintentionally overwrite existing data in the target table. Until now, customers had to build custom logic to work around this behavior. With AutoCDC Partial Updates, this is now handled automatically.
Partial Updates extend AutoCDC by allowing update events to modify only a subset of columns. For selected columns, NULL values in an incoming update are interpreted as "do not update" rather than overwriting the existing value.
This is particularly useful for CDC sources that omit unchanged values by emitting NULL. Without Partial Updates, these NULLs would overwrite existing data in the target table.
For example, suppose the target table contains: (1, 'A', 20)
An incoming update event contains: (1, NULL, 30)
By default, AutoCDC would update the row to: (1, NULL, 30).
With Partial Updates enabled, the NULL in name is treated as "leave the existing value unchanged," resulting in: (1, 'A', 30).
Enabling Partial Updates only requires adding a parameter to your AutoCDC definition. You can choose from three ways to specify which columns should be treated as partial updates:
IGNORE NULL UPDATES ON columnListIGNORE NULL UPDATES ON * EXCEPT (columnList)COLUMNS TO UPDATEFor complete syntax, examples, and usage guidance, see the Apply Partial Updates documentation.
Spark Declarative Pipelines is open source, so its most widely used flow type should be too. We are starting by contributing the Python API for AUTO CDC Type 1 to Apache Spark 4.2.
We contributed it the way the rest of Spark evolves: as a series of reviewed proposals and pull requests, not a one-time code drop (see the SPIP and SPARK-56249). Correctness with out-of-order data comes built in: a small auxiliary table tracks state from early-arriving events like delete tombstones, retried microbatches converge rather than corrupting the target, and because it builds on Spark's streaming and table abstractions rather than a storage format, it runs on both Delta Lake and Apache Iceberg.
What's next, in the open:
CREATE FLOW ... AS AUTO CDC INTO) into master, which will ship in the next Apache Spark release.NULL values from overwriting target data.Whether you’re looking to implement bitemporal compliance, set up partial updates, or explore open-source AutoCDC in Apache Spark, check out the resources below to get started:
Subscribe to our blog and get the latest posts delivered to your inbox.