Lab 03 — Transform — Silver & Gold with dbt

The goal

Raw data is honest but unusable. A rental row in bronze still carries source quirks: columns named the way the operational database happened to name them (name, not customer_name), timestamps in the source's time zone, a duration but no end time, and nothing to stop an at-least-once ingest from landing the same record twice. Nobody wants to write analytics against that. The job of transformation is to refine bronze into silver (clean, typed, deduplicated, well-named) and then into gold (modeled for analysis — a star schema of dimensions and facts).

This is the medallion architecture, and dbt is the tool that makes each layer a versioned, testable SQL file. Here's the flow you're building this lab — the middle of the platform diagram from Start Here:

The deliverable: a gold.fct_rentals fact table where every rental carries the revenue it actually earned — computed from the GPU's price as it was at the moment the rental started, not today's price. That "as-of-then" join is the heart of the lab, and it's why we need a slowly-changing dimension.

Set up the dbt project

dbt needs two things: a project (your models and config, in dbt/) and a profile (how to connect to the warehouse). Create the project structure inside the dbt/ folder the repo skeleton already has:

cd mini-griddp
mkdir -p dbt/models/staging dbt/models/marts

The project file names the project and tells dbt how to materialize each folder of models — staging as views (cheap, always fresh), marts as tables (materialized once, fast to query):

name: mini_griddp
version: "1.0"
profile: mini_griddp

model-paths: ["models"]
macro-paths: ["macros"]

models:
  mini_griddp:
    staging:
      +schema: silver
      +materialized: view
    marts:
      +schema: gold
      +materialized: table

The profile points dbt at the DuckDB file you built in Lab 02. Keep it inside the project so the whole thing is self-contained and runnable from CI later:

mini_griddp:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ../warehouse.duckdb   # the file from Lab 02, relative to dbt/
      schema: main
      threads: 4

One macro is needed before the first build. dbt's default rule for a custom schema is target schema + _ + custom schema, so +schema: silver would create main_silver and +schema: gold would create main_gold. Override generate_schema_name so the folders map to plain silver and gold, which is what every query and every later lab uses:

{#- Use a folder's +schema as the schema name itself (silver, gold),
    instead of dbt's default <target schema>_<custom schema> (main_silver). -#}
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- if custom_schema_name is none -%}
        {{ target.schema }}
    {%- else -%}
        {{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

Finally, declare the bronze tables as sources. A source is dbt's name for "a table I didn't build but want to reference." Declaring them lets you write {{ source('bronze', 'rentals') }} instead of hard-coding schema names, and gives dbt a place to attach freshness and tests later:

version: 2

sources:
  - name: bronze
    schema: bronze
    description: Raw tables landed by the Lab 02 ingest.
    tables:
      - name: customers
      - name: gpus
      - name: rentals
      - name: gpu_prices

Silver: staging models

Staging models are the silver layer. Each one maps one source table to a clean, conformed version: cast types, rename columns to a consistent style, trim junk, and deduplicate. The rule of thumb — one staging model per source table, no joins, no business logic yet. Just make the raw data trustworthy.

stg_customers

Bronze landed customers exactly as Postgres holds them. We cast, rename name to the warehouse-wide customer_name, normalize the tier, and keep one row per customer in case ingest ever lands a duplicate:

with source as (
    select * from {{ source('bronze', 'customers') }}
),

cleaned as (
    select
        cast(customer_id as integer)        as customer_id,
        trim(name)                          as customer_name,
        lower(trim(tier))                   as tier,
        cast(created_at as timestamp)       as created_at
    from source
    where customer_id is not null
),

deduped as (
    select *,
        row_number() over (
            partition by customer_id
            order by created_at desc
        ) as rn
    from cleaned
)

select
    customer_id, customer_name, tier, created_at
from deduped
where rn = 1

stg_gpus

The GPU catalog — model name and VRAM:

with source as (
    select * from {{ source('bronze', 'gpus') }}
)

select
    cast(gpu_id as integer)        as gpu_id,
    trim(model)                    as model,
    cast(vram_gb as integer)       as vram_gb
from source
where gpu_id is not null

stg_rentals

The grain of the business: one row per rental. We cast the timestamp and the duration, derive the end time the source never stored, and deduplicate on rental_id the same way:

with source as (
    select * from {{ source('bronze', 'rentals') }}
),

cleaned as (
    select
        cast(rental_id as integer)        as rental_id,
        cast(customer_id as integer)      as customer_id,
        cast(gpu_id as integer)           as gpu_id,
        cast(hours as decimal(8, 2))      as hours,
        cast(started_at as timestamp)     as started_at
    from source
    where rental_id is not null
),

with_end as (
    select *,
        -- the source records a duration; derive when the rental ended
        started_at + interval 1 hour * hours as ended_at
    from cleaned
),

deduped as (
    select *,
        row_number() over (
            partition by rental_id order by started_at desc
        ) as rn
    from with_end
)

select
    rental_id, customer_id, gpu_id, started_at, ended_at, hours
from deduped
where rn = 1

We'll also stage the price history as a fourth silver model — but because prices change over time, it needs special handling. That's the next section.

SCD2: prices over time

Here's the wrinkle. A GPU's hourly price isn't fixed — it changes as the market moves. Bronze captured every price change as a row in gpu_prices: a gpu_id, a price_per_hour, and the valid_from timestamp when that price took effect. So one GPU has a history of prices.

Now think about revenue. A rental that started in March should be billed at March's price, even if the price changed in April. If we just joined to "the current price," every historical rental would be re-priced at today's rate — and the numbers would be wrong. We need to join each rental to the price that was active at the moment it started.

This is exactly the Slowly Changing Dimension Type 2 pattern from Course 4, Chapter 03: instead of overwriting, we keep every version of a row and stamp each with a validity window — valid_from, valid_to, and an is_current flag. To find the price in effect on a given date, you find the one version whose window contains that date.

We can build this directly in SQL with a window function: order each GPU's price changes by time, and the next change's valid_from becomes this version's valid_to. The latest version gets an open-ended valid_to far in the future and is_current = true:

-- SCD2 dimension: one row per (gpu, price-validity-window).
-- valid_from / valid_to define when each price was in effect.
with source as (
    select * from {{ source('bronze', 'gpu_prices') }}
),

cleaned as (
    select
        cast(gpu_id as integer)               as gpu_id,
        cast(price_per_hour as decimal(10,2)) as price_per_hour,
        cast(valid_from as timestamp)         as valid_from
    from source
    where gpu_id is not null
),

windowed as (
    select
        gpu_id,
        price_per_hour,
        valid_from,
        -- this version ends when the next price for this GPU begins
        lead(valid_from) over (
            partition by gpu_id order by valid_from
        ) as next_change
    from cleaned
)

select
    gpu_id,
    price_per_hour,
    valid_from,
    coalesce(next_change, timestamp '9999-12-31') as valid_to,
    (next_change is null)                          as is_current
from windowed

Gold: the star schema

Gold is modeled for analysis. The classic shape is a star schema: a central fact table of measurable events (rentals), surrounded by dimension tables that describe the who/what/when (customer, GPU, date). Analysts join the fact to a few dimensions and slice. Let's build the points of the star first.

dim_customer & dim_gpu

Dimensions here are thin pass-throughs of the staging models — for a Type-1 dimension like the customer, silver is already the right shape:

select
    customer_id,
    customer_name,
    tier,
    created_at
from {{ ref('stg_customers') }}

select
    gpu_id,
    model,
    vram_gb
from {{ ref('stg_gpus') }}

dim_date

A date dimension lets you group "by month" or "by weekday" without scattering date math across every query. We generate one row per day across a range that comfortably covers the data (the generator dates everything relative to today) using DuckDB's range generator:

with days as (
    select cast(d as date) as date_day
    from range(date '2024-01-01', date '2030-01-01', interval 1 day) as t(d)
)

select
    date_day,
    cast(strftime(date_day, '%Y%m%d') as integer) as date_key,
    year(date_day)                                as year,
    month(date_day)                               as month,
    strftime(date_day, '%Y-%m')                   as year_month,
    dayofweek(date_day)                           as day_of_week
from days

fct_rentals

The fact table is where it all comes together. Each row is one completed rental, with foreign keys to the dimensions and the measures we care about — hours and, crucially, revenue. To get revenue we join each rental to dim_gpu_price on the GPU and on the validity window that contains started_at — the SCD2 "as-of-then" join:

with rentals as (
    select * from {{ ref('stg_rentals') }}
),

prices as (
    select * from {{ ref('dim_gpu_price') }}
),

priced as (
    select
        r.rental_id,
        r.customer_id,
        r.gpu_id,
        r.started_at,
        r.ended_at,
        cast(strftime(r.started_at, '%Y%m%d') as integer) as date_key,
        round(r.hours, 2)                                  as hours,
        p.price_per_hour,
        round(r.hours * p.price_per_hour, 2)               as revenue
    from rentals r
    -- join the price version whose validity window contains started_at
    left join prices p
        on  r.gpu_id = p.gpu_id
        and r.started_at >= p.valid_from
        and r.started_at <  p.valid_to
)

select * from priced

Read the join slowly: for each rental we pick the single price row where started_at falls inside [valid_from, valid_to). Because the windows don't overlap, exactly one matches — and it's the price that was live when the rental began. revenue = hours × that price. That's the whole point of the lab in three lines of SQL.

Document the marts so dbt knows the relationships (and so the docs site, later, has something to show):

version: 2

models:
  - name: fct_rentals
    description: One row per completed rental, priced as-of the start time.
    columns:
      - name: rental_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:
              arguments:
                to: ref('dim_customer')
                field: customer_id
      - name: revenue
        tests: [not_null]

The arguments: nesting is the dbt 1.10+ form; older projects put to and field directly under the test name, which still runs but prints a deprecation warning.

Build & query

One command compiles every model, runs them in dependency order (sources → staging → marts), and runs the tests. From the dbt/ folder:

cd mini-griddp/dbt
uv run dbt build --profiles-dir .

Running with dbt=1.12.6
Found 8 models, 4 data tests, 4 sources, 512 macros
1 of 12 OK created sql table model gold.dim_date ............ [OK in 0.13s]
2 of 12 OK created sql view model silver.dim_gpu_price ...... [OK in 0.11s]
3 of 12 OK created sql view model silver.stg_customers ...... [OK in 0.11s]
4 of 12 OK created sql view model silver.stg_gpus ........... [OK in 0.10s]
5 of 12 OK created sql view model silver.stg_rentals ........ [OK in 0.04s]
6 of 12 OK created sql table model gold.dim_gpu ............. [OK in 0.04s]
7 of 12 OK created sql table model gold.dim_customer ........ [OK in 0.04s]
8 of 12 OK created sql table model gold.fct_rentals ......... [OK in 0.03s]
9 of 12 PASS not_null_fct_rentals_rental_id ................. [PASS in 0.06s]
...
Completed successfully
Done. PASS=12 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=12

Now the payoff. Query the gold layer directly and ask a real analytical question — revenue by GPU model by month — joining the fact to two dimensions exactly the way the star schema is meant to be used:

cd ..
duckdb warehouse.duckdb

select
    d.year_month,
    g.model,
    count(*)                 as rentals,
    round(sum(f.hours), 1)   as total_hours,
    round(sum(f.revenue), 2) as revenue
from gold.fct_rentals f
join gold.dim_gpu  g on f.gpu_id   = g.gpu_id
join gold.dim_date d on f.date_key = d.date_key
group by 1, 2
order by 1, revenue desc;

┌────────────┬──────────────────────┬─────────┬─────────────┬──────────┐
│ year_month │        model         │ rentals │ total_hours │ revenue  │
├────────────┼──────────────────────┼─────────┼─────────────┼──────────┤
│ 2026-08    │ NVIDIA H100 80GB     │      14 │       563.4 │  2433.99 │
│ 2026-08    │ AMD MI300X 192GB     │      14 │       571.4 │  2216.95 │
│ 2026-08    │ NVIDIA A100 80GB     │      12 │       566.4 │  1500.95 │
│ ...        │ ...                  │     ... │         ... │      ... │
└────────────┴──────────────────────┴─────────┴─────────────┴──────────┘

Commit Lab 03

You just added the entire modeling layer to the platform. Commit it. Note we commit the dbt project but not the rebuildable artifacts (target/, logs) or the warehouse file itself — those are generated, so they belong in .gitignore:

cd mini-griddp
printf 'dbt/target/\ndbt/logs/\ndbt/dbt_packages/\n*.duckdb\n' >> .gitignore
git add dbt/ .gitignore
git commit -m "Lab 03: silver + gold dbt models with SCD2 pricing"

Your history now tells the story: raw ingest in Lab 02, a clean modeled warehouse in Lab 03. That's a portfolio-grade commit.

✓ Check yourself

Answer from memory before opening each one.

1. The H100's price rises on 1 April. A rental starts on 28 March and runs for 100 hours, ending in April. What rate does fct_rentals use, and for how many of the hours?

   Show answer

   The March price, for all 100 hours. The join picks the single price version whose window contains started_at; ended_at plays no part in it. Joining to the current price instead would re-price every historical rental at today's rate. See fct_rentals and SCD2: prices over time.

2. A rental's started_at is exactly equal to a price change's valid_from. Which price version does it get — and what would happen to that rental if the join were written with BETWEEN valid_from AND valid_to?

   Show answer

   The new version: windows are half-open, valid_from <= t < valid_to, so a moment belongs to exactly one. BETWEEN is closed at both ends, so that timestamp matches the old version (whose valid_to equals it) and the new one — the rental appears twice in fct_rentals, its revenue is counted twice, and the unique test on rental_id fails. See Reading the validity window.

3. fct_rentals joins prices with a left join. A rental turns up whose started_at is earlier than that GPU's first valid_from. What does its row look like, which test notices, and what would an inner join have done?

   Show answer

   The rental is kept with price_per_hour and revenue both NULL, and the not_null test on revenue fails — the gap is visible. An inner join would drop the rental silently: revenue understated, every test green. See Gold: the star schema.

4. Tiers start arriving in mixed case (Pro next to pro) and a dashboard shows five tiers instead of three. A teammate patches it with lower(tier) inside dim_customer. Why is that the wrong layer?

   Show answer

   Cleaning is centralised in staging: every mart and metric builds on the stg_* models, so a fix made there is inherited everywhere. A fix in one mart leaves every other consumer of stg_customers with the dirty value and scatters cleaning rules across the project. (Here stg_customers already applies lower(trim(tier)) for exactly this reason.) See Silver: staging models.

5. Staging models are views; marts are tables. Lab 02 lands fifty new rentals in bronze. Before anyone runs dbt, which of stg_rentals and fct_rentals shows them, and why?

   Show answer

   stg_rentals does — a view is a stored query, so it reads bronze at the moment you query it. fct_rentals is a table written by the last dbt run and stays stale until the next dbt build. That is the trade in the project file: views are cheap and always fresh, tables are fast to query but only as fresh as the last build. See Set up the dbt project.

6. Suppose the source kept a single current price per GPU, overwritten in place, instead of a row per change. What happens to the lead() approach, and what would you reach for instead?

   Show answer

   It has nothing to work with — it derives the windows from a history that bronze already holds. When each load shows only the current state you need a dbt snapshot, which compares successive runs and builds the SCD2 rows itself. The price: history begins at the first snapshot, and a change that comes and goes between two runs is never seen. When the source records its own history, the window query wins — simpler, deterministic, rebuildable from scratch. See Why not a dbt snapshot?

select
    gpu_id,
    model,
    vram_gb,
    case when vram_gb >= 80 then 'large'
         else 'standard' end as vram_class
from {{ ref('stg_gpus') }}

        round(r.hours * p.price_per_hour, 2)                  as revenue,
        round(r.hours * p.price_per_hour
              * case when r.hours > 24 then 0.9 else 1.0 end,
              2)                                              as discount_revenue

cd mini-griddp/dbt
uv run dbt build --profiles-dir .
cd .. && duckdb warehouse.duckdb \
  "select vram_class, count(*) from gold.dim_gpu group by 1;"

Takeaway

• Silver is one staging model per source table that casts types, renames columns to one style and deduplicates, and gold is the star schema built on top of it.
• A price that changes over time becomes an SCD2 dimension: order each GPU's price changes and let the next change's valid_from be this version's valid_to, with an open-ended sentinel for the current row.
• Revenue on fct_rentals uses the price in force when the rental started, joined on valid_from <= started_at < valid_to, so exactly one price row matches.
• If the H100's revenue per hour differs between months at similar usage, that is the as-of join working, not a bug.
• Without a custom schema macro dbt writes to main_silver and main_gold, and with it the layers land in silver and gold.
• Staging models are views, cheap and always fresh, and marts are tables, built once and fast to query.
• dbt build runs models and tests in dependency order, so one command proves the whole layer.

The batch tables are tamed, and the next lab handles the harder case, a high-volume stream of GPU telemetry. → Lab 04 — The Telemetry Firehose

Further reading

• dbt: Sources — dbt Labs docs, accessed 2026. Declaring bronze tables and referencing them with source().
• dbt: Custom schemas — dbt Labs docs, accessed 2026. The default schema naming rule and how to override it.
• dbt: Materializations — dbt Labs docs, accessed 2026. Views for staging and tables for marts.
• Dimensional Modeling Techniques — Kimball Group. The star schema and the slowly changing dimension types behind the price table.
• DuckDB: Window functions — DuckDB docs, accessed 2026. The lead function that builds each version's validity window.