Module 1: From Flat Tables To Dimensional Models

Module introduction: from flat tables to dimensional models

Next →

Why this module exists

data-engineering-foundations-guide ended with a real pipeline: fact_orders, dim_store, and dim_product, built across three layers — bronze, silver, gold — and verified twice to confirm that re-running it didn't duplicate a single row. That pipeline works. Kiosko's store manager received her sales report by store and by product, with revenue calculated correctly. If your only goal were "have something queryable," the story already ended there.

But foundations was honest about what it left unresolved, and said so in almost these exact words: it gave Kiosko one fact and two flat dimensions, the minimum to have something queryable, not a modeling methodology. dim_store and dim_product use natural keys, with no surrogate key, because "they're fixed catalogs that don't change throughout the guide." There's no dim_date. There's no second fact table for the app's clickstream (events, with its page_view/add_to_cart/purchase), even though that data already exists, waiting. And above all: nowhere in foundations is there a sentence that says, with precision, exactly what a row of fact_orders represents. It was said informally, in an analogy ("one order line"), but it was never declared as a design decision verified with a query.

This guide exists to close that debt, starting exactly at that last point. Before building a complete star schema (module 2), before deciding between normalizing or flattening (module 3), before historizing a dimension that changes (module 4) — before anything else — there's a question that the industry's most-used dimensional modeling method insists on answering first, and that this module 1 answers with Kiosko: what, exactly, does a row represent? That question has a technical name: it's called declaring the grain, and it's the first instinct that separates someone who designs a warehouse with judgment from someone who just joins tables until the queries "work."

Connection to the module. This module doesn't build a single new table — fact_orders, dim_store, and dim_product are still, column for column, exactly what foundations declared. What this module builds is the vocabulary and process you'll reuse in each of the seven modules that follow: Ralph Kimball's four-step process (business process → grain → dimensions → facts), applied for the first time, end to end, over the fact_orders you already have.

An analogy: the notebook ledger and formal accounting

Think of a small market stall, the kind that keeps its books in a notebook. The owner jots down each sale however it comes out: sometimes "sold 3 waters," sometimes "customer bought water and a bar, $2.35 total," sometimes just the day's total with no detail. The notebook works — at the end of the month, adding up pages, the owner roughly knows how much they made — but if an accountant asked "exactly how many units of water did you sell on Tuesday, August 4th?", the owner would have to reread every line of the notebook, guessing what each entry represents, because they never decided on a fixed unit of record ahead of time.

Formal accounting doesn't have that problem, because it starts backward: before recording the first sale, it decides what a ledger entry is — a specific transaction, with a date, an amount, and an account, always the same shape — and from then on, every row in the general ledger is exactly that, no exceptions. Any future question ("how much did we sell in water on Tuesday?") gets answered by summing entries, with confidence, because the unit of record was never ambiguous.

fact_orders, as foundations left it, already works more like the notebook than like formal accounting: you know each row is "roughly a sale," but nobody wrote down, anywhere, the exact sentence stating what a row is and verified with a query that the real data satisfies that sentence. This module turns that informal intuition into a formal, verified declaration — the first step of any serious dimensional model.

Worked example: the map of the four steps, before applying them

Before writing this module's first file, it's worth seeing the whole destination — the four steps you're about to walk through, and which lesson in this module each one shows up in.

# kimball_steps_preview.py
STEPS = [
    (1, "Select the business process", "Which Kiosko business event you are going to model first"),
    (2, "Declare the grain", "What, exactly, a row of fact_orders represents"),
    (3, "Identify the dimensions", "What context that grain can be filtered or grouped by"),
    (4, "Identify the facts", "Which numeric columns of the grain make sense to sum"),
]

print("=== Kimball's four-step process, applied to Kiosko in this module ===\n")
for number, name, question in STEPS:
    print(f"Step {number}: {name}")
    print(f"        {question}\n")

print("This module 1 completes the four steps over fact_orders, the fact that already exists.")
print("Modules 2 through 8 go deeper into each piece this first pass deliberately leaves shallow.")

What to expect. Running python3 kimball_steps_preview.py, the output is exactly this:

=== Kimball's four-step process, applied to Kiosko in this module ===

Step 1: Select the business process
        Which Kiosko business event you are going to model first

Step 2: Declare the grain
        What, exactly, a row of fact_orders represents

Step 3: Identify the dimensions
        What context that grain can be filtered or grouped by

Step 4: Identify the facts
        Which numeric columns of the grain make sense to sum

This module 1 completes the four steps over fact_orders, the fact that already exists.
Modules 2 through 8 go deeper into each piece this first pass deliberately leaves shallow.

No Kiosko numbers yet — this lesson is, deliberately, the map before the territory, the same way module 8 of foundations opened with its own bronze/silver/gold map. But notice the exact order of the four steps: grain before dimensions, dimensions before facts. That order isn't arbitrary, and lesson 3 of this module explains, with evidence, why reversing it produces broken models.

Diagram: where you were, where you're going to be

flowchart LR
    subgraph Foundations["foundations (previous guide)"]
        A["fact_orders, dim_store, dim_product\none fact, two flat dimensions\nno grain formally declared"]
    end

    subgraph M1["This module (1 of 8)"]
        B["Step 1: Select the business process\n(lesson 4)"]
        C["Step 2: Declare the grain\n(lesson 5, EXECUTED)"]
        D["Step 3-4: Dimensions + Facts\n(lesson 6, precise definition)"]
        E["Grain declared and verified\n(lesson 8, project)"]
    end

    subgraph Resto["Modules 2-8 (the rest of the guide)"]
        F["Complete star schema,\nSCD, snowflake vs OBT,\naccumulating snapshot..."]
    end

    A --> B --> C --> D --> E --> F

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: why the guide starts by declaring the grain
L2        What foundations left flat, exactly, and why that was correct then
L3        Kimball's four-step process, explained with a standalone example
L4        Step 1: selecting Kiosko's business process (sales orders)
L5        Step 2: declaring the grain of fact_orders -- EXECUTED with DuckDB
L6        Steps 3-4: facts vs dimensions, with Kimball's precise definition
L7        What changes -- in practice -- once the grain stops being implicit
L8        Project: Kiosko's formal grain declaration, verified

Lessons 2 and 3 are purely conceptual — they give you the vocabulary and the process before applying it. Lessons 4, 5, and 6 apply Kimball's four steps, one by one, over Kiosko's real fact_orders — lesson 5 is the only one in this module that runs a new query against real data, and it is, quite properly, the module's central result. Lesson 7 steps back and asks what changes in practice once the grain stops being an intuition and becomes a verified declaration. And lesson 8 closes with the mini-project: Kiosko's formal grain declaration, ready for the seven modules that follow to take for granted.

Going deeper: why this guide doesn't start by building the star schema

It's tempting, opening a guide that promises "real dimensional modeling," to want to see a diagram with several connected tables from the first lesson. This guide resists that temptation on purpose, and it's worth explaining why.

A complete star schema — with dim_date, with conformed dimensions, with surrogate keys — is a consequence of earlier decisions, not a starting point. If you build the tables before declaring what each row of your fact represents, you end up with a model that "looks good" in a diagram but that nobody can use with confidence to answer a specific business question, because nobody knows, precisely, what summing a column of that fact means. The Kimball Group — the source of the four-step process this module teaches — puts it this way: the answer to "what is the grain" determines everything that follows, and it's decided collaboratively, weighing both the business's needs and the reality of the available source data.

That's why this module, quite deliberately, doesn't touch dim_date and doesn't build a single new table. It takes the fact_orders that already exists, and asks it the question foundations never asked with precision: what, exactly, does a row represent, and how do you know for certain, not by intuition? Answering that question well is what gives module 2 — where you'll build the complete star schema — a solid foundation underneath it.

Common mistakes

Assuming that, since foundations "already modeled" Kiosko, this module is repetition. What happens: someone who finished foundations sees fact_orders, dim_store, and dim_product again in this module and assumes there's nothing new to learn, because the schema is identical. Why it happens: the schema, column for column, genuinely doesn't change in this module — the change is in the process applied over that schema, not in the schema itself. How to spot it: if you finish this module unable to recite, from memory, the exact sentence that declares the grain of fact_orders — not "a sale," but lesson 5's precise sentence — you skipped what this module actually teaches. How to fix it: pay attention to lesson 5, not the columns — this module's value is in the process of declaring and verifying, not in tables you already know.

Jumping straight to module 2, because "the star schema is the interesting part." What happens: someone impatient to see conformed dimensions and dim_date tries to start at module 2, treating this module 1 as a formality. Why it happens: a complete star schema feels more "advanced" and visually more finished than a single sentence declaring a grain. How to spot it: if, while building a star schema in module 2, you can't explain, without hesitating, why you chose that specific combination of dimensions to accompany fact_orders, it's because you skipped Kimball's steps 2 and 3, which this module teaches first. How to fix it: this guide's eight-module order isn't chronological by accident — each one depends on a decision the previous one already verified. This module 1 is the foundation the seven that follow rest on.

Confusing "declaring the grain" with "writing a comment in the code." What happens: someone writes # grain: one row per order line as a comment in their Python script and considers the work done, without verifying with any query that the real data satisfies that claim. Why it happens: a comment feels like enough documentation, and writing a verification query seems like an unnecessary extra step. How to spot it: if your "grain declaration" was never compared against a real COUNT(*) over the real data, it's an assumption shaped like a comment, not a verified declaration. How to fix it: lesson 5 of this module is going to show you, with a query actually run, why verification matters — and why "I believe the grain is X" and "I confirmed the grain is X" are two completely different claims.

Exercises

Exercise 1 — Recall foundations' debt, without looking back. Without rereading foundations' capstone, write from memory a list of at least three things fact_orders/dim_store/dim_product don't have yet (for example: do they have a surrogate key? is there a calendar table? what happens if a product's price changes?). Then compare your list against this lesson's "Why this module exists" section.

See solution

A reasonable list, taken directly from this lesson: (1) dim_store and dim_product use a natural key, not a surrogate key — there's no way to historize a change without breaking existing references; (2) there's no dim_date, so any analysis by day, week, or month would have to be derived directly from order_ts, without a reusable calendar dimension; (3) there's no second fact table for the clickstream (events), even though that data already exists; (4) nowhere was it declared, with precision and verified with a query, exactly what a row of fact_orders represents — it was said informally, in an analogy. If your list has at least three of these four points, you have a clear picture of the debt this guide starts closing.

Exercise 2 — Order the four steps from memory. Without looking at this lesson's worked example, write Kimball's four process steps in the correct order, using only their English names (Select the business process, Declare the grain, Identify the dimensions, Identify the facts).

See solution
  1. Select the business process
  2. Declare the grain
  3. Identify the dimensions
  4. Identify the facts

If you swapped the order of steps 2 and 3 — thinking dimensions get decided before the grain — pay special attention to lesson 3 of this module: it's precisely the most common mistake when learning this process for the first time, and the lesson explains with a concrete example why that specific order matters.

Exercise 3 — Explain the analogy in your own words. Using this lesson's notebook-ledger-and-formal-accounting analogy, explain in 2-3 sentences why foundations' fact_orders looks more like the notebook than like formal accounting, even though it's technically already a structured CSV table.

See solution

Foundations' fact_orders already has a fixed structure — columns, types, a schema consistent across every row — so it's not literally a messy notebook. But it resembles one in what matters: nobody declared, before building it, a precise sentence about what a row represents, nor verified that sentence against the real data with a query — the unit of record was understood by intuition ("a sale"), not by an explicit, verified design decision. Formal accounting — and real dimensional modeling — demands declaring that unit beforehand, not inferring it afterward by looking at the data.

Summary and next step

This module closes the first explicit debt foundations left behind: declaring, formally and with verification, exactly what a row of fact_orders represents. You won't build a single new table — you'll apply Ralph Kimball's four-step process (business process → grain → dimensions → facts) to the table that already exists, starting with the step most teams skip and that costs them the most later: declaring the grain before writing a single extra line of SQL.

Before moving on you should be able to: name Kimball's four process steps in order; explain, in your own words, why the grain is declared before the dimensions; and say from memory which Kiosko table is the subject of this entire module.

Lesson 2 picks up exactly where foundations left off: it names, precisely and without exaggeration, what it left flat on purpose and why that was the right decision for that guide's scope — the honest starting point before building on top of it.

Resources

Next →What foundations left flat, and why