Skip to main content

Change Data Capture (CDC)

Change Data Capture is the process of identifying and capturing changes made to data in a source database and then delivering those changes in real-time or near real-time to a downstream system, like a data warehouse or, in this case, your Data Vault staging area. Instead of extracting a full copy of a data source every time, CDC provides only the data that has changed (inserts, updates, deletes) since the last extraction.

Why is CDC important for S2V and Data Vault?

For sources where changes (updates, deletes) need to be tracked historically in your Data Vault (especially in Satellites), a clear and reliable CDC mechanism in your source system or ETL pipeline is essential. S2V relies on the information provided by your CDC process, typically in the form of specific flags in the staged data, to correctly apply these changes to the Data Vault structures. Without effective CDC, you might only capture new records or miss critical updates and deletions, leading to an incomplete or inaccurate historical view.

Key Benefits of Using CDC:

  • Reduced Data Processing: Instead of processing the entire dataset from a source system during each load cycle, CDC allows you to process only the records that have changed. This significantly reduces the volume of data transferred, staged, and processed, leading to faster ETL jobs and lower computational costs.
  • Improved Efficiency and Performance: By focusing on deltas, CDC minimizes the load on both source systems (during extraction) and target systems (like your data warehouse during loading).
  • Near Real-Time Data Integration: CDC enables more frequent updates to your Data Vault, providing fresher data for analytics and reporting.
  • Cost Savings: In cloud data warehouse environments like Snowflake, processing less data translates directly to lower compute costs.
  • Synergy with Modern Data Platform Features: CDC is highly complementary to features like Snowflake's Dynamic Tables. Dynamic Tables can automatically and incrementally refresh based on changes in underlying source tables. When these source tables are populated via a CDC process (e.g., into a staging layer), the Dynamic Tables can efficiently propagate only the new changes into the Data Vault or downstream marts, further optimizing the data pipeline.

By implementing a robust CDC strategy, you ensure that your Data Vault remains an accurate, timely, and cost-effective representation of your business operations.

warning

Critical Requirement for CDC Integrity:

  • Deterministic Ordering: Maintaining the exact sequence of operations as they occurred in the source system is not just important, it's an must for reliable CDC and subsequent Data Vault loading. If multiple changes occur for the same record within the same load window (e.g., an insert followed by an update), processing them out of order will lead to data corruption and an incorrect historical view.

  • How to Achieve Determinism:

    • Source-Provided Load Timestamp: The ideal scenario is when the source system's CDC mechanism inherently provides a guaranteed sequence via a consistently increasing timestamp with sufficient granularity.
    • Load Timestamp & Sequence Column: A common and robust pattern is to use the load timestamp in conjunction with an additional sequence number (e.g., a transaction log sequence number or a dedicated sequence column). This combination ensures that records are processed in the precise order they were captured, which is fundamental for maintaining data integrity in CDC patterns.