How to Build Your First dbt Project Against a Warehouse
dbt is a SQL transformation framework. You write models as SELECT statements, and dbt handles the dependency order, materialisation, testing and documentation. It’s free for one developer on dbt-core; Team plans on dbt Cloud start at $100 per developer per month.
The first project is mostly about conventions. The SQL is easy. Below is the setup that keeps a project maintainable after 60 models, plus the parts of it that people skip and regret.
How do you install dbt and connect a warehouse?
Pick one adapter and install it with its extra, not the bare dbt-core. The adapter is what knows your warehouse’s SQL dialect.
python3 -m venv ~/.venvs/dbt && source ~/.venvs/dbt/bin/activate
pip install dbt-postgres # or dbt-snowflake, dbt-bigquery, dbt-clickhouse
dbt --version
Then create the project:
dbt init analytics
The prompt asks which adapter, then for credentials. It writes them to ~/.dbt/profiles.yml, which should never be in the repo. For Postgres:
analytics:
target: dev
outputs:
dev:
type: postgres
host: localhost
user: dbt
password: "{{ env_var('DBT_PASSWORD') }}"
port: 5432
dbname: warehouse
schema: dbt_dev
threads: 4
threads: 4 controls parallelism. On a small warehouse, 2 is kinder; on Snowflake, 8 is normal. Raising it doesn’t make slow single models faster, it just runs independent models at the same time.
Test with dbt debug. You want “All checks passed”. The most common failure is the default schema being dbt_dev while your dbt user has no CREATE SCHEMA grant — dbt creates schemas on first run and errors out with a permission message that mentions a model name, not the schema.
How should models be organised?
Two layers before anything else, and resist adding a third until you have at least 30 models.
models/
staging/
stg_orders.sql
stg_customers.sql
_sources.yml
marts/
orders_daily.sql
customer_ltv.sql
Staging models do one row per source row, rename columns, cast types, and nothing else. No joins, no aggregation. They give you one place per source table where a renaming happens.
Mart models join and aggregate staging models into something a dashboard or analyst consumes.
The discipline is what matters: if a column is called user_id in the source and customer_id in your reporting, that rename happens once in stg_orders, not in eleven mart models.
Declare sources in _sources.yml rather than pointing at schemas directly:
sources:
- name: raw
database: warehouse
schema: raw
tables:
- name: orders
- name: customers
This gets you lineage, and source freshness checks that tell you when your loader stopped.
What does a model actually look like?
stg_orders.sql:
SELECT
id AS order_id,
customer_id,
status,
CAST(total_cents AS NUMBER(18,2)) / 100 AS total_amount,
created_at::timestamp AS created_at
FROM {{ source('raw', 'orders') }}
WHERE id IS NOT NULL
orders_daily.sql:
SELECT
DATE_TRUNC('day', o.created_at) AS order_date,
COUNT(*) AS orders,
SUM(o.total_amount) AS revenue,
COUNT(DISTINCT o.customer_id) AS buyers
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c USING (customer_id)
WHERE o.status = 'paid'
GROUP BY 1
ref() is the point of dbt. It resolves to the actual schema and table name for the current target, and it builds the DAG. Never hardcode dbt_dev.stg_orders — you’ll get dev and prod crossed.
Run it:
dbt run --select +orders_daily
The + means “this model and its ancestors”. +orders_daily+ would include descendants. --select beats running everything once you’re past a dozen models.
Which tests are worth writing on day one?
Four, chosen because each catches a failure that silently corrupts reports.
uniqueon every primary key. Duplicates from a bad join are the most common analytics bug.not_nullon join keys and timestamps.relationshipsfrom the fact table’s foreign key to the dimension’s key. This is the one that catches “we shipped a customer_id we don’t have a record for”.accepted_valueson status columns. Catches a new status code your CASE statement doesn’t handle.
In YAML:
models:
- name: stg_orders
columns:
- name: order_id
tests: [unique, not_null]
- name: status
tests:
- accepted_values:
values: ['paid', 'pending', 'refunded', 'cancelled']
Custom tests are a SQL file in tests/ that returns failing rows. The ones worth writing early: revenue never negative, and today’s order count within 50% of yesterday’s — a crude anomaly detector that has caught more real incidents than any schema test.
Run with dbt test, or dbt build to interleave runs and tests per model so a failing test stops downstream work.
What do people get wrong in the first month?
Materialising everything as a view. Views recalculate on every query and a chain of five views over 10 million rows makes a dashboard unusable. Staging as views, marts as tables is a decent default. On BigQuery, treat it the opposite way and use views for everything small because storage scanning costs more than compute.
No dbt_project.yml schema config. Without it, everything lands in one schema:
models:
analytics:
staging:
+materialized: view
+schema: staging
marts:
+materialized: table
+schema: marts
Treating dbt as an orchestrator. dbt run on a cron is fine for daily jobs. For hourly, you want Airflow, Dagster or dbt Cloud jobs with proper retries — a cron that half-fails at 3am and leaves models in an inconsistent state costs a morning of forensics.
No source freshness check. If the loader breaks, dbt happily rebuilds marts on stale data and reports success.
dbt-core or dbt Cloud?
dbt-core is free and runs anywhere. You get the CLI, the DAG, tests and docs.
dbt Cloud adds a scheduler, a browser IDE, CI on pull requests, and hosted docs. The Developer plan is free for one seat; Team is $100/developer/month.
For a solo analyst with a daily job, core plus cron is genuinely sufficient and I’d spend the $100 elsewhere. For a team of three where pull requests need a CI build to catch broken models, Cloud pays for itself — the alternative is wiring GitHub Actions yourself, which takes a day and then silently rots.
Quick Summary
- Install the adapter package (
dbt-postgres,dbt-snowflake), not baredbt-core. - Keep credentials in
profiles.ymloutside the repo, usingenv_varfor secrets. - Two layers: staging for rename/cast only, marts for joins and aggregates.
- Use
ref()andsource()everywhere so the DAG and lineage actually work. - Four tests on day one: unique, not_null, relationships, accepted_values.
- Staging as views, marts as tables; anything else and dashboards slow to a crawl.
