SCD2 Is Not Enough (Part 1) — The Bi-Temporal Foundation — From Snapshots to Deterministic…

Engineering Time in Data Platforms — A 3-Part Series

SCD2 Is Not Enough (Part 1) — The Bi-Temporal Foundation

From Snapshots to Deterministic Timelines: Engineering Time in Data Platforms

Engineering Time in Data Platforms — A 3-Part Series

Most data platforms believe they are historized. They are not. They store history — but they do not engineer time.

In this three-part MDDE deep dive, we will move from:

Basic SCD2 storage to bi-temporal standardization (this article)
Timeline compression and deterministic integration (Part 2)
Valuation reconstruction with impact-driven refresh (Part 3)

This first article lays the foundation: how to standardize heterogeneous snapshot sources into deterministic bi-temporal timelines.

If you skip this foundation, everything else collapses.

The Illusion of “Historized”

Modern data platforms are full of:

Daily feeds
Monthly extracts
Full snapshots
Delta deliveries
Filtered snapshots (where inactive records disappear)

Many of them claim to be SCD2. Most are not truly bi-temporal.

And when you attempt to integrate them, the cracks appear:

Snapshots overwrite history implicitly
Missing records silently delete business state
Delta feeds require reconstruction
Different cadences fragment timelines

Traditional SCD2 is not enough.

The MDDE Principle

In MDDE:

Everything in the platform is modeled as bi-temporal SCD2.

Not because it is fashionable. Because it is the only way to guarantee:

Deterministic integration
Late-arrival tolerance
Auditability
Controlled recalculation

1. What Bi-Temporal Actually Means

Every record has two independent timelines.

Business Timeline (State)

This answers: What was true at business time T?

System Timeline (Assertion)

This answers: What did we know at system time T?

The Four Query Types

This separation enables four distinct temporal queries:

Without bi-temporality, corrections become destructive. With it, they become manageable.

MDDE Implementation

MDDE’s DualSCD2Analyzer automatically detects temporal columns by pattern matching:

from mdde.analyzer.dual_scd2_analyzer import DualSCD2Analyzer, TemporalType

analyzer = DualSCD2Analyzer(conn)  
# Detect temporal columns  
columns = analyzer.detect_columns('ENT_customer')  
print(columns)  
# DualSCD2ColumnSet(  
#     valid_from='effective_date',  
#     valid_to='expiry_date',  
#     is_current='current_flag',  
#     load_dts='dw_insert_dts',  
#     replaced_at='dw_update_dts',  
#     is_latest='is_latest_version'  
# )  
# Classify temporality  
temporal_type = analyzer.classify_temporality('ENT_customer')  
# TemporalType.DUAL_SCD2 (confidence: 0.95)

Business Timeline Patterns Detected :

Start: valid_from, effective_from, start_date, effective_date
End: valid_to, effective_to, end_date, expiry_date
Current: is_current, current_flag, is_active

System Timeline Patterns Detected :

Start: load_dts, inscription_ts, dw_insert_dts, created_at
End: replaced_at, superseded_at, dw_update_dts
Latest: is_latest, latest_flag, is_current_record

2. Why Snapshots Are Architecturally Dangerous

Snapshot feeds:

Carry no end dates
Implicitly overwrite previous state
Often remove inactive records
Rarely provide deletion indicators

If loaded directly, they:

Break continuity
Destroy auditability
Corrupt integration

Snapshots must be converted — not trusted.

The Implicit Deletion Problem

Consider a customer snapshot from source system:

Day 1 Snapshot:

Day 2 Snapshot:

What happened to C003? Was it:

Deleted?
Inactive (filtered out)?
A source system error?

You don’t know. And that uncertainty corrupts everything downstream.

3. Converting Snapshots into Controlled SCD2

Pattern A — Daily Full Snapshots (Micro-Timelines)

For high-frequency data like:

Currency rates
Daily balances
Market prices
Position snapshots

We enforce micro-timelines (ADR-274):

valid_from = snapshot_date  
valid_to = snapshot_date + INTERVAL '1 day'

Why?

Because daily data should not create open-ended validity. Each snapshot represents exactly one day’s truth.

MDDE Implementation:

 from mdde.generator.building_blocks.temporal_blocks import (  
    TemporalBuildingBlocks,  
    TemporalConfig,  
    TemporalDialect,  
)

config = TemporalConfig(  
    dialect=TemporalDialect.DATABRICKS,  
    valid_from_col="snapshot_date",  
    valid_to_col="valid_to",  
)  
blocks = TemporalBuildingBlocks(config)

Generated SQL Pattern:

 -- Micro-timeline for daily snapshots (ADR-274)  
WITH daily_timeline AS (  
    SELECT  
        *,  
        snapshot_date AS valid_from,  
        DATE_ADD(snapshot_date, 1) AS valid_to,  
        TRUE AS is_current,  -- Each day is "current" for that day  
        CURRENT_TIMESTAMP() AS load_dts  
    FROM source_daily_rates  
)

Benefits:

Partition pruning on valid_from (performance)
Predictable compression (no open-ended rows)
Efficient hash comparison
No temporal gaps

We engineer the timeline instead of inheriting chaos.

Pattern B — Monthly Snapshots (Hash-Based SCD2)

Monthly feeds require change detection:

Hash relevant attributes
Compare with active row
Close changed record
Insert new row

MDDE Implementation:

 from mdde.generator.scd2_merge_sql import (  
    SCD2MergeSqlGenerator,  
    SCD2MergeConfig,  
    ChangeDetectionMethod,  
)

config = SCD2MergeConfig(  
    dialect="databricks",  
    change_detection_method=ChangeDetectionMethod.HASH,  
    hash_columns=["name", "segment", "credit_limit", "address_id"],  
    business_key_columns=["customer_id"],  
    valid_from_col="valid_from",  
    valid_to_col="valid_to",  
    is_current_col="is_current",  
)  
generator = SCD2MergeSqlGenerator(conn)  
merge_sql = generator.generate_scd2_merge(config)

Generated SQL:

 -- SCD2 MERGE with hash-based change detection (ADR-113)  
WITH source_with_hash AS (  
    SELECT  
        customer_id,  
        name,  
        segment,  
        credit_limit,  
        address_id,  
        MD5(CONCAT_WS('|',  
            COALESCE(CAST(name AS STRING), '^^NULL^^'),  
            COALESCE(CAST(segment AS STRING), '^^NULL^^'),  
            COALESCE(CAST(credit_limit AS STRING), '^^NULL^^'),  
            COALESCE(CAST(address_id AS STRING), '^^NULL^^')  
        )) AS row_hash,  
        snapshot_date AS valid_from  
    FROM monthly_snapshot  
),

target_current AS (  
    SELECT *  
    FROM dim_customer  
    WHERE is_current = TRUE  
),  
changes AS (  
    SELECT  
        s.*,  
        CASE  
            WHEN t.customer_id IS NULL THEN 'INSERT'  
            WHEN s.row_hash != t.row_hash THEN 'UPDATE'  
            ELSE 'NO_CHANGE'  
        END AS change_type  
    FROM source_with_hash s  
    LEFT JOIN target_current t  
        ON s.customer_id = t.customer_id  
)  
-- Step 1: Close changed records  
MERGE INTO dim_customer t  
USING (SELECT * FROM changes WHERE change_type = 'UPDATE') s  
ON t.customer_id = s.customer_id AND t.is_current = TRUE  
WHEN MATCHED THEN UPDATE SET  
    valid_to = s.valid_from,  
    is_current = FALSE;  
-- Step 2: Insert new and changed records  
INSERT INTO dim_customer  
SELECT  
    customer_id,  
    name,  
    segment,  
    credit_limit,  
    address_id,  
    row_hash,  
    valid_from,  
    CAST('9999-12-31' AS DATE) AS valid_to,  
    TRUE AS is_current,  
    CURRENT_TIMESTAMP() AS load_dts  
FROM changes  
WHERE change_type IN ('INSERT', 'UPDATE');

Hash-based detection ensures:

Idempotency (same input = same output)
Deterministic change logic
Clean SCD2 continuity
Performance at scale

Pattern C — Filtered Snapshots (Explicit Deletion)

If a key exists in target but not in snapshot:

-- Detect implicit deletions  
WITH missing_keys AS (  
    SELECT t.customer_id  
    FROM dim_customer t  
    WHERE t.is_current = TRUE  
      AND NOT EXISTS (  
          SELECT 1 FROM source_snapshot s  
          WHERE s.customer_id = t.customer_id  
      )  
)

-- Close deleted records explicitly  
UPDATE dim_customer  
SET  
    valid_to = CURRENT_DATE,  
    is_current = FALSE,  
    deleted_at = CURRENT_TIMESTAMP(),  
    operation = 'DELETE'  
WHERE customer_id IN (SELECT customer_id FROM missing_keys)  
  AND is_current = TRUE;

Deletion must be explicit. Implicit disappearance is architectural corruption.

Pattern D — Delta/CDC Feeds

CDC feeds provide operation types but often arrive out of order:

from mdde.loader.scd2_loader import SCD2DeliveryLoader, DeliveryConfig

config = DeliveryConfig(  
    operation_column="op_type",  
    operation_mapping={  
        "I": "INSERT",  
        "U": "UPDATE",  
        "D": "DELETE",  
    },  
    handle_out_of_order=True,  
    dedup_by_timestamp="event_timestamp",  
)  
loader = SCD2DeliveryLoader(conn, config)  
loader.process_delta_batch("cdc_customer_changes")

Key principles:

Deduplicate by business key + event timestamp
Order by event timestamp before processing
Handle deletes as timeline closures
Preserve full audit trail

4. The Dual SCD2 Stereotype

MDDE provides the dual_scd2 stereotype (ADR-144) for bi-temporal entities:

# Entity definition with dual_scd2 stereotype  
entity:  
  entity_name: dim_customer  
  stereotype: dual_scd2  
  attributes:  
  # Business key  
  - attribute_name: customer_id  
    data_type: STRING  
    is_business_key: true  
  # Business attributes  
  - attribute_name: name  
    data_type: STRING  
  - attribute_name: segment  
    data_type: STRING  
  # Business timeline (required by stereotype)  
  - attribute_name: valid_from  
    data_type: DATE  
  - attribute_name: valid_to  
    data_type: DATE  
  - attribute_name: is_current  
    data_type: BOOLEAN  
  # System timeline (required by stereotype)  
  - attribute_name: load_dts  
    data_type: TIMESTAMP  
  - attribute_name: replaced_at  
    data_type: TIMESTAMP  
  - attribute_name: is_latest  
    data_type: BOOLEAN  
  # Audit columns  
  - attribute_name: deleted_at  
    data_type: TIMESTAMP  
  - attribute_name: operation  
    data_type: STRING

Stereotype validation ensures:

All required temporal columns exist
Column naming follows conventions
Timeline semantics are consistent

5. Temporal Validation Checks

MDDE includes six temporal validation checks (T001-T006):

Running validation:

 from mdde.analyzer.checks.temporal_checks import (  
    BitemporalMissingAssertionCheck,  
    StateTimelineGapsCheck,  
    StateTimelineOverlapsCheck,  
    DualSCD2MissingSystemTimelineCheck,  
)

checks = [  
    BitemporalMissingAssertionCheck(conn),  
    StateTimelineGapsCheck(conn),  
    StateTimelineOverlapsCheck(conn),  
    DualSCD2MissingSystemTimelineCheck(conn),  
]  
for check in checks:  
    results = check.run(model_id)  
    for result in results:  
        print(f"[{result.severity}] {result.object_name}: {result.message}")

Example output:

[ERROR] dim_customer: Dual SCD2 entity missing system timeline columns: replaced_at, is_latest  
[WARNING] dim_product: State timeline may have overlaps for business_key: PRD-001  
[INFO] fact_orders: PIT candidate not configured - consider enabling PIT generation

6. AS-OF Query Generation

Once you have bi-temporal data, you need to query it correctly. MDDE generates all four query types:

from mdde.generator.asof_query_generator import (  
    AsOfQueryGenerator,  
    AsOfQueryConfig,  
    BitemporalQueryType,  
)

config = AsOfQueryConfig(dialect="databricks")  
generator = AsOfQueryGenerator(conn, config)  
# AS-IS: Current knowledge, current state  
as_is_sql = generator.generate_as_is_query("ENT_customer")  
# AS-OF: Current knowledge of state at business time T  
as_of_sql = generator.generate_as_of_query(  
    "ENT_customer",  
    business_time="${business_date}"  
)  
# AS-WAS: What we knew at system time T  
as_was_sql = generator.generate_as_was_query(  
    "ENT_customer",  
    system_time="${system_time}"  
)  
# AS-WAS-AS-OF: What we knew at T1 about state at T2  
full_bitemporal_sql = generator.generate_as_was_as_of_query(  
    "ENT_customer",  
    business_time="${business_date}",  
    system_time="${system_time}"  
)

Generated AS-WAS-AS-OF query:

 -- What we knew at ${system_time} about state at ${business_date}  
SELECT  
    customer_id,  
    name,  
    segment,  
    credit_limit,  
    valid_from,  
    valid_to,  
    load_dts,  
    replaced_at  
FROM dim_customer  
WHERE  
    -- Business timeline filter  
    ${business_date} >= valid_from  
    AND ${business_date} < COALESCE(valid_to, DATE'9999-12-31')  
    -- System timeline filter  
    AND load_dts <= ${system_time}  
    AND (replaced_at IS NULL OR replaced_at > ${system_time})

7. What We Achieved in Part 1

After standardization, all sources become:

Now — and only now — can we integrate safely.

Coming Next

In Part 2, we address the real complexity:

What happens when you join multiple SCD2 timelines?

And how to prevent timeline explosion using:

Change point union patterns
ASOF sampling
Hash-based compression
Deterministic integration

We’ll use MDDE’s Temporal Building Blocks (ADR-270) to compose these patterns cleanly.

Read Part 2 here:Timeline Compression and Deterministic Integration

Key Takeaways

Bi-temporal is not optional — Without both business and system timelines, you cannot audit or correct
Snapshots are dangerous — They must be converted to explicit SCD2 with closure logic
Micro-timelines control chaos — Daily data gets fixed 1-day validity, not open-ended
Hash-based change detection — Provides idempotency and determinism
Implicit deletion is corruption — Always detect and explicitly flag missing records
Stereotype enforcement — Use dual_scd2 to guarantee required columns
Validation is mandatory — Run T001-T006 checks before trusting your timelines

SCD2 Is Not Enough (Part 1) — The Bi-Temporal Foundation — From Snapshots to Deterministic…