All lessons

Module 7: Run it in production · Lesson 7.2 · about 35 min

CI/CD, environments and safe change

Make pipeline changes boring: tests and dbt build on every pull request, dev, CI and prod on the same code, data diffs that prove impact, and Docker rollbacks.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 7 · about 35 minutes · read first: 1.2 and 5.4. You’ll be able to run lint, tests, an end-to-end pipeline run and dbt build on every pull request, keep dev, CI and prod on the same code with different settings, prove what a change does with a data diff, and deploy and roll back with Docker.

CI/CD makes change boring: every pull request is tested on realistic data automatically, and a deploy is one repeatable command you can undo, not an afternoon of nervous typing on a server.

1. Why this matters

Every change to a pipeline is a chance to break a report someone relies on. Think of a restaurant kitchen changing a recipe. A good kitchen does not change it on a busy Saturday night. The cook makes one test plate, the head chef tastes it next to the old one, and only then does the new card go up.

CI (continuous integration) is the test plate: every proposed change is built and checked automatically. CD (continuous delivery or deployment) is putting the new card up the same safe way every time.

2. A CI workflow

A pull request (PR) is a proposed change on its own branch, waiting for review. This GitHub Actions workflow runs on every PR:

# .github/workflows/ci.yml
name: ci
on: [pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4          # pin each action to a major version; check its page for the current one
      - uses: astral-sh/setup-uv@v5
      - run: uv sync --frozen
      - run: uv run ruff check . && uv run ruff format --check .
      - run: uv run python starter/generate_data.py --days 14 --out data --seed 1
      - run: uv run pytest -q
      - run: uv run dagster asset materialize --select "*" -m daily_platform.definitions
        env: { P2D_ENV: ci, P2D_WAREHOUSE: duckdb }
      - run: cd dbt && uv run dbt build --target ci

Line by line: get the code, install the exact locked packages, check style, generate 14 days of data with a fixed seed (so every run sees the same data), run the unit tests, materialise every asset end to end on DuckDB, then build and test the dbt models.

CI uses DuckDB and generated data: fast, free, and it needs no secrets. A separate job, run by hand or on merge, can test against an Azure SQL test database. For that, use a GitHub environment and, better than a stored password, OIDC federation: Azure trusts GitHub to vouch for this repo and hands out a token that expires in minutes.

3. Environments: same code, different settings

dev (your laptop) ci prod
Data generator generator, fixed seed real sources
Warehouse DuckDB DuckDB Azure SQL or Postgres
Secrets .env file none managed identity or Key Vault
Schedules off off on

One setting, P2D_ENV, picks the column; dbt targets mirror it. The code never asks “am I in prod?” in a dozen places. The resources from Lesson 3.3 read the setting once.

4. Data diffs: proof of impact

Unit tests prove the logic. A data diff proves the impact: build the changed table in a CI schema and compare it with prod. Rows that are only in one side are the change.

(select * from ci.fct_restaurant_daily except select * from prod.fct_restaurant_daily)
union all
(select * from prod.fct_restaurant_daily except select * from ci.fct_restaurant_daily);

except returns rows of the first query that are not in the second. Run it both ways and you see every row that differs. Leave out columns that change on every build, such as loaded_at or generated keys, or everything will look different.

Two builds. Prod and the PR's CI build of fct_restaurant_daily, five rows each. The PR moves brand B's business-day cut-off.
CI except prod. Two rows exist only in CI: brand B on Monday (2,655) and Tuesday (2,890).
Prod except CI. Their old versions exist only in prod: 2,710 and 2,940.
The diff. Four rows, every one brand B. A changed row shows up twice, old and new. The change did exactly what we meant.
A side effect. If brand C's Monday also moved (3,350 to 3,310), the change leaked. Do not merge until you know why.

dbt can build only the models a PR changed and reuse prod for the rest (--select state:modified+ --defer --state <prod artifacts>, often called “slim CI”). The audit_helper dbt package and SQLMesh’s table diff do comparisons like the one above for you; check their docs for the current commands.

CI
Every proposed change is built and tested automatically before anyone merges it.
CD
Releasing works the same scripted way every time, and so does undoing a release.
Environment
A set of settings (data, warehouse, secrets, schedules) for the same code: dev, ci or prod.
Data diff
The rows that differ between the changed table and prod: (CI except prod) plus (prod except CI).
Physical meaning
A finance lead asks "what did last week's change do to our numbers?" You open the PR and show the diff: 24 restaurant-days for one brand moved, nothing else. That answer takes thirty seconds and ends the meeting.
Maths and principle

Principle: the symmetric difference of two tables shows every row that differs between them.

Equation:

A△B=(A∖B)∪(B∖A)

A row whose value changed is in both parts (new version in A∖B, old in B∖A), so |A△B|=2c+a+r for c changed, a added and r removed rows.

Analogy: laying two photos over each other and subtracting: only what moved stays visible.

Where it breaks: columns that differ on every build (load times, surrogate keys) make every row look changed. Exclude them.

Try it: you changed the cut-off for one brand. Which rows should appear? (Only that brand’s restaurant-days near midnight. Anything else is a bug.)

5. Deploying and rolling back

Build one Docker image holding your code (the Dagster code location and the dbt project). Push it to a registry such as GitHub Container Registry, tagged with the version. On the server:

docker compose pull && docker compose up -d     # deploy the tag named in compose.yml

Rollback is the same command with the previous tag. Keep schema changes as versioned SQL or dbt models that the pipeline applies, never typed by hand on the server. A GitHub Action on each new tag can run the deploy over SSH, or the server can pull on a timer.

“Tests passed, so the change is safe.” Tests check the rules you thought of. A data diff shows what actually moved, including the rules you did not think of. Merge when both are green and the diff is confined to what you meant.

Protect main: changes arrive only through PRs, CI must pass, and someone reviews (even future-you after a night’s sleep, or Claude Code asked to review as a sceptical senior engineer). Keep each PR small with one purpose, and keep a CHANGELOG.md.

6. Try it

  1. Add the workflow above to daily-data-platform and make it pass (on a private test repo, or locally with act).
  2. Add P2D_ENV=ci settings and a dbt ci target on DuckDB.
  3. Write a small data-diff script that compares two DuckDB schemas and prints rows only in each side, per table. Run it before and after a deliberate change.
  4. Write a slim Dockerfile based on uv and a production docker-compose.yml (Dagster webserver, daemon, code location, Postgres, Metabase).
  5. Write docs/deploy.md: tag → image → pull → up; rollback = the previous tag. Try a rollback.

What to check: a PR that breaks a dbt test turns CI red; your diff script reports only the rows you meant to change; rolling back to the previous tag brings the old behaviour back in under a minute. Commit as ci: lint, tests, end-to-end DuckDB run, dbt build; deploy docs and images.

  • CI builds and tests every pull request on DuckDB with generated data: fast, free, repeatable, no secrets.
  • dev, ci and prod run the same code; one setting picks the data, warehouse, secrets and schedules.
  • A data diff (CI except prod, plus prod except CI) proves impact; a changed row appears twice.
  • Deploy one tagged Docker image; rollback is the same command with the previous tag.
  • Sign in to the cloud from CI with OIDC federation, not stored passwords.

Check yourself

1Why does CI run on DuckDB and generated data instead of the production warehouse?
2A data diff after a change to one brand shows differences in every brand. What does that suggest?
3What is the best way for GitHub Actions to sign in to Azure?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.