Where the classic models crack

The star schema from the last two lessons works well while Northpaw has one source system and a stable business. This lesson changes both, and watches what breaks.

Example: the Riverside acquisition

In early 2026 Northpaw bought an independent practice in Leipzig, now called Northpaw Riverside. Riverside runs VetBase, an older practice system with its own conventions. Patients are numbered VB-00417, species are coded (FEL for cats, CAN for dogs), names are upper-case, and the export uses semicolons. The first VetBase export is in code/data/vetbase/2026-03-31/.

Management wants Riverside in the group reports right away. The quickest route is to append its patients to dim_pet, giving them new surrogate keys:

CREATE OR REPLACE TABLE star.dim_pet_combined AS
SELECT pet_sk, pet_id, pet_name, species, breed
FROM star.dim_pet
UNION ALL
SELECT (SELECT max(pet_sk) FROM star.dim_pet) + row_number() OVER (ORDER BY patient_no),
       patient_no, patient_name, species_code, breed
FROM vetbase.patients;

duckdb northpaw.duckdb -f module-01/07-vetbase-arrives.sql

The script then asks a simple question, how many cats Northpaw treats, and checks the microchip numbers. Output:

┌─────────┬───────┐
│ species │ pets  │
│ varchar │ int64 │
├─────────┼───────┤
│ CAN     │     1 │
│ FEL     │     2 │
│ bird    │     1 │
│ cat     │     3 │
│ dog     │     4 │
│ rabbit  │     1 │
└─────────┴───────┘
┌─────────────┬───────────────┬────────────┬──────────────┬─────────────────┐
│ clinicos_id │ clinicos_name │ vetbase_id │ vetbase_name │    microchip    │
│   varchar   │    varchar    │  varchar   │   varchar    │     varchar     │
├─────────────┼───────────────┼────────────┼──────────────┼─────────────────┤
│ P-102938    │ Mochi         │ VB-00417   │ MOCHI        │ 276098100000038 │
└─────────────┴───────────────┴────────────┴──────────────┴─────────────────┘

Two things are wrong. Cats appear as both cat and FEL, so every species report splits in two. And Mochi is in both systems: Hannah took her to Riverside for an emergency visit last year. The combined dimension counts one cat twice, under two keys, and each copy has its own history.

Integration is a modeling problem

Surrogate keys gave each record a key. They didn't tell you which records are the same thing. That is the real integration problem, and it has several layers:

• Different identifiers for the same business object. P-102938 and VB-00417 are one cat. Something must say so, and here only the microchip does. Some pets have no chip.
• Different codes for the same meaning. FEL and cat, LEIPZIG and Leipzig.
• Different histories. VetBase holds Mochi's visits from before the acquisition. A combined dimension has to decide which system's breed spelling and which owner phone number wins, and when.

A conformed dimension demands that all of this is settled before data can be loaded: map the codes, match the pets, and pick the surviving attributes. That work is real and necessary. The problem is that the star forces you to do it at load time, in one step, for every source at once, and to redo it whenever the matching rules change. Until someone agrees on those rules, Riverside's data can't enter the warehouse at all, not even as raw history.

Example: Dr. Kaya works at two clinics

In April, Dr. Aylin Kaya starts covering Harbourside two days a week, while keeping three days at Lindenau. ClinicOS can't store that in its vets.clinic_code column, so its vendor replaces the column with a new table, vet_clinic_assignments: one row per vet and clinic, with weekly hours.

The warehouse team rebuilds dim_vet from the new table, which gives one row per vet and clinic, and reruns the vet revenue report:

duckdb northpaw.duckdb -f module-01/08-vet-at-two-clinics.sql

Output:

┌───────────────────┬──────────────────────┬─────────────────────────┐
│     vet_name      │ revenue_with_dim_vet │ revenue_with_dim_vet_v2 │
│      varchar      │    decimal(38,2)     │      decimal(38,2)      │
├───────────────────┼──────────────────────┼─────────────────────────┤
│ Dr. Aylin Kaya    │               548.40 │                 1096.80 │
│ Dr. Felix Brandt  │               331.90 │                  331.90 │
│ Dr. Ines Albrecht │               379.70 │                  379.70 │
│ Dr. Kwame Mensah  │               407.00 │                  407.00 │
│ Dr. Sara Conti    │               213.50 │                  213.50 │
└───────────────────┴──────────────────────┴─────────────────────────┘

Dr. Kaya's revenue has doubled overnight, and none of it is new.

When cardinality and grain change

The star assumed that a vet belongs to one clinic. That's a one-to-many relationship (clinic → vets), baked in as a single column on dim_vet. When the business changed it to many-to-many, the dimension produced two rows for one vet_sk. Every fact row for Dr. Kaya joined to both, which is a fan-out. The model didn't adapt to the new rule. It gave wrong answers without any error.

A correct fix touches everything. dim_vet loses its clinic attribute. The vet-to-clinic relationship needs its own structure (a bridge table or a separate fact). The load code changes, historical facts may need reloading, and every report that used "the vet's clinic" must be reviewed. The normalized ClinicOS schema paid the same price: a column became a table, and every program that wrote or read vets.clinic_code had to change.

Grain changes hurt in the same way. Suppose ClinicOS starts recording which vet performed each invoice line rather than one vet per invoice. The fact table's grain and keys change, and history recorded at the old grain can't be split after the fact. So you either rebuild the fact table or run two tables side by side.

Both problems come from the same design choice. Classic models encode today's business rules, such as "one clinic per vet", "one vet per invoice" and "this system is the truth", directly into the table structures. When a rule changes, the structure has to change, and so does everything built on it.

Two classic answers: Inmon and Kimball

Two schools of warehouse design grew up around these trade-offs. Bill Inmon's approach builds a central, normalized (3NF) enterprise warehouse first, integrated across all sources, and derives dimensional data marts from it. That gives one consistent version of the data, but it needs a large up-front modeling effort, and it inherits 3NF's cascading changes. Ralph Kimball's approach builds dimensional marts one business process at a time and ties them together with conformed dimensions. It delivers value quickly, but integration happens inside each dimension load, as you saw with Riverside.

Neither is wrong. Both assume you can agree on integrated, rule-shaped structures before you store the data.

What Data Vault proposes

Data Vault, created by Dan Linstedt, changes that assumption. It separates three things that classic models fuse together, and it stores each in its own kind of table:

• Hubs hold only the business keys, for example every pet identifier ever seen, from any system.
• Links hold relationships between keys, and every link is many-to-many. So when Dr. Kaya gets a second clinic, that's just one more link row. No structure changes.
• Satellites hold the descriptive attributes and their full history, separately per source. VetBase's view of Mochi and ClinicOS's view sit side by side, and both are kept.

Notice what isn't in the diagram: no decision about which system wins, and no "current clinic" column. Integration rules, such as "VB-00417 is P-102938" or "FEL means cat", are applied later, in a separate layer, and they can be changed without reloading history. Tables are insert-only: nothing is overwritten, so any past report can be reproduced.

This flexibility has a cost. You get many more tables and many more joins, and the raw vault isn't meant for analysts to query directly. You still build star schemas at the end, from the vault. Module 7 comes back to when that trade is worth making. The rest of the course builds Northpaw's vault step by step, starting with the question that everything else depends on: what exactly identifies a pet?

Key takeaways

• Surrogate keys give records identity inside the warehouse. They don't solve integration: recognizing that two source records are the same business object.
• Classic models encode business rules such as cardinalities, grain and the "system of record" into their structures. When a rule changes, structures, loads and history must change too.
• Many-to-many changes can cause silent fan-out: wrong numbers with no error.
• Data Vault separates business keys (hubs), relationships (links) and context with history (satellites). Integration rules are applied later, over insert-only raw data.

Next: Module 1 exercises