Data Modeling II — Dimensional Modeling

Why analytics needs a different model

The schema you built in Chapter 01 was normalized: every fact lives in exactly one place, foreign keys stitch the tables together, and nothing is duplicated. That's exactly what a transactional system (an OLTP system — online transaction processing) wants. When a customer rents a GPU, you insert one row, update one balance, and trust that there's no contradictory copy anywhere. Normalization protects correctness when data is constantly changing.

But analysts don't insert rows one at a time — they ask sweeping questions across millions of them: revenue by region by month, average rental length by GPU tier, the top ten customers this quarter. To answer those against a normalized schema, every query has to re-join the same five or six tables, every time. That's painful in two ways:

• It's slow. Joining many tables over large volumes is expensive, and the engine redoes that work for every report.
• It's hard to understand. An analyst has to know which of fifteen tables holds the customer's region, and how to navigate the key chain to reach it. The model fights the question instead of serving it.

So for analytics we use a different shape — one deliberately denormalized and optimized for reading. This is OLAP: online analytical processing. The most common and most durable design is the star schema.

The star schema

A star schema has one central fact table surrounded by several dimension tables. The fact table records the events you measure; the dimensions hold the descriptive context you slice by. Draw it and it looks like a star — the fact in the middle, dimensions as points radiating out, each connected by a single key.

Here's the marketplace as a star. The fact is fct_rentals (one row per rental); the points are who rented (dim_customer), what they rented (dim_gpu), and when (dim_date):

The whole point of this shape: an analyst's question maps onto it almost word-for-word. "Revenue (a measure on the fact) by GPU model (a column on dim_gpu) by month (a column on dim_date)" becomes one join from the fact to two dimensions and a GROUP BY. No long key chains, no guessing which table holds what.

Fact tables

A fact table records events or measurements — things that happened, with numbers attached. Each row is one occurrence of the business process you're modeling: a rental, an order, a payment, a shipment. Fact tables are tall and narrow: they hold the foreign keys to the dimensions, plus the numeric measures you'll aggregate.

The most important property of a measure is its additivity — whether you can meaningfully add it up across any dimension. Get this wrong and your dashboards lie.

Dimension tables

Dimensions hold the descriptive context — the adjectives and labels you filter and group by. dim_customer knows a customer's name, tier, and country; dim_gpu knows a model's VRAM and tier. Where fact tables are tall and narrow, dimensions are wide and short: many descriptive columns, relatively few rows.

Crucially, dimensions are deliberately denormalized. In Chapter 01 you might have split a GPU's brand, tier, and spec across three normalized tables. In the dimension you flatten all of that into one wide table, even though it duplicates "NVIDIA" across many rows. That duplication is the trade we accept on purpose: it means a query touches one dimension instead of joining a little snowflake of sub-tables. Fewer joins, simpler SQL, faster reads.

Declaring the grain

Before you write a single CREATE TABLE, you must answer one question: what does one row of the fact table represent? That answer is the grain, and it is the most important decision in the entire model. Declare it as a sentence that starts with "one row per…":

• "One row per rental" — the finest, most flexible grain. Every individual rental is a row.
• "One row per customer per GPU model per day" — a pre-aggregated grain. Smaller and faster, but you've thrown away per-rental detail forever.
• "One row per customer per month" — coarser still; good for a tidy monthly summary, useless for "how long was that one rental?"

Why does this dominate everything? Because every measure and every dimension key on the fact must be true at that grain. If you declare "one row per rental" but accidentally join in a row per rental-hour, your revenue SUM double-counts — multiplied by the number of hours — and no one notices until the finance number is off by 10x. A wrong grain doesn't fail loudly; it quietly inflates totals.

For the rest of this chapter we choose the finest grain — one row per rental — because you can always roll a fine grain up to a coarse summary, but you can never recover detail you didn't keep.

Hands-on: build a star in DuckDB

Let's build it for real. First, recreate a small normalized source — the kind of thing Chapter 01 left you with: a rentals table that references customers and GPUs by id. Run this in your DuckDB session:

-- the normalized OLTP-style source (what ch.01 produced)
CREATE OR REPLACE TABLE src_customers (
  customer_id INTEGER, name VARCHAR, tier VARCHAR, country VARCHAR
);
INSERT INTO src_customers VALUES
  (1,'Acme Labs','pro','US'),
  (2,'Beta AI','free','DE'),
  (3,'Gamma Co','pro','US');

CREATE OR REPLACE TABLE src_gpus (
  gpu_id INTEGER, model VARCHAR, vram_gb INTEGER, tier VARCHAR
);
INSERT INTO src_gpus VALUES
  (10,'A100',80,'datacenter'),
  (11,'H100',80,'datacenter'),
  (12,'RTX4090',24,'consumer');

CREATE OR REPLACE TABLE src_rentals (
  rental_id INTEGER, customer_id INTEGER, gpu_id INTEGER,
  started_at TIMESTAMP, hours DOUBLE, hourly_rate DECIMAL(8,2)
);
INSERT INTO src_rentals VALUES
  (1001,1,10,'2026-05-03 09:00',5.0,2.50),
  (1002,2,12,'2026-05-03 14:00',2.0,0.80),
  (1003,1,11,'2026-05-20 10:00',8.0,3.00),
  (1004,3,10,'2026-06-02 11:00',4.0,2.50),
  (1005,3,11,'2026-06-18 16:00',6.0,3.00),
  (1006,1,12,'2026-06-18 16:00',1.5,0.80);

Now build the dimensions. Notice each gets its own surrogate key (*_key) — a clean integer the warehouse owns, independent of the source ids. We flatten descriptive columns straight in:

-- dim_customer: wide, denormalized, one row per customer
CREATE OR REPLACE TABLE dim_customer AS
SELECT
  customer_id              AS customer_key,
  name                     AS customer_name,
  tier                     AS customer_tier,
  country
FROM src_customers;

-- dim_gpu: descriptive context about each GPU model
CREATE OR REPLACE TABLE dim_gpu AS
SELECT
  gpu_id                   AS gpu_key,
  model                    AS gpu_model,
  vram_gb,
  tier                     AS gpu_tier
FROM src_gpus;

-- dim_date: one row per calendar day the business cares about.
-- A date dimension lets you GROUP BY month/weekday without date math everywhere.
CREATE OR REPLACE TABLE dim_date AS
SELECT
  CAST(d AS DATE)                          AS date_key,
  EXTRACT(year  FROM d)                    AS year,
  EXTRACT(month FROM d)                    AS month,
  STRFTIME(d, '%Y-%m')                     AS year_month,
  EXTRACT(day   FROM d)                    AS day,
  DAYNAME(d)                               AS weekday
FROM generate_series(DATE '2026-05-01', DATE '2026-06-30', INTERVAL 1 DAY) AS t(d);

Finally the fact table. Grain: one row per rental. We carry the dimension keys plus the additive measures (hours, revenue), computing revenue once at load time:

-- fct_rentals — GRAIN: one row per rental
CREATE OR REPLACE TABLE fct_rentals AS
SELECT
  r.rental_id                         AS rental_key,
  r.customer_id                       AS customer_key,   -- FK -> dim_customer
  r.gpu_id                            AS gpu_key,         -- FK -> dim_gpu
  CAST(r.started_at AS DATE)          AS date_key,        -- FK -> dim_date
  r.hours                             AS hours,           -- additive measure
  r.hours * r.hourly_rate             AS revenue          -- additive measure
FROM src_rentals r;

-- sanity check: row count must equal the number of rentals (grain check!)
SELECT count(*) AS rows, count(DISTINCT rental_key) AS distinct_rentals
FROM fct_rentals;

Now the payoff — the analytical question that was painful against the normalized source becomes a clean star query: revenue by GPU model by month.

SELECT
  d.year_month,
  g.gpu_model,
  SUM(f.revenue)  AS revenue,
  SUM(f.hours)    AS hours
FROM fct_rentals f
JOIN dim_gpu  g ON f.gpu_key  = g.gpu_key
JOIN dim_date d ON f.date_key = d.date_key
GROUP BY d.year_month, g.gpu_model
ORDER BY d.year_month, revenue DESC;

That's a complete star schema: a fact at a declared grain, three conformed-ready dimensions, and an analytical query that reads almost like the English question that prompted it. This is the shape your warehouse tables will take for the rest of the course.

✓ Check yourself

• Can you state, in one "one row per…" sentence, the grain of fct_rentals?
• Can you classify revenue, an end-of-day active_rentals count, and utilization_pct as additive, semi-additive, or non-additive — and say why?
• Why are dimensions deliberately denormalized when Chapter 01 worked so hard to normalize?
• What does the rows = distinct_rentals check protect you from?

-- GRAIN: one row per support ticket.

-- fct_support_tickets
--   ticket_key                       (degenerate key: the ticket id itself)
--   customer_key   FK -> dim_customer   <-- CONFORMED, shared with fct_rentals!
--   agent_key      FK -> dim_agent
--   priority_key   FK -> dim_priority
--   opened_date_key   FK -> dim_date     <-- dim_date is also conformed
--   resolved_date_key FK -> dim_date     (a "role-playing" dimension: dim_date
--                                          joined twice in different roles)
--   handling_minutes   measure  -> ADDITIVE   (sum across anything)
--   ticket_count = 1   measure  -> ADDITIVE   (lets you COUNT by summing)
--   was_resolved (0/1) measure  -> ADDITIVE   (sum = # resolved)
--   -- resolution_rate is NON-ADDITIVE: compute it as
--   --   SUM(was_resolved) / SUM(ticket_count) at query time, never store it.

-- dimensions:
--   dim_customer  (CONFORMED with the rentals star)
--   dim_date      (CONFORMED; role-plays as opened & resolved)
--   dim_agent     (agent name, team)
--   dim_priority  (low / medium / high / urgent)

Next

Your dimensions are clean and wide — but customers change tiers and GPUs change prices over time, and right now overwriting them would silently rewrite history. Next we fix that. → Slowly-Changing Dimensions & History