Day 15 of 45 · Pro lesson

Slowly Changing Dimensions (SCD Type 0, 1, & 2)

The curriculum for this day stays visible. The lesson, code, and workspace unlock with Pro.

Teaches: SCD Type 0 (Freeze), SCD Type 1 (Replace), SCD Type 2 (Version), valid_from & valid_to, is_current flag, point-in-time joins

Build: Multi-batch SCD Type 2 dimension engine with surrogate keys, half-open intervals, and time-travel SQL.

•Complete 15 drills on SCD strategy selection, half-open intervals, time-travel queries, and row hashing.
•Execute SCD2 customer pipeline with automated temporal invariant testing.
•Simulate 5 failure modes: duplicate active versions, inclusive boundary overlap, and late-arriving updates.
•Defend 10 senior interview questions and complete the SCD recall test.

Mental model: Freeze (0), Replace (1), Version (2). Use half-open intervals [start, end) for point-in-time fact joins.

This section walks through the idea with a short example, then the trade-offs you should mention in an interview.

In practice you start from the raw rows, apply the transform step by step, and check the shape of the result before you move on.

A common mistake is to jump straight to the final query without naming the grain or the join keys that keep the result correct.

Once the core path works, you harden it for nulls, duplicates, and late data so the pipeline stays reliable under load.

The Pro write-up covers the full explanation, worked examples, and the code you can run in the studio.

# Locked example
result = transform(frame)
print(result.head())

Already on Pro? Go to your account. Need a pass? See pricing.