Assembling Kiosko's first real star

Description

This is the module's central lesson — the star schema's equivalent of what lesson 5 of module 1 was for the grain. All the pieces already exist: fact_orders (inherited unchanged), dim_store and dim_product with surrogate keys (lesson 3), dim_date freshly built (lesson 4). This lesson joins them, for the first time, with the three JOINs that form a complete star schema — and verifies, with the same evidence-based discipline from module 1, that this assembly loses or duplicates no row at all: forty before, forty after.

Connection to the module. This lesson delivers the entire module's central executable result, the one this guide's design names explicitly: fact_orders rebuilt with the three JOINs (dim_store, dim_product, dim_date), with a row count identical to module 1's.

An analogy: the single-connection airport

Think of an airport designed as a hub: from the central terminal, any destination is a single flight away — you never have to lay over at another airport to reach any destination in the network. That's exactly a well-built star schema's promise: from fact_orders — the central terminal — reaching any store, product, or date attribute takes exactly one JOIN, never two, never an intermediate table to pass through first.

This lesson is the first real flight from that terminal: not a blueprint of the airport (you already saw that in lesson 2), but the itinerary actually executed, with real passengers — fact_orders's forty rows — arriving at their three destinations (dim_store, dim_product, dim_date) without losing anyone along the way and without anyone arriving duplicated.

Worked example: fact_orders + the three JOINs

Rebuild the star's four tables, exactly as they stood after lessons 1 through 6 of this module, and assemble the complete JOIN.

# assemble_star.py
from datetime import date, timedelta, datetime

import duckdb

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

DAY_NAMES = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]


def generate_date_dim(start_date: str, end_date: str) -> list[dict]:
    start = date.fromisoformat(start_date)
    end = date.fromisoformat(end_date)
    if end < start:
        raise ValueError(f"end_date ({end_date}) is before start_date ({start_date})")
    rows = []
    current = start
    while current <= end:
        weekday_index = current.weekday()
        rows.append({
            "date_key": int(current.strftime("%Y%m%d")),
            "calendar_date": current,
            "day_of_week": DAY_NAMES[weekday_index],
            "month": current.month,
            "quarter": (current.month - 1) // 3 + 1,
            "year": current.year,
            "is_weekend": weekday_index >= 5,
        })
        current += timedelta(days=1)
    return rows


# --- fact_orders, rebuilt identical to M1 ---
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],
)

# --- dim_store, dim_product with surrogate key ---
con.execute("CREATE TABLE dim_store_natural (store_id VARCHAR, store_name VARCHAR, city VARCHAR)")
con.executemany("INSERT INTO dim_store_natural VALUES (?, ?, ?)",
                 [(s["store_id"], s["store_name"], s["city"]) for s in DIM_STORE])
con.execute("""
    CREATE TABLE dim_store AS
    SELECT ROW_NUMBER() OVER (ORDER BY store_id) AS store_key, store_id, store_name, city
    FROM dim_store_natural
""")

con.execute("CREATE TABLE dim_product_natural (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
con.executemany("INSERT INTO dim_product_natural VALUES (?, ?, ?, ?)",
                 [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT])
con.execute("""
    CREATE TABLE dim_product AS
    SELECT ROW_NUMBER() OVER (ORDER BY product_id) AS product_key, product_id, product_name, category, unit_cost
    FROM dim_product_natural
""")

# --- dim_date ---
dim_date_rows = generate_date_dim("2026-08-01", "2026-08-31")
con.execute("""
    CREATE TABLE dim_date (
        date_key INTEGER, calendar_date DATE, day_of_week VARCHAR,
        month INTEGER, quarter INTEGER, year INTEGER, is_weekend BOOLEAN
    )
""")
con.executemany(
    "INSERT INTO dim_date VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["date_key"], r["calendar_date"], r["day_of_week"], r["month"],
      r["quarter"], r["year"], r["is_weekend"]) for r in dim_date_rows],
)

print("=== Before assembly: fact_orders count alone ===")
print(con.sql("SELECT COUNT(*) AS fact_orders_rows FROM fact_orders"))

print("=== Kiosko's first complete star: fact_orders + 3 JOINs ===")
star_query = """
    SELECT
        f.order_id,
        s.store_name,
        p.product_name,
        d.day_of_week,
        f.quantity,
        ROUND(f.revenue, 2) AS revenue
    FROM fact_orders f
    JOIN dim_store   s ON f.store_id = s.store_id
    JOIN dim_product p ON f.product_id = p.product_id
    JOIN dim_date    d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
    ORDER BY f.order_id
"""
print(con.sql(star_query + " LIMIT 5"))

print("=== Grain check: the JOIN must not lose or duplicate rows ===")
print(con.sql(f"SELECT COUNT(*) AS joined_rows FROM ({star_query}) t"))

print("=== Explicit comparison: fact_orders alone vs fact_orders + 3 JOINs ===")
before = con.sql("SELECT COUNT(*) FROM fact_orders").fetchone()[0]
after = con.sql(f"SELECT COUNT(*) FROM ({star_query}) t").fetchone()[0]
print(f"fact_orders unjoined: {before} rows")
print(f"fact_orders + dim_store + dim_product + dim_date: {after} rows")
assert before == after, "the join lost or duplicated rows -- the star is broken"
print("Verification: {} == {} -> OK, the join did not lose or duplicate a single row".format(before, after))

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

=== Before assembly: fact_orders count alone ===
┌──────────────────┐
│ fact_orders_rows │
│      int64       │
├──────────────────┤
│               40 │
└──────────────────┘

=== Kiosko's first complete star: fact_orders + 3 JOINs ===
┌──────────┬───────────────┬───────────────────────┬─────────────┬──────────┬─────────┐
│ order_id │  store_name   │     product_name      │ day_of_week │ quantity │ revenue │
│ varchar  │    varchar    │        varchar        │   varchar   │  int32   │ double  │
├──────────┼───────────────┼───────────────────────┼─────────────┼──────────┼─────────┤
│ ORD-1001 │ Kiosko Centro │ Bottled Water 600ml   │ Monday      │        3 │    1.65 │
│ ORD-1002 │ Kiosko Centro │ Energy Bar            │ Monday      │        1 │     1.2 │
│ ORD-1003 │ Kiosko Norte  │ Instant Coffee Sachet │ Monday      │        2 │     1.5 │
│ ORD-1004 │ Kiosko Centro │ Phone Charger Cable   │ Monday      │        1 │     4.5 │
│ ORD-1005 │ Kiosko Sur    │ Bottled Water 600ml   │ Monday      │        5 │    2.75 │
└──────────┴───────────────┴───────────────────────┴─────────────┴──────────┴─────────┘

=== Grain check: the JOIN must not lose or duplicate rows ===
┌─────────────┐
│ joined_rows │
│    int64    │
├─────────────┤
│          40 │
└─────────────┘

=== Explicit comparison: fact_orders alone vs fact_orders + 3 JOINs ===
fact_orders unjoined: 40 rows
fact_orders + dim_store + dim_product + dim_date: 40 rows
Verification: 40 == 40 -> OK, the join did not lose or duplicate a single row

Forty rows before assembly, forty after — the same evidence-based verification discipline you already learned in module 1, now applied to the complete star instead of a single table. This result isn't a coincidence: it's the direct consequence of two guarantees you already had beforehand. First, store_id and product_id in fact_orders always point to a valid row in dim_store/dim_product — foundations already validated this in its own pipeline, and transform_fact_orders() verifies it again with a raise ValueError if anything doesn't line up. Second, every order_ts among the forty orders falls within the August 2026 range dim_date fully covers — no date "falls off" the calendar. An inner JOIN (INNER JOIN, the default type the bare JOIN keyword uses) only produces an output row when it finds a match on both sides — and since both guarantees hold for all forty rows, none gets lost.

Diagram: the three JOINs, one by one

flowchart LR
    FO["fact_orders\n40 rows\nstore_id, product_id, order_ts"]

    FO -->|"JOIN ON store_id"| DS["dim_store\n3 rows"]
    FO -->|"JOIN ON product_id"| DP["dim_product\n4 rows"]
    FO -->|"JOIN ON date_key derived\nfrom order_ts"| DD["dim_date\n31 rows"]

    DS --> R["fact_orders_star\n40 rows -- verified"]
    DP --> R
    DD --> R

Going deeper: why the JOIN against dim_date needs a conversion, and the others don't

Notice something asymmetric in this lesson's query: the JOIN against dim_store and dim_product directly compares two text columns (f.store_id = s.store_id), but the JOIN against dim_date needs an explicit conversion: CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key. The reason is about types: order_ts is a complete TIMESTAMP — date and time, down to the second — while date_key is an INTEGER representing only the date, with no time. You can't compare a TIMESTAMP directly against an INTEGER — you first need to extract only the date part of the timestamp (strftime(f.order_ts, '%Y%m%d'), which produces text like "20260803"), and then convert that text to an integer so it matches date_key's type.

This conversion has an important consequence, worth making explicit: the JOIN against dim_date deliberately discards the time of each order. ORD-1001, with order_ts = 2026-08-03T08:14:00, joins against dim_date's row for all of 2026-08-03 — regardless of whether the order happened at 8:14 in the morning or at 11:59 at night. This is correct for dim_date's grain (one row per day, not per second), and it's exactly why order_ts stays unchanged in fact_orders — for any analysis that does need the exact time, like "what time of day does Kiosko sell the most?", the query would use order_ts directly, not dim_date.

An alternative you might consider — worth naming as a potential mistake, not a recommendation — would be comparing CAST(f.order_ts AS DATE) = d.calendar_date instead of building date_key from order_ts. It technically also works, and on some engines it can even be more readable. This lesson uses the date_key comparison because it's specifically the practice a production JOIN against a calendar dimension usually prefers: comparing integers is, generally speaking, cheaper for the engine than comparing dates or timestamps, and lesson 3 of module 3 — when you compare a JOIN's cost with EXPLAIN — is going to revisit this same idea with execution-plan evidence, not just convention.

Common mistakes

Using LEFT JOIN "for safety," without verifying afterward. What happens: someone, with the idea of "never losing a row under any circumstance," changes all three JOINs to LEFT JOIN, reasoning that this way fact_orders never loses rows even if some dimension had a problem. Why it happens: LEFT JOIN feels like the "safer" option — it keeps every row from the left side, whether or not it finds a match. How to spot it: a LEFT JOIN that finds no match doesn't fail or warn — it simply fills the dimension's columns with NULL, silently. If your referential integrity is already guaranteed (as in Kiosko), INNER JOIN and LEFT JOIN produce the same result — but if it ever stopped being guaranteed, the LEFT JOIN would hide the problem instead of making it visible with a count discrepancy. How to fix it: use INNER JOIN when you expect — and have already verified — that referential integrity holds; reserve LEFT JOIN for cases where losing a row from the left side would itself be a worse mistake than seeing NULL in the result, and always verify afterward with a count, no matter which JOIN type you use.

Comparing order_ts directly against calendar_date with no type conversion. What happens: someone writes JOIN dim_date d ON f.order_ts = d.calendar_date, expecting the engine to "understand" it should compare only the date part. Why it happens: in some languages or engines, comparisons between similar types resolve automatically without an error, so it's easy to assume it always works that way. How to spot it: if your JOIN against dim_date returns zero matching rows — even when you know the dates exist in both tables — it's almost certainly a type problem: a TIMESTAMP with a time other than midnight is never literally equal to a DATE, even though they represent "the same day" to a human. How to fix it: always convert explicitly before comparing — CAST(f.order_ts AS DATE) = d.calendar_date, or this lesson's date_key pattern — never assume the engine will resolve the ambiguity for you.

Not verifying the count after the JOIN, trusting that "if it didn't error, it's fine." What happens: someone runs the complete JOIN, sees the query run with no SQL error, and assumes the result is correct, without comparing the row count before and after. Why it happens: the absence of a syntax error feels like sufficient confirmation everything went well. How to spot it: a badly built JOIN — for example, joining on the wrong column that happens to match several rows — can execute perfectly, with no error at all, and still duplicate rows silently, inflating any sum you calculate afterward. How to fix it: this lesson's explicit verification (before == after, with an assert that would fail loudly if they didn't match) isn't an optional step — it's the only way to have evidence, not just hope, that the JOIN did exactly what you expected.

Exercises

Exercise 1 — Confirm INNER JOIN and LEFT JOIN give the same result today. Rewrite the worked example's query using LEFT JOIN instead of JOIN in all three cases, and compare the resulting count against the original INNER JOIN — concrete proof that, when referential integrity is guaranteed, both JOIN types produce the same row count.

inner_count = con.sql("""
    SELECT COUNT(*) FROM fact_orders f
    JOIN dim_store s ON f.store_id = s.store_id
    JOIN dim_product p ON f.product_id = p.product_id
    JOIN dim_date d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
""").fetchone()[0]

left_count = con.sql("""
    SELECT COUNT(*) FROM fact_orders f
    LEFT JOIN dim_store s ON f.store_id = s.store_id
    LEFT JOIN dim_product p ON f.product_id = p.product_id
    LEFT JOIN dim_date d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
""").fetchone()[0]

print(f"INNER JOIN: {inner_count} rows")
print(f"LEFT JOIN:  {left_count} rows")

INNER JOIN: 40 rows
LEFT JOIN:  40 rows

Exercise 2 — Calculate revenue by product category, through the star. Using the JOIN against dim_product (with no need for the other two), write a query that groups fact_orders by category and calculates total revenue and total units per category.

print(con.sql("""
    SELECT p.category, ROUND(SUM(f.revenue), 2) AS revenue, SUM(f.quantity) AS total_units
    FROM fact_orders f
    JOIN dim_product p ON f.product_id = p.product_id
    GROUP BY p.category
    ORDER BY p.category
"""))

┌─────────────┬─────────┬─────────────┐
│  category   │ revenue │ total_units │
│   varchar   │ double  │   int128    │
├─────────────┼─────────┼─────────────┤
│ beverages   │   44.05 │          75 │
│ electronics │    40.5 │           9 │
│ snacks      │    21.6 │          18 │
└─────────────┴─────────┴─────────────┘

Exercise 3 — Explain, without code, what would happen if dim_date only covered through July 31, 2026. Imagine that, due to a mistake, someone had generated dim_date with the range "2026-07-01" to "2026-07-31" instead of the complete August. In 2-3 sentences, explain what would happen to this lesson's JOIN's row count, and why the before == after check would catch it immediately.

Summary and next step

In this lesson you assembled, for the first time, Kiosko's complete star schema: fact_orders joined to its three dimensions — dim_store by store_id, dim_product by product_id, dim_date by a key derived from order_ts — verified with evidence that the row count didn't change: forty before, forty after. You learned why the JOIN against dim_date needs a type conversion the other two don't, and why INNER JOIN — not LEFT JOIN — is the right choice when referential integrity is already guaranteed.

Before moving on you should be able to: write, from memory, the structure of the three JOINs that assemble Kiosko's star; explain why the comparison against dim_date requires CAST/strftime while the other two don't; and describe what concrete evidence confirms a JOIN lost or duplicated no rows.

Lesson 8 — the closing mini-project — brings the entire module together into a single formal deliverable: the complete star schema, documented as a data structure, and verified against the same revenue numbers you already know from foundations.

Resources

• DuckDB — official SELECT statement and JOIN type (INNER, LEFT) documentation, the foundation of this lesson's central query. duckdb.org/docs/current/sql/query_syntax/from. In English.
• DuckDB — date formatting function documentation (strftime), used to derive date_key from order_ts in the JOIN against dim_date. duckdb.org/docs/current/sql/functions/dateformat. In English.
• Kimball Group — "Four-Step Dimensional Design Process" — the source behind why a well-declared grain (module 1) guarantees that a well-built JOIN loses or duplicates no rows. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/four-4-step-design-process. In English.