Metadata-Version: 2.4
Name: dbt-preflight
Version: 0.3.0
Summary: Warehouse-free CI for dbt pull requests: build and test the changed models against synthetic data, check house conventions, leave one review comment.
Author: JB Analytica
License-Expression: MIT
Project-URL: Homepage, https://github.com/JB-Analytica/dbt-preflight
Project-URL: Repository, https://github.com/JB-Analytica/dbt-preflight
Project-URL: Issues, https://github.com/JB-Analytica/dbt-preflight/issues
Project-URL: Changelog, https://github.com/JB-Analytica/dbt-preflight/blob/main/CHANGELOG.md
Keywords: dbt,ci,github-actions,synthetic-data,analytics-engineering,data-engineering,duckdb,code-review
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Testing
Classifier: Topic :: Software Development :: Quality Assurance
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: dbt-core<2,>=1.11
Requires-Dist: dbt-duckdb>=1.11
Requires-Dist: duckdb>=1.1
Requires-Dist: model2data>=1.3.1
Requires-Dist: pandas>=2.2
Requires-Dist: pyyaml>=6.0
Requires-Dist: sqlglot>=30.18.0
Requires-Dist: typer>=0.12
Provides-Extra: dev
Requires-Dist: pytest; extra == "dev"
Requires-Dist: pytest-cov; extra == "dev"
Requires-Dist: pre-commit; extra == "dev"
Requires-Dist: ruff>=0.1.0; extra == "dev"
Requires-Dist: ty>=0.0.4; extra == "dev"
Requires-Dist: types-pyyaml; extra == "dev"
Requires-Dist: poethepoet>=0.38.0; extra == "dev"
Dynamic: license-file

# dbt-preflight

[![PyPI](https://img.shields.io/pypi/v/dbt-preflight)](https://pypi.org/project/dbt-preflight/)
[![Python versions](https://img.shields.io/pypi/pyversions/dbt-preflight)](https://pypi.org/project/dbt-preflight/)
[![CI](https://github.com/JB-Analytica/dbt-preflight/actions/workflows/ci.yml/badge.svg)](https://github.com/JB-Analytica/dbt-preflight/actions/workflows/ci.yml)
[![Licence: MIT](https://img.shields.io/badge/licence-MIT-blue)](https://github.com/JB-Analytica/dbt-preflight/blob/main/LICENSE)

Built and maintained by [JB Analytica](https://www.jbanalytica.com/) — 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.

![dbt-preflight on a pull request that renames a column: the diff, the run, the comment](https://raw.githubusercontent.com/JB-Analytica/dbt-preflight/main/assets/demo.gif)

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](https://github.com/JB-Analytica/dbt-preflight/blob/main/docs/scenarios/README.md). If you are
pointing a coding agent at a dbt project, [docs/agents.md](https://github.com/JB-Analytica/dbt-preflight/blob/main/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](https://github.com/JB-Analytica/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:

```yaml
# .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:

```yaml
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](https://github.com/tobymao/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:

```yaml
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.

| Rule | Severity | What it wants |
| --- | --- | --- |
| `naming` | error | `staging/` models named `stg_<source>__<entity>`, `intermediate/` named `int_<entity>__<verb>`, `marts/` named `dim_<entity>` or `fct_<event>` |
| `layering` | error | A staging model reads exactly one `source()` and no `ref()`; nothing outside staging reads a `source()` |
| `primary_key` | error | At least one column tested `unique` and `not_null` |
| `description` | warn | The model has a description |
| `column_naming` | warn | snake_case; timestamps end in `_at`, dates in `_date`, booleans start with `is_` or `has_` (read from the built table's real types) |

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:

```yaml
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

```bash
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](https://github.com/JB-Analytica/dbt-preflight/blob/main/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](https://github.com/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

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

Bugs and feature requests belong in
[the issue tracker](https://github.com/JB-Analytica/dbt-preflight/issues). 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](https://github.com/JB-Analytica/dbt-preflight/blob/main/LICENSE).
