dbt-preflight

Built and maintained by JB Analytica — data platform architecture and analytics engineering.

Warehouse-free CI for dbt pull requests. On every pull request, preflight generates synthetic source data from your schema, builds the models the change can reach on DuckDB, runs their tests, checks house conventions, and leaves one review comment. No warehouse credentials anywhere in the workflow.

This is what a reviewer sees when a pull request renames customer_id in a staging model:

🛫 dbt preflight: ❌ failed

Built 7 of 8 models (1 changed) against synthetic data · 42 tests · 0 convention issues · 11 s

Changed models

Model	Build	Rows	Tests
stg_webshop__customers	✅ built	150	3 passed, 2 failed

Unchanged models this change breaks:

• stg_webshop__orders — ✅ built, 1 failing test
• dim_customers — ⏭️ skipped (an upstream model or test failed)

Also rebuilt, no new issues: stg_webshop__products, stg_webshop__order_items, fct_order_items, int_orders__items_aggregated, fct_orders.

Failing tests

• ❌ unique on stg_webshop__customers.customer_id: Binder Error: Referenced column "customer_id" not found in FROM clause!
• ❌ not_null on stg_webshop__customers.customer_id: Binder Error: Referenced column "customer_id" not found in FROM clause!
• ❌ relationships stg_webshop__orders.customer_id → stg_webshop__customers.customer_id: Binder Error: Referenced column "customer_id" not found in FROM clause!

Each failing test folds a <details> block under it with a plain-English reading of the error (this model has no column customer_id: renamed or dropped upstream?), dbt's own test name, and the compiled SQL that failed.

The comment is updated in place on every push, so a pull request carries one preflight comment, not a stack of them.

Six pull-request shapes, four that must fail and two that must pass, are recorded with the comments they produced in docs/scenarios. If you are pointing a coding agent at a dbt project, docs/agents.md says what to tell it.

Why this exists

Letting a person, or a coding agent, change a dbt project without a warehouse to test against is guesswork. The usual fixes need production data in CI (a credential most teams will not hand out) or a dbt Cloud seat. Preflight needs neither: the data is synthetic and relationship-preserving, generated by model2data from your schema, and the warehouse is a DuckDB file that lives for the length of the job.

What it checks, and what it cannot

Checks

• The changed models, everything downstream of them, and every model whose tests read them, compile and run against a schema-faithful dataset.
• Their schema, relationship and accepted-values tests pass or fail, and which rows fail.
• Column renames, dropped ref()s and broken joins are caught before merge. dbt unit tests run too, and a failing one fails the check.
• What the change did to the output. The base branch is built on the same fixtures, and the changed models plus everything downstream are compared: columns added, removed or retyped; row counts; rows whose values differ; and every metric the project defines, evaluated on both sides. A refactor that moves net revenue by 4 percent shows up as a number before a reviewer has to reason about the SQL.
• The change follows the house conventions (below).

Cannot check

• That production numbers are unchanged. A metric that does not move on synthetic data can still move on production, because the fixtures do not carry production's distribution. The diff proves the logic changed, not the size of the effect on real data.
• Warehouse-specific SQL that survives transpiling. Model SQL is rewritten from the project's dialect to DuckDB with sqlglot (see below); a model that still fails because DuckDB lacks something is reported as not verified, not as broken.
• Incremental behaviour across runs. Every preflight run is a full build on a fresh file.

Setup

Add a workflow to the repository that holds the dbt project:

# .github/workflows/preflight.yml
name: dbt preflight
on:
  pull_request:

permissions:
  contents: read
  pull-requests: write

jobs:
  preflight:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: JB-Analytica/dbt-preflight@v0

@v0 follows the latest 0.x release; pin a release tag such as @v0.2.1 for an exact version. Pull requests from forks run with a read-only token, so for those the report lands in the job summary only, with a note saying so.

If the dbt project is not at the repository root, or its sources.yml reads environment variables, add a .dbt-preflight.yml next to it:

project_dir: dbt                    # folder holding dbt_project.yml (default: .)
schema: source_system/webshop.dbml  # DBML describing the sources (default: derive from sources.yml)
rows: 200                           # rows per source table
rows_for:                           # per-table overrides, for realistic fact-to-dimension ratios
  orders: 800
  order_items: 2000
seed: 42                            # same seed, same data, on every run
locale: nl_BE                       # Faker locale for names and addresses
env:                                # variables your profiles.yml / sources.yml expect
  GCP_PROJECT: preflight
dialect: bigquery                   # SQL dialect to transpile from (default: read from profiles.yml)
metrics:                            # extra metrics to compare, for projects with none defined elsewhere
  - name: gross_revenue
    model: fct_orders
    sql: sum(gross_amount_eur)

That is the whole setup. Preflight writes its own profiles.yml, so the project's real profile and its credentials are never read.

Where the schema comes from

Preflight needs to know what the source tables look like. Three options:

1. A DBML file (schema:). If the repository already describes its source system in DBML, point at it. Note hints in the DBML (weighted statuses, skewed foreign keys, null rates) carry through to the fixtures, so the data behaves like a business. A change to the DBML file counts as a change to every source, so everything is rebuilt.

2. Derived from sources.yml. With no schema: set, preflight reads the project's sources. unique and not_null tests become keys, and relationships tests between sources become foreign keys.

3. Inferred from the staging models that read a source, for any table sources.yml leaves without columns, or without a data_type on them. Real projects (jaffle-shop, for one) often declare a source with no columns at all; the staging model that does select id as customer_id, ... from {{ source(...) }} already names every column it needs, so preflight reads that instead of asking for YAML nobody wrote. An explicit cast(x as date) or x::date sets the type first; failing that, how the staging SQL uses the column is read next - an operand of /, *, +, - against a numeric literal, or wrapped in sum(/avg(/round(, is numeric, while a comparison to a string literal or a lower(/upper(/trim(/concat( argument is varchar. Only then does the column's own name decide: _at/_timestamp/_datetime a timestamp, _date/_on a date, id/_id an integer, is_/has_/_flag/enabled/active a boolean, a name built from paid, cost, tax, fee, discount, revenue, amount, price, total, rate and the like a decimal, one built from count, number, qty, quantity, units, age, year, month, day an integer, and a handful of common attribute names (email, phone, name, status, type, sku, ...) always varchar. A name shaped like a foreign key - customer_id, or a bare customer when a raw_customers source exists - is typed as an integer and gets a ref: to that table's id when one can be found, the same referential integrity an explicit relationships test would have set up. The comment says which columns were guessed, so a reviewer can tighten them in sources.yml if a guess is wrong. Only a column no model reads either is reported rather than guessed, because a fixture with the wrong type is worse than no fixture.

   A source column named id is always the primary key. A staging model's own unique/ not_null tests on the alias it gave a source column (id as customer_id, tested as customer_id) carry back to that source column too - pk when both are declared, unique/not null alone otherwise - so a project that tests its staging models instead of its sources still gets keys in the derived schema. A cast() around the column is seen through, because a staging layer over a schemaless loader is where types get pinned; lower(email) is not, because it changes the value rather than the type.

   An accepted_values test carries back the same way, and the column is generated as an enum of exactly those values, so a column the project treats as a vocabulary is not filled with placeholder text its own test then rejects. Only a string column becomes an enum, since an enum's values are strings.

Sources declared with loader: dlt get _dlt_load_id and _dlt_id added to their fixtures. Other loaders can be declared under loader_columns: in the config.

A project with no sources at all, one whose input is its seeds, needs neither: dbt loads the seeds during the build and preflight generates nothing.

Your warehouse's SQL, on DuckDB

A project written for BigQuery says timestamp_diff(a, b, hour) and initcap(x); DuckDB has neither. Preflight transpiles each compiled model from the project's dialect to DuckDB with sqlglot before it runs, after dbt has resolved every ref() and source(). The dialect is read from a profiles.yml checked into the project directory (the adapter type of its default target), or set explicitly:

dialect: bigquery   # or snowflake, redshift, databricks, trino, ... ; duckdb/none to disable

A model sqlglot cannot parse runs as written and the comment says so. dbt's own SQL, and generic tests rendered from macros, are never transpiled; only model bodies and singular tests are.

Metrics, from wherever the project defines them

The comment's "What changed in the output" section evaluates every metric the project defines, on the base branch and on the pull request, and lists the ones that moved. Three sources are read, and a project needs only one of them:

1. dbt's semantic layer. Semantic models and metrics in the project's YAML: simple metrics with measure and metric filters, ratio and derived metrics, including ones whose inputs sit on different models (orders per customer reads fct_orders and dim_customers; each side is evaluated on its own model and the comment names both). A cumulative metric with no window and no grain to date is a running total over all time, and its final value is the plain total, so it is evaluated as one. This is the route for a project that uses dbt and nothing else. A windowed or grain-to-date cumulative metric and a conversion metric need a time spine and are reported as not evaluated.
2. Lightdash meta.metrics. Aggregate metrics on columns (sum, count_distinct, average, ... with their filters) and number metrics on the model whose sql references other metrics with ${...}.
3. metrics: in .dbt-preflight.yml. A name, a model and an aggregate SQL expression, for teams with neither of the above.

With no metrics defined, columns, row counts and differing rows are still compared, and the comment says where metrics can be defined.

Conventions

The convention checks encode the JB Analytica warehouse conventions. They run on the changed models only, so existing debt does not resurface on every pull request.

Models outside those three folders are exempt from the naming and layering rules.

That is the jba preset, at full strength when the repository has a .dbt-preflight.yml. A repository without one gets the same rules as warnings only: it never signed up for anyone's conventions, and a warning is advice where an error would be a demand. A project with its own conventions adjusts them in the config:

conventions:
  preset: jba              # jba (default) or none
  rules:
    description: off       # off | warn | error, per rule
    column_naming: warn
  layers:                  # folder under models/ -> regex a model name must match
    staging: "^stg_[a-z0-9]+__[a-z0-9_]+$"
    marts: "^(dim|fct|rpt)_[a-z0-9_]+$"
  source_layer: staging    # the only folder allowed to read source(); null allows any

Running it locally

uv tool install dbt-preflight     # or: pipx install dbt-preflight, pip install dbt-preflight
cd your-repo
dbt-preflight run --base-ref origin/main

Without --base-ref, every model counts as changed and the whole project is built. Add --comment-file preflight.md to write the comment to a file instead of stdout, and --keep-workdir to leave .preflight/ (fixtures, DuckDB file, dbt artefacts) behind for inspection.

Integrating

--summary-file preflight-summary.json writes a JSON document alongside the comment, with the verdict, counts and every finding as structured data, for a hook, a bot or a plugin to act on without parsing Markdown. docs/integration.md has the full contract: every flag, the exit code, the JSON schema, and a worked pre-pull-request hook.

How it works

1. dbt parse on the pull request, to learn the sources, models and tests.
2. Fixtures: model2data generates data for every source table, cast to the declared types, loaded into a DuckDB file whose catalog is named after the sources' database.
3. dbt parse on the base branch in a temporary worktree, then dbt ls --select state:modified to find what changed.
4. Each compiled model is transpiled from the project's dialect to DuckDB with sqlglot.
5. The selection is closed: downstream models, models whose tests read a changed model, and all their ancestors. dbt build runs on that set with --indirect-selection cautious, so every test that runs has all its inputs built.
6. Conventions are checked on the changed models, column rules against the built tables.
7. The changed models and everything downstream are built on the base branch too, into their own schemas on the same fixtures, and compared: columns, row counts, differing rows and metric values.
8. One Markdown comment, posted or updated through the GitHub API.

The bundled example in examples/webshop/ is the JB Analytica reference architecture's dbt project, made portable with two adapter.dispatch macros. It is what the test suite and the action's self-check run against.

Development

uv sync --extra dev
uv run poe check          # ruff, ty, pytest

Bugs and feature requests belong in the issue tracker. A pull request is welcome; poe check has to be green and, if you change what the comment says, uv run python scripts/scenarios.py re-records the six scenarios so the change shows up in docs/scenarios/.

Licence

MIT. See LICENSE.