If you've ever worked near a data warehouse, you've heard the word Kimball. Maybe you nodded. Maybe you quietly googled it and got a 600-page textbook and closed the tab.
Fair. Ralph Kimball's The Data Warehouse Toolkit is a lot. But the actual ideas behind dimensional modeling are small, friendly, and once you see them in a concrete example, they stick. So we're going to learn them through a coffee shop.
Specifically: Bean & Stalk, a fictional cafe with two locations, a loyalty program, a chalkboard menu that changes seasonally, and — importantly for us — surprisingly messy data.
This is Part 1 of a five-part series. Each part is its own self-contained business scenario, but they build on each other — the vocabulary this article establishes (grain, star schema, SCD2) is what every later part assumes you already have:
| Part | Scenario | What it covers |
|---|---|---|
| 1 — this one | Bean & Stalk, a coffee shop | Fact vs. dimension tables, grain, star vs. snowflake, SCD Types 0–3 |
| 2 | Tabby, a SaaS cat-tracker company | Subscription grain, account hierarchies, MRR, SCD2 on plan changes |
| 3 | Crate Expectations, e-commerce fulfillment | Accumulating snapshots that fork, late-arriving facts, semi-additive measures |
| 4 | Meadowlark Health, insurance claims | Bridge tables, real many-to-many relationships, the weighting-factor trap |
| 5 | Back to Tabby, a contract dispute | The capstone — a case with no textbook answer, three stakeholders, three defensible numbers |
You can read this one on its own; it's the fundamentals every later part leans on. Parts 2 through 5 each stand alone too, but they read best in order — Part 5 in particular assumes you've seen everything before it.
By the end of this article you'll understand:
- What fact tables and dimension tables actually are (and all their sub-types)
- How to pick a grain and why it's the most important decision you'll make
- Star vs. snowflake schemas, and why star almost always wins
- Slowly Changing Dimensions (SCD Types 0, 1, 2, and 3) without falling asleep
- How to write queries against a dimensional model that are actually pleasant to read
There's a companion repo with the full schema, seed data, and exercises for every example below. We'll point at it as we go.
Why Kimball still matters in 2026
You might be wondering if this is all dated. We have dbt. We have columnar cloud warehouses. We have the lakehouse. People keep declaring the death of the warehouse. Isn't dimensional modeling a relic?
No. Here's why.
The modern data stack changed where and how we store data, but it didn't change how humans think about business questions. When someone asks "how did the oat-milk latte do in the Pacific Northwest stores last quarter, compared to the same quarter last year?" — they are asking for a fact (sales) sliced by dimensions (product, region, time). That question has not changed since 1996. It will not change in 2036.
What dbt gave us is a cleaner way to build those fact and dimension tables. It didn't replace the shape of the model. If anything, it made dimensional modeling more accessible, because now anyone with SQL can build a star schema in a few hours.
So: Kimball isn't a legacy thing. It's a thinking tool for making data legible to humans. Let's learn it.
The core problem: operational DB ≠ analytical DB
Meet Bean & Stalk. Their point-of-sale system runs on a transactional database. Tables like orders, order_items, products, customers, payments, inventory_adjustments. Beautifully normalized. Great for the cash register.
Now the owner, Priya, wants a dashboard. "Top drinks by month. Year-over-year growth. Which baristas upsell the most food. Loyalty members who've gone quiet."
You could run those queries against the POS database directly. The first time. Maybe the second. By the fifth report you'll discover:
- The joins are six tables deep.
- A historical price change means today's
$5.25oat-milk latte and last year's$4.75oat-milk latte look like two different products unless you're careful. - Someone updated a customer's email and now you can't reconstruct what was emailed last quarter.
- The CEO's "monthly sales" query takes 40 seconds because it's aggregating across years of transaction rows every time.
The operational database is optimized for writing (taking orders fast). The analytical database should be optimized for reading (answering questions fast). Those two workloads want different shapes.
Dimensional modeling is the shape for the analytical side.
Meet Bean & Stalk
Let's set the scene so the schema makes sense.
- Two stores: Mission St (the original) and Hayes Valley (the new one).
- Drinks and food: espresso drinks, drip coffee, pastries, beans.
- Loyalty program: customers sign up, earn stamps, get a free drink after 10.
- Seasonal menu: the pumpkin spice situation comes and goes.
- Baristas: a small rotating cast.
Now let's model it.
Fact tables are the verbs
A fact table holds measurable, quantitative events. Things that happened. Each row is typically an event at a point in time, expressed as numeric measurements (called measures or facts) plus foreign keys pointing to surrounding dimensions.
If dimensions are the nouns, facts are the verbs. "We sold 2 oat-milk lattes at Mission St on Tuesday." — that's a verb (sold).
There are several common flavors of fact table. Bean & Stalk will eventually have all of them.
1. Transaction fact table
The workhorse. One row per event at its most granular level. For us: one row per line item on an order.
-- Simplified
CREATE TABLE fact_order_line (
order_line_sk BIGSERIAL PRIMARY KEY,
receipt_number BIGINT, -- degenerate dimension
order_date_sk INT REFERENCES dim_date(date_sk),
pickup_date_sk INT REFERENCES dim_date(date_sk),
product_sk INT REFERENCES dim_product(product_sk),
customer_sk INT REFERENCES dim_customer(customer_sk),
store_sk INT REFERENCES dim_store(store_sk),
barista_sk INT REFERENCES dim_barista(barista_sk),
junk_sk INT REFERENCES dim_junk(junk_sk),
quantity INT,
unit_price NUMERIC(8,2),
discount_amount NUMERIC(8,2) DEFAULT 0,
line_total NUMERIC(8,2)
);
Every order line is a row. Two lattes on one receipt? That's one row with quantity = 2. A latte and a muffin on the same receipt? Two rows. Simple.
2. Periodic snapshot fact table
Some questions want a regular photo of the world rather than a stream of events. A periodic snapshot takes a reading at a fixed interval — daily, weekly, monthly.
For Bean & Stalk: a daily snapshot of sales by drink by store. One row per (date, store, product).
CREATE TABLE fact_daily_sales (
daily_sales_sk BIGSERIAL PRIMARY KEY,
snapshot_date_sk INT REFERENCES dim_date(date_sk),
store_sk INT REFERENCES dim_store(store_sk),
product_sk INT REFERENCES dim_product(product_sk),
daily_quantity INT,
daily_revenue NUMERIC(10,2),
transaction_count INT
);
Why bother when we could just recompute from fact_order_line? Three reasons:
- Performance — a year-over-year chart on transaction rows scans millions of rows; the snapshot scans thousands.
- History of truth — if someone deletes an order tomorrow, the snapshot from yesterday still tells you what was reported then.
-
Simplicity — the dashboard query becomes
SELECT ... FROM fact_daily_salesinstead of a 12-way join.
3. Accumulating snapshot fact table
This one is for multi-stage processes that evolve over time. Think: order placed → shipped → delivered. Or for us: loyalty signup → first purchase → 5th purchase → 10th purchase (free drink earned).
One row per entity (a customer's loyalty journey). Columns for each milestone date. You update the same row as milestones are hit, rather than inserting new ones.
CREATE TABLE fact_loyalty_journey (
journey_sk BIGSERIAL PRIMARY KEY,
customer_sk INT REFERENCES dim_customer(customer_sk),
signup_date_sk INT REFERENCES dim_date(date_sk),
first_purchase_date_sk INT REFERENCES dim_date(date_sk),
fifth_purchase_date_sk INT REFERENCES dim_date(date_sk),
tenth_purchase_date_sk INT REFERENCES dim_date(date_sk),
current_status TEXT, -- 'signed_up', 'active', 'reward_earned', 'churned'
signup_to_first_days INT,
first_to_tenth_days INT
);
Note this is fundamentally different from a transaction fact (which never updates) or a periodic snapshot (which inserts new rows on a schedule). Accumulating snapshots are updated in place. That's the tell.
4. Factless fact table
Sounds like a Zen koan. It's actually simple: a fact table with no measures, only foreign keys. It exists to record that something was possible or happened without a number attached.
Bean & Stalk example: drink availability. The pumpkin spice latte is available on certain dates and not others. That "this drink was offered on this day in this store" is a fact worth recording, even though it has no quantity.
CREATE TABLE fact_drink_availability (
drink_availability_sk BIGSERIAL PRIMARY KEY,
product_sk INT REFERENCES dim_product(product_sk),
date_sk INT REFERENCES dim_date(date_sk),
store_sk INT REFERENCES dim_store(store_sk)
-- no measures! the presence of the row IS the fact
);
Now "how many days was the PSL available at Mission St in 2025?" is a trivial COUNT(*).
Quick mental model
| Type | When to use | Bean & Stalk example |
|---|---|---|
| Transaction | Atomic events | Each line on a receipt |
| Periodic snapshot | Regular photos | Daily sales by store/product |
| Accumulating snapshot | Multi-stage journeys | Loyalty signup → 10th drink |
| Factless | Coverage / eligibility | Drink available on a day |
Dimension tables are the nouns
A dimension table holds the descriptive context around the facts — the who, what, where, when, how. Dimensions are how you slice and filter. They tend to be wide (many columns) and short (far fewer rows than fact tables).
Let's meet each kind through Bean & Stalk.
Date dimension (the universal one)
Every dimensional model needs a dim_date. Yes, even in 2026. Yes, even though you could compute EXTRACT(MONTH FROM date) on the fly.
Two reasons. First, business calendar logic (fiscal quarters, holidays, "is this a weekend") is awful to compute and trivial to store. Second, joining on an integer date_sk is faster and more compression-friendly than joining on a timestamp.
CREATE TABLE dim_date (
date_sk INT PRIMARY KEY, -- e.g. 20260813 for 2026-08-13
full_date DATE NOT NULL,
day_of_week TEXT, -- 'Monday'
day_number INT,
month_number INT,
month_name TEXT,
quarter INT,
year INT,
is_weekend BOOLEAN,
holiday_name TEXT
);
The classic trick: role-playing dimensions. Bean & Stalk has both an order date and a pickup date (mobile orders). Same dim_date table, two foreign keys in the fact. In the query, you join dim_date twice with different aliases (order_date and pickup_date).
Product dimension with Slowly Changing Dimensions
Here's where it gets interesting. The dim_product looks like a normal dimension:
CREATE TABLE dim_product (
product_sk BIGSERIAL PRIMARY KEY,
product_id TEXT, -- natural key, e.g. 'OAT_LATTE'
product_name TEXT,
category TEXT, -- 'ESPRESSO_DRINK', 'DRIPO', 'PASTRY'
base_price NUMERIC(8,2),
recipe_notes TEXT,
-- SCD Type 2 columns:
valid_from DATE NOT NULL,
valid_to DATE,
is_current BOOLEAN NOT NULL
);
But Bean & Stalk changes things over time. The oat-milk latte's recipe changed in March (new oat milk vendor). Its price went up in June. How do you model that?
This is the SCD problem. Slowly Changing Dimensions. There are four common strategies. Let's walk through them with the same example.
SCD Type 0 — retain original. Never change the value. The original row is the row. Useful for things that should be immutable, like the date a customer signed up. Bean & Stalk treats product_id this way.
SCD Type 1 — overwrite. Just update the row. Old value is lost. Use this when history doesn't matter. For a typo fix in a product description (Oat Milk Latte → Oat-Milk Latte), Type 1 is fine.
SCD Type 2 — add a new row. Insert a new row with the new values, expire the old row by setting valid_to and is_current = false. This preserves full history. For price and recipe changes, Bean & Stalk uses Type 2.
A Type 2 product history looks like this:
| product_sk | product_id | product_name | base_price | valid_from | valid_to | is_current |
|---|---|---|---|---|---|---|
| 1 | OAT_LATTE | Oat-Milk Latte | 4.75 | 2025-01-01 | 2025-06-14 | false |
| 2 | OAT_LATTE | Oat-Milk Latte | 5.25 | 2025-06-15 | NULL | true |
Now your historical reports use the price that was actually charged at the time, not today's price. This is the whole point.
Laid out on a timeline, the two rows look like this — the fact table always points at whichever product_sk was valid on the day the sale happened, so a March query and an August query silently pick up different rows without needing any date logic of their own:
SCD Type 3 — add a column. Keep the old value in a separate column (previous_base_price, previous_valid_until). Useful when you only care about the previous state, not the full history. Bean & Stalk doesn't bother with Type 3 for products — Type 2 is strictly more powerful — but you'll see it in domains where people genuinely only care about "before vs after" (a plan's previous tier, an employee's previous role).
The takeaway: Type 1 and Type 2 cover ~95% of real-world cases. Don't reach for the others unless you have a specific reason.
Customer dimension
Standard Type 1-ish dimension for Bean & Stalk's loyalty members.
CREATE TABLE dim_customer (
customer_sk BIGSERIAL PRIMARY KEY,
customer_id TEXT,
name TEXT,
email TEXT,
loyalty_number TEXT,
signup_date DATE,
loyalty_tier TEXT -- 'BRONZE', 'SILVER', 'GOLD'
);
If we cared about tracking tier changes over time (Bronze → Silver → Gold), we'd make this Type 2 too.
Store and barista dimensions
dim_store is a small, stable dimension. dim_barista holds employee attributes. Both Type 1.
Junk dimension
This is a fun one. Bean & Stalk has several low-cardinality flags: size (small/medium/large), milk_type (whole/oat/almond/soy/none), syrup_flavor (none/vanilla/caramel/hazelnut), extra_shot (true/false). None of these deserve their own dimension. But putting each one as a column directly on the fact table is fine too — except it clutters things.
A junk dimension combines them into one small table of all observed combinations:
CREATE TABLE dim_junk (
junk_sk BIGSERIAL PRIMARY KEY,
size TEXT, -- SMALL / MEDIUM / LARGE
milk_type TEXT, -- WHOLE / OAT / ALMOND / SOY / NONE
syrup_flavor TEXT, -- NONE / VANILLA / CARAMEL / HAZELNUT
extra_shot BOOLEAN
);
The fact table holds a single junk_sk foreign key. Now all those flags live in one tidy place and you can still slice by them.
Degenerate dimension
A degenerate dimension is a dimension key that has no dimension table — it just lives in the fact. The classic case is a receipt number or order number. There's no dim_receipt; the receipt_number column sits directly on fact_order_line so you can group all the lines of one receipt back together. Simple, useful, slightly weird name.
Choosing the grain
If you take one thing from this article, take this: pick your grain first, before anything else.
The grain is the precise definition of what one row in a fact table represents. Until you can state the grain in one plain sentence, you're not ready to build the table.
For Bean & Stalk's transaction fact, three plausible grains:
- One row per order. Pros: tiny table. Cons: can't analyze individual drinks within an order. The oat-lattes-plus-muffin combo question becomes impossible.
-
One row per order line. Pros: fully flexible — you can always roll up to order level with
SUM(...) GROUP BY receipt_number. Cons: bigger table. (This is what we chose.) - One row per drink modification. Pros: maximally detailed. Cons: enormous table, and "how many lattes did we sell?" requires careful un-nesting. Overkill for a cafe.
The right grain is the most granular level at which a business event occurs that you'd want to analyze separately. For Bean & Stalk that's the order line. Lower than that (sub-line modifications) is overkill; higher than that (whole order) loses detail.
Same logic applies everywhere: an e-commerce fact is usually one row per order line; a payments fact might be one row per transaction; a web analytics fact might be one row per page view. State the grain out loud. If it sounds weird, reconsider.
Star vs. snowflake
You have two layout choices for a dimensional model.
Star schema: the fact table sits in the middle, dimensions radiate out, and each dimension is a flat, denormalized table. One hop from fact to any dimension.
Snowflake schema: dimensions are themselves normalized. dim_product references dim_category, which references dim_department. Three hops to get to department.
Snowflake looks cleaner to a normalized-DB brain. It uses less storage. In the 90s, when storage was expensive, that mattered.
In 2026, use star. Almost always. Reasons:
- Query simplicity. One join, not three. Your dashboard authors will thank you.
- Columnar warehouses love wide, flat dimensions. Compression is excellent. Joins are cheap. Snowflake normalization actively hurts you here.
- Human legibility. A star schema can be read and understood by an analyst in 30 seconds. A snowflake requires tracing keys around.
- Storage is cheap. The thing snowflake optimized for is no longer scarce.
Normalize your operational database. Denormalize your analytical one. That's the whole game.
A real query
Let's put it together. Top-selling drinks by month, with a barista leaderboard for the top drink.
-- Top drinks by month
SELECT
d.month_name,
p.product_name,
SUM(f.quantity) AS total_sold,
SUM(f.line_total) AS revenue
FROM fact_order_line f
JOIN dim_date d ON d.date_sk = f.order_date_sk
JOIN dim_product p ON p.product_sk = f.product_sk
WHERE d.year = 2025
GROUP BY d.month_name, p.product_name
ORDER BY d.month_number, revenue DESC;
That's the whole query. Notice how readable it is. Five joins, all one-hop, clear aliases. That's the star schema paying off.
Now the barista leaderboard for the top drink of the year:
WITH top_drink AS (
SELECT p.product_sk, p.product_name
FROM fact_order_line f
JOIN dim_product p ON p.product_sk = f.product_sk
GROUP BY p.product_sk, p.product_name
ORDER BY SUM(f.line_total) DESC
LIMIT 1
)
SELECT
b.barista_name,
SUM(f.quantity) AS drinks_made,
SUM(f.line_total) AS revenue
FROM fact_order_line f
JOIN dim_barista b ON b.barista_sk = f.barista_sk
JOIN top_drink t ON t.product_sk = f.product_sk
GROUP BY b.barista_name
ORDER BY revenue DESC
LIMIT 10;
Try writing that against the normalized POS schema. It's possible, but it'll take three times the SQL and ten times the thinking.
The full version (with role-playing date dimensions, SCD2-aware joins, and the "as-of" lookup pattern) is in queries.sql in the companion repo.
Common mistakes
These are common. Avoid them.
Duplicate facts in different tables. If
line_totallives infact_order_line, don't also store a precomputeddaily_totalnext to it in a different fact. Compute it at query time, or build a proper snapshot fact. Two sources of the same number always drift.Storing aggregates next to base rows. Adding a
monthly_totalcolumn tofact_order_lineis a Category 5 anti-pattern. It will be wrong by the second week.Forgetting SCD2 on things that change. If your product price changed and you overwrote the row, every historical report is now silently incorrect. Use Type 2.
Putting measures in dimension tables. A "current price" column on
dim_productis fine. A "total units sold last quarter" column ondim_productis a trap — it'll be stale and it muddies the dimension's purpose.Mixing grains in one fact table. One row per order line and one row per daily summary in the same table? No. Either split into two facts or pick the lower grain.
Using natural keys as surrogate keys.
product_idis great as a natural key but don't make it the primary key ofdim_product— Type 2 means you'll have multiple rows perproduct_id. Always use a surrogateproduct_sk.
Exercises
A few quick ones to test your understanding. Hints are hidden — click to expand. Full solutions and more practice are in exercises.sql in the companion repo.
1. Bean & Stalk wants to track which baristas work which shifts. Would you model "shift" as its own fact table, a dimension, or an attribute on the barista? Why?
Hint
Think about whether a shift is an event (verb → fact) or a descriptor (noun → dimension). Could it be both? What questions would each shape let you answer?
2. Write a query that uses role-playing date dimensions to find orders where the pickup date was a different day than the order date (i.e., mobile pre-orders for tomorrow).
Hint
Join dim_date twice with different aliases, once on order_date_sk and once on pickup_date_sk. Then filter where the two full_date values differ.
3. The oat-milk latte's price changed on 2025-06-15. Using the SCD2 product dimension, write a query to compute total revenue for the oat-milk latte where each transaction uses the price that was actually in effect at the time.
Hint
The fact table's product_sk already points at the correct historical row of dim_product (that's the SCD2 magic). You don't need any extra date filtering on the dimension — just sum line_total.
4. Bean & Stalk launches a "drink of the week" promotion. How would you model that using a factless fact table?
Hint
Each row: (product_sk, date_sk, store_sk). No measures. The row's existence is the fact "this drink was promoted on this day at this store."
What's next
This article covered the conceptual fundamentals — the things that show up in every dimensional model. From here, the series gets progressively less textbook and more "here's what actually goes wrong in practice":
- Part 2 applies these fundamentals to SaaS subscriptions — MRR reconstructed as-of arbitrary dates, account hierarchies, and SCD2 doing real work on plan changes.
- Part 3 covers accumulating snapshots that don't behave — orders that split into multiple shipments, carrier webhooks that arrive out of order, and a measure that genuinely can't be summed across days.
- Part 4 is bridge tables — a fact that's legitimately about more than one dimension value at once, and the trap of inventing money the moment you join through one carelessly.
- Part 5 is the capstone: a scenario with no textbook answer, where three stakeholders each have a defensible number for the same deal, and the job is building a model honest enough to tell the truth to all three at once.
Same ideas throughout, meaner problems each time. Start with Part 2 whenever you're ready.
Resources
- Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — the book. Still the best reference.
- dbt — Modeling your data — modern take on dimensional modeling with dbt.
- Analytics Engineering on the dbt blog — patterns and anti-patterns.
- Joseph Machado's Start Data Engineering — solid practical writeups.
If this was useful, the companion repo has the full schema, seed data, and a quiz that tests all of this. Parts 2 through 5 apply everything here to domains that break it in progressively more interesting ways. See you there.
Top comments (0)