SCD2: Build Effective Ranges — Solution & ExplanationProblems / SCD2: Build Effective Ranges / EditorialApproach

Each change opens an interval. effective_from is just changed_at renamed — the log's grain (one row per change) is already the dimension's grain.

The next change closes it. LEAD(changed_at) OVER (PARTITION BY customer_id ORDER BY changed_at) pulls the successor's timestamp onto the current row; the final change has no successor, and LEAD's NULL is exactly SCD2's open-ended "current" marker.

Order by customer and interval start.

Key InsightSCD Type-2 tables look elaborate — paired dates, current flags — but when the source is an ordered change log, the whole structure is one window function: intervals are just consecutive events zipped together, and LEAD/LAG are how SQL zips. Two properties come free and are worth checking on any SCD build: the intervals *tile* (each row's end = next row's start — no gaps or overlaps for point-in-time lookups to fall into), and re-entering a previous state yields distinct stints because the grain is the *change*, not the (customer, plan) pair. When you later need "the plan as of time T", this table answers with effective_from <= T AND (T < effective_to OR effective_to IS NULL).Hints

Every interval's end is defined by its successor's start — 'look at the next row in this customer's timeline' is precisely LEAD over PARTITION BY customer ORDER BY changed_at.

LEAD returns NULL past the partition's last row, and that NULL is not a problem to patch — it IS the standard SCD2 encoding for 'current row'.

Don't group by (customer, plan): a customer who leaves a plan and later returns has two separate stints, and the event log's row-per-change grain already represents that correctly.

By construction these intervals tile perfectly — each effective_to equals the next effective_from, no gaps, no overlaps. That closure property is what makes as-of joins against the dimension safe.

Solve SCD2: Build Effective Ranges yourself →