Query dbt MetricFlow Metrics as a Flight

Build this flight plan with your AI assistant

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

Open in ChatGPT Open in Claude View on GitHub

A single-file Flight that builds a dbt 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 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:

Read the query settings from the environment. Flight config keys arrive as environment variables.
Connect to MotherDuck (md:) and CREATE DATABASE IF NOT EXISTS the target.
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/<ref>.tar.gz endpoint; with one it uses the authenticated GitHub API tarball endpoint (api.github.com/repos/<owner>/<repo>/tarball/<ref>).
Discover the dbt project (dbt_project.yml) and the profile (profiles.yml) in the checkout by globbing, so any layout works.
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.
mf query --metrics … --group-by … --start-time … --end-time …, writing a CSV.
Append each result row to the snapshot table, tagged with run_at and config.

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:

-- 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:

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 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:

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 <secret_name>_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/<ref>.tar.gz URL 404s on a private repo. Store a token in a TYPE flights secret (see 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).
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) 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):

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.

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
requirements_txt: the contents of 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) — 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 and its EXAMPLES.md.
MetricFlow CLI reference: dbt MetricFlow commands.
Deeper MotherDuck or DuckDB questions: the ask_docs_question MCP tool.
Files in this template: flight.py (the single-file Flight that downloads the project over HTTPS) and requirements.txt (dbt-core, dbt-duckdb, dbt-metricflow, duckdb).

How it works​

The config-override pattern​

The snapshot table​

Querying your own model​

When a separate dbt job owns the build​

Private repositories​

Questions to answer​

Caveats​

What you'll adjust​

Run it​

Deploy as a Flight​

Security​

Learn more​