Building Kiosko's sales OBT

Description

This is the module's central lesson — the wide table's equivalent of what lesson 7 of module 2 was for the star. You build mart_daily_sales_obt: a single table, with fifteen columns, where every row already brings the date, the store, the product, and the category — everything that used to live spread across fact_orders + dim_store + dim_product + dim_date — together, ready for Kiosko's BI team to query without writing a single JOIN. And, with the same evidence-based discipline you already know, you measure that convenience's real cost: how many times each dimension value repeats, how many logical bytes of text it duplicates, and how much it weighs on disk compared to the star's four normalized tables.

Connection to the module. This lesson delivers the module's third executable result, the one this guide's design explicitly names: CREATE TABLE mart_daily_sales_obt AS SELECT ... fully denormalized, with a count of repeated columns and each table's approximate size as literal evidence of the space-vs-speed trade-off.

An analogy: serving the complete dish, once per diner and per day

Go back to lesson 1's table already set. This lesson decides, precisely, how that table gets set: not one plate for every ingredient someone requests on the spot — that would be, again, the star, where JOIN assembles the plate in real time — but a complete plate, prepared in advance, for every combination of "who's eating, what they ordered, and which day." If two people order exactly the same thing on the same day, the restaurant doesn't prepare two identical plates separately — it combines them into one bigger portion and serves it once. That's exactly what you're going to see in the row that collapses two orders into one of mart_daily_sales_obt's rows: when the same store sells the same product more than once on the same day, the OBT doesn't store one row per individual sale — it groups, serves one plate with quantity and revenue already summed, and stays there, ready for whoever comes to query it.

Worked example: mart_daily_sales_obt, built and verified

Rebuild module 2's complete star — fact_orders, dim_store, dim_product, dim_date — and build mart_daily_sales_obt with a CREATE TABLE ... AS SELECT that joins all four tables and groups by day, store, and product.

# build_obt_mart.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)
    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


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

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_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("=== Building mart_daily_sales_obt: everything flattened, no pending JOIN for the consumer ===")
con.execute("""
    CREATE TABLE mart_daily_sales_obt AS
    SELECT
        CAST(f.order_ts AS DATE)  AS sale_date,
        d.day_of_week,
        d.is_weekend,
        d.month,
        d.quarter,
        d.year,
        s.store_id,
        s.store_name,
        s.city,
        p.product_id,
        p.product_name,
        p.category,
        p.unit_cost,
        SUM(f.quantity)           AS quantity,
        ROUND(SUM(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
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
    ORDER BY sale_date, store_id, product_id
""")

print(con.sql("SELECT COUNT(*) AS obt_rows FROM mart_daily_sales_obt"))

print("=== First rows (representative columns) ===")
print(con.sql("""
    SELECT sale_date, store_name, product_name, category, quantity, revenue
    FROM mart_daily_sales_obt
    ORDER BY sale_date, store_name, product_name
    LIMIT 5
"""))

print("=== The row that collapsed two orders: ORD-1001 + ORD-1008 (S01, P001, 2026-08-03) ===")
print(con.sql("""
    SELECT sale_date, store_name, product_name, quantity, revenue
    FROM mart_daily_sales_obt
    WHERE sale_date = '2026-08-03' AND store_id = 'S01' AND product_id = 'P001'
"""))

print("=== Verification: total revenue did not change passing through the OBT ===")
obt_total = con.sql("SELECT ROUND(SUM(revenue), 2) FROM mart_daily_sales_obt").fetchone()[0]
fact_total = con.sql("SELECT ROUND(SUM(revenue), 2) FROM fact_orders").fetchone()[0]
print(f"fact_orders (7 columns, 3 separate dimension tables): {fact_total}")
print(f"mart_daily_sales_obt (15 columns, no pending JOIN):    {obt_total}")
assert obt_total == fact_total, "the OBT lost or inflated revenue"
print("Verification: revenue matches -- OK")

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

=== Building mart_daily_sales_obt: everything flattened, no pending JOIN for the consumer ===
┌──────────┐
│ obt_rows │
│  int64   │
├──────────┤
│       39 │
└──────────┘

=== First rows (representative columns) ===
┌────────────┬───────────────┬───────────────────────┬─────────────┬──────────┬─────────┐
│ sale_date  │  store_name   │     product_name      │  category   │ quantity │ revenue │
│    date    │    varchar    │        varchar        │   varchar   │  int128  │ double  │
├────────────┼───────────────┼───────────────────────┼─────────────┼──────────┼─────────┤
│ 2026-08-03 │ Kiosko Centro │ Bottled Water 600ml   │ beverages   │        5 │    2.75 │
│ 2026-08-03 │ Kiosko Centro │ Energy Bar            │ snacks      │        1 │     1.2 │
│ 2026-08-03 │ Kiosko Centro │ Phone Charger Cable   │ electronics │        1 │     4.5 │
│ 2026-08-03 │ Kiosko Norte  │ Energy Bar            │ snacks      │        2 │     2.4 │
│ 2026-08-03 │ Kiosko Norte  │ Instant Coffee Sachet │ beverages   │        2 │     1.5 │
└────────────┴───────────────┴───────────────────────┴─────────────┴──────────┴─────────┘

=== The row that collapsed two orders: ORD-1001 + ORD-1008 (S01, P001, 2026-08-03) ===
┌────────────┬───────────────┬─────────────────────┬──────────┬─────────┐
│ sale_date  │  store_name   │    product_name     │ quantity │ revenue │
│    date    │    varchar    │       varchar       │  int128  │ double  │
├────────────┼───────────────┼─────────────────────┼──────────┼─────────┤
│ 2026-08-03 │ Kiosko Centro │ Bottled Water 600ml │        5 │    2.75 │
└────────────┴───────────────┴─────────────────────┴──────────┴─────────┘

=== Verification: total revenue did not change passing through the OBT ===
fact_orders (7 columns, 3 separate dimension tables): 106.15
mart_daily_sales_obt (15 columns, no pending JOIN):    106.15
Verification: revenue matches -- OK

Stop at two numbers. First, mart_daily_sales_obt has 39 rows, not 40 — one fewer than fact_orders. That's not a bug: ORD-1001 (Kiosko Centro, P001, three units, 2026-08-03T08:14:00) and ORD-1008 (Kiosko Centro, P001, two units, 2026-08-03T10:22:00) are two different orders, at different moments on the same Monday, but they sell the same product at the same store on the same day — exactly the combination that defines a row of mart_daily_sales_obt. The query's GROUP BY combines them into a single row: 5 units (3 + 2), 2.75 in revenue (1.65 + 1.10). This OBT's grain isn't "an order line" — fact_orders's grain — it's "a product sold at a store on a day," the grain a daily sales dashboard actually needs.

Second, total revenue — 106.15 — remains exactly the same, even though the row count changed. This confirms something important about aggregating (SUM) against a coarser grain: as long as the aggregation is consistent — summing quantity and revenue within each group, without losing any row of fact_orders in the process — the business total doesn't get altered, even though the table's shape does. Losing a row (39 instead of 40) isn't the same as losing revenue — and this example's final verification confirms, with evidence, that the former happened here, not the latter.

Diagram: from four tables to one, and the grain that changes along the way

flowchart TD
    subgraph Star["Star (module 2): grain = an order line"]
        FO["fact_orders\n40 rows"]
        DS["dim_store\n3 rows"]
        DP["dim_product\n4 rows"]
        DD["dim_date\n31 rows"]
    end

    FO -->|"JOIN store_id"| DS
    FO -->|"JOIN product_id"| DP
    FO -->|"JOIN date_key"| DD

    DS --> OBT
    DP --> OBT
    DD --> OBT
    FO -->|"GROUP BY day+store+product"| OBT["mart_daily_sales_obt\n39 rows -- coarser grain\n15 columns, 0 pending JOINs"]

Going deeper: the space-vs-speed evidence, measured and honest

This guide's design explicitly asks for "a count of repeated columns and each table's approximate size as literal evidence." It's worth gathering that evidence with the same rigor as the rest of this guide — and being honest about what it does and doesn't show, at this scale.

First, the count of repeated columns — how many times the same dimension value shows up in the OBT:

print("\n=== Evidence 1: columns per table ===")
for t in ["fact_orders", "dim_store", "dim_product", "dim_date", "mart_daily_sales_obt"]:
    ncols = con.sql(f"SELECT COUNT(*) FROM pragma_table_info('{t}')").fetchone()[0]
    print(f"  {t:22} {ncols:2} columns")

print("\n=== Evidence 2: how many times each store_name repeats in the OBT ===")
print(con.sql("SELECT store_name, COUNT(*) AS times_repeated FROM mart_daily_sales_obt GROUP BY store_name ORDER BY store_name"))

print("=== Evidence 3: how many times each product_name/category repeats in the OBT ===")
print(con.sql("SELECT product_name, category, COUNT(*) AS times_repeated FROM mart_daily_sales_obt GROUP BY product_name, category ORDER BY product_name"))

=== Evidence 1: columns per table ===
  fact_orders             7 columns
  dim_store               4 columns
  dim_product              5 columns
  dim_date                7 columns
  mart_daily_sales_obt   15 columns

=== Evidence 2: how many times each store_name repeats in the OBT ===
┌───────────────┬────────────────┐
│  store_name   │ times_repeated │
│    varchar    │     int64      │
├───────────────┼────────────────┤
│ Kiosko Centro │             15 │
│ Kiosko Norte  │             13 │
│ Kiosko Sur    │             11 │
└───────────────┴────────────────┘

=== Evidence 3: how many times each product_name/category repeats in the OBT ===
┌───────────────────────┬─────────────┬────────────────┐
│     product_name      │  category   │ times_repeated │
│        varchar        │   varchar   │     int64      │
├───────────────────────┼─────────────┼────────────────┤
│ Bottled Water 600ml   │ beverages   │             15 │
│ Energy Bar            │ snacks      │             10 │
│ Instant Coffee Sachet │ beverages   │              7 │
│ Phone Charger Cable   │ electronics │              7 │
└───────────────────────┴─────────────┴────────────────┘

"Kiosko Centro" lives once in dim_store — and repeats fifteen times inside mart_daily_sales_obt. "Bottled Water 600ml" and "beverages" each live once in dim_product — and repeat fifteen times in the OBT. This is, literally, the duplication the classic argument against denormalization names: quantifiable, measurable with a query, not an assumption.

Now, the most interesting part — and the most honest to report. At this toy scale, does that repetition count translate into more bytes on disk?

import os

os.makedirs("kiosko_parquet", exist_ok=True)
for table in ["fact_orders", "dim_store", "dim_product", "dim_date", "mart_daily_sales_obt"]:
    con.execute(f"COPY {table} TO 'kiosko_parquet/{table}.parquet' (FORMAT PARQUET)")

star_total = 0
print("=== Disk size, Parquet, each star table ===")
for table in ["fact_orders", "dim_store", "dim_product", "dim_date"]:
    size = os.path.getsize(f"kiosko_parquet/{table}.parquet")
    star_total += size
    print(f"  {table:22} {size:5} bytes")
print(f"  {'total star (4 tables)':22} {star_total:5} bytes")

obt_size = os.path.getsize("kiosko_parquet/mart_daily_sales_obt.parquet")
print(f"\n  {'mart_daily_sales_obt':22} {obt_size:5} bytes")

=== Disk size, Parquet, each star table ===
  fact_orders             2029 bytes
  dim_store                688 bytes
  dim_product              967 bytes
  dim_date                1435 bytes
  total star (4 tables)   5119 bytes

  mart_daily_sales_obt    3572 bytes

Surprise: at this scale, mart_daily_sales_obt (3572 bytes) weighs less than the star's four tables added together (5119 bytes), despite repeating text on every row. This doesn't contradict the classic argument — it puts it in its correct context. Three factors explain this reversal, and it's worth naming them precisely, not leaving them as a mystery:

First, every Parquet file has a fixed metadata cost — headers, schema, per-column statistics — that doesn't depend on the number of rows. With only 3 to 39 rows per table, that fixed cost weighs proportionally far more than the data itself; at production scale, with millions of rows, that fixed cost becomes negligible against the real data.

Second, dim_date — 31 rows, the complete August calendar table — has no direct relationship to mart_daily_sales_obt's size, which only uses 7 of those 31 days (the ones that actually had sales). The star pays the full cost of a calendar dimension generated in advance; the OBT, built from real sales, never stores dates with no activity.

Third, and the most important: Parquet's dictionary encoding compresses exactly the kind of redundancy this lesson just measured. When a column has few distinct values repeated many times — store_name with only three possible values across 39 rows — Parquet stores each unique value once in a dictionary, and replaces every occurrence with a short reference. At Kiosko's scale, with only three stores and four products, that dictionary is tiny and the compression is nearly perfect — the logical duplication you measured in Evidence 2 and 3 nearly disappears in the compressed file.

None of these three factors invalidates the Fivetran benchmark cited in the previous lesson — 25% to 50% faster, 2-3x more storage, measured on real data on Redshift, Snowflake, and BigQuery: at production scale, with millions of rows and dozens of descriptive columns per dimension, the fixed metadata cost dilutes, and dictionary compression stops being "nearly perfect" because the number of unique combinations of repeated values grows with volume. Kiosko's dataset is toy-sized on purpose — hundreds of rows, not millions — and this lesson measured its size with complete honesty: at this specific scale, the OBT doesn't cost more space. The pattern reverses at real scale, and the Fivetran benchmark is the evidence for that reversal, not this Kiosko measurement.

Common mistakes

Concluding, from Kiosko's byte size, that "the OBT never costs space." What happens: someone sees mart_daily_sales_obt weighing less than the star's four tables combined, and generalizes that observation — valid only at this toy scale — as a universal property of wide tables. Why it happens: the number is right there, measured and literal — it's tempting to take it as the module's final conclusion, without reading the explanation of why it happens at this specific scale. How to spot it: if your argument for justifying an OBT in a real project cites "at Kiosko, the OBT weighed less," with no mention of the production-scale Fivetran benchmark, you have this confusion. How to fix it: this lesson's "going deeper" section explains, with three concrete factors, why the reversal happens at this scale and why it reverses back with real volume — review it before generalizing any conclusion about space.

Confusing mart_daily_sales_obt's grain with fact_orders's grain. What happens: someone, used to fact_orders having forty rows — one per order line — expects mart_daily_sales_obt to also have forty, and gets alarmed seeing 39. Why it happens: every table built so far in this guide preserved the same row count as fact_orders; this is the first time a new table deliberately has a different grain. How to spot it: if you expect assert before == after with before = 40 for mart_daily_sales_obt, like in earlier modules' join checks, you're going to fail that comparison for a reason that isn't a bug. How to fix it: remember mart_daily_sales_obt groups by day + store + product — a coarser grain than "an order line" on purpose, because it's the grain a daily sales dashboard actually needs. The correct check for this table isn't "same row count," it's "same total revenue" — exactly what this lesson's worked example verified.

Forgetting the GROUP BY and ending up with an OBT at the wrong grain. What happens: someone copies this lesson's SELECT structure but forgets to add the GROUP BY with the complete list of non-aggregated columns, and DuckDB throws a syntax error (or, on a less strict engine, produces an ambiguous result). Why it happens: with fifteen columns in the SELECT, it's easy to lose track of which ones are aggregated (SUM) and which need to appear in the GROUP BY. How to spot it: DuckDB, like most modern SQL engines, requires every non-aggregated column in the SELECT to also appear in the GROUP BY — if you're missing one, you're going to get an explicit error before the table gets built, not a silently incorrect result. How to fix it: count your SELECT's columns that don't carry SUM() or another aggregate function, and confirm the GROUP BY lists exactly those same positions — thirteen in this case, columns 1 through 13 of this lesson's query.

Exercises

Exercise 1 — Confirm 39 is exactly 40 - 1, not a coincidence. Using fact_orders, write a query confirming how many (day, store, product) combinations have more than one order — and verify that number exactly explains the difference between fact_orders's 40 rows and mart_daily_sales_obt's 39.

print(con.sql("""
    SELECT CAST(order_ts AS DATE) AS d, store_id, product_id, COUNT(*) AS n
    FROM fact_orders
    GROUP BY 1, 2, 3
    HAVING COUNT(*) > 1
    ORDER BY 1, 2, 3
"""))

┌────────────┬──────────┬────────────┬───────┐
│     d      │ store_id │ product_id │   n   │
│    date    │ varchar  │  varchar   │ int64 │
├────────────┼──────────┼────────────┼───────┤
│ 2026-08-03 │ S01      │ P001       │     2 │
└────────────┴──────────┴────────────┴───────┘

Exercise 2 — Calculate average revenue per row, star vs OBT, and explain the difference. Using fact_orders and mart_daily_sales_obt, calculate SUM(revenue) / COUNT(*) on each table and compare the two results.

print(con.sql("""
    SELECT
        (SELECT ROUND(SUM(revenue) / COUNT(*), 4) FROM fact_orders) AS avg_per_fact_row,
        (SELECT ROUND(SUM(revenue) / COUNT(*), 4) FROM mart_daily_sales_obt) AS avg_per_obt_row
"""))

┌──────────────────┬─────────────────┐
│ avg_per_fact_row │ avg_per_obt_row │
│      double      │     double      │
├──────────────────┼─────────────────┤
│           2.6537 │          2.7218 │
└──────────────────┴─────────────────┘

Exercise 3 — Explain, without code, why dim_date "weighs extra" in the star at this scale. In 2-3 sentences, explain why dim_date — with 31 rows, one for every day of August — contributes to the star's total size even though only 7 of those days had real Kiosko sales, and why that trait doesn't apply the same way to mart_daily_sales_obt.

Summary and next step

In this lesson you built mart_daily_sales_obt: fifteen columns, thirty-nine rows — one fewer than fact_orders, because two orders of the same product at the same store on the same day got grouped into one — zero pending JOINs for whoever queries it, and the same total revenue as always, 106.15, verified. You measured, with literal evidence, how many times each dimension value repeats (fifteen, thirteen, eleven for the three stores), and discovered — honestly, not despite the surprise — that at this toy scale, disk size doesn't confirm the classic pattern, and you understood precisely why.

Before moving on you should be able to: explain from memory why mart_daily_sales_obt has 39 rows and not 40; name the three factors explaining why its Parquet size doesn't reflect the production pattern at this scale; and describe the difference between "losing a row through aggregation" and "losing revenue to a mistake."

Lessons 6 and 7 close the module's argument with concrete evidence of when each shape wins: lesson 6 measures the real cost of maintaining a repeated value across all three shapes; lesson 7 resolves the same business question through all three paths and confirms they give the same result, with very different query costs.

Resources

• DuckDB — official COPY ... TO ... (FORMAT PARQUET) documentation, the command used in this lesson to export each table and measure its real disk size. duckdb.org/docs/current/sql/statements/copy. In English.
• Apache Parquet — official documentation, including the explanation of dictionary encoding behind why this lesson's OBT compresses so well at toy scale. parquet.apache.org/docs. In English.
• Fivetran — "Star Schema vs. OBT for Data Warehouse Performance" — the production-scale benchmark that contextualizes — and contrasts with — the size result measured in this lesson. fivetran.com/blog/star-schema-vs-obt. In English.
• DuckDB — aggregate function documentation (SUM, GROUP BY), the foundation for building mart_daily_sales_obt in this lesson. duckdb.org/docs/current/sql/functions/aggregates. In English.