P→D Pipeline to Dashboard nihalsulaiman.com

Day 44. CI/CD, environments and safe change

⏱ 4h · learn 60m · lab 145m · ship 20m · review 15m🏷 allBefore: Day 43

By the end of today you can

Run lint, tests and dbt build in GitHub Actions on every pull request
Use dev and prod environments with the same code and different config
Compare outputs before and after a change with a data diff
Package and deploy the platform as Docker images to a small VM

1. Why this matters

Every change to a pipeline is a chance to break a report. CI/CD makes change boring: every pull request is tested automatically against realistic data, and deploys are a repeatable command, not an afternoon of SSH.

2. A CI workflow

# .github/workflows/ci.yml
name: ci
on: [pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - 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   # end-to-end on DuckDB
        env: { P2D_ENV: ci, P2D_WAREHOUSE: duckdb }
      - run: cd dbt && uv run dbt build --target ci

CI uses DuckDB and generated data: fast, free, no secrets. A separate job (manual or on merge) can run against an Azure SQL test database using GitHub environments with secrets or, better, OIDC federation to Azure (no stored secret).

3. Environments

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

Same code, config chosen by P2D_ENV. dbt targets mirror it.

4. Data diffs

Unit tests prove logic; a data diff proves impact. Before merging a change to fct_restaurant_daily, build it in a CI schema and compare to prod: row counts, sums per day, rows that changed. Tools: dbt's --defer/--state for building only changed models ("slim CI"), dbt-audit-helper macros, SQLMesh's built-in table diff, or a simple SQL except both ways.

(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);

Maths and principle

Principle: symmetric difference shows every row that differs between two versions.

Equation: \(A \,\triangle\, B = (A \setminus B) \cup (B \setminus A)\). An intended change should produce a diff confined to the rows the change targets; anything else is a side effect.

Analogy: subtracting two images to see what moved. Where it breaks: non-deterministic columns (loaded_at, generated keys) differ every build; exclude them from the comparison.

Try it: you changed the business-day cut-off for one brand. Which rows should appear in the diff? (Only that brand's restaurant-days near midnight boundaries; a diff in other brands means a bug.)

5. Deploying

Build one Docker image with your code (Dagster code location + dbt project), push to GitHub Container Registry, and on the VM run docker compose pull && docker compose up -d. A GitHub Action on tag can do it over SSH, or the VM can pull on a timer. Keep migrations (schema changes) as versioned SQL or dbt models applied by the pipeline, never by hand.

The 5-year lens

Protect main: PRs only, CI must pass, one review (even if the reviewer is future-you after a night's sleep, or Claude Code asked to review as a sceptical senior). Small PRs with one purpose each. A CHANGELOG in the repo. Being able to say "every change to the platform has a PR with a passing data diff" is a strong signal for regulated industries.

Lab

Add the CI workflow to daily-data-platform (it can merge your earlier projects as packages or submodules). Make it pass on a branch locally with act or by pushing to a private test repo when you're ready.
Add P2D_ENV=ci config and a dbt ci target on DuckDB.
Implement a data-diff script that compares two DuckDB schemas and prints a summary; run it before/after a deliberate change.
Write a Dockerfile (uv-based, slim) and a production docker-compose.yml (Dagster webserver, daemon, code, Postgres; Metabase).
Document deploy and rollback (docs/deploy.md): tag → image → pull → up; rollback = previous tag.

Ship

ci: lint, tests, end-to-end DuckDB run, dbt build; deploy docs and images.