What foundations left flat, and why

Description

Before adding a single new idea, this lesson takes inventory: what fact_orders/dim_store/dim_product have today, exactly, and what they're missing compared to a real dimensional model. This isn't a criticism of foundations — the decision to leave it flat was correct for that guide's scope, and this lesson explains why — it's the honest starting point you need before building on top of it.

Connection to the module. This lesson doesn't run the module's central query — that comes in lesson 5 — but it does build, with real code, the checklist you'll use as a map for the rest of the guide: what Kiosko is missing, and which module resolves each point.

An analogy: the habitable house, not the finished house

A contractor who hands over a "habitable" house isn't the same as one who hands over a "finished" house. A habitable house has walls, a roof, working electricity and water — you can live in it, and for a family that needs to move in now, it's exactly what they asked for. But "habitable" isn't "finished": it has no custom closets, no landscaped garden, no automatic irrigation system. Nobody built those things by oversight — the contractor made an explicit scope decision: "this is what gets delivered in this phase, this is left for later," and that decision was the right one for the budget and the time available.

Foundations' fact_orders/dim_store/dim_product is Kiosko's habitable house: it has the essentials to live in — a reliable, safely-re-runnable sales report — but it doesn't have the "custom closets" of a real dimensional warehouse: surrogate keys, historization, a calendar dimension, more than one fact. This lesson walks through the house, room by room, pointing out what's missing and in which phase (which module of this guide) each piece gets built.

Worked example: the inventory of what exists today

First, recall exactly what foundations left behind — without adding or removing a single column:

# kiosko_today.py
FACT_ORDERS_COLUMNS = ["order_id", "store_id", "product_id", "quantity", "unit_price", "revenue", "order_ts"]

DIM_STORE_COLUMNS = ["store_id", "store_name", "city"]
DIM_PRODUCT_COLUMNS = ["product_id", "product_name", "category", "unit_cost"]

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},
]

print("=== What Kiosko already has, inherited from foundations ===\n")
print(f"fact_orders: {len(FACT_ORDERS_COLUMNS)} columns -> {FACT_ORDERS_COLUMNS}")
print(f"dim_store:   {len(DIM_STORE_COLUMNS)} columns -> {DIM_STORE_COLUMNS} ({len(DIM_STORE)} rows)")
print(f"dim_product: {len(DIM_PRODUCT_COLUMNS)} columns -> {DIM_PRODUCT_COLUMNS} ({len(DIM_PRODUCT)} rows)")

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

=== What Kiosko already has, inherited from foundations ===

fact_orders: 7 columns -> ['order_id', 'store_id', 'product_id', 'quantity', 'unit_price', 'revenue', 'order_ts']
dim_store:   3 columns -> ['store_id', 'store_name', 'city'] (3 rows)
dim_product: 4 columns -> ['product_id', 'product_name', 'category', 'unit_cost'] (4 rows)

This is exactly the same schema foundations left in its module 8 — not one column more, not one less. From here on, the rest of the lesson doesn't change any of these columns: it uses them as-is, and compares what's missing around them.

Now, the checklist — a list of the pieces a real dimensional model needs, with what Kiosko has today marked explicitly:

# dimensional_checklist.py
CHECKLIST = [
    ("fact_orders grain declared and verified with a query", False, "M1 (this guide)"),
    ("Surrogate keys in the dimensions", False, "M2"),
    ("dim_date: a reusable calendar dimension", False, "M2"),
    ("Conformed dimensions (shared across more than one fact)", False, "M2"),
    ("Snowflake: normalized dimensions when it pays off", False, "M3"),
    ("Historization of a dimension that changes (SCD)", False, "M4"),
    ("Point-in-time join against a historized dimension", False, "M5"),
    ("Explicit deduplication of repeated rows", False, "M5"),
    ("Accumulating snapshot fact table (session funnel)", False, "M6"),
    ("Cumulative table design (activity with rolling windows)", False, "M6"),
    ("Junk dimension (grouped low-cardinality flags)", False, "M7"),
    ("More than one fact coexisting with shared dimensions", False, "M7"),
]

print("=== What Kiosko still needs to become a real dimensional warehouse ===\n")
for item, exists_today, resolved_in in CHECKLIST:
    mark = "[x]" if exists_today else "[ ]"
    print(f"{mark} {item:62} -> resolved in {resolved_in}")

pending = sum(1 for _, exists_today, _ in CHECKLIST if not exists_today)
print(f"\nTotal pending pieces today: {pending} of {len(CHECKLIST)}")

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

=== What Kiosko still needs to become a real dimensional warehouse ===

[ ] fact_orders grain declared and verified with a query           -> resolved in M1 (this guide)
[ ] Surrogate keys in the dimensions                               -> resolved in M2
[ ] dim_date: a reusable calendar dimension                        -> resolved in M2
[ ] Conformed dimensions (shared across more than one fact)        -> resolved in M2
[ ] Snowflake: normalized dimensions when it pays off              -> resolved in M3
[ ] Historization of a dimension that changes (SCD)                -> resolved in M4
[ ] Point-in-time join against a historized dimension              -> resolved in M5
[ ] Explicit deduplication of repeated rows                        -> resolved in M5
[ ] Accumulating snapshot fact table (session funnel)              -> resolved in M6
[ ] Cumulative table design (activity with rolling windows)        -> resolved in M6
[ ] Junk dimension (grouped low-cardinality flags)                 -> resolved in M7
[ ] More than one fact coexisting with shared dimensions           -> resolved in M7

Total pending pieces today: 12 of 12

Twelve pending pieces, zero resolved — and that's exactly what's expected when opening this module, not a warning sign. Notice something important: the checklist's first line — "grain declared and verified" — is the one this module marks as resolved by the end of lesson 8. The other eleven are, on purpose, left for the modules that follow. No module in this guide tries to resolve more than what belongs to it.

Diagram: Kiosko's habitable house, room by room

┌─────────────────────────────────────────────────────────────┐
│  KIOSKO TODAY (inherited from foundations)                    │
│                                                                 │
│  fact_orders (7 cols)      dim_store (3 cols)                 │
│  order_id, store_id,       store_id (natural PK)               │
│  product_id, quantity,     store_name, city                    │
│  unit_price, revenue,                                          │
│  order_ts                  dim_product (4 cols)                │
│                             product_id (natural PK)             │
│                             product_name, category, unit_cost   │
│                                                                 │
│  One fact. Two dimensions. Natural keys. No dim_date.           │
│  No historization. No formally declared grain.                 │
└─────────────────────────────────────────────────────────────┘
                              │
                              │  this module declares the grain
                              v
┌─────────────────────────────────────────────────────────────┐
│  KIOSKO BY THE END OF THIS GUIDE (modules 2-8)                 │
│  Star schema + dim_date + surrogate keys + SCD-2 +              │
│  snowflake/OBT + accumulating snapshot + cumulative design +    │
│  junk dimension + formal Medallion contracts                    │
└─────────────────────────────────────────────────────────────┘

Going deeper: why "flat" was the right decision for foundations

It's worth being fair to foundations, because it's easy, in hindsight, to see a list of twelve missing pieces and think that guide "did things wrong." It didn't. Foundations' scope was to teach the complete data lifecycle — generation, storage, ingestion, transformation, serving — with a real pipeline, end to end, runnable on a laptop. Adding surrogate keys, dim_date, and SCD-2 to that guide would have meant teaching two different disciplines at once — the batch pipeline and dimensional modeling in depth — diluting both.

The discipline that best separates someone who designs software with judgment from someone who just piles on features is knowing how to say, precisely, "this isn't part of this scope, and here's why" — exactly what foundations did, and exactly what the "NOT in scope" section of this guide's design keeps doing in every module. A dim_store with a natural key, with no historization, is a correct design decision when stores don't change names within the guide's scope — and it becomes an incorrect decision the day they do change, and the model has no way to represent it. This guide exists because that day has already arrived: you're going to see, in module 4, exactly that scenario with dim_product, when a product's price genuinely changes.

Common mistakes

Treating the list of twelve pieces as "everything that needs fixing right now." What happens: someone, seeing this lesson's checklist, wants to add dim_date, surrogate keys, and SCD-2 all at once, in this very lesson, ignoring the modules' order. Why it happens: seeing a complete list of gaps triggers the urge to resolve all of them immediately. How to spot it: if you finish this lesson with code already attempting to build dim_date or a surrogate key, you got ahead of yourself — that is, literally, lesson 4 of module 2. How to fix it: each item on the list has its own specific module for a pedagogical reason — each one depends on concepts the earlier modules haven't taught yet; resolve them in the order the guide proposes, not the order that feels most urgent to you.

Thinking "flat" means "badly designed" in every context. What happens: someone generalizes the lesson and concludes that any table without a surrogate key or historization is badly designed, regardless of context. Why it happens: it's easy to turn "this is missing for Kiosko, for this purpose" into a universal rule ("you must always have surrogate keys"). How to spot it: if your reasoning doesn't mention the specific context — data volume, whether the dimension changes over time, who consumes the model — you're applying a rule without judgment. How to fix it: this lesson's "going deeper" section says it explicitly — a design decision is correct or incorrect depending on context, never in the abstract. You'll see this same idea repeat in module 3, when you compare a star schema against a wide table: neither shape is "the correct one" without knowing the use case.

Skipping the checklist because "you already know intuitively what's missing." What happens: someone with previous SQL or BI-tool experience assumes they already know every gap in a flat model, and doesn't run this lesson's script. Why it happens: concepts like "surrogate key" or "conformed dimension" can sound familiar by name, even if they've never been applied precisely to a concrete case. How to spot it: if you can't name, without looking, the exact twelve items on the checklist and which module resolves each one, your intuition isn't as complete as you think. How to fix it: run the script, read the full list once, and keep it as a reference — you'll come back to it at the end of every module in this guide, marking each piece as resolved.

Exercises

Exercise 1 — Explain why "grain declared" is the checklist's first line, not any of the other eleven. Without literally repeating the "going deeper" section, explain in 2-3 sentences why declaring the grain has to be resolved before, say, adding surrogate keys or building dim_date.

Exercise 2 — Count the pieces by module. Using the worked example's checklist, count how many pending pieces belong to each module (M2, M3, M4, M5, M6, M7), and confirm that the total sum, plus M1's piece, adds up to twelve.

from collections import Counter

CHECKLIST = [
    ("M1 (this guide)"), ("M2"), ("M2"), ("M2"), ("M3"), ("M4"),
    ("M5"), ("M5"), ("M6"), ("M6"), ("M7"), ("M7"),
]

counts = Counter(CHECKLIST)
for module in ["M1 (this guide)", "M2", "M3", "M4", "M5", "M6", "M7"]:
    print(f"{module}: {counts[module]} piece(s)")

print(f"\nTotal: {sum(counts.values())}")

M1 (this guide): 1 piece(s)
M2: 3 piece(s)
M3: 1 piece(s)
M4: 1 piece(s)
M5: 2 piece(s)
M6: 2 piece(s)
M7: 2 piece(s)

Total: 12

Exercise 3 — Argue whether foundations should have included SCD-2 from the start. In 2-3 sentences, using this lesson's "going deeper" section, argue why it would have been a mistake for foundations to include SCD-2 (historization with valid_from/valid_to) in its own module 4, instead of leaving it for this guide.

Summary and next step

In this lesson you took inventory, without adding or removing anything: fact_orders (7 columns), dim_store (3 columns), and dim_product (4 columns), exactly as foundations left them, and a list of twelve pieces a real dimensional model needs that Kiosko still doesn't have — each one assigned to a specific module in this guide. You also understood why leaving the model flat was the right decision for foundations' scope, not a mistake to correct with embarrassment.

Before moving on you should be able to: recite fact_orders's seven columns from memory; name at least four of the checklist's twelve pending pieces; and explain why "flat" isn't synonymous with "badly designed" without knowing the context.

Lesson 3 leaves the inventory behind and gets into the method: Ralph Kimball's four-step process, explained first with a small, self-contained example — not Kiosko yet — so you see the whole pattern before applying it to fact_orders in lessons 4 and 5.

Resources

• Kimball Group — "Four-Step Dimensional Design Process" — the reference behind why each piece of this checklist has a specific resolution order. kimballgroup.com/.../four-4-step-design-process. In English.
• Joe Reis & Matt Housley, Fundamentals of Data Engineering (O'Reilly, 2022) — the same book that carried foundations' entire scope, useful here for understanding why a well-defined scope is a design decision, not a limitation. oreilly.com/library/view/fundamentals-of-data/9781098108298. In English.
• Python — official collections.Counter documentation, used in this lesson's exercise 2 to count pieces by module. docs.python.org/3/library/collections.html#collections.Counter. In English.