CI/CD for dbt Projects

Analogy categoryAnalogies

Continuous integration for a dbt project means every pull request automatically builds and tests the changed models in an isolated schema before a human reviewer approves the merge, catching broken SQL, failing tests, or schema drift before they reach production. Continuous deployment then means a merge to the main branch automatically triggers a production job — dbt build against the prod target — without requiring anyone to run commands manually, so the gap between 'code reviewed' and 'live in production' is minutes, not a manual deploy step someone might forget.

🏏

Cricket analogy: It's like a bowler's revamped action being checked by a biomechanics review before they're cleared to bowl in a real match — CI checks the change in isolation before it's trusted in production.

Slim CI and State Comparison

A naive CI setup that runs dbt build on the entire project for every pull request becomes prohibitively slow once a project reaches hundreds of models. Slim CI solves this by comparing the pull request's manifest against a stored manifest.json from the last successful production run, using dbt build --select state:modified+ to build and test only models that changed plus everything downstream of them, while deferring unchanged upstream models to production's already-built state via --defer --state ./prod-run-artifacts. This means a PR touching two models out of four hundred triggers a CI job that builds only those two plus their dependents, typically completing in seconds rather than the tens of minutes a full rebuild would take.

🏏

Cricket analogy: It's like DRS reviewing only the specific delivery in question with ball-tracking rather than replaying the entire innings — slim CI checks only what changed, not the whole project.

Automated Checks Beyond dbt test

A mature dbt CI pipeline layers additional automated checks beyond dbt build and dbt test: dbt compile combined with sqlfluff enforces SQL style consistency, dbt docs generate validates that documentation coverage doesn't regress, a check for models missing a description or primary key test (often via elementary or the dbt project evaluator package) enforces house standards, and a diff of the generated manifest can flag breaking changes like a renamed or dropped column that downstream consumers depend on. These checks typically run as separate jobs or steps in GitHub Actions, GitLab CI, or dbt Cloud's own CI job type, all gating the same pull request before merge.

🏏

Cricket analogy: It's like a pre-match checklist covering not just the pitch report but also player fitness, equipment, and weather — CI layers multiple independent checks, not just one pass/fail test.

yaml

# .github/workflows/dbt_ci.yml — slim CI on pull requests
name: dbt CI
on:
  pull_request:
    branches: [main]
jobs:
  dbt_build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install dbt-snowflake
      - name: Pull production manifest for state comparison
        run: aws s3 cp s3://dbt-artifacts/prod/manifest.json ./prod-artifacts/manifest.json
      - name: Slim CI build
        run: |
          dbt deps
          dbt build --select state:modified+ --defer --state ./prod-artifacts
        env:
          DBT_TARGET: ci

Slim CI depends on having a genuinely up-to-date production manifest.json to diff against. If the stored artifact is stale — say, from a job that failed silently last week — state:modified+ can under-select models, letting a broken change slip past CI because dbt thinks it's unchanged relative to an outdated baseline.

CI builds and tests every pull request's changes in isolation before merge; CD automatically deploys merged changes to production.
Slim CI uses state:modified+ with --defer --state to build only changed models plus downstream dependents, not the whole project.
Slim CI requires an up-to-date production manifest.json as the comparison baseline; a stale one causes under-selection.
Mature pipelines add SQL linting (sqlfluff), documentation coverage checks, and schema-diff checks for breaking changes.
GitHub Actions, GitLab CI, and dbt Cloud's native CI job type can all run these checks as pull-request gates.
A merge to main should automatically trigger the production deployment job rather than relying on a manual step.
CI catches broken SQL, failing tests, and schema drift before they reach the schema BI tools actually query.