How to Set Up dbt with PostgreSQL: A Practical Guide

dbt transforms raw data inside your database using plain SQL. You write SELECT statements, dbt turns them into tables and views, and your warehouse stays clean without any data ever leaving Postgres. Install dbt-postgres, point it at your database, write a model, run dbt run. That's it. This guide walks through every step with code you can copy and run in about 30 minutes.

What does a dbt project look like?

Before we install anything, here's the structure you'll end up with:

my_project/
├── dbt_project.yml        # project config (name, version, materializations)
├── profiles.yml           # database connection details
├── models/
│   ├── staging/
│   │   ├── stg_customers.sql
│   │   └── stg_orders.sql
│   ├── marts/
│   │   └── fct_revenue.sql
│   └── schema.yml         # column tests and docs
├── tests/
│   └── assert_revenue_positive.sql
├── macros/
│   └── cents_to_dollars.sql
├── seeds/
│   └── country_codes.csv
└── snapshots/
    └── orders_snapshot.sql

Each folder has one job. models/ holds your SQL transforms. tests/ holds custom assertions. macros/ holds reusable SQL fragments (think functions). seeds/ lets you load small CSV files directly. snapshots/ tracks slowly changing dimensions. You'll spend 90% of your time in models/.

How do I install dbt-postgres?

Create a virtual environment first. Don't install dbt into your system Python -- you'll regret it when dependency versions collide.

# Create and activate a virtual environment
python -m venv dbt-env
source dbt-env/bin/activate    # Linux/Mac
# dbt-env\Scripts\activate     # Windows
 
# Install dbt with the Postgres adapter
pip install dbt-postgres
 
# Verify
dbt --version

You should see output listing dbt-core and dbt-postgres with matching versions. The dbt-postgres package pulls in dbt-core automatically -- you don't need to install them separately.

How do I initialize a new project?

dbt init my_project

dbt asks you which adapter to use. Pick postgres. It creates the folder structure shown above, plus a sample model you can delete.

Move into the project:

cd my_project

Two files matter right now: dbt_project.yml (project metadata) and profiles.yml (database credentials). Everything else can wait.

How do I configure the database connection?

dbt reads connection details from ~/.dbt/profiles.yml by default. Open it and set up your Postgres connection:

my_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: analytics_user
      password: "{{ env_var('DBT_PG_PASSWORD') }}"
      dbname: analytics
      schema: dbt_dev
      threads: 4
      keepalives_idle: 0
      connect_timeout: 10

A few things to note:

• schema: dbt_dev -- dbt creates this schema automatically. It won't touch your existing schemas. Each developer can use a different schema (dbt_alice, dbt_bob) so you don't clobber each other during development.
• threads: 4 -- how many models dbt builds in parallel. Start with 4, bump it up if your Postgres can handle more concurrent connections.
• env_var -- never hardcode passwords. Set DBT_PG_PASSWORD as an environment variable instead.
• target: dev -- you can add a prod target later with different credentials and a different schema.

Test the connection:

dbt debug

If everything's green, you're connected. If it fails, it'll tell you exactly which config value is wrong.

How do I write my first model?

A dbt model is a SQL file with a SELECT statement. That's really all it is. Create models/staging/stg_orders.sql:

select
    id as order_id,
    customer_id,
    order_date,
    amount_cents,
    amount_cents / 100.0 as amount_dollars,
    status,
    created_at,
    updated_at
from {{ source('raw', 'orders') }}
where status != 'cancelled'

And a second model that references the first. Create models/marts/fct_revenue.sql:

select
    date_trunc('month', order_date) as revenue_month,
    count(*) as total_orders,
    sum(amount_dollars) as total_revenue,
    avg(amount_dollars) as avg_order_value
from {{ ref('stg_orders') }}
where status = 'completed'
group by 1
order by 1

Two things make this different from regular SQL:

• {{ source('raw', 'orders') }} -- tells dbt which raw table to read from. You define sources in YAML (covered below). dbt tracks the dependency.
• {{ ref('stg_orders') }} -- references another dbt model by name. dbt figures out the fully qualified table name and builds models in the right order. If fct_revenue depends on stg_orders, dbt builds stg_orders first. Always use ref() instead of hardcoding table names.

Materializations

By default, dbt creates views. You can change this per model or globally in dbt_project.yml:

# dbt_project.yml
models:
  my_project:
    staging:
      +materialized: view       # lightweight, always fresh
    marts:
      +materialized: table      # physical table, faster queries

The four materializations:

How do I build and run models?

dbt run

Output looks like this:

Running with dbt=1.9.0
Found 2 models, 0 sources, 0 tests
 
Concurrency: 4 threads (target='dev')
 
1 of 2 START sql view model dbt_dev.stg_orders .................. [RUN]
1 of 2 OK created sql view model dbt_dev.stg_orders ............. [CREATE VIEW in 0.12s]
2 of 2 START sql table model dbt_dev.fct_revenue ................. [RUN]
2 of 2 OK created sql table model dbt_dev.fct_revenue ............ [SELECT 24 in 0.08s]
 
Finished running 1 view model, 1 table model in 0.38s.
Completed successfully.

What happened in Postgres: dbt created a schema called dbt_dev (from your profile config), built stg_orders as a view, then built fct_revenue as a table. You can query both right now:

select * from dbt_dev.fct_revenue limit 5;

To run a single model:

dbt run --select fct_revenue

To run a model and everything it depends on:

dbt run --select +fct_revenue

How do I add tests?

dbt has two kinds of tests: schema tests (declared in YAML) and custom tests (raw SQL).

Schema tests

Create or edit models/schema.yml:

version: 2
 
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'completed', 'refunded']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
 
  - name: fct_revenue
    columns:
      - name: revenue_month
        tests:
          - unique
          - not_null
      - name: total_revenue
        tests:
          - not_null

Four built-in tests:

• unique -- no duplicate values in this column
• not_null -- no NULLs
• accepted_values -- column values must be in the given list
• relationships -- every value exists in another model's column (referential integrity)

Custom tests

For anything the built-in tests don't cover, write SQL. Create tests/assert_revenue_positive.sql:

-- This test PASSES when the query returns zero rows.
-- Any rows returned = test failure.
 
select
    revenue_month,
    total_revenue
from {{ ref('fct_revenue') }}
where total_revenue < 0

Run all tests:

dbt test

Found 7 tests
 
7 of 7 PASS ..................................................... [PASS in 0.45s]
 
Finished running 7 tests in 0.82s.
Completed successfully.

Run tests after every dbt run. Better yet, run both together:

dbt build    # runs models, tests, snapshots, and seeds in dependency order

For a deeper look at data quality testing beyond dbt, see our comparison of data quality tools.

How do I generate documentation?

dbt builds a documentation site from your models, tests, and YAML descriptions -- including a visual lineage graph.

Add descriptions to models/schema.yml:

version: 2
 
models:
  - name: fct_revenue
    description: "Monthly revenue aggregation from completed orders."
    columns:
      - name: revenue_month
        description: "First day of the month (truncated from order_date)."
      - name: total_orders
        description: "Count of completed orders in that month."
      - name: total_revenue
        description: "Sum of order amounts in dollars."

Generate and serve the docs:

dbt docs generate
dbt docs serve

This opens a browser at http://localhost:8080 with a searchable site showing every model, column, test, and source -- plus a DAG visualization of how models depend on each other. It's one of dbt's best features and costs you nothing beyond writing the YAML descriptions you should be writing anyway.

How do I set up sources?

Sources tell dbt about raw tables that exist in your database but aren't managed by dbt. Create models/staging/sources.yml:

version: 2
 
sources:
  - name: raw
    database: analytics
    schema: public
    tables:
      - name: orders
        description: "Raw orders from the application database."
        loaded_at_field: updated_at
        freshness:
          warn_after: { count: 12, period: hour }
          error_after: { count: 24, period: hour }
      - name: customers
        description: "Raw customer records."
        loaded_at_field: created_at
        freshness:
          warn_after: { count: 24, period: hour }
          error_after: { count: 48, period: hour }

Now {{ source('raw', 'orders') }} in your models resolves to analytics.public.orders. If someone renames the table, you change it in one place.

The freshness block is useful. Run:

dbt source freshness

It checks when each source table was last updated (via loaded_at_field) and warns or errors if the data is stale. Catch problems before your dashboard shows yesterday's numbers as today's.

How do I handle large tables with incremental models?

When you have millions of rows, rebuilding the entire table on every run wastes time. Incremental models only process new or changed rows.

Create models/marts/fct_daily_orders.sql:

{{
    config(
        materialized='incremental',
        unique_key='order_date'
    )
}}
 
select
    date_trunc('day', order_date) as order_date,
    count(*) as order_count,
    sum(amount_dollars) as daily_revenue
from {{ ref('stg_orders') }}
 
{% if is_incremental() %}
    where order_date > (select max(order_date) from {{ this }})
{% endif %}
 
group by 1

How this works:

1. First run: dbt builds the full table (the WHERE clause is skipped because the table doesn't exist yet).
2. Subsequent runs: dbt only processes rows where order_date is newer than the latest existing row. The {{ this }} variable references the current table.
3. unique_key: if a row with the same order_date already exists, dbt updates it instead of creating a duplicate.

To force a full rebuild:

dbt run --select fct_daily_orders --full-refresh

Incremental models are the biggest performance win in dbt for Postgres. A table with 50 million rows that takes 3 minutes to rebuild can be incrementally updated in seconds. The tradeoff: your WHERE filter logic has to be correct, or you'll miss rows. Test carefully.

How do I deploy dbt on a schedule?

During development, you run dbt run from your laptop. In production, you need it running automatically. Here are three approaches, simplest first.

Cron (simplest)

# Run dbt every day at 6 AM UTC
0 6 * * * cd /opt/dbt/my_project && /opt/dbt/dbt-env/bin/dbt run --target prod >> /var/log/dbt.log 2>&1

Works fine for small teams. The downside: no retry on failure, no alerting unless you add it, and debugging a failed run means reading log files.

GitHub Actions

# .github/workflows/dbt-daily.yml
name: dbt daily run
on:
  schedule:
    - cron: '0 6 * * *'
  workflow_dispatch:
 
jobs:
  dbt-run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - run: pip install dbt-postgres
      - run: dbt run --target prod
        env:
          DBT_PG_PASSWORD: ${{ secrets.DBT_PG_PASSWORD }}
      - run: dbt test

Better than cron: you get logs in GitHub, notifications on failure, and manual trigger via workflow_dispatch. Good middle ground for teams that already use GitHub.

Orchestrators

For more complex setups -- multiple dbt projects, dependencies on upstream data loads, SLA monitoring -- use a proper orchestrator. Airflow, Dagster, and Prefect all have first-class dbt integrations. But don't reach for one until cron or GitHub Actions isn't enough. Orchestrators carry real operational weight.

If you're weighing dbt Core against dbt Cloud, Cloud handles scheduling, CI, and a hosted docs site out of the box. Core gives you full control and no recurring cost.

Frequently asked questions

Can I use dbt with an existing Postgres database?

Yes. dbt doesn't modify your existing tables. It reads from them (via sources) and writes to a separate schema that you specify in profiles.yml. Your application database stays untouched.

What's the difference between dbt Core and dbt Cloud?

dbt Core is the free, open-source CLI tool covered in this guide. dbt Cloud adds a web IDE, job scheduling, CI/CD, hosted documentation, and team collaboration features. Start with Core. Move to Cloud when you need collaboration or managed scheduling. We wrote a full comparison.

How does dbt compare to writing raw SQL scripts?

dbt adds dependency resolution (build order), testing, documentation, incremental loading, and environment management on top of your SQL. You still write SQL -- dbt just gives it structure. The alternative is maintaining a folder of numbered SQL files and a shell script that runs them in order, which works until it doesn't.

Is Postgres a good warehouse for dbt?

For small to mid-size datasets (up to a few hundred million rows), Postgres works well. It's free, you probably already have it, and dbt supports it fully. For larger workloads, teams typically move to Snowflake, BigQuery, or Redshift. See our Postgres vs MySQL comparison for more on Postgres as an analytics database.

How do I handle sensitive data like passwords in profiles.yml?

Use environment variables with {{ env_var('VAR_NAME') }}. Never commit passwords to profiles.yml. In CI/CD, set them as secrets (GitHub Actions secrets, Airflow variables, etc.).

Try Fastero free -- skip the dbt setup. Connect your PostgreSQL database, ask questions in SQL or English, get dashboards and analytics instantly. No credit card required.