Day 15Phase 03 · Data Engineering Fundamentals
Tracking history: SCD Type 1 and 2
The curriculum for this day stays visible. The lesson, code, and workspace unlock with a pass.
By the end of today you can
- Distinguish when to overwrite attributes (SCD Type 1) versus track historical versions (SCD Type 2).
- Model valid_from, valid_to, and is_current columns using half-open intervals [start, end).
- Execute point-in-time joins between fact transactions and historical dimension states.
The Problem: When Dimensions Change Over Time
Priya (customer C42) bought something for ₹1,500 on 15 August, while she lived in Bengaluru.
In September she moves to Hyderabad, and ShopKart’s app overwrites her city. Guess which city the August revenue lands in.
This is the first section of the lesson. The rest unlocks with a pass.
Core topics: 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.
Mental model: Freeze (0), Replace (1), Version (2). Use half-open intervals [start, end) for point-in-time fact joins.
Already have a pass? Go to your account. Need a pass? See pricing.