lesson 84 Warehouse & reporting projects · advanced

Latest-state reporting versus SCD Type 2

Compare current-state reporting with preserved history.

the idea

The foundational project keeps latest state. Type 1 overwrites attributes; Type 2 retains effective versions with distinct keys. Decide whether a report means current classification or classification at event time. Here customer_history uses nonoverlapping half-open validity ranges; NULL end means current.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customer_history5 columns · 3 rows

version_keyinteger · key	customer_idtext	countrytext	valid_fromtext	valid_totext
101	C001	United States	2024-01-01	2025-02-01
102	C001	Canada	2025-02-01	NULL
103	C002	United Kingdom	2024-01-01	NULL

all 3 rows

dim_customer4 columns · 5 rows

customer_keyinteger · key	customer_idtext	nametext	countrytext
1	C001	Alice Nguyen	United States
2	C002	Ben	United Kingdom
3	C003	Cara	United Kingdom
4	C004	Dev	India
5	C005	Elena	Canada

all 5 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT customer_id,country
FROM customer_history
WHERE valid_to IS NULL;

Watch for: This lab queries prebuilt history. It does not implement a full SCD loader, deletes, corrections, or late-arrival reconciliation.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Understand the source contract. Fictional extracts, load counts, invalid dates, versioned dimensions, refresh staging and a data catalog. The foundation project is latest-state; separate history and incremental labs demonstrate extensions.
step-1.sql
sql›
```
SELECT *
FROM customer_history;
```
2
Execute the transformation. The foundational project keeps latest state. Type 1 overwrites attributes; Type 2 retains effective versions with distinct keys. Decide whether a report means current classification or classification at event time. Here customer_history uses nonoverlapping half-open validity ranges; NULL end means current.
step-2.sql
sql›
```
SELECT customer_id,country
FROM customer_history
WHERE valid_to IS NULL;
```

Test another question. This lab queries prebuilt history. It does not implement a full SCD loader, deletes, corrections, or late-arrival reconciliation.

step-3.sql

SELECT f.sale_key,c.customer_id,h.country
FROM fact_sales f
JOIN dim_customer c ON c.customer_key=f.customer_key
JOIN customer_history h
  ON h.customer_id=c.customer_id
  AND f.order_date>=h.valid_from
  AND (f.order_date<h.valid_to OR h.valid_to IS NULL)