The customer moved cities. Slowly changing dimensions, explained simply

How tables remember what used to be true, and why that quiet choice decides whether your reports can be trusted

A customer moved from Pune to Mumbai.

In life, that is a small event. Boxes, a new address, a change of routine.

In data, it is one of the quietest and most consequential events you will ever handle. Because now your table has to decide something. Does it remember that this customer used to live in Pune? Or does it simply forget?

That decision has a name. Slowly changing dimensions.

Why this matters more than it sounds

Think about the reports built on top of that customer row.

Last year, the sales team ran a campaign in Pune. It worked. Revenue went up.

This year, someone asks a fair question. How much revenue came from Pune customers last year?

If your table overwrote the address, that customer now looks like a Mumbai customer. Their old orders quietly move to a city they did not live in at the time. The report is not broken. It runs fine. It just tells you something that never happened.

Most wrong numbers are not caused by failed jobs. They are caused by tables that forgot something important.

That is the real subject here. Not a modeling pattern to memorize, but a question to answer honestly for every attribute you store. Do we need to know what this looked like back then?

Facts change fast. Dimensions change slowly

A quick separation, because the naming confuses people.

Facts are the events. An order was placed. A payment cleared. A sensor sent a reading. They arrive constantly and they do not change after the fact.

Dimensions are the descriptions around those events. The customer, the product, the store, the employee, the plan they were on. These change too, just far more slowly. A customer changes address once in three years. A product changes category once. A price tier changes when someone in a meeting decides it should.

Slow does not mean unimportant. It means easy to miss.

Type 1: overwrite and move on

The simplest choice. The new value replaces the old one.

MERGE INTO silver.customers AS target
USING bronze.customers_daily AS source
  ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET
  target.address = source.address,
  target.phone = source.phone,
  target.updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT *

One row per customer. Always current. Easy to query, easy to explain, cheap to store.

Use Type 1 when the old value has no meaning. A corrected spelling of a name. A fixed typo in an email. A phone number that was simply wrong. Nobody will ever ask what the wrong value used to be.

And here is the part people forget. Type 1 does not mean the history is destroyed forever. On Delta Lake, the previous version of the table is still there for a while, which is why time travel is such a useful safety net. But table versions are for recovery and auditing, not for answering business questions. You should not build a revenue report on top of a version history.

Type 2: keep the old row, open a new one

Here the table remembers.

Instead of updating the address in place, you close the old row and insert a new one. The customer now has two rows, each valid for a period of time.

SELECT customer_id, address, valid_from, valid_to, is_current
FROM silver.customers
WHERE customer_id = 'C-101'
ORDER BY valid_from

C-101 | Pune   | 2024-01-15 | 2026-03-02 | false
C-101 | Mumbai | 2026-03-02 | null       | true

Three small columns carry the whole idea.

valid_from is when this version became true. valid_to is when it stopped being true, and stays null while it is still current. is_current is a convenience flag so everyday queries stay simple.

Now both questions have honest answers. Where does this customer live? Filter on is_current. Where did they live when that order was placed? Join on the order date falling between valid_from and valid_to.

SELECT o.order_id, o.order_date, c.address
FROM gold.orders AS o
JOIN silver.customers AS c
  ON o.customer_id = c.customer_id
 AND o.order_date >= c.valid_from
 AND (o.order_date < c.valid_to OR c.valid_to IS NULL)

That join is the whole payoff. The report stops describing today and starts describing the day it happened.

Writing a Type 2 merge without breaking it

The logic surprises people the first time, because a single incoming change needs two writes. One update to close the old row. One insert to open the new one.

A MERGE statement can only take one action per matched row, so the usual approach is two passes. First close the rows whose tracked values changed. Then insert the new versions.

-- Pass 1: close the rows that changed
MERGE INTO silver.customers AS target
USING staging.customer_changes AS source
  ON target.customer_id = source.customer_id
 AND target.is_current = true
WHEN MATCHED AND target.address <> source.address THEN UPDATE SET
  target.valid_to = source.change_date,
  target.is_current = false

-- Pass 2: open the new versions
INSERT INTO silver.customers
SELECT
  source.customer_id,
  source.address,
  source.change_date AS valid_from,
  CAST(NULL AS DATE) AS valid_to,
  true AS is_current
FROM staging.customer_changes AS source

Two rules keep this safe.

Compare only the columns you actually track. If you compare every column, a meaningless change in an unused field will create a new version for no reason, and your dimension will grow for no reason. Decide deliberately which attributes deserve history.

Make it safe to run twice. Jobs fail and retry. If the same change lands again, the pass 1 condition should no longer match, and pass 2 should not insert a duplicate version. This is the same idempotency thinking behind incremental processing, and it matters just as much here.

Where this lives in the medallion layers

Bronze holds what the source sent, untouched. Daily snapshots or a change feed. Nothing decided yet.

Silver is where the dimension is maintained, where the merge runs and history is built.

Gold is where the join happens and the answer is shaped for people to read.

If that split feels familiar, it is the same shape described in medallion architecture explained. Slowly changing dimensions are one of the clearest examples of why Silver exists at all. Bronze cannot hold this logic, because Bronze should not decide anything. Gold should not hold it either, because then every dashboard would rebuild the same history in its own slightly different way.

The mistakes that show up in real projects

Tracking history for everything. Ten tracked columns on a wide dimension will multiply your row count fast. Track what someone will ask about.

Forgetting late-arriving changes. A change dated three days ago arrives today. If you always close the current row as of today, your validity windows will overlap or leave gaps. Order the incoming changes by their real change date, not by arrival time.

Overlapping windows. Two rows for the same key both marked current, or two windows that cover the same day, and every join silently doubles the rows. A simple daily check that counts current rows per key catches this early. This is the sort of quiet defect worth writing an explicit data quality check for.

No end date convention. Half the pipeline uses null for open rows, the other half uses 9999-12-31. Pick one and write it down.

Deletes ignored. If a customer disappears from the source, closing their current row is usually more honest than deleting the whole history.

When Type 1 is the better engineering choice

There is a temptation to track everything, because history feels safer.

It is not free. More rows, more joins that need a date range, more explaining, more chances for someone to forget is_current and double their totals.

So ask the plain question. Will anyone ever need to know the previous value? If the honest answer is no, choose Type 1 and keep the table small and clear. Choosing less is a real design decision, not a shortcut.

The idea underneath

Every dimension table is a small statement about memory.

Type 1 says the present is enough. Type 2 says the past still counts.

Neither is correct in general. What is correct is deciding on purpose, writing it down, and building reports that agree with the decision.

That is what most of data engineering turns out to be. Not clever code, but choices made carefully enough that the numbers can be defended a year later.

Practise this properly

Reading about a merge and writing one are different experiences. The second one teaches you.

In BricksNotes, the Slowly Changing Dimensions lesson walks through Type 1 and Type 2 with a real dataset in Databricks Free Edition, and it sits beside the lessons on Delta Lake, incremental processing, and medallion architecture so the pieces connect instead of floating on their own. If you are just starting out, Start Here is the calmer entry point.

If you enjoyed this one, you may also like the small files problem explained and what did this table look like last Tuesday.

Take a table you already own. Pick one column. Decide whether it deserves a memory.

Brick by brick.