Skip to main content

Run dbt transformations from a Flight

Preview
This feature is in preview and is subject to change.

You have a dbt project and want it to run close to your MotherDuck data without maintaining a separate scheduler. In this guide, a Flight installs git, clones the dbt-ingestion-s3 example from motherduck-cookbook, writes a runtime profile that uses the injected MotherDuck token, runs dbt seed, runs dbt build, and records the run in docs_playground.flights_demo.dbt_runs.

The demo builds models into docs_playground and keeps a small run ledger in your own MotherDuck account. The clone-and-install flow keeps the example self-contained; for production, keep per-run setup as small as possible.

Before you start​

The Flight runtime authenticates to MotherDuck for you and injects the credential as MOTHERDUCK_TOKEN, which the generated dbt profile reads through env_var(). To run a scheduled Flight as a service account instead, see Authentication, config, and secrets.

info

This guide installs Debian git and clones a public GitHub repository at the start of each run. For a production Flight, package the project source closer to the runtime or keep the setup step narrow so most run time goes to dbt work instead of environment preparation.

The example writes a profiles.yml file at runtime with MOTHERDUCK_TOKEN referenced through env_var(). Do not paste a MotherDuck token value into dbt profiles, project files, or Flight source.

Create the Flight​

Create the Flight on demand first, then add cron after the first dbt build succeeds.

Create the dbt transformation Flight
SELECT flight_id, flight_name, current_version
FROM MD_CREATE_FLIGHT(
  name := 'docs_dbt_transform',
  requirements_txt := array_to_string([
      'duckdb==1.5.3',
      'dbt-duckdb==1.10.1'
  ], chr(10)),
  source_code := $flight$
import os
import pathlib
import subprocess
import textwrap
import duckdb

REPO_URL = "https://github.com/motherduckdb/motherduck-cookbook.git"
PROJECT_DIR = pathlib.Path("/tmp/motherduck-cookbook/dbt-ingestion-s3")

def run(command, cwd=None):
  print("$ " + " ".join(command))
  subprocess.run(command, cwd=cwd, check=True)

def main():
  os.environ.setdefault("HOME", "/tmp")
  run(["apt-get", "update"])
  run(["apt-get", "install", "-y", "git"])

  if not PROJECT_DIR.exists():
      run(["git", "clone", "--depth", "1", REPO_URL, "/tmp/motherduck-cookbook"])

  profiles_yml = PROJECT_DIR / "profiles.yml"
  profiles_yml.write_text(textwrap.dedent("""
      dbt_ingestion_s3:
        outputs:
          flight:
            type: duckdb
            path: "md:docs_playground?motherduck_token={{ env_var('MOTHERDUCK_TOKEN') }}"
            schema: flights_demo_dbt
            threads: 1
        target: flight
  """).strip() + "\n")

  seed_file = PROJECT_DIR / "seeds" / "flight_run_config.csv"
  seed_file.write_text("setting,value\nrunner,flight\nwarehouse,docs_playground\n")

  run(["dbt", "deps", "--profiles-dir", "."], cwd=PROJECT_DIR)
  run(["dbt", "seed", "--target", "flight", "--profiles-dir", "."], cwd=PROJECT_DIR)
  run(["dbt", "build", "--target", "flight", "--profiles-dir", "."], cwd=PROJECT_DIR)

  con = duckdb.connect("md:")
  con.execute("CREATE SCHEMA IF NOT EXISTS docs_playground.flights_demo")
  con.execute("""
      CREATE TABLE IF NOT EXISTS docs_playground.flights_demo.dbt_runs (
          run_at TIMESTAMPTZ,
          repo_url VARCHAR,
          project_path VARCHAR,
          target_schema VARCHAR
      )
  """)
  con.execute(
      """
      INSERT INTO docs_playground.flights_demo.dbt_runs
      VALUES (current_timestamp, ?, ?, ?)
      """,
      [REPO_URL, str(PROJECT_DIR), "flights_demo_dbt"],
  )
  print("dbt build completed")

if __name__ == "__main__":
  main()
$flight$
);
SQL Editor loading...
Login to connect

Run and inspect it​

The MD_* Flight table functions only accept literal parameters, not subqueries or lateral join columns, so store the Flight ID in a SQL variable first. The next cells reuse it through getvariable:

Set the dbt Flight ID
SET VARIABLE dbt_flight_id = (
  SELECT flight_id
  FROM MD_LIST_FLIGHTS()
  WHERE flight_name = 'docs_dbt_transform'
  ORDER BY created_at DESC
  LIMIT 1
);
SQL Editor loading...
Login to connect

Trigger a manual run:

Run the dbt Flight
SELECT *
FROM MD_RUN_FLIGHT(
  flight_id := getvariable('dbt_flight_id')
);
SQL Editor loading...
Login to connect

Poll for completion:

Check dbt Flight runs
SELECT run_number, status, flight_version, created_at
FROM MD_LIST_FLIGHT_RUNS(
  flight_id := getvariable('dbt_flight_id')
)
ORDER BY run_number DESC
LIMIT 5;
SQL Editor loading...
Login to connect

Read the run log if dbt fails. Store the latest run number in a variable first:

Set the latest dbt run number
SET VARIABLE dbt_run_number = (
  SELECT max(run_number)
  FROM MD_LIST_FLIGHT_RUNS(
      flight_id := getvariable('dbt_flight_id')
  )
);
SQL Editor loading...
Login to connect
Read the latest dbt run log
SELECT logs
FROM MD_GET_FLIGHT_LOGS(
  flight_id := getvariable('dbt_flight_id'),
  run_number := getvariable('dbt_run_number')
);
SQL Editor loading...
Login to connect

Schedule the dbt build​

After the manual run succeeds, add a daily 07:45 UTC schedule. Schedule updates are metadata-only; they do not create a new Flight version.

Schedule the dbt Flight
CALL MD_UPDATE_FLIGHT(
  flight_id := getvariable('dbt_flight_id'),
  schedule_cron := '45 7 * * *'
);
SQL Editor loading...
Login to connect

Query one of the dbt models:

Read a dbt model
SELECT
  domain,
  count
FROM
  docs_playground.flights_demo_dbt.top_domains
ORDER BY
  count DESC
LIMIT
  20;
SQL Editor loading...
Login to connect

Confirm that dbt seed ran:

Read the dbt seed
SELECT
  setting,
  value
FROM
  docs_playground.flights_demo_dbt.flight_run_config
ORDER BY
  setting;
SQL Editor loading...
Login to connect

Adapt the pattern​

  • Replace REPO_URL and PROJECT_DIR with your dbt repository and project path.
  • Keep profiles generated at runtime so secrets stay out of git.
  • Run dbt deps only when you need packages, and install git before dbt deps if packages come from git.
  • Run production schedules as a service account with only the database privileges the dbt project needs.