Module 1: From Flat Tables To Dimensional Models

What changes when the grain is explicit

Next →

Description

So far, this guide has declared the grain of fact_orders with a query and a formal sentence. This lesson answers a more uncomfortable question: does it actually matter, in practice, to have done it this way? What would have broken if someone, in a hurry, had declared the grain as "an order" instead of "an order line" — the trap lesson 5 already warned about, but that doesn't show up today because both readings give the same number (40)? This lesson builds the scenario where the difference stops being theoretical, with a query actually run.

Connection to the module. This lesson doesn't change Kiosko's real fact_orders — it's still, exactly, lesson 5's forty rows — it adds, temporarily and in a controlled way, a hypothetical order with two product lines, to demonstrate with numbers why lesson 5's precise declaration matters even when, today, it matches a simpler declaration.

An analogy: the insurance you never used, until you needed it

Someone who pays for car insurance for ten years without ever having an accident might reasonably wonder if that expense was worth it. The answer doesn't depend on whether they had an accident — it depends on whether, the day they had one, the insurance would have responded correctly. A poorly arranged policy, with vaguely declared coverage, can go ten years without anyone noticing the problem — until the day of the crash, when the imprecise declaration ("basic coverage" instead of "full coverage, with the exact detail of what it covers") ends up costing far more than expected.

Declaring the grain as "an order line" instead of "an order" is exactly that insurance: today, with Kiosko selling a single product per order, both declarations produce the same number, and the difference feels academic. This lesson is the "simulated crash" — the controlled scenario where, if Kiosko started allowing multiple products in a single order (perfectly reasonable for a real convenience store), the imprecise declaration would stop answering correctly, and the precise one would keep working with no change at all.

Worked example: a hypothetical order with two product lines

Start from lesson 5's real fact_orders, already loaded into DuckDB with its forty rows. Now, add — explicitly and in a controlled way, not as part of Kiosko's real data — a hypothetical order: a customer buying two different products in a single visit, something Kiosko's current point of sale doesn't generate today, but a perfectly plausible business extension.

# hypothetical_multiline_order.py -- continues over lesson 5's con and fact_orders
print("=== Grain BEFORE the hypothetical scenario (real fact_orders, 40 rows) ===")
print(con.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT order_id) AS distinct_order_ids,
        COUNT(DISTINCT order_id || '-' || product_id) AS distinct_order_product_lines
    FROM fact_orders
"""))

# Hypothetical scenario: a customer buys P001 and P002 in the SAME order (ORD-9001)
# Fixed and deterministic -- not real Kiosko data, a controlled extension
con.execute("""
    INSERT INTO fact_orders VALUES
    ('ORD-9001', 'S01', 'P001', 2, 0.55, 1.10, '2026-08-10T08:00:00'),
    ('ORD-9001', 'S01', 'P002', 1, 1.20, 1.20, '2026-08-10T08:00:00')
""")

print("\n=== Grain AFTER adding a hypothetical multi-line order ===")
print(con.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT order_id) AS distinct_order_ids,
        COUNT(DISTINCT order_id || '-' || product_id) AS distinct_order_product_lines
    FROM fact_orders
"""))

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

=== Grain BEFORE the hypothetical scenario (real fact_orders, 40 rows) ===
┌────────────┬────────────────────┬──────────────────────────────┐
│ total_rows │ distinct_order_ids │ distinct_order_product_lines │
│   int64    │       int64        │            int64             │
├────────────┼────────────────────┼──────────────────────────────┤
│         40 │                 40 │                           40 │
└────────────┴────────────────────┴──────────────────────────────┘

=== Grain AFTER adding a hypothetical multi-line order ===
┌────────────┬────────────────────┬──────────────────────────────┐
│ total_rows │ distinct_order_ids │ distinct_order_product_lines │
│   int64    │       int64        │            int64             │
├────────────┼────────────────────┼──────────────────────────────┤
│         42 │                 41 │                           42 │
└────────────┴────────────────────┴──────────────────────────────┘

There's the difference, with real numbers. After adding ORD-9001 with its two lines, total_rows climbs to 42 (40 + 2, correct: two physical rows were added). But distinct_order_ids only climbs to 41 (40 + 1, because ORD-9001 is a single order_id, repeated across two rows). And distinct_order_product_lines climbs to 42 — exactly matching total_rows.

There's the concrete proof lesson 5 promised: if you had declared the grain using only order_id ("a row represents an order"), this check would fail — total_rows (42) would no longer match distinct_order_ids (41) — and that discrepancy would be the signal that the real grain is no longer "an order," but something finer. The correct declaration — order_id || '-' || product_id, "an order line" — still matches perfectly (42 == 42), without you having to change a single word of lesson 5's original declaration. A well-declared grain survives a business change; a poorly declared one breaks on the first case it didn't anticipate.

Diagram: the same grain, verified across two scenarios

flowchart TD
    subgraph Escenario1["Real scenario (40 rows, today)"]
        A1["total_rows = 40"]
        A2["distinct order_id = 40"]
        A3["distinct order_id+product_id = 40"]
        A1 -.matches.-> A2
        A1 -.matches.-> A3
    end

    subgraph Escenario2["Hypothetical scenario (+1 multi-line order)"]
        B1["total_rows = 42"]
        B2["distinct order_id = 41 -- NO LONGER MATCHES"]
        B3["distinct order_id+product_id = 42 -- STILL MATCHES"]
        B1 -.breaks.-> B2
        B1 -.holds.-> B3
    end

    Escenario1 -->|"add ORD-9001\nwith 2 lines"| Escenario2

Going deeper: why this isn't an artificial exercise

It's worth being honest about something: today, in Kiosko's real fact_orders, this scenario never happens — the point of sale foundations designed generates one order per product line, so order_id and the order_id-product_id combination always match. Someone could reasonably ask whether this lesson solves a problem that doesn't actually exist.

The honest answer is: it doesn't exist today, but it's exactly the kind of change that happens all the time in real production systems, with no advance warning. A point of sale that gets updated to allow a "basket with several products" in a single transaction, a source system that switches vendors and brings a slightly different format, a product team that decides to group related purchases under a shared session identifier — any of these changes, common in the real life of a warehouse, would silently alter the grain of a table that never precisely declared what it expected. If the table has downstream queries (dashboards, reports, other pipelines) that assumed, without verifying it, that order_id identified a unique row, those consumers would start producing incorrect results — silently, with no visible error, exactly the most expensive kind of bug to find in production.

This connects directly to two modules that follow. In module 5, you're going to learn to detect real duplicates with ROW_NUMBER()/QUALIFY — a different situation from this one (there, the problem is a row repeated by mistake, not a new, legitimate row that changes the grain), but the same discipline of "verify with a query, don't assume" is what carries both techniques. And in module 7, you're going to build validate_gold_schema(), a function that compares expected columns against real columns before publishing any gold table — this same lesson's spirit, carried into an automated, permanent check instead of a manual, one-time one.

Common mistakes

Thinking this scenario "really broke" fact_orders. What happens: someone, after running the worked example, worries because their fact_orders now has 42 rows instead of 40, and doesn't know how to "revert" the change. Why it happens: this lesson's INSERT modifies the current session's in-memory DuckDB connection, and that can feel like a permanent change. How to spot it: if you close the DuckDB connection (con.close()) or simply re-run lesson 5's complete script from scratch, fact_orders goes back to having exactly 40 rows — this lesson's INSERT never touched any file on disk. How to fix it: treat this example for what it is, a controlled in-memory experiment — if you want to keep working with Kiosko's real fact_orders after this lesson, re-run lesson 5's declare_grain.py on a fresh connection.

Concluding the lesson proves Kiosko "should" allow multi-line orders. What happens: someone interprets this lesson as a business recommendation — that Kiosko should change its point of sale to allow several products per order. Why it happens: the hypothetical example feels, given its detail, like a real proposal. How to spot it: if your takeaway from this lesson is about Kiosko's business instead of about data modeling, you drifted from the point. How to fix it: this lesson's point is exclusively technical — showing that a precise grain declaration survives a hypothetical change that an imprecise declaration wouldn't survive. It is not, in any sense, a recommendation about how Kiosko's business should operate.

Assuming that declaring the grain precisely "costs more work" and isn't worth it if it matches today. What happens: someone argues that, since COUNT(DISTINCT order_id) and COUNT(DISTINCT order_id || '-' || product_id) give the same number today, it isn't worth writing the more precise version — it's "extra work with no immediate benefit." Why it happens: the cost of writing the composite key is real and visible today; the benefit — surviving a future change — is invisible until the change happens. How to spot it: if your reasoning is "it gives the same number anyway," you're optimizing for the present without considering the cost of a silent bug down the road. How to fix it: the cost of writing order_id || '-' || product_id instead of order_id is minimal — one more concatenation function — the cost of not doing it, the day the real grain changes without anyone noticing, is much higher — incorrect dashboards, business decisions based on badly aggregated numbers. This lesson exists precisely to make that future cost visible today.

Exercises

Exercise 1 — Repeat the experiment with a three-line order. Modify the worked example to add a different hypothetical order, ORD-9002, with three distinct product lines (choose your own products and quantities, keeping them fixed and deterministic). Run the grain-verification query and confirm which numbers change and which don't.

See solution 
con.execute("""
    INSERT INTO fact_orders VALUES
    ('ORD-9002', 'S02', 'P001', 1, 0.55, 0.55, '2026-08-10T09:00:00'),
    ('ORD-9002', 'S02', 'P002', 1, 1.20, 1.20, '2026-08-10T09:00:00'),
    ('ORD-9002', 'S02', 'P003', 1, 0.75, 0.75, '2026-08-10T09:00:00')
""")

print(con.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT order_id) AS distinct_order_ids,
        COUNT(DISTINCT order_id || '-' || product_id) AS distinct_order_product_lines
    FROM fact_orders
"""))

Expected output (continuing on the table that already had ORD-9001 from the worked example, now with 42 rows):

┌────────────┬────────────────────┬──────────────────────────────┐
│ total_rows │ distinct_order_ids │ distinct_order_product_lines │
│   int64    │       int64        │            int64             │
├────────────┼────────────────────┼──────────────────────────────┤
│         45 │                 42 │                           45 │
└────────────┴────────────────────┴──────────────────────────────┘

total_rows climbs to 45 (42 + 3), distinct_order_ids only climbs to 42 (41 + 1, because the three new lines share the same order_id), and distinct_order_product_lines climbs to 45 — again, matching total_rows. The pattern holds with a three-line order exactly as it did with a two-line one: the grain declaration by order_id just keeps breaking further (the gap between total_rows and distinct_order_ids grows), while the declaration by order line keeps holding with no adjustment at all.

Exercise 2 — Calculate how much a poorly designed report would "break." Imagine a report (hypothetical, don't build it) that calculated "average revenue per order" by dividing SUM(revenue) by COUNT(DISTINCT order_id). Using the worked example's numbers (after adding ORD-9001), explain in 2-3 sentences whether that report would still be correct under the multi-line scenario, and why.

See solution

COUNT(DISTINCT order_id) is still the correct metric for "number of orders" even in the multi-line scenario — in fact, it's exactly the question that column does answer correctly: how many distinct transactions there were, regardless of how many lines each has. The "average revenue per order" report would still be correct, because it divides total revenue by the real number of transactions (41 after adding ORD-9001), not by the number of lines. The error would only show up if someone used COUNT(DISTINCT order_id) expecting it to represent the number of product lines sold — that's the metric COUNT(DISTINCT order_id || '-' || product_id) answers correctly, and COUNT(DISTINCT order_id) doesn't. This exercise's lesson: every count query should be used for the question it actually answers, and that question always depends on the exact grain being counted.

Exercise 3 — Argue why foundations' undeclared grain "worked the same" throughout that whole guide. Foundations never declared the grain with a query like lesson 5's, and yet its pipeline worked correctly from start to finish. In 2-3 sentences, explain why that was possible, and why it wouldn't be a safe guarantee for a warehouse that keeps growing.

See solution

Foundations worked without formally declaring the grain because, throughout that entire guide, the real data never violated the implicit assumption — every order always had exactly one product, so "an order" and "an order line" matched the whole time, and the ambiguity never showed up. That's not a guarantee it would keep working: it was, at bottom, good luck with the data, not a verified property of the design. A real warehouse, receiving data from source systems that change over time, can't depend on that luck holding — declaring and verifying the grain with a query, as this guide has taught since lesson 5, is the difference between trusting something will keep being true and knowing, with evidence, that it's true today, with a way to check again tomorrow.

Summary and next step

In this lesson you demonstrated, with a query run over a controlled hypothetical scenario, why lesson 5's precise grain declaration — "an order line," not "an order" — matters in practice, not just in vocabulary. Adding an order with two product lines broke the match between total_rows and distinct_order_ids (42 versus 41), but it didn't break the match between total_rows and distinct_order_product_lines (42 versus 42) — concrete proof that a well-declared grain survives changes a poorly declared one wouldn't survive.

Before moving on you should be able to: explain, with your own numbers, the difference between the two ways of declaring fact_orders's grain; reproduce this lesson's experiment with a different hypothetical order; and argue why "it gives the same number today" isn't a sufficient reason to prefer the less precise declaration.

With the grain declared, verified, and stress-tested against a hypothetical scenario, lesson 8 — this module's mini-project — brings Kimball's four complete steps together into a single, formal deliverable: Kiosko's grain declaration, documented and verified start to finish.

Resources

← PreviousSteps 3-4: facts vs dimensions, a precise definitionNext →Mini-project: Kiosko's grain declaration