How to Build Your First dbt Project Against a Warehouse

October 28, 2026

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.

  1. unique on every primary key. Duplicates from a bad join are the most common analytics bug.
  2. not_null on join keys and timestamps.
  3. relationships from 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”.
  4. accepted_values on 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 bare dbt-core.
  • Keep credentials in profiles.yml outside the repo, using env_var for secrets.
  • Two layers: staging for rename/cast only, marts for joins and aggregates.
  • Use ref() and source() 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.
Tags: