Keeping history in a star

A warehouse must answer questions about the past, while the source systems only know the present. This lesson shows how a star schema keeps history, and where that gets expensive.

Example: January's report changes by itself

Hannah Becker owns Mochi, a British Shorthair. In January and February, Mochi was treated at Northpaw Lindenau in Leipzig, where Hannah lived. On 9 March Hannah moved to Dresden. ClinicOS overwrote her city and phone number, and set updated_at to 2026-03-09.

On 1 March, finance ran a report of January revenue by the owner's city. On 1 April they ran the same report again, for the same month. The script 04-scd-type1.sql replays this. It builds dim_owner from the February export and runs the report. Then it loads the March export the simplest possible way, by overwriting changed values in place, and runs the report again:

UPDATE star.dim_owner AS o
SET city = src.city
FROM read_csv('data/clinicos/2026-03-31/owners.csv', all_varchar = true) AS src
WHERE src.owner_id = o.owner_id
  AND src.city IS DISTINCT FROM o.city;

duckdb northpaw.duckdb -f module-01/04-scd-type1.sql

Output (1 March, then 1 April; DuckDB's column-type row is left out here and in the rest of this lesson):

┌─────────┬─────────────────┐
│  city   │ january_revenue │
├─────────┼─────────────────┤
│ Dresden │          199.90 │
│ Hamburg │          204.90 │
│ Hanover │          225.00 │
│ Leipzig │          253.50 │
└─────────┴─────────────────┘
┌─────────┬─────────────────┐
│  city   │ january_revenue │
├─────────┼─────────────────┤
│ Dresden │          369.90 │
│ Hamburg │          204.90 │
│ Hanover │          225.00 │
│ Leipzig │           83.50 │
└─────────┴─────────────────┘

Not a single January invoice changed, yet €170 of January revenue moved from Leipzig to Dresden. Mochi's January blood panel now looks as if it came from a Dresden customer, before Hannah ever lived there.

Slowly changing dimensions: type 1

Dimension attributes change over time, but rarely: an owner moves, a treatment is renamed, a clinic gets a new manager. Changes like these are called slowly changing dimensions (SCDs), and there is a standard set of ways to handle them.

What you just ran is type 1: overwrite. The dimension keeps only the current value, and every fact, old or new, is described by today's version. Type 1 is the right choice when the old value was wrong (a typo in a pet's name) or when history has no business meaning (the phone number used for the next reminder mailing). It is the wrong choice whenever a report groups past facts by the attribute. The report doesn't fail. It quietly gives a different answer every time the dimension changes, and nobody can reproduce last month's numbers.

Example: remembering where Hannah lived

Now rebuild dim_owner so that it keeps versions. City becomes a tracked attribute: when it changes, the current row is closed and a new row is opened. Phone stays type 1.

duckdb northpaw.duckdb -f module-01/05-scd-type2.sql

The March load runs in three steps: overwrite type 1 columns, close changed versions, open new ones. Closing and opening look like this:

-- Step 2: close the current version when a type 2 column changed.
UPDATE star.dim_owner AS o
SET valid_to = CAST(src.updated_at AS DATE), is_current = false
FROM src
WHERE src.owner_id = o.owner_id
  AND o.is_current
  AND src.city IS DISTINCT FROM o.city;

-- Step 3: open a new version for changed owners and brand-new owners.
INSERT INTO star.dim_owner (owner_id, owner_name, phone, city, valid_from, valid_to, is_current)
SELECT src.owner_id, src.first_name || ' ' || src.last_name, src.phone, src.city,
       CAST(src.updated_at AS DATE), DATE '9999-12-31', true
FROM src
WHERE NOT EXISTS (SELECT 1 FROM star.dim_owner AS o
                  WHERE o.owner_id = src.owner_id AND o.is_current);

Hannah now has two rows. Output:

┌──────────┬──────────┬───────────────┬─────────────────┬─────────┬────────────┬────────────┬────────────┐
│ owner_sk │ owner_id │  owner_name   │      phone      │  city   │ valid_from │  valid_to  │ is_current │
├──────────┼──────────┼───────────────┼─────────────────┼─────────┼────────────┼────────────┼────────────┤
│        3 │ O-2203   │ Hannah Becker │ +49 351 5550303 │ Leipzig │ 1900-01-01 │ 2026-03-09 │ false      │
│        7 │ O-2203   │ Hannah Becker │ +49 351 5550303 │ Dresden │ 2026-03-09 │ 9999-12-31 │ true       │
└──────────┴──────────┴───────────────┴─────────────────┴─────────┴────────────┴────────────┴────────────┘

The script then rebuilds the fact table. Each invoice line looks up the owner version that was valid on the invoice date:

JOIN star.dim_owner     AS o ON o.owner_id       = i.owner_id
                            AND i.invoice_date  >= o.valid_from
                            AND i.invoice_date   < o.valid_to

Hannah's revenue now lands where she lived at the time. Output:

┌────────────┬─────────┬───────────────┐
│ month_name │  city   │    revenue    │
├────────────┼─────────┼───────────────┤
│ January    │ Leipzig │        170.00 │
│ February   │ Leipzig │         40.00 │
│ March      │ Dresden │         75.00 │
└────────────┴─────────┴───────────────┘

Type 2 and surrogate keys

Type 2: add a new row keeps one row per version of a dimension member. Each row carries its validity interval:

• valid_from and valid_to define a half-open interval: the version applies from valid_from up to, but not including, valid_to. Half-open intervals make lookups simple (>= and <), and they never overlap.
• The open version gets a far-future valid_to such as 9999-12-31, so lookups need no NULL handling. is_current is a convenience flag for "give me today's version".

Now owner_id appears twice for Hannah, so it can't be the primary key any more. Each version gets its own surrogate key (owner_sk), an integer that the warehouse assigns and the business never sees. Facts store the surrogate key of the version that was valid when the event happened. Once that link is written, every later report agrees about January.

Surrogate keys also decouple the warehouse from the source's identifiers. If ClinicOS ever renumbers owners, or a second system brings its own numbering, the facts don't change. Keep that thought in mind for the next lesson, because this is exactly where it gets tested.

You don't have to track every column. Here dim_owner is a hybrid: city is type 2, and phone is overwritten on all versions (type 1). This is common, because tracking a column that nobody reports on only multiplies rows. For completeness, type 3 keeps a "previous value" column next to the current one. It is rarely used, because it remembers only one step back.

Example: new pets and revenue in one report

Clinic managers want one monthly view that shows revenue next to the number of newly registered pets. Registrations are a different business event from invoices, so they get their own fact table at the grain "one row per pet registration". Crucially, it reuses the same dim_clinic and dim_date:

duckdb northpaw.duckdb -f module-01/06-conformed-dimensions.sql

The query aggregates each fact table separately, then joins the two results on clinic and month. Output (first six rows):

┌──────────────────────┬─────────────┬───────────────┬──────────┐
│     clinic_name      │ month_start │    revenue    │ new_pets │
├──────────────────────┼─────────────┼───────────────┼──────────┤
│ Northpaw Eastgate    │ 2026-01-01  │        199.90 │        0 │
│ Northpaw Eastgate    │ 2026-02-01  │         45.00 │        0 │
│ Northpaw Eastgate    │ 2026-03-01  │        134.80 │        0 │
│ Northpaw Harbourside │ 2026-01-01  │        204.90 │        2 │
│ Northpaw Harbourside │ 2026-02-01  │         87.00 │        0 │
│ Northpaw Harbourside │ 2026-03-01  │        253.50 │        0 │

Conformed dimensions

A conformed dimension is one dimension table, with the same keys and the same meaning, that is shared by several fact tables. Because both facts use dim_clinic and dim_date, "Harbourside in January" means the same thing in both, and their numbers can be put side by side. This is called drilling across:

Notice that no fact joins another fact directly. Each is summarized to a common level first, and only then are the summaries joined on the shared dimension keys. Joining the two fact tables row by row would multiply revenue by the number of registrations.

fact_pet_registration has no numeric column at all. Counting its rows is the measure. Fact tables like this are sometimes called factless facts.

Conformed dimensions are how Kimball-style warehouses grow: one business process at a time, all tied together by shared dimensions. The catch is that "shared" requires agreement. Every team and every source must map its data onto the same dim_clinic before it can join the party.

In practice

• Check what the change date really means. This script uses updated_at as the start of Hannah's new version. In ClinicOS, updated_at changes on any edit. If Hannah had fixed a typo in her email on 20 March, the move would appear to have happened on the 20th. Ask the source team which date carries business meaning, such as a "moved on" date. That question comes back throughout this course.
• Type 2 on fast-changing columns explodes row counts. Track only attributes that reports actually group or filter by at a past point in time.
• Type 2 updates rows in place. Closing a version means updating valid_to. On large tables, and when two loads overlap, that update is where things go wrong. Data Vault avoids updates entirely, as you'll see in module 3.

Key takeaways

• Type 1 overwrites and keeps only the current value. It silently changes historical reports whenever an attribute changes.
• Type 2 keeps one row per version with a half-open validity interval. Facts point to the version valid at event time through a surrogate key.
• Surrogate keys let one business key have many versions, and they protect facts from changes in source identifiers.
• Conformed dimensions are shared by several fact tables, so their measures can be compared by drilling across.

Next: Where the classic models crack