# Run a dbt Project as a Flight via GitHub Archive
> A reusable Flight that downloads a dbt project from a GitHub repo over HTTPS, runs dbt build on MotherDuck, and appends dbt's run_results.json to a snapshot table — one row per node per run. Use when you want one deployed Flight that builds a dbt project on a schedule and keeps a queryable history of build and test health.
[View source on GitHub](https://github.com/motherduckdb/motherduck-cookbook/tree/main/flight-plans/flight-dbt-build-gh-archive)
Category: automation
Features: flights
Tags: dbt

AI assistant prompt:

```text
I want one deployed Flight that builds my dbt project on MotherDuck on a schedule and keeps a queryable history of build and test health — per-model status, timing, and test pass/fail over time — instead of just running dbt and losing the results. Help me adapt the "Run a dbt Project as a Flight via GitHub Archive" recipe to my own dbt repo and use case, using it as a guide: https://motherduck.com/docs/cookbook/flight-dbt-build-gh-archive
```

A single-file Flight that runs a dbt project on MotherDuck and records what
happened — downloading the dbt project as a GitHub archive **over HTTPS at run
time** (no git binary needed) and appending dbt's own `run_results.json` to a
snapshot table, **one row per node per run**. The source repository, commit, and
subdirectory are constants in `flight.py`. To build another project, change those
constants and deploy a new Flight version.
This is the dbt-only sibling of [flight-dbt-metricflow](/cookbook/flight-dbt-metricflow/),
reusing the same HTTPS-archive fetch engine; where that template runs `mf query`
against a semantic model, this one runs plain `dbt build` and snapshots the
results.

## How it works

A Flight runs as a single `flight.py` in a fresh, torn-down container.
Embedding a copy of the dbt project would drift from the canonical example, so
`flight.py` **downloads the project over HTTPS at run time** (stdlib only — no
clone, no repo history) and shells out to the `dbt` CLI against it. (The
runtime does preinstall `git`; [flight-dbt-build-git](/cookbook/flight-dbt-build-git/)
is the sibling that clones instead.)

1. Read config from the environment. Flight `config` keys arrive as environment
   variables.
2. Connect to MotherDuck (`md:`) and `CREATE DATABASE IF NOT EXISTS` the
   snapshot database.
3. Download the pinned repository commit as a gzip archive into a temp directory
   and extract it. With no `GIT_TOKEN` secret, the Flight
   uses the public `…/archive/<ref>.tar.gz` endpoint; with one it uses the
   authenticated GitHub API tarball endpoint
   (`api.github.com/repos/<owner>/<repo>/tarball/<ref>`).
4. Discover the dbt project (`dbt_project.yml`) and its profile (`profiles.yml`)
   inside the pinned subdirectory.
5. Run dbt with `--target DBT_TARGET`. `RUN_MODE=build` runs `dbt seed` then
   `dbt build` (run + test); `RUN_MODE=test` runs `dbt test` only. The project's own
   `profiles.yml` is used as-is; its MotherDuck target reads the database name
   from an `env_var()`, which the Flight feeds using `DB_ENV_VAR`, so models land
   in `MODELS_DATABASE`.
6. Parse `target/run_results.json` and append one row per dbt node to the
   snapshot table, tagged with `run_at`, the git repo/ref, the dbt version, and
   the run mode. The full node is kept as a `JSON` column; typed columns
   (`status`, `execution_time`, `failures`, …) are pulled out for easy querying.

```text
config (env) ── download pinned archive ── dbt build/test ── run_results.json ── snapshot table
 per-run values         fixed source commit       on MotherDuck      one row per node    append, JSON, run_at
```

### Build vs test mode

`RUN_MODE` picks what the Flight does with the fetched project:

- `RUN_MODE=build` (default) runs `dbt seed` then `dbt build` — priming the seed
  tables, then materializing and testing every model. This is the Flight that owns
  the build: it materializes the tables and records every node's result. (Seeding
  first is deliberate; see the cold-start caveat below.)
- `RUN_MODE=test` runs `dbt test` only, for when a **separate job already built
  the tables**. It does not write models, but the Flight still creates databases
  when needed and appends `run_results.json` rows. Point `PROJECT_REPO` and
  `PROJECT_REF` at the same project version as the build job.

`SELECT` scopes either mode to a subset of nodes (for example `tag:nightly`);
empty means the whole project.

### The failure policy

dbt exits non-zero both when a model fails to build and when a test assertion
fails. Those are different events, and the Flight treats them differently:

- A **node error** — a model, seed, or snapshot that failed to build, or a test
  that errored (status `error`) — fails the Flight run. Something is broken.
- A **test failure** — a test whose assertion did not hold (status `fail`) — is
  **recorded in the snapshot table but does not fail the run**. Scheduled
  quality drift is a trend to watch, not a page in the middle of the night.

So the Flight runs dbt tolerating its non-zero exit, always writes the snapshot,
and only then re-raises if any node actually errored. The one hard failure with
nothing to record is dbt not producing `run_results.json` at all (a parse or
compile error before any node ran).

### The config-override pattern

A Flight's `config` is a `MAP(VARCHAR, VARCHAR)` of non-secret values, injected
as environment variables. You override it **per run** without editing or
re-versioning the Flight. Source selection is not part of config.

```sql
-- create once with default config
FROM MD_CREATE_FLIGHT(
  name := 'dbt_build',
  source_code := '...flight.py...',
  requirements_txt := '...requirements.txt...',
  config := MAP {
    'RUN_MODE': 'build',
    'DBT_TARGET': 'prod',
    'DB_ENV_VAR': 'MOTHERDUCK_DATABASE',
    'MODELS_DATABASE': 'analytics_flight',
    'SNAPSHOT_DATABASE': 'analytics_flight',
    'SNAPSHOT_TABLE': 'dbt_run_results'
  }
);

-- run with a one-off override: test-only, same Flight
FROM MD_RUN_FLIGHT(
  flight_id := '…',
  config := MAP {'RUN_MODE': 'test'}
);
```

The override is merged over the stored config — provided keys win, omitted keys
keep the Flight default. **Keys must already exist on the Flight**; a per-run
override changes values, it cannot introduce a new key.

### The snapshot table

Every run appends one row per dbt node, so the table is a time series of build
and test health. The full node is stored as `JSON`, so the schema survives any
project or node selection, while typed columns make the common queries easy. To
find tests that are failing across the most recent runs:

```sql
SELECT run_at, unique_id, status, failures
FROM dbt_churn_flight.dbt_run_results
WHERE resource_type = 'test' AND status = 'fail'
ORDER BY run_at DESC;
```

`resource_type` (`model`, `seed`, `test`, `snapshot`, …), `execution_time`, and
`rows_affected` are all columns too, so the same table answers "which model is
getting slower?" and "what did last night's build touch?".

## Questions to answer

- Which repository commit and subdirectory hold the dbt project?
- Should each run **build** the project (`RUN_MODE=build`) or only **test** an
  already-built one (`RUN_MODE=test`)?
- What target does the project's `profiles.yml` define (`DBT_TARGET`), and which
  env var does that target read the database name from (`DB_ENV_VAR`)?
- Which database and table should hold the models (`MODELS_DATABASE`) and the
  `run_results` history (`SNAPSHOT_DATABASE`/`SNAPSHOT_TABLE`)?
- Should the build run on a schedule, and at what cadence?

## Caveats

- **Run-time network to GitHub only.** The Flight downloads the project archive
  over HTTPS at run time, so it needs egress to GitHub — and the archive
  endpoints are **GitHub-specific**. For a non-GitHub host (GitLab, Bitbucket,
  self-hosted) use the git-clone sibling
  [flight-dbt-build-git](/cookbook/flight-dbt-build-git/) instead.
- **A private repo needs a `GIT_TOKEN` secret.** Without one, the public
  `…/archive/<ref>.tar.gz` URL 404s on a private repo. Store a token in a `TYPE
  flights` secret (see [Deploy as a Flight](#deploy-as-a-flight)); the Flight
  then uses the authenticated API endpoint.
- **The build runs every time, and concurrent builds conflict.** With
  `RUN_MODE=build`, each run does `dbt build` against the same database, so a
  large project is slower and **parallel `build` runs collide** on the shared
  tables (MotherDuck rejects the losers with a write-write conflict). For fan-out
  use `RUN_MODE=test`. It does not build models, but it still writes the snapshot.
- **Build mode seeds before it builds.** The default project's staging models
  read the seed tables as dbt `source()`s, and dbt does not sequence a model
  after a seed it only reads as a source. So `RUN_MODE=build` runs `dbt seed`
  first and then `dbt build`, which is what makes a cold first run on a brand-new
  `MODELS_DATABASE` succeed rather than error on models that ran before the seeds.
  `RUN_MODE=test` assumes the tables already exist and does no seeding.
- **Test failures are recorded but do not fail the Flight — model errors do.** A
  failing test assertion (status `fail`) lands in the snapshot table and the run
  stays green; a model, seed, or snapshot that errors (status `error`) fails the
  run. Watch quality drift by querying the table, not by watching for red runs.
- **The project's profile must match your config.** Its `profiles.yml` must
  expose the target database through `env_var()` matching `DB_ENV_VAR`, and it
  must define the target you pass as `DBT_TARGET`. The default
  `dbt-churn-prediction` reads `MOTHERDUCK_DATABASE` and has a `prod` target,
  which is why `DB_ENV_VAR`/`DBT_TARGET` default to those values.
- **Keep the token out of config.** The runtime attaches a MotherDuck token and
  injects it as `MOTHERDUCK_TOKEN`; never place a token in `config`.

## What you'll adjust

`read_config()` reads the per-run settings. Set them as Flight config. Change
`PROJECT_REPO`, `PROJECT_REF`, or `PROJECT_SUBDIR` in `flight.py` and deploy a
new version to build another project.

| Config key | Default | Purpose |
|---|---|---|
| `RUN_MODE` | `build` | `build`: `dbt build` (seed + run + test). `test`: `dbt test` when another job owns the build. |
| `DBT_TARGET` | `prod` | The target in the project's `profiles.yml` to run against (`dbt --target`). |
| `DB_ENV_VAR` | `MOTHERDUCK_DATABASE` | The env var the profile's target reads its database name from, using `env_var()`. |
| `MODELS_DATABASE` | `dbt_churn_flight` | Database the models build into; fed to the profile through `DB_ENV_VAR`. |
| `SELECT` | (empty) | Optional dbt node selection, e.g. `tag:nightly`. Empty runs the whole project. |
| `SNAPSHOT_DATABASE` | `dbt_churn_flight` | Database holding the `run_results` history. Created if missing. |
| `SNAPSHOT_TABLE` | `dbt_run_results` | Append-only table of node results (`run_at`, config, typed columns, `node` JSON). |

`GIT_TOKEN` is **not** a config key — it is a secret. Store it in a `TYPE
flights` secret (see [Deploy as a Flight](#deploy-as-a-flight)) so it never lands
in the Flight's `config` MAP or the logs. Public repos need no token at all.
`MOTHERDUCK_TOKEN` is likewise not config: the runtime injects it automatically
at run time.

## Run it

You need a MotherDuck account and an access token. The default repo/ref make a
fresh deploy produce a successful run with no other credentials.

Smoke-test locally before deploying (this downloads the project over HTTPS,
builds it in your account, and appends one batch of rows to `dbt_run_results`):

```bash
export MOTHERDUCK_TOKEN=your_token_here
uv run --with-requirements requirements.txt flight.py
```

Override a run setting inline. To use another project, change the `PROJECT_*`
constants and deploy a new Flight version. For a private repo, set `GIT_TOKEN` as
a bare environment variable locally.

```bash
RUN_MODE=test \
  uv run --with-requirements requirements.txt flight.py
```

### Deploy as a Flight

Create the Flight with `MD_CREATE_FLIGHT` (no deploy SQL is checked in; adapt the
arguments), passing:

- `name`: a Flight name, for example `dbt_build`
- `source_code`: the contents of [`flight.py`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-build-gh-archive/flight.py)
- `requirements_txt`: the contents of [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-build-gh-archive/requirements.txt)
- `config`: a `MAP` of `RUN_MODE`, `DBT_TARGET`/`DB_ENV_VAR`, `MODELS_DATABASE`, and
  `SNAPSHOT_DATABASE`/`SNAPSHOT_TABLE`
- `flight_secret_names` (private repos only): the `TYPE flights` secrets to inject,
  e.g. `['git_auth']`. **Required for a private repo** — see below.

A MotherDuck token is attached automatically and injected at run time as
`MOTHERDUCK_TOKEN`; no token argument is needed. For a **private** dbt repo, two
steps are both required — create the secret **and** attach it to the Flight.

First create a `TYPE flights` secret holding a GitHub token with **Contents:
Read-only** on that repo. Flights-secret params go inside a `PARAMS MAP` (a bare
`GIT_TOKEN '…'` property is rejected with `Unknown parameter 'git_token'`):

```sql
CREATE SECRET git_auth IN motherduck (
  TYPE flights,
  PARAMS MAP {'GIT_TOKEN': 'github_pat_...'}
);
```

Then **attach it** by passing `flight_secret_names := ['git_auth']` to
`MD_CREATE_FLIGHT`. At run time the runtime injects each param as
`<secret_name>_<PARAM>` (here `git_auth_GIT_TOKEN`); `resolve_secret` in
`flight.py` matches any env var ending in `_GIT_TOKEN`, so the secret name is yours
to choose. Only when the secret is **both created and attached** does the Flight
take the authenticated GitHub API archive endpoint — miss either step and it falls
back to the public endpoint, which 404s on a private repo. If the repo lives in a
SAML-protected org, the token must also be **SSO-authorized** for that org (an
unauthorized token 403s).

Create the Flight without a schedule first, trigger one manual run with
`MD_RUN_FLIGHT(flight_id := …)` (the id is returned by `MD_CREATE_FLIGHT` and
listed by `MD_LIST_FLIGHTS()`), and confirm the snapshot database and a batch of
`dbt_run_results` rows appear. Once green, add a schedule by updating
`schedule_cron` with `MD_UPDATE_FLIGHT` (`0 6 * * *`, 06:00 UTC daily, is a
reasonable default); schedule updates are metadata-only and do not create a new
version.

## Security

- **Identifier safety.** `SNAPSHOT_DATABASE` and `SNAPSHOT_TABLE` flow into
  `CREATE`/`INSERT` statements that cannot be parameterized, so each is
  double-quote-escaped (`_ident`) before any SQL runs.
- **Parameterized data.** Every value written into the snapshot row (the git
  repo/ref, the run mode, and the node results parsed from `run_results.json`) is
  passed as a bound parameter, never string-formatted into SQL.
- **Pinned source.** The Flight downloads the commit in `PROJECT_REF`. Per-run
  config cannot change the code it executes. Change a `PROJECT_*` constant and
  deploy a new version to use another source.
- **Token in a secret, in the header.** A private repo's `GIT_TOKEN` belongs in a
  `TYPE flights` secret, never in `config` (which is stored on the Flight and
  logged). At run time the token is sent in the `Authorization` header of the
  GitHub API request, not in the URL, so it does not reach the Flight logs.

## Learn more

- Flight mechanics (creating, running, scheduling, secrets): the MotherDuck MCP
  `get_flight_guide` tool.
- The local, terminal-driven dbt example this Flight builds by default:
  [dbt-churn-prediction](/cookbook/dbt-churn-prediction/).
- The MetricFlow sibling of this template, which builds the same fetch engine
  around `mf query`: [flight-dbt-metricflow](/cookbook/flight-dbt-metricflow/).
- The git-clone sibling of this template, which fetches with `git` instead of
  the GitHub archive endpoint (any git host, per-commit `git_sha` provenance):
  [flight-dbt-build-git](/cookbook/flight-dbt-build-git/).
- Deeper MotherDuck or DuckDB questions: the `ask_docs_question` MCP tool.
- Files in this template: [`flight.py`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-build-gh-archive/flight.py) (the single-file Flight that
  downloads the project over HTTPS, runs dbt, and snapshots `run_results.json`)
  and [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-build-gh-archive/requirements.txt) (`dbt-core`, `dbt-duckdb`,
  `duckdb`).


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fcookbook%2Fflight-dbt-build-gh-archive%2F&page_title=Run%20a%20dbt%20Project%20as%20a%20Flight%20via%20GitHub%20Archive&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.