CI/CD Data Testing with dbt and DuckDB: Pre-Flight Assertions for Analytics Pipelines

Testing dbt models in continuous integration typically involves triggering a staging run on a remote cloud data warehouse such as Snowflake, BigQuery, or Databricks. While thorough, this approach introduces operational friction: warehouse spin-up delays, queueing, credit consumption, and multi-minute pipeline runs for even minor SQL modifications.

By using the dbt-duckdb adapter, analytics teams can execute standard dbt tests (unique, not_null, relationships, and custom singular assertions) directly inside an ephemeral CI runner against local Parquet snapshots or seed files.

This shifts assertion testing left: models and contracts are validated within the pipeline runner before triggering downstream runs on target warehouses.

Architecture Overview

A dedicated CI profile directs dbt to compile and execute against an in-memory or temporary file-based DuckDB instance. Upstream sources map to lightweight Parquet fixtures stored in the repository, pulled from an artifact registry, or fetched from object storage.

1. Project Configuration

profiles.yml

Define a ci_duckdb target alongside production and development targets. Using an environment variable or setting a local file path (target/ci_duckdb.db) isolates test artifacts within the current runner:

jaffle_shop:
  target: dev
  outputs:
    dev:
      type: snowflake  # or bigquery / databricks / postgres
      account: "{{ env_var('DBT_ACCOUNT') }}"
      user: "{{ env_var('DBT_USER') }}"
      # ... warehouse credentials
    
    ci_duckdb:
      type: duckdb
      path: 'target/ci_duckdb.db'
      threads: 4
      extensions:
        - parquet
        - httpfs

Source Mapping (models/staging/sources.yml)

Configure sources to resolve to Parquet files during CI execution, while defaulting to warehouse tables in standard environments:

version: 2

sources:
  - name: raw_data
    schema: "{{ 'main' if target.name == 'ci_duckdb' else 'raw_production' }}"
    tables:
      - name: orders
        meta:
          external_location: "{{ 'fixtures/raw_orders.parquet' if target.name == 'ci_duckdb' else none }}"
      - name: customers
        meta:
          external_location: "{{ 'fixtures/raw_customers.parquet' if target.name == 'ci_duckdb' else none }}"

Inside model definitions, conditionally route inputs based on the active target:

-- models/staging/stg_orders.sql
{{ config(materialized='view') }}

WITH source AS (
    {% if target.name == 'ci_duckdb' %}
    SELECT * FROM read_parquet('fixtures/raw_orders.parquet')
    {% else %}
    SELECT * FROM {{ source('raw_data', 'orders') }}
    {% endif %}
)

SELECT
    order_id::VARCHAR AS order_id,
    customer_id::VARCHAR AS customer_id,
    net_amount::DOUBLE AS net_amount,
    tax_amount::DOUBLE AS tax_amount,
    gross_amount::DOUBLE AS gross_amount
FROM source

Assertion Definitions

All native dbt tests function without modification under the dbt-duckdb adapter.

Generic Tests (models/staging/schema.yml)

version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: net_amount
        tests:
          - not_null

Singular Business Assertions (tests/assert_net_plus_tax_equals_gross.sql)

Singular tests query compiled models directly, raising a failure if any offending rows return:

SELECT
    order_id,
    net_amount,
    tax_amount,
    gross_amount,
    ROUND(ABS((net_amount + tax_amount) - gross_amount), 4) AS delta
FROM {{ ref('stg_orders') }}
WHERE ROUND(ABS((net_amount + tax_amount) - gross_amount), 4) > 0.01

Platform-Agnostic CI Implementation

To keep the pipeline portable across GitLab CI, Jenkins, Tekton, or GitHub Actions, test orchestration and reporting are handled by a standard shell script and a small parser script rather than vendor-specific plugins.

Artifact Parser (scripts/parse_dbt_results.py)

This script reads target/run_results.json and outputs a standardized Markdown report while returning a non-zero exit code on failures:

#!/usr/bin/env python3
import json
import sys
from pathlib import Path

results_file = Path("target/run_results.json")
if not results_file.exists():
    print("Error: target/run_results.json not found.", file=sys.stderr)
    sys.exit(1)

with open(results_file) as f:
    data = json.load(f)

results = data.get("results", [])
total = len(results)
passed = sum(1 for r in results if r["status"] in ("pass", "success"))
failed = total - passed
status_text = "PASSED" if failed == 0 else "FAILED"

lines = [
    f"## dbt Pre-Flight Quality Check: {status_text}",
    f"**Summary:** {passed}/{total} nodes passed ({failed} failures).\n",
    "| Node Name | Type | Status | Execution Time | Message |",
    "| :--- | :--- | :---: | :---: | :--- |"
]

for r in results:
    node_id = r["unique_id"]
    node_type = node_id.split(".")[0]
    name = node_id.split(".")[-1]
    status = "PASS" if r["status"] in ("pass", "success") else "FAIL"
    duration = f"{r.get('execution_time', 0):.2f}s"
    msg = (r.get("message") or "").replace("\n", " ").strip()
    lines.append(f"| `{name}` | `{node_type}` | {status} | {duration} | {msg} |")

report_content = "\n".join(lines)
Path("dbt_report.md").write_text(report_content)
print(report_content)

sys.exit(0 if failed == 0 else 1)

GitLab CI Pipeline Configuration

The pipeline runs the pre-flight suite inside standard Python container runners. Using GitLab’s native API integration or merge request description updates, the generated report attaches directly to the MR.

# .gitlab-ci.yml
stages:
  - preflight
  - deploy_staging

default:
  image: python:3.11-slim

variables:
  PIP_CACHE_DIR: "$CI_PROJECT_DIR/.cache/pip"

cache:
  paths:
    - .cache/pip/

dbt_preflight_assertions:
  stage: preflight
  rules:
    - if: '$CI_PIPELINE_SOURCE == "merge_request_event"'
      changes:
        - "models/**/*"
        - "seeds/**/*"
        - "tests/**/*"
        - "dbt_project.yml"
  before_script:
    - apt-get update && apt-get install -y --no-install-recommends curl jq
    - pip install dbt-core dbt-duckdb duckdb
    - mkdir -p fixtures
    # Fetch sanitized test datasets (e.g. from internal S3, MinIO, or generic package registry)
    # Mock data generation for illustration:
    - python3 -c "
      import duckdb;
      con = duckdb.connect();
      con.execute(\"COPY (SELECT 'CUST_' || lpad(i::VARCHAR, 3, '0') AS customer_id FROM generate_series(1, 50) t(i)) TO 'fixtures/raw_customers.parquet' (FORMAT PARQUET);\");
      con.execute(\"COPY (SELECT 'ORD_' || lpad(i::VARCHAR, 6, '0') AS order_id, 'CUST_' || lpad(((i % 50) + 1)::VARCHAR, 3, '0') AS customer_id, ROUND(random() * 200 + 10, 2) AS net_amount, ROUND((random() * 200 + 10) * 0.19, 2) AS tax_amount, 0.0 AS gross_amount FROM generate_series(1, 10000) t(i)) TO 'fixtures/temp.parquet' (FORMAT PARQUET);\");
      con.execute(\"COPY (SELECT order_id, customer_id, net_amount, tax_amount, ROUND(net_amount + tax_amount, 2) AS gross_amount FROM 'fixtures/temp.parquet') TO 'fixtures/raw_orders.parquet' (FORMAT PARQUET);\");
      "
  script:
    - dbt deps --target ci_duckdb
    # Run transformations and assertions locally in DuckDB
    - dbt build --target ci_duckdb --profiles-dir .
  after_script:
    - python3 scripts/parse_dbt_results.py || true
    # Post report back to the GitLab Merge Request using CI_JOB_TOKEN
    - |
      if [ -n "$CI_MERGE_REQUEST_IID" ] && [ -f "dbt_report.md" ]; then
        REPORT_BODY=$(jq -Rs . < dbt_report.md)
        curl --silent --request POST \
          --header "JOB-TOKEN: $CI_JOB_TOKEN" \
          --header "Content-Type: application/json" \
          --data "{\"body\": $REPORT_BODY}" \
          "${CI_API_V4_URL}/projects/${CI_PROJECT_ID}/merge_requests/${CI_MERGE_REQUEST_IID}/notes"
      fi
  artifacts:
    when: always
    paths:
      - target/run_results.json
      - dbt_report.md
    expire_in: 7 days

Execution Lifecycle Across Platforms

Operational Considerations

  1. State-Based Testing (dbt slim CI): In projects with hundreds of models, test execution can be further restricted by comparing the current branch state against an exported production manifest.json:Bashdbt test --select state:modified+ --state path/to/prod/artifacts --target ci_duckdb
  2. Deterministic Test Fixtures: Treat Parquet fixtures as explicit integration contracts. Storing small, anonymized baseline tables (under 50 MB) inside an object storage bucket or a GitLab Generic Package Registry keeps runner setup fast and decoupled from production access controls.
  3. Cross-Engine Macro Compatibility: When using dialect-specific syntax (such as Snowflake window framing or date manipulation), isolate them within dbt macros using conditional blocks:SQL{% macro dateadd(datepart, interval, date) %} {% if target.type == 'duckdb' %} {{ date }} + INTERVAL '{{ interval }} {{ datepart }}' {% else %} DATEADD({{ datepart }}, {{ interval }}, {{ date }}) {% endif %} {% endmacro %}

Authors

  • Marc Matt

    Senior Data Architect with 15+ years of experience helping Hamburg’s leading enterprises modernize their data infrastructure. I bridge the gap between legacy systems (SAP, Hadoop) and modern AI capabilities.

    I help clients:

    Migrate & Modernize: Transitioning on-premise data warehouses to Google Cloud/AWS to reduce costs and increase agility.

    Implement GenAI: Building secure RAG (Retrieval-Augmented Generation) pipelines to unlock value from internal knowledge bases using LangChain and Vector DBs.
    Scale MLOps: Operationalizing machine learning models from PoC to production with Kubernetes and Airflow.

    Proven track record leading engineering teams.

  • saidah

Posted

in

, ,

by