Lab 03 — Transform — Silver & Gold with dbt

The goal

Raw data is honest but unusable. A rental row in bronze still carries source quirks: a price stored as text, the same record landed twice by an at-least-once ingest, columns named the way the operational database happened to name them. 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"]

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

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 as text. We cast, normalize the email, and keep the latest row per customer in case ingest landed a duplicate:

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

cleaned as (
    select
        cast(customer_id as integer)        as customer_id,
        trim(company_name)                  as company_name,
        lower(trim(email))                  as email,
        upper(trim(country))                as country,
        cast(created_at as timestamp)       as created_at,
        cast(_ingested_at as timestamp)     as ingested_at
    from source
    where customer_id is not null
),

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

select
    customer_id, company_name, email, country, created_at
from deduped
where rn = 1

stg_gpus

The GPU catalog — model name, VRAM, and the architecture generation:

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

select
    cast(gpu_id as integer)        as gpu_id,
    trim(model)                    as model,
    upper(trim(architecture))      as architecture,
    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 timestamps and the duration, 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(started_at as timestamp)     as started_at,
        cast(ended_at as timestamp)       as ended_at,
        cast(_ingested_at as timestamp)   as ingested_at
    from source
    where rental_id is not null
),

with_hours as (
    select *,
        -- billable hours, rounded up; ongoing rentals (no end) excluded downstream
        date_diff('minute', started_at, ended_at) / 60.0 as hours
    from cleaned
),

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

select
    rental_id, customer_id, gpu_id, started_at, ended_at, hours
from deduped
where rn = 1 and ended_at is not null

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 effective_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 effective_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(effective_from as timestamp)     as effective_from
    from source
    where gpu_id is not null
),

windowed as (
    select
        gpu_id,
        price_per_hour,
        effective_from as valid_from,
        -- this version ends when the next price for this GPU begins
        lead(effective_from) over (
            partition by gpu_id order by effective_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,
    company_name,
    email,
    country,
    created_at
from {{ ref('stg_customers') }}

select
    gpu_id,
    model,
    architecture,
    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 the range of the data using DuckDB's range generator:

with days as (
    select cast(d as date) as date_day
    from range(date '2025-01-01', date '2027-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:
              to: ref('dim_customer')
              field: customer_id
      - name: revenue
        tests: [not_null]

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.x  Found 8 models, 4 sources, 4 tests
1 of 12 OK created sql view model silver.stg_customers ...... [OK in 0.05s]
2 of 12 OK created sql view model silver.stg_gpus ........... [OK in 0.04s]
3 of 12 OK created sql view model silver.stg_rentals ........ [OK in 0.05s]
4 of 12 OK created sql view model silver.dim_gpu_price ...... [OK in 0.06s]
5 of 12 OK created sql table model gold.dim_customer ........ [OK in 0.07s]
6 of 12 OK created sql table model gold.dim_gpu ............. [OK in 0.05s]
7 of 12 OK created sql table model gold.dim_date ............ [OK in 0.08s]
8 of 12 OK created sql table model gold.fct_rentals ......... [OK in 0.09s]
9 of 12 PASS unique_fct_rentals_rental_id ................... [PASS]
...
Completed successfully
Done. PASS=12 WARN=0 ERROR=0 SKIP=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 ..
uv run 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-03    │ H100 SXM     │     142 │      3380.5 │ 27044.00 │
│ 2026-03    │ A100 80GB    │     118 │      2610.0 │ 13050.00 │
│ 2026-04    │ H100 SXM     │     160 │      3902.0 │ 33167.00 │
│ ...        │ ...          │     ... │         ... │      ... │
└────────────┴──────────────┴─────────┴─────────────┴──────────┘

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

• Can you explain the difference between the silver and gold layers — what each is for?
• Why do staging models hold all the cleaning, and not the marts?
• How does the SCD2 join guarantee each rental is priced at the rate that was live when it started — and why would joining on the current price be wrong?
• In the star schema, which table is the fact and which are dimensions? What's the grain of fct_rentals?
• What does dbt build do that dbt run alone does not?

select
    customer_id,
    company_name,
    email,
    country,
    case when country = 'US' then 'domestic'
         else 'international' end as tier,
    created_at
from {{ ref('stg_customers') }}

        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 .. && uv run duckdb warehouse.duckdb \
  "select tier, count(*) from gold.dim_customer group by 1;"

Next

Batch tables are tamed — now let's handle the hard part: a high-volume stream of GPU telemetry that you can't just load row by row. → Lab 04 — The Telemetry Firehose