# Run dbt transforms as a scheduled job

Source: https://docs.quake.ai/resources/deployments/deploy-dbt-transforms
Markdown: https://docs.quake.ai/resources/deployments/deploy-dbt-transforms.md
> Compose the Airflow and self-managed PostgreSQL templates to run dbt Core in a container on a schedule against your warehouse.

---

# Run dbt transforms as a scheduled job

[dbt Core](https://docs.getdbt.com/) runs SQL transforms inside your warehouse. Each run builds staging and mart models, runs tests, and writes documentation artifacts. dbt is a batch job: a container starts, executes `dbt run`, and exits. There is no long-running dbt service to keep online.

This deployment pattern composes two [validated OpenTofu templates](/docs/platform/validation#how-infrastructure-templates-are-checked): [Airflow](/resources/iac-templates/airflow) schedules the work, and [self-managed PostgreSQL](/resources/iac-templates/self-managed-postgres) holds the warehouse dbt writes to. You bring the dbt project, the container image, and the schedule. Quake AI supplies the compute and storage those templates provision.

<Figure size="md" caption="dbt runs as a short-lived container job. Airflow (or cron on a host with warehouse access) starts the container; dbt connects to Postgres, runs models, and exits.">

```d2
direction: right

scheduler: Schedule {
  airflow: "Airflow scheduler\n(or cron)"
  trigger: "DAG run / cron\nfires on interval"
  airflow -> trigger
}

vm: Quake AI {
  airflow_vm: "Airflow instance\n(airflow template)"
  dbt: "dbt container\n(dbt run, then exit)"
  pg: "PostgreSQL warehouse\n(self-managed-postgres template)" {shape: cylinder}
}

scheduler.trigger -> vm.airflow_vm: start job
vm.airflow_vm -> vm.dbt: docker run
vm.dbt -> vm.pg: SQL transforms
```

</Figure>

<PricingCompanion
  components={[
    { kind: "template", slug: "airflow", required: true },
    { kind: "template", slug: "self-managed-postgres", required: true },
  ]}
/>



dbt does not expose a daemon or UI that stays up between runs. A template would only wrap a one-shot container invocation, which duplicates what Airflow or cron already do. This page documents the composition instead: apply the orchestration and warehouse templates, then wire dbt into the schedule layer you already run.



## Prerequisites

Before you wire dbt, confirm you have:

- Airflow running from the [Deploy Airflow with the airflow template](/resources/deployments/deploy-airflow-template) walkthrough. The scheduler and web UI must be reachable from the instance where dbt runs (typically the same Airflow host).
- A Postgres warehouse from [Deploy the self-managed PostgreSQL template with OpenTofu](/resources/deployments/deploy-self-managed-postgres-template). Note the private IP, database name, and application role. dbt connects over the network; it does not co-locate with Postgres by default.
- Network path between the Airflow instance and the Postgres private IP. Both templates can share a private subnet, or you can place a bastion or VPN between them. See [How to set up SSH bastion access into a private subnet](/docs/network/how-to/ssh-bastion-access) if your laptop needs to reach private addresses during setup.
- A dbt project checked into git (models, `dbt_project.yml`, and a `profiles.yml` that reads connection settings from environment variables).
- Docker available on the Airflow host (the `airflow` template installs Docker Compose for the Airflow stack; use the same engine for dbt runs).

If you prefer ClickHouse as the warehouse, apply the [ClickHouse template](/resources/iac-templates/clickhouse) instead of Postgres and point dbt at ClickHouse with the [`dbt-clickhouse`](https://github.com/ClickHouse/dbt-clickhouse) adapter. The scheduling shapes below stay the same; only the profile target and adapter package change.

## Step 1: Stand up the composed templates

Apply each template in its own working directory if you have not already:

1. Follow [Deploy the self-managed PostgreSQL template with OpenTofu](/resources/deployments/deploy-self-managed-postgres-template) and record the warehouse private IP, database name, and role name from your tfvars file.
2. Follow [Deploy Airflow with the airflow template](/resources/deployments/deploy-airflow-template) on a instance that can reach the warehouse security group. Add the Airflow host private IP to the Postgres template `allowed_cidrs` before apply if the database security group does not already permit it.

Store database credentials in Airflow Connections or in a restricted env file on the host. Do not commit passwords to your dbt repo or to DAG source in git.

## Step 2: Add a dbt profile that reads from the environment

On your workstation, create `profiles.yml` beside your dbt project. The profile below expects `DBT_HOST`, `DBT_USER`, `DBT_PASSWORD`, `DBT_DATABASE`, and `DBT_SCHEMA` at run time:

```yaml
warehouse:
  target: prod
  outputs:
    prod:
      type: postgres
      host: "{{ env_var('DBT_HOST') }}"
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: "{{ env_var('DBT_DATABASE') }}"
      schema: "{{ env_var('DBT_SCHEMA') }}"
      threads: 4
```

Commit `profiles.yml` when every secret comes from `env_var()`. Keep production values in Airflow Variables, Airflow Connections, or a root-owned env file on the scheduler host.

## Step 3: Invoke dbt from Airflow

Mount your dbt project into the official dbt Postgres image and pass the env vars from an Airflow Connection. Example DAG using `DockerOperator`:

```python
from datetime import datetime

from airflow import DAG
from airflow.providers.docker.operators.docker import DockerOperator

with DAG(
    dag_id="dbt_daily_models",
    start_date=datetime(2026, 1, 1),
    schedule="@daily",
    catchup=False,
) as dag:
    DockerOperator(
        task_id="dbt_run",
        image="ghcr.io/dbt-labs/dbt-postgres:1.9.latest",
        api_version="auto",
        auto_remove="force",
        command="dbt run --profiles-dir /usr/app/profiles",
        mounts=[
            {"source": "/opt/dbt/my_project", "target": "/usr/app", "type": "bind"},
            {"source": "/opt/dbt/profiles", "target": "/usr/app/profiles", "type": "bind"},
        ],
        environment={
            "DBT_HOST": "{{ conn.warehouse_postgres.host }}",
            "DBT_USER": "{{ conn.warehouse_postgres.login }}",
            "DBT_PASSWORD": "{{ conn.warehouse_postgres.password }}",
            "DBT_DATABASE": "{{ conn.warehouse_postgres.schema }}",
            "DBT_SCHEMA": "analytics",
        },
    )
```

Copy your dbt project to `/opt/dbt/my_project` on the Airflow host (git pull, rsync, or a CI deploy step). Create an Airflow Connection named `warehouse_postgres` in the Airflow UI under **Admin** > **Connections** with the warehouse host, login, password, and database.

Unpause the DAG in the Airflow UI and trigger a run. Check task logs for `dbt run` output and confirm new relations appear in Postgres.

## Step 4: Alternative: cron on a host with warehouse access

When you do not need Airflow's dependency graph or UI, run the same container from cron on any host that can reach the warehouse private IP (the Airflow instance, a bastion, or a dedicated runner VM).

Create `/etc/dbt/dbt.env` on the host with root-only permissions:

```bash
DBT_HOST=192.168.40.10
DBT_USER=app_role
DBT_PASSWORD=REPLACE_AT_DEPLOY_TIME
DBT_DATABASE=analytics
DBT_SCHEMA=analytics
```

Replace `REPLACE_AT_DEPLOY_TIME` with the value from your secret store before the first run. Do not check this file into git.

Add a cron entry:

```cron
15 2 * * * root docker run --rm --env-file /etc/dbt/dbt.env \
  -v /opt/dbt/my_project:/usr/app \
  -v /opt/dbt/profiles:/usr/app/profiles \
  ghcr.io/dbt-labs/dbt-postgres:1.9.latest \
  dbt run --profiles-dir /usr/app/profiles >> /var/log/dbt-run.log 2>&1
```

The container starts at 02:15, runs models, writes logs to `/var/log/dbt-run.log`, and exits.

## Step 5: Verify transforms in the warehouse

SSH to the Postgres host or connect through your bastion and list relations dbt created:

```bash
psql -h localhost -U app_role -d analytics -c "\dt analytics.*"
```

Run a spot check on a mart model:

```sql
SELECT COUNT(*) FROM analytics.fct_orders;
```

If counts look wrong, re-run with `dbt run --select model_name` and inspect `dbt` logs on the Airflow task or in `/var/log/dbt-run.log`.

## Clean up

Tear down in reverse order when you finish testing:

1. Pause or delete the Airflow DAG (or remove the cron entry).
2. Run `tofu destroy` in the Airflow template working directory.
3. Run `tofu destroy` in the self-managed-postgres template working directory.

## Next steps

- [Airflow template](/resources/iac-templates/airflow)
- [Self-managed PostgreSQL template](/resources/iac-templates/self-managed-postgres)
- [Deploy Airflow with the airflow template](/resources/deployments/deploy-airflow-template)
- [Deploy the self-managed PostgreSQL template with OpenTofu](/resources/deployments/deploy-self-managed-postgres-template)
- [Data pipelines and analytics solution brief](/resources/solutions/data-pipelines-and-analytics)