dbt (data build tool) lets you transform data inside your warehouse by writing SELECT statements. dbt turns each model into a view or table, works out the order to build them from their dependencies, tests the results and generates documentation with a lineage graph. In this tutorial you will install dbt Core with the PostgreSQL adapter on Ubuntu 24.04, create a project, load sample data, build staging and mart models, add data tests, convert a model to incremental and schedule nightly runs with a systemd timer.
Prerequisites
- A server running Ubuntu 24.04 LTS, for example a CubePath VPS. 1 GB of RAM is enough for dbt itself; the database does the heavy lifting.
- A non-root user with
sudoprivileges. The examples useyour_useras the username. - A PostgreSQL database. This tutorial installs PostgreSQL 16 locally in Step 1; if you already have a server, create the user and database there and skip the installation.
Step 1 - Creating a PostgreSQL database for dbt
Install PostgreSQL from the Ubuntu repositories:
sudo apt update
sudo apt install -y postgresql
Create a login role for dbt and a database owned by it. Owning the database lets dbt create the schemas it builds into. Replace your_strong_password with a password of your own:
sudo -u postgres psql -c "CREATE ROLE dbt_user WITH LOGIN PASSWORD 'your_strong_password';"
sudo -u postgres createdb -O dbt_user analytics
Test the login over TCP, the same way dbt will connect:
psql "postgresql://dbt_user@localhost:5432/analytics" -c "SELECT current_user;"
Enter the password when prompted. The output should show dbt_user.
Step 2 - Installing dbt Core in a virtual environment
dbt is a Python package. Ubuntu 24.04 does not allow pip install into the system Python, so install it in a virtual environment:
sudo apt install -y python3-venv
python3 -m venv ~/dbt-env
source ~/dbt-env/bin/activate
Install dbt Core and the PostgreSQL adapter. Since dbt 1.8 the adapters no longer pull in dbt-core, so install both:
pip install --upgrade pip
pip install dbt-core dbt-postgres
Check the installation:
dbt --version
Core:
- installed: 1.10.11
- latest: 1.10.11 - Up to date!
Plugins:
- postgres: 1.9.1 - Up to date!
Your versions may differ. Every time you open a new shell, run source ~/dbt-env/bin/activate before using dbt.
Step 3 - Creating the project and connection profile
Create a project without the interactive profile questions, since you will write the profile by hand:
cd ~
dbt init my_analytics --skip-profile-setup
cd my_analytics
The project contains, among others, dbt_project.yml and the directories models/, seeds/, tests/, macros/ and snapshots/. Remove the example models:
rm -rf models/example
dbt reads connection details from ~/.dbt/profiles.yml, outside the project, so credentials stay out of version control. Create it:
mkdir -p ~/.dbt
nano ~/.dbt/profiles.yml
my_analytics:
target: dev
outputs:
dev:
type: postgres
host: localhost
port: 5432
user: dbt_user
password: "{{ env_var('DBT_PASSWORD') }}"
dbname: analytics
schema: dbt_dev
threads: 4
The password is read from the DBT_PASSWORD environment variable. schema is the schema where dbt builds your models for this target. Restrict the file and export the password for the current session:
chmod 600 ~/.dbt/profiles.yml
export DBT_PASSWORD='your_strong_password'
Now replace the generated project configuration:
nano dbt_project.yml
name: my_analytics
version: "1.0.0"
config-version: 2
profile: my_analytics
model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
analysis-paths: ["analyses"]
clean-targets: ["target", "dbt_packages"]
models:
my_analytics:
staging:
+materialized: view
marts:
+materialized: table
Staging models become views, so they are always current and cost nothing to store. Marts become tables, so dashboards query precomputed results.
Check the configuration and the connection:
dbt debug
...
Connection test: [OK connection ok]
All checks passed!
Step 4 - Loading sample data with seeds
Seeds are small CSV files that dbt loads into tables. They are meant for reference data such as country codes, but they are also a quick way to get sample raw data for this tutorial. Create two files:
nano seeds/raw_customers.csv
id,email,country
1,[email protected],ES
2,[email protected],US
3,[email protected],NL
nano seeds/raw_orders.csv
id,customer_id,status,amount_cents,created_at,updated_at
101,1,shipped,12050,2026-03-01 10:15:00,2026-03-02 09:00:00
102,2,pending,7500,2026-03-02 12:30:00,2026-03-02 12:30:00
103,1,delivered,21099,2026-03-03 08:05:00,2026-03-05 16:20:00
104,3,cancelled,4999,2026-03-04 18:45:00,2026-03-04 19:00:00
Load them:
dbt seed
...
1 of 2 OK loaded seed file dbt_dev.raw_customers ........ [INSERT 3 in 0.08s]
2 of 2 OK loaded seed file dbt_dev.raw_orders ........... [INSERT 4 in 0.06s]
In a real project the raw tables are loaded by an ingestion tool and declared as sources in a YAML file; models then read them with {{ source('name', 'table') }} instead of {{ ref() }}.
Step 5 - Writing staging and mart models
A model is a .sql file with one SELECT. The ref() function points to another model or seed; dbt replaces it with the real table name and uses it to build the dependency graph.
Create the staging models, which rename and cast columns and do nothing else:
mkdir -p models/staging models/marts
nano models/staging/stg_customers.sql
select
id as customer_id,
lower(email) as email,
country
from {{ ref('raw_customers') }}
nano models/staging/stg_orders.sql
select
id as order_id,
customer_id,
status,
amount_cents / 100.0 as amount,
created_at::timestamp as created_at,
updated_at::timestamp as updated_at
from {{ ref('raw_orders') }}
Create a mart model that joins them into one row per order:
nano models/marts/fct_orders.sql
select
o.order_id,
o.customer_id,
c.email,
c.country,
o.status,
o.amount,
o.created_at,
o.updated_at
from {{ ref('stg_orders') }} as o
left join {{ ref('stg_customers') }} as c
on o.customer_id = c.customer_id
Build the models:
dbt run
...
1 of 3 OK created sql view model dbt_dev.stg_customers .... [CREATE VIEW in 0.07s]
2 of 3 OK created sql view model dbt_dev.stg_orders ....... [CREATE VIEW in 0.05s]
3 of 3 OK created sql table model dbt_dev.fct_orders ...... [SELECT 4 in 0.09s]
Query the result directly in PostgreSQL:
psql "postgresql://dbt_user@localhost:5432/analytics" -c "SELECT order_id, email, amount FROM dbt_dev.fct_orders ORDER BY order_id;"
To build only part of the graph, use selectors: dbt run --select fct_orders builds one model, and dbt run --select +fct_orders builds it and everything upstream of it.
Step 6 - Adding data tests
Tests are queries that return the rows breaking a rule; a test passes when it returns nothing. Generic tests are declared in YAML next to the models:
nano models/staging/schema.yml
version: 2
models:
- name: stg_customers
columns:
- name: customer_id
data_tests:
- unique
- not_null
- name: stg_orders
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: status
data_tests:
- accepted_values:
values: ["pending", "shipped", "delivered", "cancelled"]
- name: customer_id
data_tests:
- relationships:
to: ref('stg_customers')
field: customer_id
For rules that do not fit a generic test, write a singular test: a SQL file in tests/ that selects the bad rows:
nano tests/assert_no_negative_amounts.sql
select order_id, amount
from {{ ref('fct_orders') }}
where amount < 0
Run the tests:
dbt test
...
Done. PASS=7 WARN=0 ERROR=0 SKIP=0 TOTAL=7
dbt build combines everything: it loads seeds, runs models and runs their tests in dependency order, and skips downstream models when an upstream test fails. It is the command to use in scheduled runs.
Step 7 - Converting the mart to an incremental model
A table model is rebuilt from scratch on every run. For large fact tables, an incremental model only processes rows that changed since the last run and merges them in. Replace models/marts/fct_orders.sql:
nano models/marts/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id'
)
}}
select
o.order_id,
o.customer_id,
c.email,
c.country,
o.status,
o.amount,
o.created_at,
o.updated_at
from {{ ref('stg_orders') }} as o
left join {{ ref('stg_customers') }} as c
on o.customer_id = c.customer_id
{% if is_incremental() %}
where o.updated_at > (select max(updated_at) from {{ this }})
{% endif %}
On the first run, or with --full-refresh, is_incremental() is false and the whole table is built. On later runs only rows with a newer updated_at are selected, and unique_key makes dbt update existing orders instead of duplicating them. Build it once from scratch, since it was a plain table before:
dbt run --select fct_orders --full-refresh
Now simulate new data by appending a line to seeds/raw_orders.csv:
echo "105,2,pending,3300,2026-03-06 11:00:00,2026-03-06 11:00:00" >> seeds/raw_orders.csv
dbt seed --select raw_orders
dbt run --select fct_orders
1 of 1 OK created sql incremental model dbt_dev.fct_orders ... [INSERT 0 1 in 0.11s]
Only one row was processed. Run dbt run --select fct_orders --full-refresh whenever you change the model's columns or logic, so the whole table is rebuilt.
Step 8 - Generating documentation
dbt generates a static documentation site from your project, including column descriptions from the YAML files and a lineage graph:
dbt docs generate
dbt docs serve --port 8080
The server runs until you press Ctrl+C. Do not open port 8080 in the firewall; from your local machine, open an SSH tunnel instead and browse to http://localhost:8080:
ssh -L 8080:127.0.0.1:8080 your_user@your_server_ip
Click the lineage icon in the bottom right corner to see how seeds, staging models and the mart depend on each other.
Step 9 - Scheduling nightly runs with systemd
A systemd timer runs dbt build on a schedule and keeps the output in the journal. Store the password in an environment file readable only by your user:
printf "DBT_PASSWORD=%s\n" 'your_strong_password' > ~/.dbt/env
chmod 600 ~/.dbt/env
Create the service unit, replacing your_user with your username:
sudo nano /etc/systemd/system/dbt-build.service
[Unit]
Description=dbt build for my_analytics
After=network-online.target postgresql.service
Wants=network-online.target
[Service]
Type=oneshot
User=your_user
WorkingDirectory=/home/your_user/my_analytics
EnvironmentFile=/home/your_user/.dbt/env
ExecStart=/home/your_user/dbt-env/bin/dbt build --target dev
Create the timer:
sudo nano /etc/systemd/system/dbt-build.timer
[Unit]
Description=Run dbt build every night
[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true
[Install]
WantedBy=timers.target
Enable the timer and run the service once by hand to check it:
sudo systemctl daemon-reload
sudo systemctl enable --now dbt-build.timer
sudo systemctl start dbt-build.service
journalctl -u dbt-build.service -n 20 --no-pager
The journal should end with Completed successfully and a Done. PASS=... summary. systemctl list-timers dbt-build.timer shows the next run.
Troubleshooting
Env var required but not provided: 'DBT_PASSWORD'. Export the variable in the current shell, or check the EnvironmentFile path in the systemd unit.
Could not find profile named 'my_analytics'. The profile key in dbt_project.yml must match the top-level key in ~/.dbt/profiles.yml.
password authentication failed for user "dbt_user". The password in DBT_PASSWORD does not match the role. Reset it with sudo -u postgres psql -c "ALTER ROLE dbt_user PASSWORD 'your_strong_password';".
A model fails with a SQL error. Run dbt compile --select model_name and read the rendered SQL in target/compiled/my_analytics/models/. You can paste it into psql to debug it.
A test fails. Rerun it with dbt test --select model_name --store-failures; dbt saves the failing rows in a table in an audit schema (named after your target schema with a _dbt_test__audit suffix) so you can inspect them.
Conclusion
You installed dbt Core with the PostgreSQL adapter, built a small project with seeds, staging and mart models, protected it with data tests, turned the fact table into an incremental model and scheduled nightly builds. Next, declare your real raw tables as sources with freshness checks, keep the project in Git and run dbt build against a separate CI target on every pull request, or add packages such as dbt_utils through a packages.yml file.