Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 41 ยท CI and Best Practices

CI with Slim CI and State Selection

Test every pull request safely: build only changed models with state:modified+, defer to production, clone data, and add CI checks for docs and style.

Continuous integration (CI) answers one question before code is merged: "if we deploy this change, will the project still work?" For dbt that means building the changed models and running their tests in a safe schema, automatically, on every pull request. Done naively, CI rebuilds the entire project each time, which is slow and expensive. Slim CI builds only what changed.

dbt and a warehouse cannot run on this site, so commands and workflow files are read-only. The selection logic is shown with SQL stand-ins you can run.

The goal of CI

A good dbt pull-request check does four things:

  1. Compiles the project (catches Jinja and ref errors).
  2. Builds the models that changed, plus everything downstream, into a temporary schema.
  3. Runs the tests on those models.
  4. Reports pass or fail on the pull request, blocking the merge when it fails.

State: comparing against production

dbt can compare your branch with a previous run, using its manifest.json. Save the production manifest after each production job, download it in CI to a folder (say prod-artifacts/), and select by state:

Terminal
dbt build --select state:modified+ --defer --state prod-artifacts

Breaking this down:

  • --state prod-artifacts points to the folder with production's manifest.json.
  • state:modified selects nodes whose definition changed compared with that manifest: SQL, config, YAML, or a macro they use.
  • The trailing + adds everything downstream, because a change can break children.
  • --defer tells dbt: for models that are not built in this run, read the production table instead of failing because it does not exist in the CI schema.

Useful variants:

Selector Meaning
state:modified Changed nodes only
state:modified+ Changed nodes and descendants
state:new Nodes that did not exist in production
state:modified.body Only SQL body changes
state:modified.configs Only config changes
result:error+ Nodes that failed in the last run in the state folder, plus descendants

Here is the idea in SQL: two manifests as tables, and the nodes whose checksum differs (dbt compares checksums of files and configs):

SQL Stand-in: which nodes are modified?

Press Run (or Ctrl + Enter) to see the result here.

stg_orders changed and fct_refunds is new, so the selection (state:modified+) is stg_orders, fct_orders and fct_refunds. The untouched staging model stg_customers and dim_customers are not rebuilt, so CI is fast.

Deferral in action

fct_orders reads stg_customers, which CI did not build. With --defer, dbt rewrites the ref('stg_customers') to point at the production relation. Your CI models compute from production data without copying it. The idea in SQL: resolve each ref to the CI schema if built, otherwise to prod:

SQL Stand-in: resolving refs with defer

Press Run (or Ctrl + Enter) to see the result here.

For incremental models, deferral combined with a clone (dbt clone, where the warehouse supports zero-copy cloning) gives CI a copy of the production table so the incremental path is tested too.

A pull-request workflow

YAML
name: dbt-ci
on:
  pull_request:
    branches: [main]
jobs:
  ci:
    runs-on: ubuntu-latest
    env:
      DBT_CI_SCHEMA: dbt_ci_${{ github.event.pull_request.number }}
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: "3.12" }
      - run: pip install dbt-snowflake
      - run: dbt deps
      - name: Fetch production manifest
        run: ./scripts/download_prod_artifacts.sh prod-artifacts
      - run: dbt build --target ci --select state:modified+ --defer --state prod-artifacts

The CI target writes to a schema unique to the pull request (dbt_ci_123). A cleanup job should drop that schema when the pull request closes: dbt Cloud does this automatically, with dbt Core you write a small drop schema ... cascade step (a drop_schema macro run with dbt run-operation).

flowchart LR
  PR["Pull request"] --> DEP["dbt deps and compile"]
  DEP --> SEL["state:modified+ vs prod manifest"]
  SEL --> B["dbt build in dbt_ci_123, defer to prod"]
  B --> R{"All pass?"}
  R -->|"Yes"| OK["Merge allowed"]
  R -->|"No"| X["Fix and push again"]
  OK --> CLEAN["Drop CI schema"]

Slim CI builds only what changed.

More CI checks

  • Style: run SQLFluff (sqlfluff lint models) for consistent formatting.
  • Governance: dbt_project_evaluator or dbt-checkpoint fail on missing descriptions, missing tests or models that skip the staging layer.
  • Parse check: dbt parse in a quick first job catches syntax errors in seconds.
  • Row count or data diffs: compare changed models against production with audit_helper to review the actual effect of the change.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does state:modified+ select?

    state:modified compares with the saved manifest, and the + adds descendants.
  2. 2. What does --defer do?

    Refs to unbuilt models resolve to the production relations, so CI does not need a full copy.
  3. 3. Why give each pull request its own CI schema?

    Isolation prevents collisions and protects production data.
  4. 4. Where does the manifest for state comparison usually come from?

    Production runs upload manifest.json, and CI downloads it as the reference state.

Summary

  • CI builds and tests a change in an isolated schema before merging.
  • Slim CI uses state:modified+ with a production manifest and --defer for speed.
  • Clone data or defer so incremental and downstream models can be tested.
  • Add linting and governance checks alongside dbt build.

Next: Project Structure and Naming Conventions