Data Engineering · Episode 04 · 1:47
SCD Type 2 explained: keep history when your customer moves
Overwriting the customer's region (Type 1) changed last quarter's East revenue from $6,700 to $2,500. Type 2 closes the old row and adds a new one, so Q2 stays $6,700 and the August sale counts in the West.
What you’ll needPython 3.10+, DuckDB
What you’ll need
- python.org/downloads →
Python 3.10 or newerRuns the code. Check yours with python3 --version. - code.visualstudio.com →
VS Code or CursorEither works; so does any editor you like. - git-scm.com/downloads →
gitGets the code from GitHub. 
DuckDB 1.0+SQL on files and tables, no database serverpip install "duckdb>=1.0"
Set it up
- 1Get the code
git clone https://github.com/DayanEbrar0X/data-anatomy.ai.git cd data-anatomy.ai
- 2Make a virtual environmentIt keeps this project's packages separate from the rest of your computer.
python3 -m venv .venv source .venv/bin/activate
- 3Install the packagesrequirements.txt installs every lesson's packages. This one needs only: DuckDB.
pip install -r requirements.txt # or just this lesson: pip install "duckdb>=1.0"
- 4Run it
cd data-engineering/18-scd-type-2 python3 src/scd.py
Project structure18-scd-type-2/
Project structure
data-engineering/18-scd-type-2/Open on GitHub →
- data/
- customers.csv3 customers: id, name, region
- sales.csv5 sales in Q2 and Q3 2026: customer, day, usd
- short/
- scd.pyrunthe code from the video
- shop.pyloads data/ into DuckDB tables
- src/
- scd.pymainthe code from the video
- shop.pyloads data/ into DuckDB tables
- README.mdthe idea, how to run it, a line-by-line walkthrough, things to try
data-engineering/18-scd-type-2/src/scd.pyOpen on GitHub →
import duckdb
from shop import setup
db = duckdb.connect()
setup(db) # 3 customers, 5 sales in Q2 and Q3
def by_region(dim, q=2, asof=""):
rows = db.sql(f"""
SELECT region, sum(usd) FROM sales
JOIN {dim} ON cust = id {asof}
WHERE quarter(day) = {q}
GROUP BY region ORDER BY region""")
return ", ".join(f"{r} ${u:,}"
for r, u in rows.fetchall())
print("Q2 before:", by_region("customers"))
# Type 1: overwrite the row in place
db.sql("CREATE TABLE t1 AS FROM customers")
db.sql("UPDATE t1 SET region='West' WHERE id=1")
print("Q2 type 1:", by_region("t1"))
# Type 2: close the old row, insert a new one
db.sql("""CREATE TABLE t2 AS SELECT *,First 24 of 35 lines
Transcript
Your customer moved, and your report rewrote history. The fix is a slowly changing dimension, Type 2. Type 1 is a whiteboard: erase and rewrite. Type 2 is a ledger: add a line.
Our shop: three customers, five sales. Ana lives East, until July 1, when she moves West. DuckDB, in memory. A helper loads the customers and the sales.
One query reports revenue: join each sale to its customer, then sum by region. We print Q2 as it stands. Type 1 copies the table, then overwrites Ana's region in place. Then Q2 again, from that table.
Type 2 adds three columns: valid from, valid to, and is current. Every row starts open-ended, valid until the year 9999. When Ana moves, close her old row: valid to July 1, no longer current. Then insert a new row: Ana, West, from July 1.
Now the join is as-of: each sale meets the version valid on its day. Last, Q3 and Q2 from the Type 2 table. Let's run it. Before the move, Q2: East $6,700, West $3,000.
Type 1 says East $2,500, West $7,200. Ana's spring sales jumped West. Nothing new was sold. Type 2 puts her August sale, $1,800, in the West.
And Q2 is back to East $6,700. History kept. Real dimensions change all the time: addresses, sales reps, price tiers. dbt snapshots build exactly this, with valid from and valid to on every row.
The gotcha: join on the date range, not just the id, or Ana's sales count twice. Type 1 rewrites the past. Type 2 keeps it, one row per version.
Read the lesson on GitHub →- #scdtype2
- #duckdb
- #datawarehouse
- #dataengineering
