Publishing the OBT mart for the dashboard team

Description

The warehouse already has everything the brief asked for, but spread across five different tables — fact_orders, dim_store, dim_date, dim_product_scd, and the point-in-time join that has to be written by hand every time. This lesson closes the warehouse's gold layer by building mart_daily_sales_obt: module 3's wide table, rebuilt here with a decisive difference no previous module could make — the JOIN against dim_product_scd uses lesson 4's point-in-time pattern, not the static dimension the original module 3 used. The result is a single table, with day + store + product grain, ready for the BI team to query with a simple GROUP BY, without writing a single BETWEEN valid_from AND valid_to on their own.

Connection to the module. This is the only genuinely new piece in the entire capstone, exactly as lesson 1 anticipated: the combination of module 3's wide table with modules 4 and 5's historized dimension and its correct join. No previous module could build this version, because dim_product_scd didn't exist when module 3 built its own OBT.

An analogy: the plated dish, not the raw ingredients on the table

Think about the difference between handing someone a complete recipe with all the raw ingredients on the table — including safety instructions about which knife to use for each cut — and handing them the already prepared dish, cut, cooked, and served. An experienced cook can work with the raw ingredients with no problem; someone who just wants to eat, can't. The complete recipe isn't "wrong" — it's exactly what a cook needs — but demanding a diner follow it themselves, every time they're hungry, is asking them for a skill they never wanted to develop.

fact_orders + dim_product_scd + the point-in-time JOIN are the raw ingredients and the complete recipe — perfect for someone who already masters dimensional modeling, like you after seven modules. mart_daily_sales_obt, published in this lesson, is the already-served dish: the BI team sits down at the table and finds each product's correct category already resolved, with nothing to cook themselves.

Worked example: the OBT, with the correct join already resolved inside

Part 1 — Building mart_daily_sales_obt with the point-in-time join

This script continues on top of lessons 4 and 5's same con connection — fact_orders, dim_store, dim_date, and dim_product_scd are already complete and verified.

# capstone_obt.py -- mart_daily_sales_obt (continues on top of con, with the star and dim_product_scd already ready)
con.execute("""
    CREATE TABLE mart_daily_sales_obt AS
    SELECT
        CAST(f.order_ts AS DATE) AS sale_date, dt.day_of_week, dt.is_weekend,
        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,
        ROUND(SUM(f.revenue - f.quantity * p.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_store s ON f.store_id = s.store_id
    JOIN dim_date  dt ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = dt.date_key
    JOIN dim_product_scd p
        ON f.product_id = p.product_id
       AND f.order_ts BETWEEN p.valid_from AND COALESCE(p.valid_to, DATE '9999-12-31')
    GROUP BY 1,2,3,4,5,6,7,8,9,10
""")
obt_rows = con.sql("SELECT COUNT(*) FROM mart_daily_sales_obt").fetchone()[0]
obt_revenue = con.sql("SELECT ROUND(SUM(revenue), 2) FROM mart_daily_sales_obt").fetchone()[0]
obt_categories = con.sql("SELECT DISTINCT category FROM mart_daily_sales_obt ORDER BY category").fetchall()

print("Part 1 -- mart_daily_sales_obt: the OBT for the BI team")
print(f"  mart_daily_sales_obt {obt_rows:3} rows, total revenue = {obt_revenue}")
print(f"  categories present: {[c[0] for c in obt_categories]}")
assert obt_revenue == 106.15
assert "snacks" in {c[0] for c in obt_categories} and "health-snacks" not in {c[0] for c in obt_categories}
print("  Verification OK: the published OBT already carries the correct category ('snacks'), without BI writing the JOIN")

What to expect.

Part 1 -- mart_daily_sales_obt: the OBT for the BI team
  mart_daily_sales_obt  39 rows, total revenue = 106.15
  categories present: ['beverages', 'electronics', 'snacks']

Verification OK: the published OBT already carries the correct category ('snacks'), without BI writing the JOIN

Thirty-nine rows — the same coarser grain module 3 already established (day + store + product, collapsing the forty order lines where two sales of the same product, same store, same day get grouped into a single row) — and 106.15 in revenue, identical as always. But notice the category list: ['beverages', 'electronics', 'snacks'] — without health-snacks. That's not chance or luck: it's Part 1's point-in-time JOIN doing, inside this table, exactly what lesson 4 demonstrated was correct. Anyone on the BI team who opens mart_daily_sales_obt and groups by category is going to get the correct breakdown, without ever having written a BETWEEN or a MERGE INTO in their life.

Part 2 — The final report, exactly as management would ask for it

print("\n=== Report for Kiosko's management ===")
print(con.sql("""
    SELECT store_name, ROUND(SUM(revenue), 2) AS revenue, ROUND(SUM(margin), 2) AS margin
    FROM mart_daily_sales_obt GROUP BY store_name ORDER BY store_name
"""))
print(con.sql("""
    SELECT category, ROUND(SUM(revenue), 2) AS revenue, ROUND(SUM(margin), 2) AS margin
    FROM mart_daily_sales_obt GROUP BY category ORDER BY category
"""))

What to expect.

=== Report for Kiosko's management ===
┌───────────────┬─────────┬────────┐
│  store_name   │ revenue │ margin │
│    varchar    │ double  │ double │
├───────────────┼─────────┼────────┤
│ Kiosko Centro │    38.3 │   17.4 │
│ Kiosko Norte  │   38.8  │  17.65 │
│ Kiosko Sur    │   29.05 │   12.1 │
└───────────────┴─────────┴────────┘

┌─────────────┬─────────┬────────┐
│  category   │ revenue │ margin │
│   varchar   │ double  │ double │
├─────────────┼─────────┼────────┤
│ beverages   │   44.05 │  14.75 │
│ electronics │    40.5 │   21.6 │
│ snacks      │    21.6 │   10.8 │
└─────────────┴─────────┴────────┘

Two queries, two single-line GROUP BYs each — neither has a JOIN, neither mentions dim_product_scd, valid_from, nor MERGE INTO. This is, precisely, what lesson 2's brief asked for: management (or any BI analyst) gets the correct margin by category — 10.8 for snacks, not 9.36 for a health-snacks that didn't even exist on those sales' date — without needing to understand a single technical word of the dimensional modeling that made that number possible.

Diagram: the OBT as the layer that absorbs the complexity, not hides it

flowchart LR
    subgraph Data_Team["Data team (modules 1-8)"]
        A["fact_orders\ndim_store, dim_date\nhistorized dim_product_scd\npoint-in-time JOIN"]
    end
    subgraph OBT["mart_daily_sales_obt (this lesson)"]
        B["39 rows\nGROUPED BY day+store+product\ncategory ALREADY resolved"]
    end
    subgraph BI["BI team"]
        C["GROUP BY category\nSUM(revenue)\nwith no idea it's SCD"]
    end
    A -->|"complexity gets resolved ONCE,\nhere, not every time BI queries"| B --> C

The arrow between "Data team" and "mart_daily_sales_obt" doesn't represent hiding complexity — lesson 4 already demonstrated, in complete transparency, exactly which modeling decision makes this table correct. It represents absorbing it, once, where the knowledge lives to make it correctly, instead of distributing it to every person who only needs the result.

Going deeper: what gets lost by flattening into an OBT, and why it doesn't matter here

Module 3 already warned that a wide table has a cost: repeated columns, more disk space, and the risk that a massive UPDATE (like a store name change) has to touch many more rows than in a normalized dimension. This lesson doesn't change that trade-off — it's still true — but it's worth being explicit about something that does change: mart_daily_sales_obt, as this lesson builds it, does not get updated with an incremental MERGE INTO like dim_product_scd — it gets rebuilt completely every time the warehouse runs again, with a simple CREATE TABLE ... AS SELECT. That's a deliberate decision, consistent with the boundary this guide declared since module 1: real orchestration, with incremental updates of a derived table, is territory of airflow-and-declarative-orchestration-guide. Here, "publishing the OBT" means rebuilding it from scratch every time, exactly as you already did in module 3 — this lesson's difference is which query rebuilds it, not how it gets updated over time.

Common mistakes

Rebuilding mart_daily_sales_obt joining against dim_product (the static version) instead of dim_product_scd. What happens: someone, remembering module 3's code, copies that query almost literally, joining fact_orders with dim_product instead of dim_product_scd. Why it happens: dim_product still exists in the warehouse — it was never deleted — and its JOIN is simpler to write, with no BETWEEN at all. How to spot it: if your mart_daily_sales_obt shows P002 always as snacks regardless of date — because dim_product, the original catalog's static table, never got updated with August's change — your OBT "by coincidence" gives the correct result for this specific dataset, but not because you applied the correct pattern. How to fix it: this lesson's central point is using dim_product_scd with the point-in-time JOIN, not dim_product — in a dataset where the category change happened during the sales week (not after, as in Kiosko), joining against the static version would give a different, and wrong, result from this lesson's.

Forgetting COALESCE(p.valid_to, DATE '9999-12-31') and losing the current product's sales. What happens: someone writes the point-in-time JOIN without the COALESCE, leaving f.order_ts BETWEEN p.valid_from AND p.valid_to plain. Why it happens: it seems like a reasonable simplification, and it works perfectly for a dimension's closed versions (the ones that do have valid_to). How to spot it: if your OBT loses complete rows for a product — for example, if P001, P003, or P004, whose only version has valid_to = NULL, disappeared from the result — you have exactly this error: BETWEEN x AND NULL is never true in SQL, no matter x's value. How to fix it: this lesson's COALESCE(p.valid_to, DATE '9999-12-31') — the same pattern you already used in modules 4 and 5 — turns "no closing date" (NULL) into a closing date far in the future, so the BETWEEN comparison also works for any product's current version.

Thinking 39 rows (instead of 40) is an error in this lesson. What happens: someone, seeing mart_daily_sales_obt has 39 rows while fact_orders has 40, suspects the point-in-time JOIN lost a row. Why it happens: after several lessons insisting a JOIN should never lose or duplicate rows, seeing a different number triggers automatic alarm. How to spot it: check each table's grain — fact_orders has "order line" grain (40 rows), mart_daily_sales_obt has "day + store + product" grain (39 rows), because two order lines of the same product, same store, same day get grouped into a single OBT row. How to fix it: this isn't an error — it's exactly the same behavior you already saw in module 3, when the original OBT also had 39 rows for the same reason. The correct check isn't "same number of rows?", it's "same total revenue?" — and 106.15 in both tables confirms the aggregation didn't lose a single cent, even though the row count changes because the grain changed on purpose.

Exercises

Exercise 1 — Confirm the OBT reproduces revenue by product, not just by category. Write a query that groups mart_daily_sales_obt by product_name and confirms the same numbers you already know since module 1 (33.55/21.6/10.5/40.5).

print(con.sql("""
    SELECT product_name, ROUND(SUM(revenue), 2) AS revenue
    FROM mart_daily_sales_obt GROUP BY product_name ORDER BY product_name
"""))

┌───────────────────────┬─────────┐
│      product_name     │ revenue │
│        varchar        │ double  │
├───────────────────────┼─────────┤
│ Bottled Water 600ml   │   33.55 │
│ Energy Bar            │   21.6  │
│ Instant Coffee Sachet │   10.5  │
│ Phone Charger Cable   │    40.5 │
└───────────────────────┴─────────┘

Exercise 2 — Simulate what would happen if a sale occurred after August 15th. Without modifying fact_orders, add a hypothetical P002 row with order_ts = '2026-08-20T10:00:00' to a temporary copy, and confirm the point-in-time JOIN would resolve it to health-snacks, not to snacks.

con.execute("""
    CREATE TABLE fact_orders_hypothetical AS
    SELECT * FROM fact_orders
    UNION ALL
    SELECT 'ORD-9999', 'S01', 'P002', 1, 1.30, 1.30, TIMESTAMP '2026-08-20 10:00:00'
""")
print(con.sql("""
    SELECT f.order_id, f.order_ts, p.category
    FROM fact_orders_hypothetical f
    JOIN dim_product_scd p
        ON f.product_id = p.product_id
       AND f.order_ts BETWEEN p.valid_from AND COALESCE(p.valid_to, DATE '9999-12-31')
    WHERE f.order_id = 'ORD-9999'
"""))

┌──────────┬─────────────────────┬───────────────┐
│ order_id │      order_ts       │   category    │
│ varchar  │      timestamp      │    varchar    │
├──────────┼─────────────────────┼───────────────┤
│ ORD-9999 │ 2026-08-20 10:00:00 │ health-snacks │
└──────────┴─────────────────────┴───────────────┘

Exercise 3 — Explain, from memory, why this lesson is the only one in the capstone module 3 couldn't have anticipated. In 2-3 sentences, explain what module 3 was missing, at the moment it was written, to build this same version of mart_daily_sales_obt.

Summary and next step

In this lesson you closed the warehouse's gold layer: mart_daily_sales_obt, thirty-nine rows, with each product's category already resolved with lesson 4's point-in-time join — snacks, not health-snacks — without the BI team having to write that JOIN on their own. You confirmed, with two single-line queries each, the exact report Kiosko's management asked for in lesson 2's brief: revenue and margin by store, revenue and margin by category.

Before moving on you should be able to: explain why this OBT uses dim_product_scd and not dim_product; recite from memory the revenue and margin by category (beverages 44.05/14.75, electronics 40.5/21.6, snacks 21.6/10.8); and explain why 39 rows in the OBT, against 40 in fact_orders, isn't an error.

Lesson 7 steps back from the code: with the complete warehouse already built, it names, one by one, the Data Engineering ecosystem's sibling guides that deepen every real limitation this warehouse still has.

Resources

• Fivetran — "Star Schema vs. OBT for Data Warehouse Performance" — the benchmark that already justified, in module 3, why a wide table makes sense for a specific BI consumer. fivetran.com/blog/star-schema-vs-obt. In English.
• dataarchitect.studio — "One Big Table vs the Star Schema: The Real Trade-off" — the layered argument (star as foundation, OBT as service) this lesson concretely applies, serving the OBT over the already-historized star. dataarchitect.studio/essays/one-big-table-vs-star-schema. In English.
• DuckDB — official documentation on the CREATE TABLE ... AS SELECT statement, the mechanism rebuilding mart_daily_sales_obt completely on every run. duckdb.org/docs/current/sql/statements/create_table. In English.
• DuckDB — official Python client documentation, the interface that runs every query in this lesson. duckdb.org/docs/current/clients/python/overview. In English.