Mini-project: Kiosko's grain declaration

Description

This project closes the module by integrating all four complete steps: you chose the business process (lesson 4), declared and verified the grain with a real query (lesson 5), precisely classified facts and dimensions (lesson 6), and proved that declaration survives a hypothetical business change (lesson 7). What's left is bringing it all together into a single formal deliverable: the grain document that the seven modules that follow — and anyone new who joins Kiosko's data team — are going to take for granted without re-litigating it.

The project has three parts. First, you rebuild and verify fact_orders end to end, exactly like in lesson 5, but now as the final delivery step, not an isolated exercise. Second, you document the four steps in a formal, reusable structure for the rest of the guide. Third, you verify against foundations: you confirm that this rebuilt fact_orders, cell by cell, produces exactly the same numbers as the original pipeline — the final proof that rebuilding the fact in this guide introduced no silent difference.

Connection to the module. This project introduces no new concept — it's the final integration of the seven previous lessons, with one piece of polish: packaging the grain declaration as a formal data structure (GRAIN_DECLARATION), instead of leaving it scattered across several lessons.

An analogy: the delivery record, not one more essay

Every lesson in this module was a rehearsal of a different piece of Kimball's process. This project is the delivery record: the single document that gathers, in one place, the final decision about which business process was modeled, what each row represents, which dimensions and facts it has, and the evidence that all of that was verified against the real data — exactly what a serious data team would put in writing before anyone else starts building on top of that fact.

The material: everything this module built, in a single flow

You need, in the same folder: kiosko.py (with DIM_STORE, DIM_PRODUCT, Order, transform_fact_orders, from lesson 5) and raw_orders.py (the fixed week of forty orders, also from lesson 5).

The reference solution, verified

Part 1 — Rebuild and verify the grain, as the final deliverable

# grain_declaration.py
from datetime import datetime

import duckdb

from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS

orders = [
    Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
          unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
    for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)

con = duckdb.connect()
con.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany(
    "INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
      r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders],
)

print("=== Kiosko: grain declaration, final deliverable ===\n")
print("Part 1 -- grain verification")
grain_check = con.sql("""
    SELECT COUNT(*) AS total_rows,
           COUNT(DISTINCT order_id || '-' || product_id) AS distinct_lines
    FROM fact_orders
""").fetchone()
total_rows, distinct_lines = grain_check
print(f"total_rows={total_rows} distinct_lines={distinct_lines}")
assert total_rows == distinct_lines, "the declared grain does not match the real data"
print("Verification: total_rows == distinct_lines -> OK, the declared grain holds\n")

This first part isn't an isolated exercise — it's the guarantee that backs everything that follows: if the grain didn't hold up under evidence, documenting a formal declaration in Part 2 wouldn't make any sense.

Part 2 — Document the four steps as a formal structure

GRAIN_DECLARATION = {
    "business_process": "Selling a product at a Kiosko store",
    "grain": "One row represents a product sold within a specific order (an order line)",
    "dimensions": ["dim_store", "dim_product", "order_id (degenerate dimension)"],
    "facts": {
        "additive": ["quantity", "revenue"],
        "captured_non_additive": ["unit_price"],
    },
    "verified_row_count": total_rows,
}

print("Part 2 -- Kiosko's formal grain declaration")
for key, value in GRAIN_DECLARATION.items():
    print(f"{key}: {value}")

Notice that GRAIN_DECLARATION isn't a decorative object — each of its five fields corresponds exactly to a lesson in this module: business_process comes from lesson 4, grain from lesson 5, dimensions and facts from lesson 6, and verified_row_count is the numeric evidence from Part 1 itself. This structure is, literally, the "grain contract" that modules 2 through 8 of this guide are going to take for granted without re-litigating it.

Part 3 — Verify against foundations, number by number

print("\nPart 3 -- cross-check against foundations")
print(con.sql("SELECT ROUND(SUM(revenue), 2) AS total_revenue FROM fact_orders"))
print(con.sql("""
    SELECT store_id, COUNT(*) AS order_count, SUM(quantity) AS total_units, ROUND(SUM(revenue), 2) AS revenue
    FROM fact_orders
    GROUP BY store_id
    ORDER BY store_id
"""))

What to expect. Running the complete python3 grain_declaration.py (all three parts together), the output is exactly this:

=== Kiosko: grain declaration, final deliverable ===

Part 1 -- grain verification
total_rows=40 distinct_lines=40
Verification: total_rows == distinct_lines -> OK, the declared grain holds

Part 2 -- Kiosko's formal grain declaration
business_process: Selling a product at a Kiosko store
grain: One row represents a product sold within a specific order (an order line)
dimensions: ['dim_store', 'dim_product', 'order_id (degenerate dimension)']
facts: {'additive': ['quantity', 'revenue'], 'captured_non_additive': ['unit_price']}
verified_row_count: 40

Part 3 -- cross-check against foundations
┌───────────────┐
│ total_revenue │
│    double     │
├───────────────┤
│        106.15 │
└───────────────┘
┌──────────┬─────────────┬─────────────┬─────────┐
│ store_id │ order_count │ total_units │ revenue │
│ varchar  │    int64    │   int128    │ double  │
├──────────┼─────────────┼─────────────┼─────────┤
│ S01      │          16 │          34 │    38.3 │
│ S02      │          13 │          37 │    38.8 │
│ S03      │          11 │          31 │   29.05 │
└──────────┴─────────────┴─────────────┴─────────┘

Stop at Part 3, because it's the check that gives the whole project its confidence: 106.15 in total revenue, and 38.3/38.8/29.05 by store — identical, down to the last cent, to the numbers you already saw in foundations' module 8. This confirms something none of the seven earlier lessons proved this directly: rebuilding fact_orders in this guide, from fixed data declared in Python instead of reading the original CSV files, introduced no difference at all — it's, mathematically, the same fact, just now with its grain formally declared and verified, something the original fact_orders from foundations never had.

Diagram: the four steps, closed out with evidence

flowchart TD
    A["Step 1 (L4): business_process =\n'Selling a product at a Kiosko store'"] --> B
    B["Step 2 (L5): grain =\n'An order line', VERIFIED with COUNT(*)==COUNT(DISTINCT...)"] --> C
    C["Steps 3-4 (L6): dimensions =\n[dim_store, dim_product, degenerate order_id]\nfacts = {additive, captured}"] --> D
    D["L7: the grain survives a\nhypothetical business change (verified)"] --> E
    E["GRAIN_DECLARATION\nthe formal contract this project delivers"]
    E --> F["Modules 2-8: take this contract\nfor granted without re-litigating it"]

Closing out lesson 2's checklist, piece by piece

Only one of the twelve rows in lesson 2's checklist ended up marked resolved — and it's exactly the one that had to be resolved first: without a declared and verified grain, none of the remaining eleven pieces would have a reliable foundation to build on.

Common mistakes

Delivering the grain declaration without Part 1's verification. What happens: someone, in a hurry to show GRAIN_DECLARATION as the final result, jumps straight to Part 2 without running Part 1's verification first. Why it happens: Part 2's data structure looks more presentable as "the result," and the verification query feels like a disposable preliminary step. How to spot it: if your final deliverable includes no executed evidence that total_rows == distinct_lines, you're documenting a claim, not a verified declaration — exactly the trap lesson 1 of this module already warned about. How to fix it: Part 1 of this project isn't optional — it's the guarantee that makes everything that follows in Part 2 and Part 3 trustworthy.

Copying GRAIN_DECLARATION without understanding each field. What happens: someone reuses the GRAIN_DECLARATION structure in their own project, with their own data, without being able to explain what each field means or where its value comes from. Why it happens: copying a structure that "already works" is faster than building it from scratch, but it can be done without real understanding. How to spot it: if you can't explain, without looking at the code, why facts has two categories (additive and captured_non_additive) instead of a single list, you need to revisit lesson 6. How to fix it: every field in this structure has a full lesson behind it that justifies it — before reusing it in a project of your own, confirm you can explain each field in your own words.

Considering "modeling done" once the grain is declared. What happens: someone finishes this project, sees a complete, verified GRAIN_DECLARATION, and concludes they now have a real dimensional warehouse. Why it happens: a well-made grain declaration feels like a complete achievement, and it's easy to forget it's only the first step of eight modules. How to spot it: if you can't name, from memory, at least three of the eleven pieces still pending in this lesson's checklist table, you need to reread lesson 2. How to fix it: fact_orders still uses natural keys, still has no dim_date, still has no historization — this project closes the first step of eight, not the whole guide.

Exercises

Exercise 1 — Extend GRAIN_DECLARATION with a verification-date field. Without using datetime.now() (forbidden in this guide to keep reproducibility), add a fixed verified_on field to GRAIN_DECLARATION with the date on which, narratively, Kiosko closed this analysis — use the last day of the data week, "2026-08-09".

GRAIN_DECLARATION["verified_on"] = "2026-08-09"
print(f"verified_on: {GRAIN_DECLARATION['verified_on']}")

verified_on: 2026-08-09

Exercise 2 — Verify the grain by product, not just by store. Part 3 of the project verified revenue by store. Write the equivalent query grouped by product_id, and confirm the numbers match foundations' gold report (P001: 16 orders, 61 units, revenue 33.55; P002: 10, 18, 21.6; P003: 7, 14, 10.5; P004: 7, 9, 40.5).

print(con.sql("""
    SELECT product_id, COUNT(*) AS order_count, SUM(quantity) AS total_units, ROUND(SUM(revenue), 2) AS revenue
    FROM fact_orders
    GROUP BY product_id
    ORDER BY product_id
"""))

┌────────────┬─────────────┬─────────────┬─────────┐
│ product_id │ order_count │ total_units │ revenue │
│  varchar   │    int64    │   int128    │ double  │
├────────────┼─────────────┼─────────────┼─────────┤
│ P001       │          16 │          61 │   33.55 │
│ P002       │          10 │          18 │    21.6 │
│ P003       │           7 │          14 │    10.5 │
│ P004       │           7 │           9 │    40.5 │
└────────────┴─────────────┴─────────────┴─────────┘

Exercise 3 — Explain, from memory, one Kiosko product's complete journey through the eight modules that follow. Without looking at the guide's design, pick one of Kiosko's four products (say, P004, the phone charger cable) and describe in a 4-6 sentence paragraph what's going to happen to it across this guide's eight modules: how it's represented today, and what it gains in each following module (star schema, snowflake, historization, deduplication, accumulating snapshot, junk dimension).

Summary and next step: the end of module 1

With this mini-project you close out module 1 completely. You applied, start to finish, Ralph Kimball's four-step process to fact_orders: you chose the business process (selling a product at a Kiosko store), declared and verified its grain with a query executed in DuckDB (an order line — total_rows == distinct_lines, 40 == 40), precisely classified its dimensions and facts (foreign keys, degenerate dimension, additive and captured measures), and proved that declaration survives a hypothetical business change. The result — GRAIN_DECLARATION, verified, with total revenue 106.15 identical to foundations — is the formal contract the rest of this guide takes for granted.

You took the first step of an eight-module journey: fact_orders is still, column for column, exactly the same fact foundations left behind — what changed is that you now know, with evidence rather than intuition, what each of its rows represents.

Where you go next. Module 2 — the-star-schema-and-conformed-dimensions — builds, on top of this already-declared grain, Kiosko's complete star schema: surrogate keys, dim_date, and the three JOINs that rebuild fact_orders connected to all three dimensions at once.

Resources

• Kimball Group — "Four-Step Dimensional Design Process" — the complete source for the process this project closes out, applied start to finish on Kiosko. kimballgroup.com/.../four-4-step-design-process. In English.
• "The Data Warehouse Toolkit", 3rd edition (Kimball & Ross, Wiley) — the canonical reference behind the full vocabulary used in this module, from the grain to the classification of facts and dimensions. wiley.com/en-jp/The+Data+Warehouse+Toolkit. In English.
• DuckDB — official Python client documentation, the tool that ran every verification in this module. duckdb.org/docs/current/clients/python/overview. In English.
• Joe Reis & Matt Housley, Fundamentals of Data Engineering (O'Reilly, 2022) — the same framework that carried foundations start to finish, now applied to this guide's first dimensional modeling step. oreilly.com/library/view/fundamentals-of-data/9781098108298. In English.