1. Introduction

In my earlier article, I explained why we combine Slowly Changing Dimension Type 2 (SCD2) with Point-in-Time (PIT) tables instead of relying only on snapshots.

This piece is the technical deep dive: we’ll cover storage vs compute trade-offs, a metadata-driven approach, and the mechanics of implementation with Databricks code.

2. The Storage vs Compute Trade-Off

It’s tempting to argue that snapshots aren’t expensive to use. After all:

Sources often deliver snapshots (daily or monthly).
Many reporting consumers also want snapshot-shaped datasets (month-end, reporting date).

But snapshots hide two major costs:

Storage bloat — every delivery duplicates unchanged rows.
Historical ambiguity — if you want to know “When exactly did this change occur?” you must reconstruct timelines by diffing snapshots.

Snapshots

Load: trivial → INSERT everything.
Storage: high → duplicates explode with every delivery.
Query: inefficient for historical questions (timeline reconstruction required).

SCD2

Load: heavier → requires diff detection.
Storage: low → only changes are stored.
Query: efficient → validity intervals (valid_from, valid_to) are explicit.

Comparison

Approach	Load Cost	Query Cost	Storage Cost	Functional History
Snapshots	Low	High	High	Implicit (must reconstruct)
SCD2	High	Low	Low	Explicit (directly stored)

3. Why Not Pick Just One?

Snapshots are convenient for delivery — they match how sources send data and how many consumers expect to receive it.
But they are inefficient for integration. Duplicates accumulate, and reconstructing history is painful.
SCD2 is efficient for integration. It captures functional history directly and aligns with the business Logical Data Model (LDM):
- Core entities (party, account, product) stay stable.
- Characteristic and associative entities (address links, marital status, product attributes) explicitly carry validity periods.
But SCD2 alone can be costly for fast-changing data (e.g., balances, exchange rates) where every row changes.

Solution: Dual approach

Store all historized tables in a dual SCD2 structure.
Use metadata to decide:
- SCD2 mode for slow-changing data.
- Snapshot mode (valid_from = valid_to) for fast-changing data.
Expose data to consumers via views and PIT tables, which can materialize snapshots, historized tables, or dimensional models as needed.

This separation of concerns ensures:

Integration layer → compact, historized, aligned with the LDM.
Delivery layer → consumer-specific models (snapshot, historized, dimensional).

4. Dual SCD2 Physical Structure

Every historized table follows the same schema:

surrogate_key (system-generated).
business_key (natural identifier).
valid_from, valid_to.
is_current.
True SCD2 → history maintained via open/closed intervals.
Snapshot-mode → records close immediately (valid_to = valid_from).

5. Metadata-Driven Historisation

Rule engine

If table flagged SCD2 → run diff logic.
If table flagged Snapshot → insert directly.

Benefits

Unified structure across all tables.
Governance via metadata.
Flexibility to change historisation strategy per table.

6. Handling Redeliveries

When a source re-sends data for the same reporting date:

Restrict comparison to that valid_from partition.
Compare against existing rows.
Logically delete/replace if differences exist.

Keeps compute localized and efficient.

7. Point-in-Time (PIT) Tables

Purpose

Provide snapshot-shaped views without recomputing history.

Mechanics

Map each business_key to the correct historized row for a given date.
Implemented as a join index or bridge table.

Benefits

Fast snapshot reconstruction.
Consistent handling of redeliveries.
High query performance for BI, APIs, and data science.

8. Partitioning Strategy

Partition historized tables by valid_from:

Limits compute during redelivery checks.
Improves pruning for PIT queries.
Enables ETL parallelism.

9. Cost Analysis

Snapshots → linear growth, timeline reconstruction overhead.
SCD2 → proportional to changes, explicit history, diff detection overhead.
Balanced →
- Apply SCD2 where data is mostly stable.
- Apply snapshot-mode where data always changes.
- Use PIT to make snapshots fast for consumers.

10. Conclusion

The dual SCD2 + PIT pattern delivers a pragmatic balance:

Cost efficiency (storage + compute).
Performance (fast queries, efficient ETL).
Flexibility (multiple delivery models).

By aligning the integration layer with the business LDM (functional history explicit) and separating it from the delivery layer (snapshots, dimensional models), we get both clarity and efficiency. PIT tables bridge the gap by turning historized data into fast, snapshot-ready views.

Addendum: Databricks Implementation

1. SCD2 and Snapshot Loading Logic

from pyspark.sql import functions as F
from delta.tables import DeltaTable

# Paths
SOURCE_PATH = "/mnt/source/delivery_table"
TARGET_PATH = "/mnt/historized/table"

# Incoming snapshot from source
incoming = (
    spark.read.format("delta")
    .load(SOURCE_PATH)
    .withColumn("valid_from", F.current_date())
)

# Read existing historized table
if DeltaTable.isDeltaTable(spark, TARGET_PATH):
    history = DeltaTable.forPath(spark, TARGET_PATH).toDF()
else:
    history = spark.createDataFrame([], incoming.schema)  # empty if first load

# Metadata-driven table mode
table_mode = "SCD2"  # could also be "SNAPSHOT"

SCD2 Mode

if table_mode == "SCD2":
    join_cond = [
        incoming.business_key == history.business_key,
        history.is_current == True
    ]
    comparison = incoming.join(history, join_cond, "left")

    changes = comparison.filter(
        (history.business_key.isNull()) |
        (F.md5(F.concat_ws("||", *incoming.columns)) != history.hash_value)
    )

    updates = (
        history.join(changes, "business_key")
        .withColumn("valid_to", F.current_date())
        .withColumn("is_current", F.lit(False))
    )

    new_records = (
        changes
        .withColumn("surrogate_key", F.monotonically_increasing_id())
        .withColumn("valid_to", F.lit("9999-12-31"))
        .withColumn("is_current", F.lit(True))
    )

    final = history.union(updates).union(new_records)

Snapshot Mode

elif table_mode == "SNAPSHOT":
    new_records = (
        incoming
        .withColumn("surrogate_key", F.monotonically_increasing_id())
        .withColumn("valid_to", F.col("valid_from"))
        .withColumn("is_current", F.lit(False))
    )
    final = history.union(new_records)

Save Result

(final
 .write
 .format("delta")
 .mode("overwrite")
 .option("overwriteSchema", "true")
 .save(TARGET_PATH))

2. Building PIT Tables

PIT_PATH  = "/mnt/pit/table"
as_of_date = "2025-05-01"

scd2 = spark.read.format("delta").load(TARGET_PATH)

resolved = (
    scd2
    .filter((F.col("valid_from") <= as_of_date) & (as_of_date <= F.col("valid_to")))
    .select(
        F.lit(as_of_date).alias("as_of_date"),
        "business_key",
        "surrogate_key"
    )
)

if DeltaTable.isDeltaTable(spark, PIT_PATH):
    pit = DeltaTable.forPath(spark, PIT_PATH)
    (
        pit.alias("t")
        .merge(
            resolved.alias("s"),
            "t.as_of_date = s.as_of_date AND t.business_key = s.business_key"
        )
        .whenMatchedUpdate(set={"surrogate_key": "s.surrogate_key"})
        .whenNotMatchedInsert(values={
            "as_of_date": "s.as_of_date",
            "business_key": "s.business_key",
            "surrogate_key": "s.surrogate_key"
        })
        .execute()
    )
else:
    (
        resolved
        .repartition("as_of_date")
        .write.format("delta")
        .partitionBy("as_of_date")
        .mode("overwrite")
        .save(PIT_PATH)
    )

3. Using PIT to Materialize a Snapshot

pit = spark.read.format("delta").load(PIT_PATH).filter(F.col("as_of_date") == as_of_date)
scd2 = spark.read.format("delta").load(TARGET_PATH)

snapshot = (
    pit.alias("p")
    .join(scd2.alias("h"), F.col("p.surrogate_key") == F.col("h.surrogate_key"))
    .select("p.as_of_date", "h.*")
)

snapshot.display()

4. Performance Best Practices

Partitioning:
- SCD2 → by valid_from.
- PIT → by as_of_date.
Indexing/Z-ORDER: on business_key and surrogate_key.
Compaction: OPTIMIZE + VACUUM to keep Delta tables efficient.
Validation checks: ensure PIT rows match SCD2 referential integrity.

Technical Deep-Dive: Dual SCD2, Snapshots, and PIT Tables

1. Introduction

2. The Storage vs Compute Trade-Off

Snapshots

SCD2

3. Why Not Pick Just One?

4. Dual SCD2 Physical Structure

5. Metadata-Driven Historisation

Rule engine

Benefits

6. Handling Redeliveries

7. Point-in-Time (PIT) Tables

Purpose

Mechanics

Benefits

8. Partitioning Strategy

9. Cost Analysis

10. Conclusion

Addendum: Databricks Implementation

1. SCD2 and Snapshot Loading Logic

SCD2 Mode

Snapshot Mode

Save Result

2. Building PIT Tables

3. Using PIT to Materialize a Snapshot

4. Performance Best Practices