Rebuilding `dim_store` and `dim_date`

Description

Time to write the project's first two real marts. dim_store is as simple as it gets: an almost literal translation of stg_stores, with ref() instead of a table name, materialized as table instead of view. dim_date is different in an important sense, already previewed in lesson 1: it depends on no ref() or source() at all — it generates its own fixed date range, with pure SQL, using a technique you may not know yet: a recursive CTE.

This lesson builds both, runs them with dbt run, and confirms their exact results — three stores, thirty-one complete days of August 2026 — before lesson 5 uses them, together with stg_orders, to build fact_orders.

Connection to the module. Lessons 2 and 3 gave you ref() and materializations separately, with small, deliberately trivial examples. This lesson is where both pieces combine for the first time in a model that's actually going to stay in the project.

Worked example: dim_store.sql

Inside models/marts/ (create it if it doesn't exist yet), the project's first real mart:

-- models/marts/dim_store.sql
select
    store_id,
    store_name,
    city
from {{ ref('stg_stores') }}

There's no mystery here — it's, literally, stg_stores with no changes at all, just now referenced with ref() instead of queried directly, and materialized as table instead of view (inherited from the change you're going to make in dbt_project.yml, this module's lesson 6 — for now, it still runs fine, with the table materialization you're going to set later). Why such a simple mart, if you already had stg_stores? Because dim_store fills a different role inside the project: it's the star schema piece other marts — fact_orders, in lesson 5 — are going to reference by its dimension name, not by its staging name. That layer distinction — staging cleans the data, marts organize it into the final dimensional model — is the same one you already saw in module 2, applied one level up now.

Run the model:

dbt run --select dim_store

What to expect.

1 of 1 START sql table model main.dim_store .................................... [RUN]
1 of 1 OK created sql table model main.dim_store ............................... [OK in 0.06s]

Finished running 1 table model in 0 hours 0 minutes and 0.16 seconds (0.16s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

Notice sql table model — not view model, like you saw in module 2. Confirm the result:

dbt show --select dim_store --output json

What to expect.

{
  "node": "dim_store",
  "show": [
    {"store_id": "S01", "store_name": "Kiosko Centro", "city": "Bogota"},
    {"store_id": "S02", "store_name": "Kiosko Norte", "city": "Lima"},
    {"store_id": "S03", "store_name": "Kiosko Sur", "city": "Santiago"}
  ]
}

Three stores, identical to stg_stores — the translation to ref() didn't change a single piece of data, only the way that data ends up organized inside the project.

Worked example (continued): dim_date.sql, with no ref() at all

dim_date is different by design: a date dimension describes no specific Kiosko data — there's no dates.csv file in raw_data/ — it describes the calendar itself, something that can be generated in full with SQL, for whatever range the business needs. As you saw in lesson 1's exercise 2, that's why this mart has no ref() or source() in its FROM.

-- models/marts/dim_date.sql
with recursive date_spine as (
    select date '2026-08-01' as calendar_date
    union all
    select calendar_date + interval 1 day
    from date_spine
    where calendar_date < date '2026-08-31'
)

select
    cast(strftime(calendar_date, '%Y%m%d') as integer) as date_key,
    calendar_date,
    strftime(calendar_date, '%A') as day_of_week,
    extract(month from calendar_date) as month,
    extract(quarter from calendar_date) as quarter,
    extract(year from calendar_date) as year,
    extract(dow from calendar_date) in (0, 6) as is_weekend
from date_spine

It's worth reading this in two parts, because it combines two ideas you may never have seen together.

Part 1 — the recursive CTE that generates the range

A recursive CTE (WITH RECURSIVE) is a query that references itself to build a result step by step, instead of computing it all at once. It always has three pieces:

• The anchor case: select date '2026-08-01' as calendar_date — the first row, the starting point. Without this row, the recursion would have nowhere to start from.
• The recursive case: select calendar_date + interval 1 day from date_spine where ... — notice that date_spine appears inside its own definition. Every time DuckDB runs this part, it takes the most recent row it already generated and produces the next one, adding one day.
• The cutoff condition: where calendar_date < date '2026-08-31'. Without this clause, the recursion would never finish — it would generate dates forever, until it ran out of memory or time. DuckDB documents this explicitly: "the query must be formulated in a way that ensures termination, otherwise, it may run into an infinite loop" — the cutoff condition isn't optional, it's the part that guarantees the query terminates.

date_spine's result, before any calculated column, is a single column — calendar_date — with 31 rows: 2026-08-01, 2026-08-02, ..., up to 2026-08-31. The UNION ALL connects the anchor case with the recursive case, exactly the way you already used UNION ALL in module 1 to combine two SELECTs — the only difference is that here one of the two halves refers to itself.

Part 2 — the calculated columns over each date

With the complete range already generated, the final SELECT computes six more columns, each with a DuckDB date function:

• date_key — a YYYYMMDD integer (for example, 20260803), the most common way to give a date dimension a numeric key that's sortable and readable at a glance.
• day_of_week — the day's full name (Monday, Tuesday...), with strftime(..., '%A').
• month, quarter, year — extracted directly with extract(...), each one an integer.
• is_weekend — true if extract(dow from calendar_date) gives 0 (Sunday) or 6 (Saturday), DuckDB's convention for the day of the week in numeric form (0 = Sunday, 6 = Saturday — verifiable by running extract(dow from date '2026-08-02'), which gives 0, confirming that August 2, 2026 is, in fact, a Sunday).

Run the model:

dbt run --select dim_date

What to expect.

1 of 1 START sql table model main.dim_date ..................................... [RUN]
1 of 1 OK created sql table model main.dim_date ................................ [OK in 0.09s]

Finished running 1 table model in 0 hours 0 minutes and 0.14 seconds (0.14s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

Verify the total row count, and the range's first few days:

dbt show --select dim_date --limit 5 --output json

What to expect.

{
  "node": "dim_date",
  "show": [
    {"date_key": 20260801, "calendar_date": "2026-08-01", "day_of_week": "Saturday", "month": 8, "quarter": 3, "year": 2026, "is_weekend": true},
    {"date_key": 20260802, "calendar_date": "2026-08-02", "day_of_week": "Sunday",   "month": 8, "quarter": 3, "year": 2026, "is_weekend": true},
    {"date_key": 20260803, "calendar_date": "2026-08-03", "day_of_week": "Monday",   "month": 8, "quarter": 3, "year": 2026, "is_weekend": false},
    {"date_key": 20260804, "calendar_date": "2026-08-04", "day_of_week": "Tuesday",  "month": 8, "quarter": 3, "year": 2026, "is_weekend": false},
    {"date_key": 20260805, "calendar_date": "2026-08-05", "day_of_week": "Wednesday","month": 8, "quarter": 3, "year": 2026, "is_weekend": false}
  ]
}

August 1, 2026 falls on a Saturday — is_weekend: true — and Kiosko's real order week (August 3 to 9, which you already know from earlier modules) falls Monday through Sunday, fully contained inside this range. Confirm the total:

dbt show --inline "select count(*) as n from {{ ref('dim_date') }}"

What to expect.

Previewing inline node:
|  n |
| -- |
| 31 |

Thirty-one days — the whole month of August, not one more, not one less. And confirm the weekend breakdown, an independent check that is_weekend computed correctly:

dbt show --inline "select is_weekend, count(*) as n from {{ ref('dim_date') }} group by is_weekend order by is_weekend"

What to expect.

Previewing inline node:
| is_weekend |  n |
| ---------- | -- |
| false      | 22 |
| true       |  9 |

22 weekdays plus 9 weekend days makes 31 — August 2026 has four Saturdays and five Sundays (or the other way around, depending on which day the month starts on), and the number lines up exactly with the total count you already confirmed.

Going deeper: why not dbt.date_spine?

If you search "date spine" in dbt's documentation, you're going to find dbt_utils.date_spine() — a macro from the official dbt_utils package that generates exactly the same kind of range you just built by hand, with this syntax:

{{ dbt_utils.date_spine(
    datepart="day",
    start_date="cast('2026-08-01' as date)",
    end_date="cast('2026-09-01' as date)"
   )
}}

(Notice end_date is exclusive — to include all of August, the correct end_date is September 1, not August 31.) This guide doesn't use that macro, on purpose, for two concrete reasons, not to avoid something "harder":

• Installing a package is a real extra step. Using dbt_utils requires declaring a packages.yml file, running dbt deps (which downloads the package from the dbt Hub, and so needs an internet connection), and understanding the concept of a "dbt package" — reusable Jinja code another team wrote and published. This guide covers macros and packages in module 7, not before; introducing dbt_utils here would pull a concept out of place, just for one date dimension.
• The recursive CTE is pure SQL, with no external dependency at all. Everything you ran in this lesson is standard DuckDB — the same engine you've known since module 1, with nothing new to install and no dependency on the dbt Hub being available the day someone clones this project.

Neither way is "the correct one" in the abstract — in a large team's dbt project, with many date dimensions repeated across projects, dbt_utils.date_spine() saves rewriting the same recursive CTE over and over, and that's why it's the more common choice in the industry. This guide chooses the recursive CTE because it's SQL you already know how to read in full, with no package in the way — and because, for a single fixed one-month range, the practical difference between the two is minimal.

Common mistakes

Forgetting the cutoff condition in the recursive CTE. What happens: someone writes select calendar_date + interval 1 day from date_spine with no WHERE at all, expecting the recursion to "know" when to stop. Why it happens: in a normal (non-recursive) UNION ALL, there's no concept of "stopping" at all — each half is a complete, independent query, so it isn't intuitive that a recursive CTE does need an explicit condition. How to spot it: with no cutoff condition, the query runs indefinitely (or up to some internal DuckDB limit, if one exists), never finishing with a result — DuckDB's own manual explicitly warns about this risk. How to fix it: every recursive CTE needs a condition on the recursive case that eventually stops holding — in this model, where calendar_date < date '2026-08-31', which dbt stops satisfying as soon as calendar_date reaches the range's last day.

Confusing date '2026-08-01' (a date literal) with the text '2026-08-01' (a string). What happens: someone writes select '2026-08-01' as calendar_date (without the word date before it), and the addition calendar_date + interval 1 day fails or behaves unexpectedly. Why it happens: DuckDB, like many SQL engines, distinguishes between a typed date literal (date '2026-08-01') and a text string that looks like a date ('2026-08-01') — adding an interval to a text string doesn't mean the same thing as adding it to a real date. How to spot it: if dim_date fails with a type error at compile time, or if calendar_date ends up as varchar instead of date in the final result, check that every date literal has the explicit date prefix. How to fix it: always write date 'YYYY-MM-DD' when you want a real date literal in DuckDB — the same explicit-typing discipline you already applied with cast() in module 2's staging models.

Expecting dim_date to automatically cover any future Kiosko order date. What happens: someone adds an order with order_ts from September 2026 (outside dim_date's current range) and is surprised when that order disappears from fact_orders in lesson 5 (where dim_date takes part in a JOIN). Why it happens: it's easy to think of a date dimension as "every possible date," instead of a fixed, deliberate range. How to spot it: any date outside 2026-08-01 through 2026-08-31 simply doesn't exist in dim_date — a JOIN against that dimension, if it's an INNER JOIN, is going to silently drop any row with a date outside the range. How to fix it: dim_date's range is an explicit design decision, not an accident — for this guide, all of August 2026 covers Kiosko's real order week (August 3 to 9) with room to spare; a real project generally defines a much wider range (several years), precisely so it doesn't have to be adjusted every time the business changes.

Exercises

Exercise 1 — Extend the range to two months. Modify dim_date.sql so the range covers August 1 through September 30, 2026 (two full months). Run dbt run --select dim_date and confirm the new total row count.

with recursive date_spine as (
    select date '2026-08-01' as calendar_date
    union all
    select calendar_date + interval 1 day
    from date_spine
    where calendar_date < date '2026-09-30'
)

select
    cast(strftime(calendar_date, '%Y%m%d') as integer) as date_key,
    calendar_date,
    strftime(calendar_date, '%A') as day_of_week,
    extract(month from calendar_date) as month,
    extract(quarter from calendar_date) as quarter,
    extract(year from calendar_date) as year,
    extract(dow from calendar_date) in (0, 6) as is_weekend
from date_spine

Exercise 2 — Add a month_name column. Extend dim_date.sql with a seventh column, month_name, showing the month's full name (August, not 8). Use the same strftime function you already used for day_of_week.

select
    cast(strftime(calendar_date, '%Y%m%d') as integer) as date_key,
    calendar_date,
    strftime(calendar_date, '%A') as day_of_week,
    strftime(calendar_date, '%B') as month_name,
    extract(month from calendar_date) as month,
    extract(quarter from calendar_date) as quarter,
    extract(year from calendar_date) as year,
    extract(dow from calendar_date) in (0, 6) as is_weekend
from date_spine

Exercise 3 — Argue why dim_date depends on no ref(). In 2-3 sentences, and using what you learned in this lesson and in lesson 1, explain why it makes sense for dim_date to be the only one of this module's three marts with no declared dependency inside the project.

Summary and next step

In this lesson you built the project's first two real marts: dim_store, an almost direct translation of stg_stores with ref() and table materialization, and dim_date, generated entirely with a recursive CTE — with no ref() or source() at all — covering all of August 2026 (31 rows, with date_key, day_of_week, month, quarter, year, and is_weekend computed). You saw WITH RECURSIVE's syntax — anchor case, recursive case, cutoff condition — and why this guide prefers that technique over the dbt_utils.date_spine() macro, without that meaning one is "better" than the other in every project.

Before moving on you should be able to: write a recursive CTE's three-part structure from memory; and explain why dim_date needs no external Kiosko data to exist.

Lesson 5 builds the star schema's central piece: fact_orders, joining stg_orders with these two marts — dim_store and dim_date — via ref(), this guide's first time a model depends on more than one source at once.

Resources

• DuckDB — "Common Table Expressions (CTEs)," the official documentation including the recursive CTE section, with the exact warning about needing a cutoff condition quoted in this lesson. duckdb.org/docs/stable/sql/query_syntax/with. In English.
• DuckDB — "Date Functions," the complete reference for strftime, extract, and date arithmetic (date + interval) used in dim_date.sql. duckdb.org/docs/stable/sql/functions/date. In English.
• dbt-utils — the official GitHub repository, with the date_spine() macro mentioned in this lesson's deeper dive, for when a real project does justify installing the package. github.com/dbt-labs/dbt-utils. In English.
• dbt Developer Hub — "ref()," again lesson 2's reference, this time applied to the project's first real mart (dim_store). docs.getdbt.com/reference/dbt-jinja-functions/ref. In English.