When the OBT still wins

Description

The previous lesson put a number on the wide table's maintenance cost: twenty-two rows to rename a category, against a single one in the normalized dimension. It would be just as easy, after that lesson, to conclude the OBT is never worth it. This lesson closes the argument in the opposite direction, with the same discipline: it takes the business question lesson 4 used as a conceptual example — revenue by category, by city, split by weekend — and resolves it, for real, through all three paths. You're going to confirm, with a programmatic comparison, that all three shapes give exactly the same result — and you're going to see, with your own eyes, which of the three queries was simplest to write.

Connection to the module. This lesson runs, with real data, the comparison lesson 4 only counted in the abstract (3 JOINs, 4 JOINs, 0 JOINs). It closes the space-vs-speed argument with the missing half: speed, not just space.

An analogy: the same question, asked three times, to three different employees

Imagine you ask the same question to three Kiosko employees, each organized differently. To the first — who works with the star — you ask "how much did we sell in beverages, in Bogota, on weekends?", and they have to go to the sales file, cross-reference it with the products file, cross-reference it with the stores file, and cross-reference it with the calendar — four steps, but each file is small and easy to look up. To the second — who works with the snowflake — you ask the same question, but their products file is, in turn, split in two: first they have to go to the products file, and from there jump to a separate categories file — one more step than the first employee. To the third — who works with the OBT — you ask the same question, and they answer by looking at a single, already-assembled daily report, with no file to go to at all — the answer is right there, ready, just adding up the correct rows.

All three are going to give you the same number. This lesson confirms it, not assumes it. But the third employee, with the report already assembled, is the one who answers fastest — and that's, precisely, the speed gain that justifies the maintenance cost you measured in the previous lesson.

Worked example: the same question, three paths, one result

Rebuild the star, the snowflake, and the OBT — this module's three complete shapes — and answer the same business question through each path: revenue by category, by store city, split into weekend versus weekday.

# same_question_three_shapes.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")
con.execute("""
    CREATE TABLE dim_category AS
    SELECT ROW_NUMBER() OVER (ORDER BY category) AS category_id, category AS category_name
    FROM (SELECT DISTINCT category FROM dim_product_natural) t
""")
con.execute("""
    CREATE TABLE dim_product_normalized AS
    SELECT ROW_NUMBER() OVER (ORDER BY n.product_id) AS product_key, n.product_id, n.product_name, c.category_id, n.unit_cost
    FROM dim_product_natural n JOIN dim_category c ON n.category = c.category_name
""")

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

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
""")

# the same business question, resolved through all three paths
star_q = """
    SELECT p.category, s.city, d.is_weekend, ROUND(SUM(f.revenue), 2) AS revenue
    FROM fact_orders f
    JOIN dim_product p ON f.product_id = p.product_id
    JOIN dim_store   s ON f.store_id = s.store_id
    JOIN dim_date    d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
    GROUP BY p.category, s.city, d.is_weekend
    ORDER BY p.category, s.city, d.is_weekend
"""
snowflake_q = """
    SELECT c.category_name AS category, s.city, d.is_weekend, ROUND(SUM(f.revenue), 2) AS revenue
    FROM fact_orders f
    JOIN dim_product_normalized p ON f.product_id = p.product_id
    JOIN dim_category c            ON p.category_id = c.category_id
    JOIN dim_store    s            ON f.store_id = s.store_id
    JOIN dim_date     d            ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
    GROUP BY c.category_name, s.city, d.is_weekend
    ORDER BY c.category_name, s.city, d.is_weekend
"""
obt_q = """
    SELECT category, city, is_weekend, ROUND(SUM(revenue), 2) AS revenue
    FROM mart_daily_sales_obt
    GROUP BY category, city, is_weekend
    ORDER BY category, city, is_weekend
"""

print("Question: revenue by category, by store city, split weekend / weekday\n")

print("=== star: 3 JOINs ===")
print(con.sql(star_q))

print("=== snowflake: 4 JOINs ===")
print(con.sql(snowflake_q))

print("=== OBT: 0 JOINs ===")
print(con.sql(obt_q))

star_rows = con.sql(star_q).fetchall()
sf_rows = con.sql(snowflake_q).fetchall()
obt_rows = con.sql(obt_q).fetchall()
print(f"Rows: star={len(star_rows)}, snowflake={len(sf_rows)}, obt={len(obt_rows)}")
print(f"star == snowflake: {star_rows == sf_rows}")
print(f"star == obt:       {star_rows == obt_rows}")

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

Question: revenue by category, by store city, split weekend / weekday

=== star: 3 JOINs ===
┌─────────────┬──────────┬────────────┬─────────┐
│  category   │   city   │ is_weekend │ revenue │
│   varchar   │ varchar  │  boolean   │ double  │
├─────────────┼──────────┼────────────┼─────────┤
│ beverages   │ Bogota   │ false      │   10.35 │
│ beverages   │ Bogota   │ true       │    5.15 │
│ beverages   │ Lima     │ false      │     9.6 │
│ beverages   │ Lima     │ true       │     6.1 │
│ beverages   │ Santiago │ false      │     9.0 │
│ beverages   │ Santiago │ true       │    3.85 │
│ electronics │ Bogota   │ false      │     9.0 │
│ electronics │ Bogota   │ true       │     9.0 │
│ electronics │ Lima     │ false      │    13.5 │
│ electronics │ Santiago │ false      │     4.5 │
│ electronics │ Santiago │ true       │     4.5 │
│ snacks      │ Bogota   │ false      │     4.8 │
│ snacks      │ Lima     │ false      │     4.8 │
│ snacks      │ Lima     │ true       │     4.8 │
│ snacks      │ Santiago │ false      │     4.8 │
│ snacks      │ Santiago │ true       │     2.4 │
└─────────────┴──────────┴────────────┴─────────┘
  16 rows                             4 columns

=== snowflake: 4 JOINs ===
┌─────────────┬──────────┬────────────┬─────────┐
│  category   │   city   │ is_weekend │ revenue │
│   varchar   │ varchar  │  boolean   │ double  │
├─────────────┼──────────┼────────────┼─────────┤
│ beverages   │ Bogota   │ false      │   10.35 │
│ beverages   │ Bogota   │ true       │    5.15 │
│ beverages   │ Lima     │ false      │     9.6 │
│ beverages   │ Lima     │ true       │     6.1 │
│ beverages   │ Santiago │ false      │     9.0 │
│ beverages   │ Santiago │ true       │    3.85 │
│ electronics │ Bogota   │ false      │     9.0 │
│ electronics │ Bogota   │ true       │     9.0 │
│ electronics │ Lima     │ false      │    13.5 │
│ electronics │ Santiago │ false      │     4.5 │
│ electronics │ Santiago │ true       │     4.5 │
│ snacks      │ Bogota   │ false      │     4.8 │
│ snacks      │ Lima     │ false      │     4.8 │
│ snacks      │ Lima     │ true       │     4.8 │
│ snacks      │ Santiago │ false      │     4.8 │
│ snacks      │ Santiago │ true       │     2.4 │
└─────────────┴──────────┴────────────┴─────────┘
  16 rows                             4 columns

=== OBT: 0 JOINs ===
┌─────────────┬──────────┬────────────┬─────────┐
│  category   │   city   │ is_weekend │ revenue │
│   varchar   │ varchar  │  boolean   │ double  │
├─────────────┼──────────┼────────────┼─────────┤
│ beverages   │ Bogota   │ false      │   10.35 │
│ beverages   │ Bogota   │ true       │    5.15 │
│ beverages   │ Lima     │ false      │     9.6 │
│ beverages   │ Lima     │ true       │     6.1 │
│ beverages   │ Santiago │ false      │     9.0 │
│ beverages   │ Santiago │ true       │    3.85 │
│ electronics │ Bogota   │ false      │     9.0 │
│ electronics │ Bogota   │ true       │     9.0 │
│ electronics │ Lima     │ false      │    13.5 │
│ electronics │ Santiago │ false      │     4.5 │
│ electronics │ Santiago │ true       │     4.5 │
│ snacks      │ Bogota   │ false      │     4.8 │
│ snacks      │ Lima     │ false      │     4.8 │
│ snacks      │ Lima     │ true       │     4.8 │
│ snacks      │ Santiago │ false      │     4.8 │
│ snacks      │ Santiago │ true       │     2.4 │
└─────────────┴──────────┴────────────┴─────────┘
  16 rows                             4 columns

Rows: star=16, snowflake=16, obt=16
star == snowflake: True
star == obt:       True

Sixteen rows, in all three shapes, with exactly the same revenue values in the same order — the programmatic comparison star_rows == sf_rows and star_rows == obt_rows confirms it with a literal True, not a visual inspection. This is half the evidence this lesson needs: all three paths are interchangeable in terms of result. The other half is in the source code you just read: count obt_q's query lines against star_q's and snowflake_q's. obt_q is a five-line SELECT ... GROUP BY ... ORDER BY, with not a single JOIN. star_q needs three explicit JOINs; snowflake_q, four. For someone on Kiosko's BI team who doesn't know the complete dimensional model by heart — which table has which column, what the join key is called — the query against the OBT is, objectively, simpler to get right on the first try.

Diagram: the same answer, three query efforts

flowchart LR
    Q["Revenue by category,\nby city, by weekend"]

    Q --> S["star\n3 explicit JOINs\n16 rows"]
    Q --> SF["snowflake\n4 explicit JOINs\n16 rows"]
    Q --> O["OBT\n0 JOINs, just GROUP BY\n16 rows"]

    S --> R["Same result,\nverified == True"]
    SF --> R
    O --> R

Going deeper: the OBT as a service layer, not a model replacement

It's worth closing this lesson by returning to the dataarchitect.studio argument lesson 4 cited: the star remains the central model, and the OBT is a service layer built on top of it. This lesson just showed, with executed evidence, why that service layer has real value: not because the star is "bad" — it's still perfectly capable of answering the same question, with the same correct result — but because who queries the wide table, in a real use case, is almost never the same person who designed the dimensional model.

Think of Kiosko's BI team — business analysts, not data engineers — building a daily sales dashboard a store manager is going to check every morning. That analyst doesn't need to know that category lives in dim_product, or that dim_date requires a type conversion to join against order_ts — information that is indeed indispensable for whoever maintains the dimensional model, but is pure noise for someone who just wants to answer "how much did we sell yesterday, by category?" mart_daily_sales_obt translates the complete dimensional model — with all its discipline of surrogate keys, conformed dimensions, and normalization where it applies — into a shape that analyst can query without having to learn it first. The maintenance cost you measured in lesson 6 gets paid by the team that maintains the data pipeline, once, when they regenerate the table; the simplicity gain gets collected by every analyst, every time they open their BI tool and write a query with no JOIN. That asymmetry — a cost paid few times, a benefit collected many times — is, in one sentence, this entire module's argument.

Common mistakes

Thinking "0 JOINs" means the OBT has no complexity cost at all. What happens: someone, impressed by obt_q's simplicity, concludes querying the OBT requires no business knowledge at all, just knowing how to write a GROUP BY. Why it happens: the absence of JOINs in the SQL code feels like a total absence of complexity. How to spot it: if someone queries mart_daily_sales_obt without understanding its grain is "a product sold at a store on a day" — not "an order line" — they can write an AVG(revenue) expecting the average per individual sale, and get, with no error at all, a number that actually averages by day-store-product combination (the same problem you saw in lesson 5's exercise 2). How to fix it: the OBT simplifies the query's syntax — the "how" — but doesn't eliminate the need to understand each column's grain and meaning — the "what." Documenting a wide table's grain, with the same discipline this guide applied to fact_orders since module 1, remains mandatory.

Using Python's == comparison between results of different numeric types and trusting it always works. What happens: someone copies this lesson's star_rows == obt_rows verification pattern to compare results where a numeric column comes as int in one query and as float or Decimal in another, and the comparison fails due to a type difference, not a real value difference. Why it happens: Python compares tuples element by element, and 1 == 1.0 is True, but Decimal('1.00') == 1.0 can behave less obviously depending on context. How to spot it: if your == check returns False even though the numbers "look equal" when printed, suspect an underlying type difference before assuming the data genuinely differs. How to fix it: in this lesson, all three queries use ROUND(SUM(revenue), 2) consistently across all three paths, guaranteeing revenue's type and precision are identical across the three compared tuples — that deliberate consistency is what makes == a reliable check here.

Concluding that, because the OBT won this simplicity comparison, it should always be the default shape for new tables. What happens: someone, convinced by this lesson, decides every new Kiosko model should be built directly as a wide table, skipping the star schema entirely. Why it happens: having seen the simplicity gain up close makes it easy to forget the maintenance cost lesson 6 already measured, and the condition — a known, repeated query pattern — lesson 4 established as a requirement. How to spot it: if your plan for a completely new business fact, with no BI consumer identified yet, is to build a wide table directly without going through a star schema first, you're missing this guide's sequencing discipline. How to fix it: remember dataarchitect.studio's layered argument — the star gets built first, as the flexible, central model; the OBT gets built afterward, on top of the star, once a concrete consumer with a known query pattern that justifies it already exists. This guide never built the OBT before the star, and that sequence wasn't a coincidence.

Exercises

Exercise 1 — Count the SQL code lines of each query. Without running anything new, count how many SQL lines (not counting blank lines) each of the worked example's three queries (star_q, snowflake_q, obt_q) has, and confirm the order of complexity matches each one's JOIN count.

Exercise 2 — Solve a fourth question through all three paths and verify it matches. Write the three versions (star, snowflake, OBT) of the question "total revenue by day of the week" (no category, city, or weekend breakdown), and confirm with == that all three give the same result.

star_dow = con.sql("""
    SELECT d.day_of_week, ROUND(SUM(f.revenue), 2) AS revenue
    FROM fact_orders f
    JOIN dim_date d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
    GROUP BY d.day_of_week
    ORDER BY MIN(d.calendar_date)
""").fetchall()

obt_dow = con.sql("""
    SELECT day_of_week, ROUND(SUM(revenue), 2) AS revenue
    FROM mart_daily_sales_obt
    GROUP BY day_of_week
    ORDER BY MIN(sale_date)
""").fetchall()

print(f"star == obt: {star_dow == obt_dow}")
print(star_dow)

star == obt: True
[('Monday', 15.85), ('Tuesday', 15.85), ('Wednesday', 9.55), ('Thursday', 11.05), ('Friday', 18.05), ('Saturday', 31.85), ('Sunday', 3.95)]

Exercise 3 — Explain, without code, why this lesson's == comparison is stronger than checking the tables "by eye." In 2-3 sentences, explain what kind of error could slip through unnoticed if you only compared the three printed results, looking at them side by side, instead of using star_rows == obt_rows in code.

Summary and next step

In this lesson you solved the same business question through this module's three paths — star, snowflake, OBT — and confirmed, with a programmatic comparison (==, not visual inspection), that all three give exactly the same result: sixteen rows, same revenue. The difference between them isn't in correctness — all three are correct — it's in query effort: zero JOINs in the OBT against three in the star and four in the snowflake. You also understood why that simplicity gain has more value when whoever's querying isn't the same person who maintains the dimensional model.

Before moving on you should be able to: explain why this lesson's == comparison is more reliable than reviewing tables visually; count from memory the number of JOINs in each of the worked example's three queries; and recite this module's central asymmetry — a maintenance cost paid few times, against a query benefit collected many times.

Lesson 8 — the closing mini-project — brings all three shapes together into a single formal deliverable: built at once, verified against the same revenue as always, and documented in a declaration that compares, number by number, when each one wins.

Resources

• dataarchitect.studio — "One Big Table vs the Star Schema: The Real Trade-off" — the source for the layered architecture (star as foundation, OBT as service) this lesson confirms with executed evidence. dataarchitect.studio/essays/one-big-table-vs-star-schema. In English.
• Fivetran — "Star Schema vs. OBT for Data Warehouse Performance" — the production benchmark backing, at real scale, the speed gain this lesson confirmed in the structure of Kiosko's queries. fivetran.com/blog/star-schema-vs-obt. In English.
• Microsoft Learn — "Understand star schema and the importance for Power BI" — the section on snowflake dimensions documents, from a real BI tool's perspective, why "fewer tables" is usually preferred for the end consumer. learn.microsoft.com/en-us/power-bi/guidance/star-schema. In English.
• DuckDB — official Python client documentation, the tool that ran every verification in this lesson. duckdb.org/docs/current/clients/python/overview. In English.