Auditing Data Lineage in SCD Environments

Implement metadata tracking to capture the source system and timestamp for every versioned record in the dimension table.

In this lesson, you will learn how to embed metadata directly into your Slowly Changing Dimension (SCD) tables to ensure complete transparency of your data’s origin and lifecycle.

When you manage historical versions of data (like in Type 2 SCDs), it is not enough to know what changed; you must also be able to prove where that change originated and when your ETL pipeline processed it. Without this, debugging data quality issues becomes a guessing game.

Adding Audit Columns

To track data lineage, we append two specific columns to every record in our dimension table: source_system_id and record_load_timestamp.

source_system_id: Identifies the upstream application or database that provided the data (e.g., CRM_PROD_01, WEB_LOG_SYNC).
record_load_timestamp: Captures the exact moment the ETL process inserted or updated that specific version of the record.

Consider this dim_customer table structure:

CREATE TABLE dim_customer (
    customer_key SERIAL PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(255),
    -- SCD Type 2 tracking
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    is_current BOOLEAN,
    -- Audit Metadata for lineage
    source_system_id VARCHAR(50),
    record_load_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Why This Matters

When an analyst notices a customer’s address changed incorrectly, they can query the dim_customer table to see the lineage. If the source_system_id points to an legacy database, the engineer knows exactly which upstream job to investigate. Furthermore, the record_load_timestamp allows you to reconstruct the state of your data warehouse as it existed at any point in the past, even if the business logic behind the ETL has changed since then.

By treating metadata as a first-class citizen alongside your business attributes, you transform your dimension tables from simple lookups into verifiable audit logs. This practice ensures that every row in your star schema carries its own history, making your pipeline robust and fully auditable.

Key Takeaway: Embedding source_system_id and record_load_timestamp directly into your SCD records creates an immutable audit trail that bridges the gap between raw upstream data and analytical outputs.