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

Change data capture (CDC) pipelines often require hundreds of lines of complex MERGE logic, which AUTO CDC in Apache Spark Declarative Pipelines (SDP) automates for SCD Type 1, Type 2, and Snapshot CDC. The latest update introduces bitemporal support in open-source Apache Spark 4.2, enabling point-in-time reconstruction of data as it existed at any given moment, a requirement under SEC Rule 17a-4 and FINRA recordkeeping rules that have led to over $2 billion in fines since 2021.

Bitemporal AUTO CDC introduces four system-managed columns—__START_AT, __END_AT, __SYSTEM_START_AT, and __SYSTEM_END_AT—to track both business and system time. This allows the engine to handle out-of-order events and rewrite affected history when corrections arrive with earlier timestamps, ensuring accurate audits. The feature works for both dimension and fact tables, such as trade history or sensor readings, and is available in Beta for serverless SDP or Pro/Advanced editions, with pipelines pinned to the PREVIEW channel.

For machine learning reproducibility, bitemporal tables store historical data as rows rather than relying on Delta Lake time travel, which can be disrupted by VACUUM operations. Users can log business and system timestamps as MLflow parameters to reconstruct the exact dataset used for training, even after underlying files are optimized or vacuumed. This ensures compliance with audit requirements without risking data loss.

AutoCDC Partial Updates now handles CDC sources that emit only changed fields as NULL, preventing unintended overwrites of existing data. By interpreting NULL values as 'do not update,' the feature preserves unchanged columns while applying updates to specified fields. This capability is enabled via a parameter in the AutoCDC definition and supports three methods for specifying partial update columns, with full syntax and examples available in the documentation.