Your First dbt Project

31.1 Installing dbt with the DuckDB Adapter (15 min)

dbt talks to each database through an adapter. For DuckDB we use the community adapter dbt-duckdb, which also installs dbt-core.

cd /workspaces/macro-data-course
source .venv/bin/activate
pip install dbt-duckdb
pip freeze > requirements.txt
dbt --version

The output lists the dbt-core version and the installed duckdb plugin. Note both in your notes: the dbt documentation is versioned, so always read the version matching yours.

dbt Labs also develops a newer engine ("Fusion") alongside the open-source dbt-core. This course uses dbt-core + dbt-duckdb, which is free, open source and runs entirely inside your Codespace. The SQL and project concepts carry over.

Recommended: install the VS Code extension "dbt Power User" for syntax highlighting of Jinja-SQL, go-to-definition on ref(), and lineage previews. It's optional, since everything today works from the terminal.

31.2 Creating the Project (25 min)

Step 1 — Scaffold

dbt init macro_dbt --skip-profile-setup
cd macro_dbt
ls -R

dbt init creates a skeleton: dbt_project.yml, folders (models/, seeds/, macros/, tests/, analyses/, snapshots/) and an example model. Delete the example:

rm -rf models/example
mkdir -p models/staging models/intermediate models/marts

Add dbt's working folders to the repository's .gitignore:

macro_dbt/target/
macro_dbt/dbt_packages/
macro_dbt/logs/

Step 2 — dbt_project.yml: the project's settings

Replace its content with:

name: macro_dbt
version: "1.0.0"
config-version: 2
profile: macro_dbt              # which connection profile to use (next step)

model-paths: ["models"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
test-paths: ["tests"]
analysis-paths: ["analyses"]
snapshot-paths: ["snapshots"]

target-path: "target"           # compiled SQL and run artefacts
clean-targets: ["target", "dbt_packages"]

models:
  macro_dbt:
    +materialized: view         # default for every model (Day 32 refines this)

YAML (like TOML on Day 23) is a settings format where indentation means nesting. Use spaces, never tabs. The + prefix marks a configuration (as opposed to a folder name).

Step 3 — profiles.yml: how to connect

dbt looks for profiles.yml in the current folder before looking in ~/.dbt/. Keeping it in the project makes the setup reproducible for everyone. There are no secrets here: a DuckDB connection is just a file path.

Create macro_dbt/profiles.yml:

macro_dbt:
  target: dev                       # default target
  outputs:
    dev:
      type: duckdb
      path: "{{ env_var('MACRO_DUCKDB_PATH', '../macro.duckdb') }}"
      schema: dev                   # base schema for dev builds
      threads: 4
    prod:
      type: duckdb
      path: "{{ env_var('MACRO_DUCKDB_PATH', '../macro.duckdb') }}"
      schema: main
      threads: 4

• A target is a named environment. dev is where you experiment; prod is what dashboards read. Same code, different destinations, just as on Day 23.
• {{ env_var('MACRO_DUCKDB_PATH', '../macro.duckdb') }} is your first piece of Jinja: read an environment variable, with a default. The path is relative to where you run dbt (the macro_dbt/ folder).
• Both targets point to the same file for now. They are kept apart by schema names: dev models will land in dev_* schemas (Day 32).

Step 4 — Check the connection

dbt debug

You want to see All checks passed!. If you see a lock error, quit any open duckdb macro.duckdb session first: DuckDB still allows only one writer (Day 13).

31.3 Sources: Telling dbt Where Raw Data Lives (23 min)

dbt distinguishes two kinds of inputs:

Declaring sources gives you one place to rename, document and (later) test raw tables, and puts them on the lineage graph.

Create models/staging/_sources.yml:

version: 2

sources:
  - name: raw_dlt
    description: "Raw tables loaded by macro_ingest (dlt), Module 3."
    schema: raw_dlt
    tables:
      - name: boc_observations
        description: "Bank of Canada Valet observations, long format, merged incrementally."
      - name: fred_observations
        description: "FRED graph CSV observations, long format."
      - name: statcan_observations
        description: "StatCan WDS vector observations, long format."
      - name: statcan_cpi_full
        description: "Full StatCan table 18-10-0004-01 (CPI), all columns as text."
      - name: statcan_gdp_full
        description: "Full StatCan table 36-10-0104-01 (GDP, expenditure-based), all columns as text."
      - name: _dlt_loads
        description: "dlt load log: one row per successful load."

The leading underscore in _sources.yml is a naming convention: YAML property files sort to the top of the folder, above the models.

dbt parse            # reads the whole project; reports YAML/Jinja errors without touching the database
dbt ls --resource-type source

31.4 Your First Models and dbt run (25 min)

A model is a .sql file containing one SELECT statement. The file name becomes the model (and table/view) name.

Model 1 — Hello, dbt

Create models/staging/hello_dbt.sql:

select 2.25 as overnight_rate, 3.0 as cpi_yoy, 2.25 - 3.0 as real_rate

dbt run --select hello_dbt

Look at the log: dbt created a view called hello_dbt in the schema dev. Check it:

cd .. && duckdb macro.duckdb -readonly -c "select * from dev.hello_dbt" && cd macro_dbt

You wrote a SELECT; dbt wrote CREATE VIEW dev.hello_dbt AS SELECT …. Delete hello_dbt.sql now, since it has served its purpose.

Style note: the dbt community writes SQL keywords in lowercase, and we follow that convention from now on. Case doesn't change the meaning. What matters is consistency.

Model 2 — Staging BoC observations from a source

Create models/staging/stg_boc__observations.sql:

-- Grain: one row per BoC series × observation date
with source as (

    select * from {{ source('raw_dlt', 'boc_observations') }}

)

select
    cast(date as date)  as obs_date,
    series_id,
    value,
    source              as source_system,
    _dlt_load_id
from source

dbt run --select stg_boc__observations

{{ source('raw_dlt', 'boc_observations') }} compiles to the real table name (something like "macro"."raw_dlt"."boc_observations"). If a source is ever renamed or moved, you change the YAML, not every model.

Model 3 — The full observations model (a port from Day 30)

Create models/staging/stg_macro__observations.sql, the dbt version of Day 30's sql/12_stg_macro__observations.sql. For today, series metadata still comes from the Module 2 seed table (we move it into dbt tomorrow):

-- Grain: one row per series × observation date, all providers
with unioned as (

    select date, series_id, value, source, _dlt_load_id
    from {{ source('raw_dlt', 'boc_observations') }}

    union all

    select date, series_id, value, source, _dlt_load_id
    from {{ source('raw_dlt', 'fred_observations') }}

    union all

    select date, series_id, value, source, _dlt_load_id
    from {{ source('raw_dlt', 'statcan_observations') }}

),

loads as (

    select load_id, inserted_at
    from {{ source('raw_dlt', '_dlt_loads') }}

)

select
    cast(u.date as date)  as obs_date,
    u.series_id,
    s.series_name,
    u.source,
    u.value,
    s.frequency,
    s.monthly_agg,
    l.inserted_at         as loaded_at
from unioned as u
join seeds.seed_series as s using (series_id)          -- temporary: Module 2 seed (Day 32 fixes this)
left join loads as l on l.load_id = u._dlt_load_id

dbt run --select staging

--select staging builds every model in the models/staging/ folder.

31.5 ref() and the DAG (23 min)

Models reference other models with {{ ref('model_name') }}. From these references dbt builds a DAG (directed acyclic graph): a map of which models depend on which. That's how dbt knows the build order, without any file numbering.

An intermediate model using ref()

Create models/intermediate/int_rates__monthly_long.sql:

-- Grain: one row per month × series (rates, FX, oil, CLI, unemployment)
select
    cast(date_trunc('month', obs_date) as date)  as month,
    series_name,
    case
        when monthly_agg = 'last' then arg_max(value, obs_date)
        else avg(value)
    end                                           as value
from {{ ref('stg_macro__observations') }}
group by all

dbt run --select int_rates__monthly_long

Selecting parts of the DAG

Preview a selection without running it:

dbt ls --select +int_rates__monthly_long
dbt ls --select stg_macro__observations+

Economist's analogy: the DAG is an input–output table for your data. Change a raw input and my_model+ tells you everything downstream that must be recomputed.

Try it 31.5

1. What does dbt ls --select +int_rates__monthly_long list, and why does it include sources?
2. Deliberately misspell the ref() (e.g. ref('stg_macro_observations')) and run dbt parse. Read the error message, then fix it.

31.6 Compiled SQL, dbt show, and a Regression Check (25 min)

What dbt actually runs

dbt compile --select int_rates__monthly_long
cat target/compiled/macro_dbt/models/intermediate/int_rates__monthly_long.sql
cat target/run/macro_dbt/models/intermediate/int_rates__monthly_long.sql

When something behaves oddly, read the compiled SQL first. It's plain DuckDB SQL you can paste into the CLI.

Previewing results without opening DuckDB

dbt show --select int_rates__monthly_long --limit 5
dbt show --inline "select series_name, count(*) as n from {{ ref('int_rates__monthly_long') }} group by 1"

Mini-project: port stg_statcan__cpi and prove it matches

1. Create models/staging/stg_statcan__cpi.sql, the dbt version of Day 30's sql/10_stg_statcan__cpi.sql: same logic and column order, reading {{ source('raw_dlt', 'statcan_cpi_full') }} and (temporarily) joining seeds.seed_geo.
2. Build it.
3. Prove it returns exactly the same rows as the Module 2 view staging.stg_statcan__cpi, using except in both directions (the Day 30 regression technique).

-- models/staging/stg_statcan__cpi.sql
-- Source: raw_dlt.statcan_cpi_full (StatCan 18-10-0004-01, CPI, monthly, NSA)
-- Grain:  one row per month × geography × product group

with source as (

    select * from {{ source('raw_dlt', 'statcan_cpi_full') }}

),

renamed as (

    select
        cast(ref_date || '-01' as date)       as month,
        geo,
        products_and_product_groups           as component,
        vector                                as vector_id,
        uom                                   as index_base,
        try_cast(value as double)             as cpi,
        nullif(trim(status), '')              as status_flag,
        coalesce(terminated = 't', false)     as is_terminated
    from source

)

select
    r.month,
    r.geo,
    g.geo_code,
    coalesce(g.geo_level, 'city')             as geo_level,
    r.component,
    r.vector_id,
    r.index_base,
    r.cpi,
    r.status_flag,
    r.is_terminated
from renamed as r
left join seeds.seed_geo as g using (geo)       -- temporary: Module 2 seed
where r.cpi is not null

dbt run --select stg_statcan__cpi

dbt show --inline "
select 'only_dbt' as side, * from (
    select * from {{ ref('stg_statcan__cpi') }}
    except
    select * from staging.stg_statcan__cpi)
union all
select 'only_module2', * from (
    select * from staging.stg_statcan__cpi
    except
    select * from {{ ref('stg_statcan__cpi') }})
" --limit 20

Day 31 checklist

• dbt and the DuckDB adapter are installed; dbt debug passes
• I understand dbt_project.yml (project settings) vs profiles.yml (connections and targets)
• I declare raw tables as sources and read them with source()
• I write models as single SELECTs and connect them with ref()
• I can select parts of the DAG with +, folders and paths
• I read compiled SQL, preview with dbt show, and verify ports with except

cd ..
git add .
git commit -m "Day 31: dbt project, sources, first models, DAG"
git push