Developer docs / AI Fabric tutorials

DuckDB + dbt data warehouse

  • Volume
  • Image DSL

You don't need a cloud data warehouse to run a real analytics pipeline. dbt turns SQL into a versioned, testable transform pipeline, and DuckDB is a fast in-process analytical engine. Together they give you a warehouse that runs anywhere there's a CPU. In this tutorial you'll run a dbt project against a DuckDB backend on AI Fabric, on demand, with the data and the warehouse file living on a Volume.

The Volume is what makes this practical: your dbt project and the resulting .duckdb file persist between runs, so DuckDB rebuilds only the models that changed instead of starting from scratch every time. There's no server to keep running: the whole pipeline spins up when you call it and spins down when it's done.

Before you start

You need the AI Fabric CLI installed and authenticated: run blade setup once. The job runs on CPU, so the image is built from bladekit.Image.debian_slim. Upload your dbt project (models, dbt_project.yml, and a DuckDB profile) to the dbt-data Volume before the first run.

Create the app, Volume, and image

Create a file called duckdb_dbt.py. Declare the App, a Volume to hold the dbt project and the warehouse file, and a lightweight Debian image with DuckDB, the dbt-duckdb adapter, and the usual data libraries:

duckdb_dbt.py
import bladekit

app = bladekit.app("duckdb-dbt")

data_vol = bladekit.Volume.from_name("dbt-data", create_if_missing=True)

image = (
    bladekit.Image.debian_slim(python_version="3.12")
    .pip_install("duckdb", "dbt-duckdb", "pandas", "pyarrow")
)

Run the dbt transformations

Mount the Volume at /data and give the function room to work with cpu=4 and memory=8192. The warehouse file lives on the Volume at /data/warehouse.duckdb so it survives between runs. The function shells out to dbt run, pointing both --project-dir and --profiles-dir at the project on the Volume:

Python
@app.function(
    image=image,
    cpu=4,
    memory=8192,
    timeout=1800,
    volumes={"/data": data_vol},
)
def run_dbt_pipeline(project_dir: str = "/data/dbt_project") -> dict:
    import subprocess, pathlib, duckdb

    # Ensure the DuckDB file lives on the Volume
    db_path = "/data/warehouse.duckdb"

    # Run dbt transformations
    result = subprocess.run(
        ["dbt", "run", "--project-dir", project_dir, "--profiles-dir", project_dir],
        capture_output=True, text=True,
    )
    print(result.stdout)

Inspect the resulting tables

Once dbt finishes, open the same DuckDB file and look at what it built. SHOW TABLES lists the models dbt materialized, and a COUNT(*) per table gives you a quick sanity check on row counts. Return those counts alongside dbt's exit code so the caller knows the run succeeded:

Python
    # Query the resulting tables
    con = duckdb.connect(db_path)
    tables = con.execute("SHOW TABLES").fetchall()
    row_counts = {t[0]: con.execute(f"SELECT COUNT(*) FROM {t[0]}").fetchone()[0] for t in tables}
    con.close()

    return {"tables": row_counts, "dbt_exit_code": result.returncode}

Trigger the pipeline

The local_entrypoint runs on your machine. It calls the remote pipeline and prints the row count for each table dbt built:

Python
@app.local_entrypoint()
def main():
    result = run_dbt_pipeline.remote()
    for table, count in result["tables"].items():
        print(f"  {table}: {count} rows")

Run it

Run the program with the AI Fabric CLI:

Terminal
blade run duckdb_dbt.py

Output
  stg_orders: 1500 rows
  dim_customers: 320 rows
  fct_revenue: 90 rows

DuckDB reads the Parquet and CSV files from the Volume, runs your dbt models, and writes the results back to warehouse.duckdb on the same Volume. Because the warehouse file persists, the next run rebuilds only the models whose inputs changed, and you pay only for the vCPU-minutes the pipeline actually used.

Take it for a spin

Everything above in one file, ready to run.

duckdb_dbt.py
import bladekit

app = bladekit.app("duckdb-dbt")

data_vol = bladekit.Volume.from_name("dbt-data", create_if_missing=True)

image = (
    bladekit.Image.debian_slim(python_version="3.12")
    .pip_install("duckdb", "dbt-duckdb", "pandas", "pyarrow")
)

@app.function(
    image=image,
    cpu=4,
    memory=8192,
    timeout=1800,
    volumes={"/data": data_vol},
)
def run_dbt_pipeline(project_dir: str = "/data/dbt_project") -> dict:
    import subprocess, pathlib, textwrap, duckdb

    # The DuckDB warehouse file lives on the Volume so it persists between runs.
    db_path = "/data/warehouse.duckdb"

    # ── 1. Seed a small source table directly in DuckDB ──
    con = duckdb.connect(db_path)
    con.execute("CREATE OR REPLACE TABLE raw_orders AS SELECT * FROM (VALUES "
                "(1,'alice',120.0),(2,'bob',80.0),(3,'alice',50.0),"
                "(4,'carol',200.0),(5,'bob',20.0)) AS t(id, customer, amount)")
    con.close()

    # ── 2. Bundle a minimal dbt project on the Volume (in-code, no upload) ──
    proj = pathlib.Path(project_dir)
    (proj / "models").mkdir(parents=True, exist_ok=True)

    (proj / "dbt_project.yml").write_text(textwrap.dedent("""\
        name: fabric_demo
        profile: fabric_demo
        version: '1.0.0'
        config-version: 2
        models:
          fabric_demo:
            +materialized: table
    """))

    # profiles.yml points dbt-duckdb at the same warehouse file.
    (proj / "profiles.yml").write_text(textwrap.dedent(f"""\
        fabric_demo:
          target: dev
          outputs:
            dev:
              type: duckdb
              path: {db_path}
    """))

    # One model: revenue per customer, built from the seeded raw_orders table.
    (proj / "models" / "customer_revenue.sql").write_text(
        "select customer, sum(amount) as total_revenue, count(*) as n_orders "
        "from raw_orders group by customer order by total_revenue desc")

    # ── 3. Run dbt ──
    result = subprocess.run(
        ["dbt", "run", "--project-dir", str(proj), "--profiles-dir", str(proj)],
        capture_output=True, text=True,
    )
    print(result.stdout)
    print(result.stderr)

    # ── 4. Inspect the resulting tables + show the model rows ──
    con = duckdb.connect(db_path)
    tables = [t[0] for t in con.execute("SHOW TABLES").fetchall()]
    row_counts = {t: con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] for t in tables}
    rows = con.execute("SELECT * FROM customer_revenue").fetchall()
    con.close()

    return {"tables": row_counts, "customer_revenue": rows,
            "dbt_exit_code": result.returncode}

@app.local_entrypoint()
def main():
    result = run_dbt_pipeline.remote()
    print("Tables:")
    for table, count in result["tables"].items():
        print(f"  {table}: {count} rows")
    print("customer_revenue model:")
    for row in result["customer_revenue"]:
        print(f"  {row}")

Concepts used: Run, Image build, Volume

Contact the team

Loading the form…