Same data, two shapes

Before building a Data Vault, it helps to be sharp on the two models it borrows from. This lesson answers one real question against both of them, so you can see what each shape is good at.

Example: one question, two databases

Northpaw Veterinary Group runs its clinics on ClinicOS, a practice-management system. Every night ClinicOS exports its tables as CSV files. The folder code/data/clinicos/2026-03-31/ holds the export from the end of March 2026: 4 clinics, 6 owners, 9 pets, 5 vets, a treatment price list, and 18 invoices with 30 invoice lines.

If you haven't set up the code repo yet, follow Before you start first. Then, from the code/ folder, load the export into DuckDB exactly as ClinicOS stores it:

duckdb northpaw.duckdb -f module-01/01-clinicos-oltp.sql

Output:

┌───────────────┬───────────┐
│  table_name   │ row_count │
│    varchar    │   int64   │
├───────────────┼───────────┤
│ clinics       │         4 │
│ invoice_lines │        30 │
│ invoices      │        18 │
│ owners        │         6 │
│ pets          │         9 │
│ treatments    │         8 │
│ vets          │         5 │
└───────────────┴───────────┘

Now the finance team asks: what was our revenue per clinic and per species in Q1 2026? Revenue sits on invoice lines, the clinic on the invoice header, and the species on the pet. So the query walks from lines to invoices, then out to clinics and pets:

SELECT
    c.clinic_name,
    p.species,
    sum(l.quantity * l.unit_price) AS revenue
FROM clinicos.invoice_lines AS l
JOIN clinicos.invoices      AS i ON i.invoice_no  = l.invoice_no
JOIN clinicos.clinics       AS c ON c.clinic_code = i.clinic_code
JOIN clinicos.pets          AS p ON p.pet_id      = i.pet_id
WHERE i.invoice_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31'
GROUP BY c.clinic_name, p.species
ORDER BY c.clinic_name, p.species;

duckdb northpaw.duckdb -f module-01/02-revenue-3nf.sql

Output (first five rows):

┌──────────────────────┬─────────┬───────────────┐
│     clinic_name      │ species │    revenue    │
│       varchar        │ varchar │ decimal(38,2) │
├──────────────────────┼─────────┼───────────────┤
│ Northpaw Eastgate    │ cat     │         75.00 │
│ Northpaw Eastgate    │ dog     │        304.70 │
│ Northpaw Harbourside │ bird    │         45.00 │
│ Northpaw Harbourside │ cat     │        168.50 │
│ Northpaw Harbourside │ dog     │        331.90 │

Now build a second shape of the same data: a star schema with one table of measurable events (invoice lines) surrounded by descriptive tables. Here is the heart of it, the fact table:

CREATE TABLE star.fact_invoice_line AS
SELECT
    CAST(strftime(i.invoice_date, '%Y%m%d') AS INTEGER) AS date_key,
    c.clinic_sk,
    o.owner_sk,
    p.pet_sk,
    v.vet_sk,
    t.treatment_sk,
    l.invoice_no,                                   -- degenerate dimension
    l.line_no,
    l.quantity,
    l.quantity * l.unit_price AS amount             -- the measure
FROM clinicos.invoice_lines AS l
JOIN clinicos.invoices  AS i ON i.invoice_no     = l.invoice_no
JOIN star.dim_clinic    AS c ON c.clinic_code    = i.clinic_code
JOIN star.dim_owner     AS o ON o.owner_id       = i.owner_id
JOIN star.dim_pet       AS p ON p.pet_id         = i.pet_id
JOIN star.dim_vet       AS v ON v.vet_id         = i.vet_id
JOIN star.dim_treatment AS t ON t.treatment_code = l.treatment_code;

The script also builds the dimension tables and then asks the same question:

SELECT
    c.clinic_name,
    p.species,
    sum(f.amount) AS revenue
FROM star.fact_invoice_line AS f
JOIN star.dim_clinic AS c ON c.clinic_sk = f.clinic_sk
JOIN star.dim_pet    AS p ON p.pet_sk    = f.pet_sk
JOIN star.dim_date   AS d ON d.date_key  = f.date_key
WHERE d.year = 2026 AND d.quarter = 1
GROUP BY c.clinic_name, p.species
ORDER BY c.clinic_name, p.species;

duckdb northpaw.duckdb -f module-01/03-star-schema.sql

You get the same ten rows, to the cent. Both designs hold the same facts. The difference is their shape, and each shape is built for a different job.

Two jobs, two designs

ClinicOS has an operational job: at the front desk, a receptionist creates an invoice for Biscuit's rabies vaccination, and it must be stored quickly and correctly, while dozens of other bookings happen at the same time. Systems built for this are called OLTP systems (online transaction processing). They read and write a few rows at a time, and they must never contradict themselves.

To stay consistent, OLTP schemas are normalized: every fact is stored in exactly one place. Picture what happens without that. Suppose invoices were kept as one wide table, repeating the owner's phone number on every line:

When Lena changes her number, every copy must be updated. Miss one, and the table holds two phone numbers for one person. This is an update anomaly. Normalization removes such anomalies step by step:

• First normal form (1NF): every column holds one atomic value, with no lists inside a cell and no repeating column groups (treatment_1, treatment_2, …).
• Second normal form (2NF): in a table with a composite key, every non-key column depends on the whole key. The treatment depends on (invoice_no, line_no), but the pet depends only on invoice_no, so pets move to an invoice header table.
• Third normal form (3NF): non-key columns depend on the key and nothing else. The owner's phone depends on the owner, not on the invoice, so it moves to an owners table.

The result is the ClinicOS schema you loaded: seven narrow tables connected by foreign keys. It is ideal for writing. For analysis it has a cost: you must know which path of joins leads to each attribute, and the path differs from question to question.

The second shape serves the analytical job. Analysts read many rows at once, group them and compare periods. They want a model that is predictable to query and that keeps history. This is dimensional modeling, developed and popularized by Ralph Kimball.

Facts, dimensions and grain

A dimensional model splits data into two kinds of tables:

• A fact table records measurable business events: here, one row per invoice line with the measures quantity and amount. Fact tables are long and narrow.
• A dimension table holds the descriptive context used to filter and group facts: which clinic, which pet, which treatment, which day. Dimensions are wide and relatively short, and they are deliberately denormalized: dim_pet carries species and breed directly, with no separate species table.

Arranged together, they form a star schema:

Every analytical question has the same shape: start at the fact table, and take one step out to each dimension you need. That regularity is the main advantage of a star. It isn't about fewer joins. Our question needed three joins in both schemas. The benefit is that the analyst never has to discover a path through the model.

The most important design decision in a star is the grain: a precise statement of what one fact row represents. "One row per invoice line" is a grain. "Revenue per clinic per day" is a different grain, and it would lose the pet and treatment detail. Declare the grain first. It decides which dimensions can attach to the fact. A pet fits invoice-line grain; a monthly clinic budget does not.

Two more terms appear in the fact table above. invoice_no is a degenerate dimension: an identifier that is useful for grouping and tracing back, but has no attributes of its own, so it lives in the fact table with no dimension table. The _sk columns are surrogate keys: integers generated by the warehouse instead of the source systems' own identifiers. The next lesson shows why they matter.

In practice

• Don't point BI tools at the OLTP database. Analytical queries scan large ranges and compete with the front desk for the same resources. Copy the data out first, as ClinicOS's nightly export does.
• Write the grain down in the table's description. Most double-counting bugs in dimensional models are grain mismatches: a measure joined at a finer grain than it was recorded at.
• Measures should be additive where possible. amount can be summed across any dimension. A stored "running balance" cannot, which is why you store the events and derive the balance.

Try it

Write a query that returns revenue per treatment category (consultation, vaccine, …) per month. Write it once against clinicos and once against star. In the 3NF version, count how many tables you had to look into before you knew where category lives.

Key takeaways

• Operational (OLTP) systems are normalized so that each fact lives in one place and writes stay consistent. 1NF, 2NF and 3NF remove repeating groups, partial dependencies and transitive dependencies.
• Analytical models are shaped for reading: a fact table of measurable events surrounded by denormalized dimension tables.
• The grain, meaning what one fact row represents, is the first and most important decision in a star schema.
• A star's advantage is its predictable query shape, not a smaller number of joins.

Next: Keeping history in a star