Slowly Changing Dimensions: Type 1

Why Type 1 exists

A dimension table describes an entity, like a customer or a product, and stores its attributes: name, city, tier. Those attributes drift. A customer moves, a product gets renamed, and someone fat-fingers a city at data entry. The Slowly Changing Dimension (SCD) question is simple to state: when an attribute changes, what do you do with the old value?

Type 1 is the bluntest answer. Overwrite the old value in place and keep no history. You reach for it when the old value was simply wrong: a typo, a misspelled city, a value nobody should ever see again. You do not want a durable record of a mistake. You want it corrected everywhere, as if it had always been right.

The mental model: match on the natural key, overwrite, add no rows

Every dimension row carries two kinds of key. The surrogate key (customer_key) is a stable integer that fact tables join on. The natural key (email) identifies the real-world entity. A Type 1 apply step matches incoming rows to existing ones on the natural key and overwrites the changed attributes in place, adding no new row to preserve the old value. The surrogate key never moves, so fact tables keep pointing at the same customer_key and nothing downstream breaks. A genuinely new customer is still inserted with a fresh key: that is a new row for a new entity, not a second row for a change.

Worked example

-- dim_customer before         stg_customer (corrected dump)
-- key email          city       email          city
-- 1   ana@shop.com   Sanfran    ana@shop.com   San Francisco
-- 2   ben@shop.com   Austin

UPDATE dim_customer
SET name = (SELECT s.name FROM stg_customer s WHERE s.email = dim_customer.email),
    city = (SELECT s.city FROM stg_customer s WHERE s.email = dim_customer.email)
WHERE email IN (SELECT email FROM stg_customer);

-- dim_customer after:
-- 1   ana@shop.com   San Francisco   <- fixed, same customer_key
-- 2   ben@shop.com   Austin          <- untouched, absent from the dump

Row count stays at 2, customer_key 1 is unchanged, and Ben, who is not in the dump, is left alone.

The portable one-statement form is an upsert, which the practice uses to overwrite existing customers and insert brand-new ones together:

INSERT INTO dim_customer (email, name, city, tier)
SELECT email, name, city, tier FROM stg_customer
WHERE true
ON CONFLICT(email) DO UPDATE SET
  name = excluded.name,
  city = excluded.city,
  tier = excluded.tier;

excluded is the row that would have been inserted. On a key collision SQLite runs the DO UPDATE branch instead. This requires a UNIQUE index on email.

Pitfalls

• Drop the WHERE ... IN guard and you nuke everyone. In the correlated UPDATE, a customer absent from the dump has a subquery that returns nothing, so city is set to NULL. The guard restricts the update to emails that actually exist in staging.
• The WHERE true before ON CONFLICT is not decoration. In INSERT ... SELECT ... ON CONFLICT, SQLite's parser can misread ON as a join clause. Inserting a WHERE (even WHERE true) between the SELECT and ON CONFLICT disambiguates it.
• Type 1 destroys "what was the value on date X." If finance ever needs the city at the time of sale, Type 1 is the wrong tool and you need Type 2 (next lesson). Choosing Type 1 is a business decision, not a silent default.

Interview nuance: a production apply step must be idempotent, because pipelines retry and backfill and will run the same batch twice. Type 1 is idempotent by construction. Overwriting city with the value it already holds is a no-op, and on the second run the "new" customer already exists, so the upsert takes the DO UPDATE branch instead of inserting a duplicate. That is exactly why the hidden checks re-run your script and assert the row count did not move.