Step 2: declaring the grain of fact_orders

Description

This is the module's central lesson. You're going to rebuild fact_orders exactly as foundations left it — same columns, same catalog, same fixed week of forty orders — and load it into DuckDB to run the query that declares the grain with evidence, not intuition: SELECT COUNT(*), COUNT(DISTINCT order_id || '-' || product_id) FROM fact_orders. If those two numbers match, you confirm something precise: every row of fact_orders is already, today, "an order line" — a specific product, sold within a specific order.

Connection to the module. This is step 2 of Kimball's process — declare the grain — and it is, quite properly, the entire module's central executable result. Lessons 6, 7, and 8 take this query's result for granted.

An analogy: the grain isn't "how many," it's "how big is each piece"

Go back to the invoice analogy from this guide's introduction: if you ask someone "how many things are on this invoice?", the question is ambiguous until you clarify the unit. How many invoices? How many lines on the invoice (one per product)? How many individual units, adding up each line's quantity? All three are valid answers to different questions — "3 invoices," "8 lines," "23 units" can all be true about the same pile of paper, depending on what you're counting.

Declaring the grain is, exactly, deciding and verifying which of those units is the one a row of your table represents. It's not a question of "how many rows are there" — that's just COUNT(*) — it's the question of what, unambiguously, each of those rows represents. This lesson answers that question for fact_orders, with a query that verifies it, not an assumption.

Worked example: rebuilding fact_orders and declaring its grain

First, Kiosko's catalog — identical, value for value, to what foundations declared. If you already have kiosko.py from that guide in your working folder, you can reuse it as-is; it's repeated in full here so this guide is self-contained:

# kiosko.py
from dataclasses import dataclass
from datetime import datetime

DIM_STORE = [
    {"store_id": "S01", "store_name": "Kiosko Centro", "city": "Bogota"},
    {"store_id": "S02", "store_name": "Kiosko Norte", "city": "Lima"},
    {"store_id": "S03", "store_name": "Kiosko Sur", "city": "Santiago"},
]

DIM_PRODUCT = [
    {"product_id": "P001", "product_name": "Bottled Water 600ml", "category": "beverages", "unit_cost": 0.40},
    {"product_id": "P002", "product_name": "Energy Bar", "category": "snacks", "unit_cost": 0.60},
    {"product_id": "P003", "product_name": "Instant Coffee Sachet", "category": "beverages", "unit_cost": 0.35},
    {"product_id": "P004", "product_name": "Phone Charger Cable", "category": "electronics", "unit_cost": 2.10},
]


@dataclass
class Order:
    order_id: str
    store_id: str
    product_id: str
    quantity: int
    unit_price: float
    order_ts: datetime


def transform_fact_orders(rows: list[Order], dim_store: list[dict], dim_product: list[dict]) -> list[dict]:
    store_index = {s["store_id"]: s for s in dim_store}
    product_index = {p["product_id"]: p for p in dim_product}
    fact_rows = []
    for order in rows:
        if order.store_id not in store_index:
            raise ValueError(f"unknown store_id: {order.store_id}")
        if order.product_id not in product_index:
            raise ValueError(f"unknown product_id: {order.product_id}")
        fact_rows.append({
            "order_id": order.order_id,
            "store_id": order.store_id,
            "product_id": order.product_id,
            "quantity": order.quantity,
            "unit_price": order.unit_price,
            "revenue": order.quantity * order.unit_price,
            "order_ts": order.order_ts,
        })
    return fact_rows

Now, Kiosko's complete fixed week — the same seven files, orders_2026-08-03.csv through orders_2026-08-09.csv, from foundations, forty orders total — declared as fixed data in Python so this lesson runs without depending on external files:

# raw_orders.py -- Kiosko's fixed week, identical to foundations (modules 1, 2, and 8)
RAW_ORDERS = [
    # Monday 2026-08-03 (8 orders)
    ("ORD-1001", "S01", "P001", 3, 0.55, "2026-08-03T08:14:00"),
    ("ORD-1002", "S01", "P002", 1, 1.20, "2026-08-03T08:20:00"),
    ("ORD-1003", "S02", "P003", 2, 0.75, "2026-08-03T08:31:00"),
    ("ORD-1004", "S01", "P004", 1, 4.50, "2026-08-03T09:02:00"),
    ("ORD-1005", "S03", "P001", 5, 0.55, "2026-08-03T09:15:00"),
    ("ORD-1006", "S02", "P002", 2, 1.20, "2026-08-03T09:47:00"),
    ("ORD-1007", "S03", "P003", 1, 0.75, "2026-08-03T10:05:00"),
    ("ORD-1008", "S01", "P001", 2, 0.55, "2026-08-03T10:22:00"),
    # Tuesday 2026-08-04 (6 orders)
    ("ORD-2001", "S01", "P002", 1, 1.20, "2026-08-04T08:05:00"),
    ("ORD-2002", "S02", "P001", 4, 0.55, "2026-08-04T08:40:00"),
    ("ORD-2003", "S03", "P004", 1, 4.50, "2026-08-04T09:12:00"),
    ("ORD-2004", "S01", "P003", 3, 0.75, "2026-08-04T09:50:00"),
    ("ORD-2005", "S02", "P002", 2, 1.20, "2026-08-04T10:15:00"),
    ("ORD-2006", "S03", "P001", 6, 0.55, "2026-08-04T10:33:00"),
    # Wednesday 2026-08-05 (2 orders)
    ("ORD-3001", "S02", "P004", 2, 4.50, "2026-08-05T08:10:00"),
    ("ORD-3002", "S01", "P001", 1, 0.55, "2026-08-05T08:22:00"),
    # Thursday 2026-08-06 (5 orders)
    ("ORD-4001", "S01", "P001", 4, 0.55, "2026-08-06T08:10:00"),
    ("ORD-4002", "S02", "P003", 2, 0.75, "2026-08-06T08:45:00"),
    ("ORD-4003", "S03", "P002", 1, 1.20, "2026-08-06T09:20:00"),
    ("ORD-4004", "S01", "P004", 1, 4.50, "2026-08-06T09:55:00"),
    ("ORD-4005", "S02", "P001", 3, 0.55, "2026-08-06T10:30:00"),
    # Friday 2026-08-07 (7 orders)
    ("ORD-5001", "S01", "P002", 2, 1.20, "2026-08-07T08:05:00"),
    ("ORD-5002", "S03", "P001", 4, 0.55, "2026-08-07T08:30:00"),
    ("ORD-5003", "S02", "P004", 1, 4.50, "2026-08-07T08:58:00"),
    ("ORD-5004", "S01", "P003", 2, 0.75, "2026-08-07T09:22:00"),
    ("ORD-5005", "S03", "P002", 3, 1.20, "2026-08-07T09:47:00"),
    ("ORD-5006", "S02", "P001", 5, 0.55, "2026-08-07T10:15:00"),
    ("ORD-5007", "S01", "P001", 2, 0.55, "2026-08-07T10:40:00"),
    # Saturday 2026-08-08 (9 orders)
    ("ORD-6001", "S01", "P001", 6, 0.55, "2026-08-08T08:00:00"),
    ("ORD-6002", "S02", "P002", 3, 1.20, "2026-08-08T08:18:00"),
    ("ORD-6003", "S03", "P001", 4, 0.55, "2026-08-08T08:35:00"),
    ("ORD-6004", "S01", "P004", 2, 4.50, "2026-08-08T08:52:00"),
    ("ORD-6005", "S02", "P003", 3, 0.75, "2026-08-08T09:10:00"),
    ("ORD-6006", "S03", "P002", 2, 1.20, "2026-08-08T09:28:00"),
    ("ORD-6007", "S01", "P003", 1, 0.75, "2026-08-08T09:45:00"),
    ("ORD-6008", "S02", "P001", 7, 0.55, "2026-08-08T10:02:00"),
    ("ORD-6009", "S03", "P004", 1, 4.50, "2026-08-08T10:20:00"),
    # Sunday 2026-08-09 (3 orders)
    ("ORD-7001", "S01", "P001", 2, 0.55, "2026-08-09T09:15:00"),
    ("ORD-7002", "S02", "P002", 1, 1.20, "2026-08-09T09:40:00"),
    ("ORD-7003", "S03", "P001", 3, 0.55, "2026-08-09T10:05:00"),
]

And now, the real worked example: build fact_orders, load it into DuckDB, and declare its grain with this module's central query.

# declare_grain.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)
print(f"Total rows rebuilt in fact_orders: {len(fact_orders)}\n")

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("=== Declaring the grain of fact_orders ===")
print(con.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT order_id || '-' || product_id) AS distinct_order_product_lines
    FROM fact_orders
"""))

What to expect. Running python3 declare_grain.py (with pip install duckdb already done), the output is exactly this:

Total rows rebuilt in fact_orders: 40

=== Declaring the grain of fact_orders ===
┌────────────┬──────────────────────────────┐
│ total_rows │ distinct_order_product_lines │
│   int64    │            int64             │
├────────────┼──────────────────────────────┤
│         40 │                           40 │
└────────────┴──────────────────────────────┘

Forty total rows, forty distinct order_id-product_id combinations. The two numbers match exactly, and that match is the evidence — not anyone's word, not an intuition — that today, in Kiosko's fact_orders, every row represents exactly one unique combination of order and product. With that evidence in hand, the formal grain declaration reads as follows:

The grain of fact_orders is: a row represents one product sold within a specific order — an order line.

Notice the exact wording: the grain is declared as "an order line" (order + product), not as "an order" alone. Those are different claims, and even though they give the same number today — because every order Kiosko generated, as foundations built it, contains exactly one product — they aren't the same statement. Lesson 7 of this module shows, with an executed query, exactly why that difference matters even when today's numbers happen to match.

Diagram: what each part of the query measures

flowchart TD
    A["fact_orders: 40 rows"] --> B["COUNT(*)\ncounts EVERY ROW, regardless of content"]
    A --> C["order_id || '-' || product_id\nbuilds a composite key per row"]
    C --> D["COUNT(DISTINCT ...)\ncounts how many UNIQUE combinations exist"]
    B --> E{"Are the two numbers\nequal?"}
    D --> E
    E -->|"YES (40 == 40)"| F["The declared grain (order+product)\nMATCHES the data's reality"]
    E -->|"NO"| G["There are duplicate rows OR the real\ngrain is different from the one declared"]

Going deeper: why the composite key, and not just order_id

Notice a deliberate detail in the query: it didn't use COUNT(DISTINCT order_id) alone — it built a composite key, order_id || '-' || product_id, concatenating both columns with || (the text-concatenation operator in standard SQL, which DuckDB supports directly). Why wasn't order_id enough on its own?

Because order_id alone assumes that every order has a single product — that is, it assumes the answer before verifying it. If fact_orders had, today or in the future, an order with two different product lines, COUNT(DISTINCT order_id) would give a number lower than COUNT(*) (because the same order_id would repeat across two rows), and that difference would be exactly the signal that the real grain is finer than "an order." The composite key order_id || '-' || product_id is the correct way to verify the grain you actually care about — an order line — without assuming beforehand that order and line are the same thing. You're going to see this distinction demonstrated with concrete numbers, on a hypothetical scenario, in lesson 7.

A technical note about the separator: the - inside order_id || '-' || product_id isn't decorative — it prevents a key collision. Without a separator, an order_id="ORD-1" with product_id="P01" would produce the same concatenated text ("ORD-1P01") as an order_id="ORD-1P" with product_id="01" — two different combinations that, with no separator, would look identical. With the - in between, that collision (unlikely with Kiosko's format, but real in general) disappears.

Common mistakes

Declaring the grain as "an order" instead of "an order line." What happens: someone, seeing that COUNT(*) and COUNT(DISTINCT order_id) give the same number today (40 and 40), concludes the grain is "an order," without using the composite key. Why it happens: with Kiosko's current data, both declarations produce the same number, so the mistake doesn't show up with this specific week of data. How to spot it: if your grain declaration uses the word "order" without mentioning "product" or "line," and your verification only used order_id, you didn't test the correct hypothesis — you tested a stronger hypothesis than what this data can actually support. How to fix it: always use the finest composite key your schema allows you to verify — order_id || '-' || product_id, as this lesson did — even if the result matches a simpler declaration. Lesson 7 shows, with numbers, why this distinction isn't pedantry.

Trusting COUNT(*) alone, with no COUNT(DISTINCT ...) to compare against. What happens: someone runs SELECT COUNT(*) FROM fact_orders, sees 40, and declares the grain with no further verification. Why it happens: COUNT(*) is the simplest possible query, and it feels like enough evidence. How to spot it: COUNT(*) alone can't detect duplicate rows — if fact_orders had, due to an ingestion bug, the same order line repeated twice, COUNT(*) would still report the total physical row count, with no signal that duplication exists. How to fix it: always compare COUNT(*) against COUNT(DISTINCT <grain key>) — if they're equal, there are no duplicates on that key; if COUNT(*) is higher, you have exact duplicates that module 5's deduplication lesson will teach you to resolve.

Running the query on partial data and generalizing the result. What happens: someone runs the grain query on only one day of the week (say, just Monday, 8 rows) and declares the grain confident in that partial result. Why it happens: testing with less data is faster, and eight rows look like enough evidence at a glance. How to spot it: if your verification query didn't run over the complete week (forty rows, all seven days), your evidence is partial — a duplication bug that only shows up on Saturday (the day with the most orders) would go completely unnoticed if you only checked Monday. How to fix it: this lesson's query runs over all forty rows of the complete week, exactly as it should — verifying the grain on a subset is never enough evidence about the whole set.

Exercises

Exercise 1 — Verify the grain with a third query. Using the fact_orders already built in DuckDB by the worked example, write an additional query that confirms there's no row with quantity <= 0 — a different check from the grain one, but just as important before trusting any downstream analysis.

print(con.sql("SELECT COUNT(*) AS rows_with_invalid_quantity FROM fact_orders WHERE quantity <= 0"))

┌────────────────────────────┐
│ rows_with_invalid_quantity │
│           int64            │
├────────────────────────────┤
│                          0 │
└────────────────────────────┘

Exercise 2 — Calculate the grain by store. Using fact_orders, write a query that confirms the declared grain (an order line) holds within each store separately: for each store_id, compare COUNT(*) against COUNT(DISTINCT order_id || '-' || product_id).

print(con.sql("""
    SELECT
        store_id,
        COUNT(*) AS total_rows,
        COUNT(DISTINCT order_id || '-' || product_id) AS distinct_lines
    FROM fact_orders
    GROUP BY store_id
    ORDER BY store_id
"""))

┌──────────┬────────────┬────────────────┐
│ store_id │ total_rows │ distinct_lines │
│ varchar  │   int64    │     int64      │
├──────────┼────────────┼────────────────┤
│ S01      │         16 │             16 │
│ S02      │         13 │             13 │
│ S03      │         11 │             11 │
└──────────┴────────────┴────────────────┘

Exercise 3 — Explain why 40 == 40 doesn't prove there are no additional rejected rows. The main query's two numbers (total_rows and distinct_order_product_lines) match at 40. In 2-3 sentences, explain why that match confirms there are no duplicates, but doesn't, on its own, confirm that the forty rows are all the real orders Kiosko processed that week.

Summary and next step

In this lesson you declared the grain of fact_orders with evidence, not intuition: you rebuilt the complete table — forty orders, Kiosko's fixed week — in DuckDB, and ran SELECT COUNT(*), COUNT(DISTINCT order_id || '-' || product_id) FROM fact_orders, confirming both numbers match at 40. The resulting formal declaration: a row of fact_orders represents an order line — a product sold within a specific order, not "an order" alone, even though both readings give the same number today.

Before moving on you should be able to: write the grain-verification query used in this lesson from memory; explain why a composite key (order_id || '-' || product_id) is used instead of order_id alone; and recite fact_orders's formal grain declaration, word for word.

With the grain now declared and verified, lesson 6 resolves steps 3 and 4 of Kimball's process — dimensions and facts — with the precise definition that goes beyond the "first glance" version you already saw in foundations.

Resources

• Kimball Group — "Four-Step Dimensional Design Process" — the source that defines "declare the grain" as the central step this lesson executes on Kiosko. kimballgroup.com/.../four-4-step-design-process. In English.
• DuckDB — official Python client documentation, used in this lesson to build and query fact_orders. duckdb.org/docs/current/clients/python/overview. In English.
• DuckDB — text function documentation, including concatenation with ||, used here to build the grain's composite key. duckdb.org/docs/current/sql/functions/text. In English.
• Python — official dataclasses documentation, reused unchanged from foundations to represent Order. docs.python.org/3/library/dataclasses.html. In English.