Deploy dbt Core on a VPS

dbt Core turns your data warehouse into a version-controlled, tested, and documented transformation pipeline. You write SELECT statements, dbt handles the DDL, dependency ordering, testing, and documentation. It is a Python package with no server component, which makes it an ideal fit for a small RamNode Cloud VPS: you get a dedicated, always-on transformation runner for the price of a coffee, without paying per-seat for a hosted orchestration platform.

This guide builds a production-ready dbt Core installation on Ubuntu 24.04 with a local PostgreSQL warehouse, scheduled runs under systemd timers, and a documentation site served over HTTPS.

What You Will Build

• dbt Core 1.9+ installed in an isolated Python virtual environment
• A PostgreSQL 16 warehouse running on the same VPS (or configured to point at a remote warehouse)
• A working dbt project with staging and mart layers, sources, and tests
• Automated runs on a schedule via systemd timers with logging and failure alerts
• The dbt docs site served by Nginx behind HTTP basic auth and a Let's Encrypt certificate
• A Git-based deployment workflow so changes ship by pushing to a branch

Server Requirements

dbt itself is light. The resource question is really about your warehouse.

Deploy Ubuntu 24.04 LTS from the RamNode control panel. Everything below assumes a fresh instance with an unprivileged sudo user and SSH key authentication already configured.

Step 1: Prepare the System

Update the base system and install build dependencies. dbt adapters compile C extensions during installation, so the toolchain is required.

sudo apt update && sudo apt upgrade -y
sudo apt install -y python3 python3-venv python3-dev python3-pip \
  build-essential libpq-dev git curl ca-certificates

Verify the Python version. Ubuntu 24.04 ships 3.12, which current dbt releases support.

python3 --version

Create a dedicated service account. Never run scheduled transformations as root or as your login user.

sudo useradd --system --create-home --home-dir /opt/dbt --shell /bin/bash dbt
sudo mkdir -p /opt/dbt/{projects,logs,venv}
sudo chown -R dbt:dbt /opt/dbt

Step 2: Install PostgreSQL as the Warehouse

Skip this step if you are pointing dbt at Snowflake, BigQuery, Redshift, or an existing database.

sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

Create the warehouse database and the roles dbt will use. Use two roles: one for transformations, one read-only for BI tools.

sudo -u postgres psql <<'SQL'
CREATE ROLE dbt_user WITH LOGIN PASSWORD 'CHANGE_ME_STRONG_PASSWORD';
CREATE ROLE analytics_ro WITH LOGIN PASSWORD 'CHANGE_ME_TOO';
CREATE DATABASE analytics OWNER dbt_user;
\c analytics
CREATE SCHEMA raw AUTHORIZATION dbt_user;
CREATE SCHEMA analytics AUTHORIZATION dbt_user;
GRANT USAGE ON SCHEMA analytics TO analytics_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE dbt_user IN SCHEMA analytics
  GRANT SELECT ON TABLES TO analytics_ro;
SQL

Tune PostgreSQL for a transformation workload. On a 4 GB instance, edit /etc/postgresql/16/main/postgresql.conf:

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 32MB
maintenance_work_mem = 256MB
max_parallel_workers_per_gather = 2
random_page_cost = 1.1

random_page_cost = 1.1 matters on RamNode NVMe storage. The default of 4.0 assumes spinning disks and pushes the planner away from index scans it should be using.

Restart to apply:

sudo systemctl restart postgresql

Keep PostgreSQL bound to localhost. If you need remote BI access, tunnel over SSH or restrict the port with the RamNode cloud firewall rather than exposing 5432 to the internet.

Step 3: Install dbt Core

Install into a virtual environment owned by the service account. This isolates dbt from system Python and lets you upgrade adapters without breaking OS tooling.

sudo -u dbt -H bash
cd /opt/dbt
python3 -m venv venv
source venv/bin/activate
pip install --upgrade pip wheel
pip install dbt-core dbt-postgres
dbt --version

Swap the adapter for your warehouse if you are not using PostgreSQL: dbt-snowflake, dbt-bigquery, dbt-redshift, dbt-databricks, or dbt-duckdb.

Pin your versions before going to production. Unpinned adapters will surprise you during an unattended upgrade.

pip freeze > /opt/dbt/requirements.txt

Step 4: Create the Project

cd /opt/dbt/projects
dbt init analytics_project

Answer the prompts, then move the profile out of the interactive location and manage it explicitly. Create /opt/dbt/.dbt/profiles.yml:

analytics_project:
  target: prod
  outputs:
    prod:
      type: postgres
      host: 127.0.0.1
      port: 5432
      user: dbt_user
      password: "{{ env_var('DBT_PG_PASSWORD') }}"
      dbname: analytics
      schema: analytics
      threads: 4
      keepalives_idle: 0
      connect_timeout: 10
      retries: 2
    dev:
      type: postgres
      host: 127.0.0.1
      port: 5432
      user: dbt_user
      password: "{{ env_var('DBT_PG_PASSWORD') }}"
      dbname: analytics
      schema: dbt_dev
      threads: 2

Never commit credentials. Pull them from the environment and store the secret in a root-owned file:

sudo install -o dbt -g dbt -m 600 /dev/null /opt/dbt/.dbt/env
sudo -u dbt tee /opt/dbt/.dbt/env >/dev/null <<'EOF'
DBT_PG_PASSWORD=CHANGE_ME_STRONG_PASSWORD
EOF
sudo chmod 600 /opt/dbt/.dbt/env

Set threads to roughly your vCPU count for a local warehouse. On a remote warehouse you can go higher, since the VPS is only dispatching queries.

Step 5: Define Sources and Models

Declare your raw tables so dbt can track freshness and lineage. Create models/staging/_sources.yml:

version: 2

sources:
  - name: raw
    database: analytics
    schema: raw
    loaded_at_field: _ingested_at
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: orders
      - name: customers

Add a staging model at models/staging/stg_orders.sql:

{{ config(materialized='view') }}

select
    id                              as order_id,
    customer_id,
    lower(trim(status))             as order_status,
    amount_cents / 100.0            as order_amount,
    created_at::timestamptz         as ordered_at
from {{ source('raw', 'orders') }}
where created_at is not null

Add a mart at models/marts/fct_customer_orders.sql:

{{ config(
    materialized='incremental',
    unique_key='customer_id',
    on_schema_change='append_new_columns'
) }}

select
    c.customer_id,
    count(o.order_id)               as order_count,
    sum(o.order_amount)             as lifetime_value,
    max(o.ordered_at)               as last_order_at
from {{ ref('stg_customers') }} c
left join {{ ref('stg_orders') }} o
    on o.customer_id = c.customer_id

{% if is_incremental() %}
where o.ordered_at > (select coalesce(max(last_order_at), '1900-01-01') from {{ this }})
{% endif %}

group by 1

Use view for staging and incremental or table for marts. On a small VPS, incremental models are what keep your run times flat as data grows.

Add tests in models/marts/_models.yml:

version: 2

models:
  - name: fct_customer_orders
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: lifetime_value
        tests:
          - dbt_utils.accepted_range:
              min_value: 0

Install the utility package by creating packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.3.0", "<2.0.0"]

Then run:

cd /opt/dbt/projects/analytics_project
export DBT_PROFILES_DIR=/opt/dbt/.dbt
set -a && source /opt/dbt/.dbt/env && set +a
dbt deps
dbt debug
dbt build

dbt build runs models and their tests in dependency order, which is what you want in production. dbt run followed by dbt test lets bad data propagate downstream before the tests catch it.

Step 6: Schedule Runs with systemd

Cron works, but systemd timers give you journal integration, failure handling, and no silent failures from a missing PATH.

Create a wrapper script at /opt/dbt/run-dbt.sh:

#!/usr/bin/env bash
set -euo pipefail

export DBT_PROFILES_DIR=/opt/dbt/.dbt
export PATH=/opt/dbt/venv/bin:$PATH
set -a && source /opt/dbt/.dbt/env && set +a

cd /opt/dbt/projects/analytics_project

dbt source freshness || echo "Freshness warnings present, continuing"
dbt build --target prod

Make it executable:

sudo chmod +x /opt/dbt/run-dbt.sh
sudo chown dbt:dbt /opt/dbt/run-dbt.sh

Create /etc/systemd/system/dbt-build.service:

[Unit]
Description=dbt build for analytics_project
After=network-online.target postgresql.service
Wants=network-online.target

[Service]
Type=oneshot
User=dbt
Group=dbt
WorkingDirectory=/opt/dbt/projects/analytics_project
ExecStart=/opt/dbt/run-dbt.sh
TimeoutStartSec=3600
StandardOutput=journal
StandardError=journal
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ReadWritePaths=/opt/dbt

Create /etc/systemd/system/dbt-build.timer:

[Unit]
Description=Run dbt build hourly

[Timer]
OnCalendar=hourly
RandomizedDelaySec=300
Persistent=true

[Install]
WantedBy=timers.target

Enable and verify:

sudo systemctl daemon-reload
sudo systemctl enable --now dbt-build.timer
sudo systemctl list-timers dbt-build.timer
sudo systemctl start dbt-build.service
sudo journalctl -u dbt-build.service -f

Persistent=true catches up on a missed run after a reboot. RandomizedDelaySec prevents a thundering herd if you later run several projects on the same box.

Alert on Failure

Add a failure handler unit at /etc/systemd/system/dbt-alert@.service:

[Unit]
Description=Alert on failure of %i

[Service]
Type=oneshot
ExecStart=/usr/bin/curl -s -X POST https://ntfy.example.com/dbt \
  -H "Title: dbt job failed" \
  -d "Unit %i failed on $(hostname)"

Then add this to the [Unit] section of dbt-build.service:

OnFailure=dbt-alert@%n.service

Point it at ntfy, Gotify, a Slack webhook, or whatever you already run.

Step 7: Serve dbt Docs

dbt generates a static documentation site with full lineage graphs. Build it and serve it with Nginx instead of leaving dbt docs serve running.

Add doc generation to the end of run-dbt.sh:

dbt docs generate
sudo rsync -a --delete target/ /var/www/dbt-docs/

Give the dbt user permission for just that rsync with a narrow sudoers entry:

echo 'dbt ALL=(root) NOPASSWD: /usr/bin/rsync -a --delete /opt/dbt/projects/analytics_project/target/ /var/www/dbt-docs/' | sudo tee /etc/sudoers.d/dbt-docs
sudo chmod 440 /etc/sudoers.d/dbt-docs

Install Nginx and create the site at /etc/nginx/sites-available/dbt-docs:

server {
    listen 80;
    server_name docs.example.com;

    root /var/www/dbt-docs;
    index index.html;

    auth_basic "dbt docs";
    auth_basic_user_file /etc/nginx/.htpasswd;

    location / {
        try_files $uri $uri/ =404;
    }
}

Create credentials, enable the site, and issue a certificate:

sudo apt install -y nginx apache2-utils certbot python3-certbot-nginx
sudo mkdir -p /var/www/dbt-docs
sudo htpasswd -c /etc/nginx/.htpasswd analyst
sudo ln -s /etc/nginx/sites-available/dbt-docs /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx
sudo certbot --nginx -d docs.example.com

The docs site exposes your full schema and model logic. Basic auth is the minimum. Consider restricting by source IP in the RamNode firewall as well.

Step 8: Git-Based Deployment

Keep the project in Git and deploy by pulling. Add a deploy key to your repository, then:

sudo -u dbt -H bash
cd /opt/dbt/projects/analytics_project
git init
git remote add origin git@github.com:yourorg/analytics_project.git
git fetch origin
git checkout -B main origin/main

Add a deploy script at /opt/dbt/deploy.sh:

#!/usr/bin/env bash
set -euo pipefail
export PATH=/opt/dbt/venv/bin:$PATH
cd /opt/dbt/projects/analytics_project
git fetch origin
git reset --hard origin/main
dbt deps
dbt parse
echo "Deployed $(git rev-parse --short HEAD)"

dbt parse catches Jinja and ref errors before the next scheduled run tries to execute a broken DAG.

Make sure .gitignore contains:

target/
dbt_packages/
logs/
profiles.yml
.env

Maintenance

Rotate logs. dbt writes to logs/dbt.log and rotates at 10 MB by default, but the file count grows. Add /etc/logrotate.d/dbt:

/opt/dbt/projects/*/logs/*.log {
    weekly
    rotate 4
    compress
    missingok
    notifempty
    copytruncate
}

Back up the warehouse. Add a nightly dump and ship it off the VPS:

sudo -u postgres pg_dump -Fc analytics > /var/backups/analytics-$(date +%F).dump

Upgrade deliberately. Test in the dev target first:

source /opt/dbt/venv/bin/activate
pip install --upgrade dbt-core dbt-postgres
dbt build --target dev
pip freeze > /opt/dbt/requirements.txt

Troubleshooting

dbt debug fails on connection. Confirm the password variable is exported. Run set -a && source /opt/dbt/.dbt/env && set +a before invoking dbt manually. Check that DBT_PROFILES_DIR points at the directory, not the file.

Compilation error: model not found. You referenced a model with ref() that does not exist or lives in a disabled path. Run dbt ls to see what dbt actually discovered, and check model-paths in dbt_project.yml.

Runs get killed by the OOM killer. Check sudo journalctl -k | grep -i oom. Lower threads in profiles.yml, lower work_mem in PostgreSQL, or convert large table materializations to incremental.

Incremental model returns stale rows. Your is_incremental() filter is probably comparing against a column that is null for new rows. Run dbt build --full-refresh --select model_name to rebuild, then fix the predicate.

Timer never fires. Run systemctl list-timers --all and confirm the unit is listed. A timer with no matching .service of the same name silently does nothing.

Permission denied writing to target/. The service account must own the whole project tree. Run sudo chown -R dbt:dbt /opt/dbt.

Next Steps

Add dbt-expectations for stronger data quality assertions. Wire the build into CI so pull requests run dbt build --select state:modified+ against a scratch schema. If you outgrow systemd timers, deploy Dagster or Airflow on a second RamNode instance and call this box as a remote executor.