# Query dbt MetricFlow Metrics as a Flight > A reusable Flight that downloads a dbt + MetricFlow project from a GitHub repo over HTTPS, builds it on MotherDuck, and runs mf query for a metric chosen per run through Flight config. Each run appends the result to a snapshot table. Use when your dbt semantic model lives in a repo and you want one deployed Flight that answers many metric questions and builds a metric time series on a schedule. [View source on GitHub](https://github.com/motherduckdb/motherduck-cookbook/tree/main/flight-plans/flight-dbt-metricflow) Category: analytics Features: flights Tags: dbt, metricflow AI assistant prompt: ```text I want to query metrics from a dbt MetricFlow semantic model on MotherDuck and store scheduled results. Help me adapt the "Query dbt MetricFlow Metrics as a Flight" recipe to my own data and use case, using it as a guide: https://motherduck.com/docs/cookbook/flight-dbt-metricflow ``` A single-file Flight that builds a [dbt MetricFlow](https://docs.getdbt.com/docs/build/about-metricflow) semantic model on MotherDuck and queries it. It downloads a pinned dbt project as a GitHub archive over HTTPS at run time. Flight config chooses the metric, grouping, and date window for each run. Each run appends its result to a snapshot table, so a scheduled Flight builds a queryable time series of metric values. This is the Flight counterpart to the local [dbt-metricflow](/cookbook/dbt-metricflow/) example. There, `mf query` prints to your terminal; here the same semantic model runs in a scheduled, torn-down container and its output has to land somewhere durable. ## How it works A Flight runs as a single `flight.py` in a fresh container that ships **no git binary**. 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) and shells out to the `dbt` and `mf` CLIs against it: 1. Read the query settings from the environment. Flight `config` keys arrive as environment variables. 2. Connect to MotherDuck (`md:`) and `CREATE DATABASE IF NOT EXISTS` the target. 3. Download the pinned repository commit as a gzip archive into a temp directory and extract it. Public vs private is decided at run time: with no `GIT_TOKEN` secret it uses the public `…/archive/.tar.gz` endpoint; with one it uses the authenticated GitHub API tarball endpoint (`api.github.com/repos///tarball/`). 4. Discover the dbt project (`dbt_project.yml`) and the profile (`profiles.yml`) in the checkout by globbing, so any layout works. 5. Produce the semantic manifest `mf query` needs. With `BUILD_MODELS = True` (default) the Flight owns the build. `dbt seed` and `dbt run --target motherduck` materialize the tables. With `BUILD_MODELS = False`, the Flight runs `dbt parse` and expects a separate dbt job to have built the tables. The Flight still creates its target database if needed and appends query results. The fetched project's own `profiles.yml` is used as-is; its MotherDuck path reads the database name from `MD_DATABASE` using dbt's `env_var()`, so nothing is written from scratch. 6. `mf query --metrics … --group-by … --start-time … --end-time …`, writing a CSV. 7. Append each result row to the snapshot table, tagged with `run_at` and config. ```text query config ── download pinned archive ── dbt seed/run ── mf query ── snapshot table per-run values fixed source commit build on MotherDuck append, JSON, run_at ``` ### The config-override pattern This is the reason the Flight exists. 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: ```sql -- create once with default config FROM MD_CREATE_FLIGHT( name := 'dbt_metricflow', source_code := '...flight.py...', requirements_txt := '...requirements.txt...', config := MAP { 'METRICS': 'revenue,orders,customers', 'GROUP_BY': 'metric_time__month', 'START_DATE': '2024-01-01', 'END_DATE': '2024-12-31' } ); -- run with a one-off override: a different metric and window, same Flight FROM MD_RUN_FLIGHT( flight_id := '…', config := MAP {'METRICS': 'revenue_per_customer', 'START_DATE': '2024-02-01', 'END_DATE': '2024-02-29'} ); ``` 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 `mf query` output columns change with the metric and grouping you ask for, so a fixed-column table would break the first time someone overrides `METRICS`. Each result row is therefore stored as a `JSON` column, keeping one table usable across every run: ```sql SELECT run_at, metrics, group_by, result FROM ecommerce_metrics_flight.metric_snapshots ORDER BY run_at DESC; -- pull a field back out of the JSON SELECT run_at, result->>'revenue' AS revenue FROM ecommerce_metrics_flight.metric_snapshots WHERE metrics = 'revenue,orders,customers'; ``` Verified across two runs: a `revenue,orders,customers` run and a `revenue_per_customer` override produce different JSON shapes yet coexist in the one table, each tagged with the config that produced it. ### Querying your own model `PROJECT_REPO`, `PROJECT_REF`, and `PROJECT_SUBDIR` in `flight.py` point at this cookbook's [dbt-metricflow](/cookbook/dbt-metricflow/) example. `PROJECT_REF` is an immutable commit SHA. To query your own metrics, change those constants and deploy a new Flight version. Your project needs a `profiles.yml` with a `motherduck` target whose path resolves the database. The example uses `path: "md:{{ env_var('MD_DATABASE', 'ecommerce_test_db') }}"`, which the Flight sets from its `MD_DATABASE` constant. ### When a separate dbt job owns the build `mf query` needs two things: the project's **parsed semantic manifest** (`target/semantic_manifest.json`) and the **materialized tables** in the warehouse. Only the manifest must be produced by this Flight — the tables just have to exist. `dbt seed`/`dbt run` materialize them, but so does any other job. If a separate dbt job already builds the models into MotherDuck, set `BUILD_MODELS = False` in `flight.py` and deploy a new version. The Flight then runs `dbt parse` to create the manifest locally, and `mf query` reads the tables that the other job built. This avoids rebuilding the models and avoids replacing real fact tables with the demo seed. It does not make the Flight read-only. The Flight can create the target database and always appends query results to its snapshot table. Point `PROJECT_REPO` and `PROJECT_REF` at the same project version as the dbt job so the semantic definitions match the tables. ### Private repositories A public repo needs no credentials. For a **private** GitHub repo, store a personal access token (fine-grained, **Contents: Read-only** on that repo) in a MotherDuck `TYPE flights` secret with a `GIT_TOKEN` param: ```sql CREATE SECRET git_auth IN motherduck ( TYPE flights, GIT_TOKEN 'github_pat_...' ); ``` `resolve_secret('GIT_TOKEN')` in `flight.py` reads it at run time — the Flight injects each secret param as `_GIT_TOKEN`, and the helper accepts either that or a bare `GIT_TOKEN` env var (handy locally). When a token is present, the Flight switches to the authenticated GitHub API archive endpoint and sends the token in an `Authorization` header (never in the URL, so it stays out of the logs). ## Questions to answer - Which repository commit and subdirectory hold the dbt project? - Which metrics matter, and what dimension and date window should each run query? - Which target database should hold the built models and the snapshot table? - Will runs vary the metric per trigger (config override), run on a fixed schedule, or both? - Does the project's time spine range cover your requested dates? ## Caveats - **Run-time network, no git.** The Flight downloads the project archive over HTTPS at run time (stdlib only — the container has no `git`), so it needs egress to GitHub. The archive endpoints are **GitHub-specific**; a non-GitHub host (GitLab, Bitbucket, self-hosted) would need a different fetch. - **A private repo needs a `GIT_TOKEN` secret.** Without one, the public `…/archive/.tar.gz` URL 404s on a private repo. Store a token in a `TYPE flights` secret (see [Private repositories](#private-repositories)); the Flight then uses the authenticated API endpoint. - **Override changes values, not keys.** A per-run `config` override only sets new values for keys that already exist on the Flight. This template exposes only query settings through config. - **Time-dimension queries are bounded by the spine.** The example spine generates `2024-01-01` to `2025-12-31`. `START_DATE`/`END_DATE` outside that window, or grouping by `metric_time__*` beyond it, returns no rows. Widen the spine in your project. - **The build runs every time, and concurrent builds conflict.** With `BUILD_MODELS = True`, each run does `dbt seed` + `dbt run` against the same database, so a large project is slower and **parallel runs collide** on the shared tables (MotherDuck rejects the losers with a write-write conflict). For a separate dbt job, set `BUILD_MODELS = False` to run `dbt parse` before the query (see [When a separate dbt job owns the build](#when-a-separate-dbt-job-owns-the-build)). - **Derived metrics reference metric names, not measures.** `revenue_per_customer` is `revenue / customers`, both metrics. A raw measure name in a derived `expr` will not resolve. - **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 only the query settings. Set those settings as Flight config and override them with `MD_RUN_FLIGHT`. Change the source, destination, or build mode constants in `flight.py`, then deploy a new Flight version. | Config key | Default | Purpose | |---|---|---| | `METRICS` | `revenue,orders,customers` | Comma-separated metric(s) `mf query` computes. Override per run. | | `GROUP_BY` | `metric_time__month` | Dimension(s) to slice by, e.g. `metric_time__day`, `order_id__status`. | | `START_DATE` | `2024-01-01` | `mf query --start-time`; must fall inside the time spine. | | `END_DATE` | `2024-12-31` | `mf query --end-time`; must fall inside the time spine. | `PROJECT_REPO`, `PROJECT_REF`, `PROJECT_SUBDIR`, `BUILD_MODELS`, `MD_DATABASE`, and `SNAPSHOT_TABLE` are constants in `flight.py`. `GIT_TOKEN` is a secret, not a config key. Store it in a `TYPE flights` secret (see [Private repositories](#private-repositories)) so it never lands in the Flight's `config` map or logs. ## 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 to `metric_snapshots`): ```bash export MOTHERDUCK_TOKEN=your_token_here uv run --with-requirements requirements.txt flight.py ``` Override a query setting inline. To use another dbt project, change the `PROJECT_*` constants and deploy a new Flight version. For a private repository, set `GIT_TOKEN` as a bare environment variable locally. A deployed Flight reads it from a Flights secret. ```bash METRICS=revenue_per_customer GROUP_BY=metric_time__month \ 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_metricflow` - `source_code`: the contents of [`flight.py`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-metricflow/flight.py) - `requirements_txt`: the contents of [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-metricflow/requirements.txt) - `config`: `METRICS`, `GROUP_BY`, `START_DATE`, and `END_DATE` A MotherDuck token is attached automatically and injected at run time as `MOTHERDUCK_TOKEN`; no token argument is needed. For a private dbt repo, also `CREATE SECRET … (TYPE flights, GIT_TOKEN '…')` first (see [Private repositories](#private-repositories)) — the Flight injects it at run time. 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_FLIGHTS()`), and confirm the database and a `metric_snapshots` row appear. Trigger a second run with a per-run `config` override to confirm the override reaches the query (a different metric or window shows up in the new snapshot row). Once green, add a schedule (`0 6 * * *`, 06:00 UTC daily, is a reasonable default) by updating `schedule_cron` with `MD_UPDATE_FLIGHT`; schedule updates are metadata-only and do not create a new version. ## Security - **Identifier safety.** `MD_DATABASE` and `SNAPSHOT_TABLE` flow into `CREATE` statements that cannot be parameterized, so each is double-quote-escaped (`_ident`) before any SQL runs. - **Parameterized data.** The snapshot row (metrics, grouping, dates, and the result JSON) is written with bound parameters, never string-formatted into SQL. - **Pinned source.** The Flight downloads the repository commit in `PROJECT_REF`. Per-run config cannot change the code it downloads. Change a `PROJECT_*` constant and deploy a new version to adopt 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 logged and stored on the Flight). 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 version of this project, with an `mf query` cookbook: [dbt-metricflow](/cookbook/dbt-metricflow/) and its [EXAMPLES.md](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-metricflow/../../dbt-metricflow/EXAMPLES.md). - MetricFlow CLI reference: [dbt MetricFlow commands](https://docs.getdbt.com/docs/build/metricflow-commands). - 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-metricflow/flight.py) (the single-file Flight that downloads the project over HTTPS) and [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dbt-metricflow/requirements.txt) (`dbt-core`, `dbt-duckdb`, `dbt-metricflow`, `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-metricflow%2F&page_title=Query%20dbt%20MetricFlow%20Metrics%20as%20a%20Flight&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.