The brief: Kiosko needs a versioned warehouse

Description

data-modeling-for-analytics-guide ended with a complete dimensional warehouse, verified by hand: fact_orders, dim_store, dim_date, dim_product historized with SCD type 2, dim_category, dim_order_flags, fact_sessions, fact_store_activity, and mart_daily_sales_obt. Every piece has its own Python script, run in the correct order by someone who remembers that order — the same problem this whole guide's introduction named back in module 1: transformation as loose scripts, not as real code. This module, the guide's last, designs no new model — it solves the operational problem still open: bringing the five pieces that still live outside the dbt project (dim_category, dim_order_flags, fact_sessions, fact_store_activity, mart_daily_sales_obt) into kiosko_analytics/, where fact_orders, dim_store, and dim_date have already lived since module 3.

This lesson doesn't write any SQL yet — it's the brief that justifies why it's worth doing, with the same real-business discipline module 1 of this guide used to justify why dbt itself was worth it.

Connection to the module. Lesson 1 named the five missing pieces. This lesson explains, with a concrete scenario, what it would cost Kiosko not to port them — the same kind of argument module 1 already used to justify why "loose scripts, run in the correct order by someone who remembers" doesn't scale. Lesson 3 writes the real SQL.

An analogy: the recipe only one person knows how to cook

Imagine a restaurant where the signature dish — the one people order most — exists, is delicious, and has a written recipe. The problem is where that recipe lives: in the head of the chef who invented it, in a personal notebook they keep at home, in a notepad nobody else has ever seen. The dish stays on the menu, customers keep ordering it, and it works perfectly well — as long as that specific chef keeps working there, every day, without missing one. The day they get sick, go on vacation, or simply change jobs, the restaurant discovers it had a signature dish nobody else could reproduce exactly. The recipe was never badly written — it was badly kept: in a place that depended on a single person, not in a place any new cook could open, follow, and trust to produce the same result.

The five marts data-modeling-for-analytics-guide built are, precisely, that recipe. Each one has a Python script that produces the correct result — already verified, already tested — but that script lives outside any versioned project, with no automated test confirming it still works if something changes, with no description telling a new teammate what each column does. Porting it to kiosko_analytics/ is, exactly, writing the recipe into the whole restaurant's system: versioned, with documented ingredients, with a quality control that runs on its own before the dish is served.

The brief, in the words of someone at Kiosko

Imagine Kiosko's data team — the person who's spent seven modules building this dbt project with you — gets this message from an analytics teammate:

"We have scripts that calculate the app's conversion funnel, how many active days each store had, and a sales report the BI team already uses. The problem is they live in a separate folder, nobody runs them in the right order except me, and last week someone asked 'does this still reflect today's data or is it from a month ago' and I couldn't answer for sure. Can we bring this into the dbt project we already have, so it runs with the same daily dbt build?"

This message has three concrete complaints, and each one maps to something dbt — the same dbt you already used in modules 1 through 7 — already solves:

1. "They live in a separate folder." → The five new marts are going to live in models/marts/, the same folder that's already had dim_store, dim_date, and fact_orders since module 3. One single place, one single pattern, no special folder.
2. "Nobody runs them in the right order except me." → ref() — module 3's central mechanism — resolves the execution order automatically, from the dependencies you declare. Nobody needs to memorize that fact_store_activity has to run after fact_orders; dbt already knows, because the ref('fact_orders') inside fact_store_activity.sql tells it.
3. "I couldn't answer for sure whether it reflects today's data." → dbt build rebuilds the complete project every time it runs, over raw_data/kiosko/'s current data; and the data_tests you're going to declare in lesson 4 confirm, with evidence and not with memory, that the result is still correct.

Why this isn't "starting over"

It's worth being explicit about something lesson 1 already hinted at: this module redesigns no model. The SQL you're going to write in lesson 3 is a direct translation of the SQL data-modeling-for-analytics-guide already validated — same grain, same columns, same numbers — now expressed as a dbt .sql file with ref() instead of a manual DuckDB connection. The following table summarizes, mart by mart, what changes and what doesn't:

Mart	What does NOT change	What DOES change (the dbt translation)
dim_category	The grain (one row per distinct category), the ROW_NUMBER() logic	The source goes from a direct DuckDB connection to ref('stg_products')
dim_order_flags	The six fixed payment_method × channel combinations	The cartesian product gets declared with a VALUES clause, with no Python loop at all
fact_sessions	The MAX(CASE WHEN ...) pattern, the one-row-per-session grain	The source goes from a Python list loaded by hand to ref('stg_events')
fact_store_activity	The definition of "the last 7/30 days," the exact result per store and day	The day-by-day Python loop gets replaced by a SQL window function, in a single SELECT
mart_daily_sales_obt	The grain (day + store + product), the GROUP BY that collapses duplicate orders	The product dimension goes from dim_product (flat table) to dim_product_snapshot, joined with a point-in-time join (dbt_valid_from/dbt_valid_to against order_ts)

The only row in that table worth pausing on is the last: mart_daily_sales_obt is going to use dim_product_snapshot — the snapshot module 5 built — instead of a flat dim_product table, because this project never built an un-historized dim_product. The snapshot already fills that role, but not with a "current version" filter: the OBT joins each fact_orders line against the product version that was real on that sale's date, with the same point-in-time join data-modeling-for-analytics-guide already taught in its own module 5 — the correct guarantee for a historical fact, different from "what version of the product exists today."

Diagram: where each piece lives, before and after

BEFORE (end of module 7)                     AFTER (end of module 8)
                                              
kiosko_analytics/                            kiosko_analytics/
├── models/marts/                            ├── models/marts/
│   ├── dim_store.sql                        │   ├── dim_store.sql
│   ├── dim_date.sql                         │   ├── dim_date.sql
│   └── fact_orders.sql                      │   ├── fact_orders.sql
│                                             │   ├── dim_category.sql       <- NEW
data-modeling-for-analytics-guide/           │   ├── dim_order_flags.sql    <- NEW
  (loose .py scripts, outside               │   ├── fact_sessions.sql      <- NEW
   any versioned project)                    │   ├── fact_store_activity.sql <- NEW
  - dim_category                             │   └── mart_daily_sales_obt.sql <- NEW
  - dim_order_flags                          └── ...
  - fact_sessions
  - fact_store_activity
  - mart_daily_sales_obt

The five pieces on the left don't disappear from data-modeling-for-analytics-guide — that guide is still the source of truth for why the model has the shape it has. What changes is that Kiosko no longer depends on someone running the right script, in the right order, from a separate folder: those same five pieces now also live inside the versioned, tested project, reproducible with one command.

Going deeper: why "port" and not "migrate"

It's worth distinguishing two words that get used as if they were the same, and they aren't. Migrating a system typically implies the old system stops being used — it gets shut down, archived, nobody goes back to it. Porting a mart, in this module's sense, doesn't shut anything down: data-modeling-for-analytics-guide is still a complete learning resource, with its own pedagogical narrative about why each dimensional pattern exists. What this module does is closer to publishing a production version of something already designed in a practice environment: the design doesn't change, but where it lives, how it gets tested, and who can run it with no help, does.

This distinction matters for the rest of the guide: when lesson 3 writes dim_category.sql, you're not going to find any new justification for why dim_category normalizes category into its own table — that justification already exists, complete, in data-modeling-for-analytics-guide's module 3. What you're going to find is the technical translation: how that same logic gets written inside a dbt .sql file, with ref() instead of a manual connection.

Common mistakes

Thinking this module has to "improve" the marts' design while porting them. What happens: someone, while writing dim_category.sql in dbt, decides to add a new column, or change category_id's ordering criteria, because "since I'm already here, might as well improve it." Why it happens: porting code to a new system instinctively feels like a review opportunity — it's easy to confuse "translating" with "redesigning." How to spot it: if your dim_category.sql produces a different category_id than the one data-modeling-for-analytics-guide already published, or a column that guide never declared, you changed the design, not just the engine. How to fix it: this module's goal is for the result to be identical, verifiable number for number against what the sibling guide already produced — any real improvement to the dimensional model is a conversation for data-modeling-for-analytics-guide, not for this guide.

Ignoring the brief and jumping straight to the code. What happens: someone, eager to get to lesson 3, doesn't read this lesson's brief and starts writing dim_category.sql with no clear idea why these five specific pieces need to live inside kiosko_analytics/, instead of staying as loose scripts that "work fine." Why it happens: after seven modules writing dbt SQL, the code feels like the real work, and a lesson with no "What to expect" block at all can feel like a step you can skip. How to spot it: if you can't explain, in one sentence, why this lesson's message from the analytics teammate is a real business problem — not just a technical one — you're missing the context that makes lesson 3's work make sense. How to fix it: this lesson's brief is the same discipline this guide's module 1 already demanded — "what is analytics engineering and why it matters" — now applied to the capstone: every line of SQL you're going to write answers a concrete business complaint, not an abstract exercise.

Assuming "porting" means the numeric result can vary a little, "because it's a different implementation". What happens: someone runs dim_category.sql in dbt, gets three categories instead of the ones they remembered from data-modeling-for-analytics-guide, and chalks it up to "small, normal differences between implementations." Why it happens: it's tempting to think two different engines (a direct Python connection versus a dbt-duckdb model) naturally produce slightly different results. How to spot it: if your row count or your total revenue doesn't match, byte for byte, what data-modeling-for-analytics-guide already published (adjusted, when applicable, for the data's current state — as you're going to see with dim_category, which does reflect P002's category change because this project already has that updated source since module 5), something got mistranslated, it's not an expected variation. How to fix it: declarative SQL over the same data produces the same result, regardless of the engine — DuckDB is DuckDB, whether it's driven by a Python script or by dbt-duckdb — any numeric difference is a sign something in the translation is wrong, never an acceptable variation.

Exercises

Exercise 1 — Write your own version of the analytics teammate's message. Using what you know about fact_store_activity (the cumulative table design from data-modeling-for-analytics-guide's module 6), write, in 2-3 sentences, a message similar to this lesson's, but focused specifically on the problem of not knowing whether the store activity data is up to date.

See solution

One possible version: "We have a store-activity report — how many active days each one had in the last 7 and 30 days — that runs from a script on my laptop. Last week someone used it to decide which store to reinforce staffing at, and I later found out the script hadn't run in four days. Can we bring it into the dbt project, so it rebuilds automatically every time someone runs dbt build, instead of depending on me remembering to run it by hand?" The central problem — same as in the original message — isn't that the calculation is wrong, it's that nobody can trust when it was last calculated, with no dependency on one specific person.

Exercise 2 — Identify which row in the "what changes and what doesn't" table is riskiest to mistranslate. Of this lesson's table's five rows, which mart carries the highest risk of the dbt translation producing a different result from the original, and why?

See solution

fact_store_activity is the riskiest, because it's the only one of the five where the build mechanism changes fundamentally — from a Python loop that processes one value at a time, to a SQL window function that recalculates everything in a single pass. Even though data-modeling-for-analytics-guide's module 6 lesson 6 already verified, with zero differences, that both approaches give the same result, it's still the deepest mechanism change of the five: the other four pieces (dim_category, dim_order_flags, fact_sessions, mart_daily_sales_obt) were already, at their origin, declarative SQL queries — only where they read data from changes — while fact_store_activity also changes how it gets calculated, not just where the data comes from.

Exercise 3 — Explain why dim_product_snapshot, joined with a point-in-time join, replaces a flat dim_product in mart_daily_sales_obt with no guarantee lost. In 2-3 sentences, using what you know from module 5, explain why joining fact_orders against dim_product_snapshot with the condition order_ts >= dbt_valid_from AND order_ts < COALESCE(dbt_valid_to, ...) gives a correct result, even though Kiosko's catalog has changed since the sales happened.

See solution

The point-in-time join selects, for each fact_orders line, the product version that was real at that exact sale's moment — neither before that version existed, nor after it closed — which is a guarantee a flat, un-historized dim_product could never give: a flat table only knows the current state, so it would join any sale against today's version, regardless of when it really happened. dim_product_snapshot can also answer questions about the past — what category did P002 have before August 15 — something a flat dim_product could never do; and for mart_daily_sales_obt's purpose (a wide table with each sale's real state, not a mirror of today's catalog), the point-in-time join is, precisely, the piece that makes the result historically correct, not just "up to date."

Summary and next step

This brief wrote no SQL — it justified, with a concrete business scenario, why the five pieces data-modeling-for-analytics-guide already designed need to live inside the same versioned project as the rest of Kiosko. You saw, mart by mart, what translates literally and what only changes mechanism (the data source, never the result), and why dim_product_snapshot — not a flat dim_product this project never built — is the right piece to complete mart_daily_sales_obt.

Before moving on you should be able to: explain, in your own words, the difference between "porting" and "redesigning"; and name which of the five new pieces has the biggest mechanism change between the original version and the dbt version.

Lesson 3 writes the real SQL: the five .sql files in models/marts/, each with the exact pattern of a module you already know.

Resources

• data-modeling-for-analytics-guide — modules 3, 6, and 7, the exact source of the dimensional design this module ports. This ecosystem's sibling guide.
• dbt Labs — "What is analytics engineering?," already cited in this guide's module 1, the same definition — treating transformation with a software team's practices — that carries this brief's whole argument. getdbt.com/blog/what-is-analytics-engineering. In English.
• dbt Developer Hub — "About dbt projects," already cited in modules 1 and 2, again confirming why models/marts/ is the only place these five pieces need to live. docs.getdbt.com/docs/build/projects. In English.