Implementing Data CI/CD

When to use

• Adding CI checks to a dbt/SQL/pipeline repo.
• Automating lint, compile, and tests on pull requests.
• Speeding up CI by building only changed models (Slim CI).
• Promoting changes across dev → staging → prod.
• Do NOT use for reviewing an individual PR's logic (use reviewing-data-pipeline-code).

Workflow

- [ ] Lint SQL (SQLFluff) and check formatting on every PR
- [ ] Compile the project to catch ref/Jinja errors early
- [ ] Build + test only changed models against prod parents (Slim CI)
- [ ] Run in an isolated CI schema; tear it down after
- [ ] Promote across environments with the same code, different targets

1. Lint first — SQLFluff catches style and some correctness issues fast and cheap, before spinning up a warehouse.
2. Compile — dbt compile (or parse) fails on bad ref()/Jinja without running SQL.
3. Slim CI — build and test only state:modified+ using a production manifest.json as state, and defer unchanged parents to prod so CI doesn't rebuild the whole project.
4. Isolate — run into a unique CI schema (e.g. per PR) and drop it afterward so runs don't collide.
5. Promote — the same code runs against dev/staging/prod via --target; never fork logic per environment.

Patterns

dbt Slim CI (GitHub Actions sketch):

- run: dbt deps
- run: dbt compile
- run: |
    dbt build --select state:modified+ \
      --defer --state ./prod-artifacts \
      --target ci

--select state:modified+ builds changed models and their children; --defer --state points unchanged parents at prod, so CI stays fast. A fresh/stale prod-artifacts/manifest.json is required for correct selection.

SQLFluff config (.sqlfluff) — pin the dialect and templater so lint matches your warehouse and dbt:

[sqlfluff]
dialect = snowflake
templater = dbt

Environment promotion — dev (developer schemas) → staging (full build on merge) → prod (scheduled). Same repo, different profiles.yml targets and credentials.

Common pitfalls

• Rebuilding the whole project on every PR — slow and expensive; use Slim CI with state:modified + deferral.
• Stale/missing state manifest — state:modified selects the wrong nodes; refresh prod artifacts in CI.
• Shared CI schema across PRs — concurrent runs clobber each other; use a unique schema per PR and drop it.
• Skipping dbt deps in CI — macro/package-not-found failures.
• Different logic per environment — drift and "works in dev" bugs; vary only the target/config, not the code.
• No teardown — orphaned CI schemas accumulate cost.