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
- 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 productionmanifest.json:Bashdbt test --select state:modified+ --state path/to/prod/artifacts --target ci_duckdb - 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.
- 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 %}