Chapter 27: CI/CD for Data Pipelines

"We reverted the commit in four minutes. The rows it wrote are still there, and they will be there in five years."

Overview

Every chapter in Part IV and Part V has produced code — models, DAGs, tests, scripts — and every one of those changes has to reach production somehow. This chapter is about that path.

The temptation is to say "do what application teams do," and about seventy percent of that is right. The remaining thirty percent is where data teams get hurt, and it is concentrated in four differences that §27.1 sets out. Each of them forces a specific compromise, and a data CI/CD pipeline that has not made those compromises deliberately has made them by accident.

The chapter's organizing claim:

A data deploy has two halves and most teams build only one. Shipping the code is the easy half and is genuinely just software. Getting the data into the state the new code implies — a backfill, a migration, a rebuild — is the half that is unique to this work, cannot be rolled back, and is usually left to whoever notices.

Chapter 19 §19.14 asked where the production manifest.json lives; that is §27.4. Chapter 25 Case Study 1 asked for a margin-impact line in the pull request; that is §27.13.

27.1 Four Ways This Is Harder

One: you cannot easily test against realistic data.

An application test constructs its inputs. A transformation's correctness is a function of data you do not have in CI — production is where the null emails, the duplicate SKUs, the wholesale distributor, and the leading zeros live, and production data usually cannot leave production for reasons Chapter 31 covers.

Two: the build is slow and expensive.

A unit test suite is seconds and free. A full dbt build is minutes and dollars — Kestrel's is 22 minutes and $2.93 (Chapter 19 §19.14) — which makes "run everything on every commit" a real budget line and a real patience line.

Three: there is no rollback for data.

git revert restores the code in four minutes. The rows the bad code wrote are still there, and undoing them is a separate operation with its own risk. §27.8.

Four: staging is not production-like in the one dimension that matters.

An application's staging environment can be genuinely representative. A data staging environment with 1% of the data does not contain the row that breaks the model, and one with 100% of the data is production with a different name and the same PII. §27.5.

📐 Design Decision — the four differences do not have a common fix, and pretending otherwise is the mistake

Each difference forces a distinct compromise, and teams get into trouble by picking one answer and applying it everywhere:

Difference	The compromise
no realistic test data	fixtures for logic, sampled prod for shape — §27.3
slow, expensive builds	slim CI, and accept that it tests less — §27.4
no data rollback	shadow and blue-green, and a repair plan — §27.8, §27.9
staging is not representative	defer to production upstreams, and accept the permission cost

Notice that three of the four are "accept something." That is the honest shape of this chapter: data CI/CD is a set of trades, not a set of best practices, and a team that has not named what it is giving up has given up something anyway.

The one thing not to trade is speed (§27.13), because a slow pipeline gets routed around and a routed-around pipeline provides no assurance while still appearing on the diagram.

27.2 The Pipeline, Ordered by Yield

Order the checks by what they catch per second, so a bad change fails in ten seconds rather than in eleven minutes.

                        typical    catches
1. lint / format          3 s      style, obvious errors
2. DAG + model parse      8 s      import errors, syntax, §24.2's parse time
3. policy lints          12 s      §24's wall clock, §19's hardcoded refs,
                                   §22's SUM without ::BIGINT
4. unit tests            25 s      logic, on fixtures — §27.3
5. slim build + tests   3.1 min    the real thing, on what changed — §27.4
6. full build          22.0 min    nightly only, not per commit

Steps 1–4 need no warehouse at all, which is the property that makes them worth building first: they run on a laptop, in a container, in a fork, with no credentials.

Step 3 is the one most teams do not have, and it is where this book's linters go:

# .github/workflows/ci.yml -- the cheap half
- run: ruff check . && sqlfluff lint models/
- run: python -m pytest tests/test_dags.py          # Ch. 24 §24.11
- run: python scripts/dag_lint.py dags/             # Ch. 24
- run: python scripts/manifest_audit.py --hardcoded  # Ch. 19 CS1
- run: python scripts/coverage.py target/manifest.json  # Ch. 23
- run: python scripts/lint_money_casts.py models/    # Ch. 22 §22.9

Six checks, under twenty seconds, and between them they cover four of this book's incidents.

🔎 Read the Plan — the yield of each stage, measured

Kestrel instrumented its CI for a quarter: which stage caught each failure, and how long that stage takes.

Stage	Failures caught	Median cost per catch
lint / format	31	3 s
parse	9	8 s
policy lints	14	12 s
unit tests	22	25 s
slim build	18	3.1 min
full nightly build	4	22 min, and a night late

The policy lints caught fourteen failures at twelve seconds each, and every one of them was a class of defect that would otherwise have reached production — a wall-clock task, a hardcoded reference, an uncast money sum.

The four caught only by the nightly build are the interesting row. All four were interactions the slim build could not see: a model that was not modified, and was not downstream of anything modified, but shared a target table. §27.4's limitation, showing up in the data.

The ordering rule: put a check as early as its dependencies allow, and measure what it catches. A check that has caught nothing in a year is either redundant or is protecting against something that has stopped happening — and both are worth knowing, which is Chapter 23 §23.10's argument applied to CI.

27.3 Testing Without Production Data

Three options, and the honest position is that you need all three, because each cannot exercise what the others can.

Fixtures. Hand-written rows, in the test. Exercises logic: does a cancelled order get excluded, does the dedup keep the right row, is the tiebreaker applied. dbt unit tests (Chapter 19 §19.7) are this.

Cannot exercise: anything about the shape of real data — cardinality, skew, nulls where you did not expect them, the leading zeros.

Sampled production. A slice, masked. Exercises shape.

Cannot exercise: the rare row, which is the one that breaks things. A 1% sample of Kestrel's orders contains the wholesale distributor with probability... well:

$$P(\text{a 1\% sample includes at least one of the 12 distributors}) = 1 - 0.99^{12} = 11.4\%$$

Which means the shape you tested against is missing the entity that causes Chapter 21's skew, nine times out of ten.

Synthetic data. Generated, deliberately including the pathologies. Kestrel's seed_kestrel.py (Chapter 7) does this: hard deletes, a wholesale customer at ~8% of lines, null emails, duplicated SKUs, a future timestamp.

Cannot exercise: the pathology you did not think of, which is by definition the interesting one.

And a fourth thing, which is not a data source but is where the three meet. An integration test runs the real pipeline end to end against one of the above — extract, load, transform, assert — and it is the only check that exercises the joins between components: that the loader's output schema is what the model expects, that the model's output is what the export reads, that the DAG's task ordering matches the data dependencies.

Run one, nightly, against synthetic data, and keep it small enough to finish in minutes. Its value is not correctness — the unit tests cover that — but wiring, and wiring breaks on exactly the changes that look safest: a renamed column, a moved file, a changed default. Kestrel's integration test is 140 rows and catches about one thing a month, all of them the kind that would otherwise be found at 05:00.

⚠️ Failure Mode — a sample that is representative in aggregate and wrong where it matters

Random sampling preserves the distribution and destroys the tail, and the tail is where every incident in this book lives.

A 1% random sample of Kestrel's order lines is 64,831 rows. It has the right average order value, the right status mix, the right daily shape — and an 11.4% chance of containing a wholesale distributor, a near-zero chance of containing the 18 negative quantities from Chapter 23 Case Study 1, and no reason at all to include the 346 leading-zero SKUs from Chapter 22 Case Study 2.

Sample deliberately, not randomly. The recipe:

sql -- 1. Every entity that is structurally unusual, in full. SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE customer_type = 'wholesale') UNION ALL -- 2. The extremes of every measure that matters. SELECT * FROM orders WHERE order_id IN ( SELECT order_id FROM order_items ORDER BY quantity DESC LIMIT 50) UNION ALL -- 3. Anything a test has ever failed on. THIS is the valuable one. SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM quarantine.order_items) UNION ALL -- 4. Then a random sample, for the shape. SELECT * FROM orders TABLESAMPLE BERNOULLI (1)

Clause 3 is the one to build first. Every row that has ever caused a test to fail is a row that has proven it can break something, and a fixture set that grows by one row per incident is the cheapest regression suite in this book.

27.4 Slim CI in Practice

Chapter 19 §19.14 introduced it; here is what it costs to run.

dbt build --select state:modified+ --defer --state ./prod-manifest

Three operational requirements, and the second is a decision rather than a task:

Somewhere to keep the production manifest. The production job writes target/manifest.json to S3 after each successful run; CI downloads it. Four lines, and without it state:modified has nothing to compare against:

- name: fetch production manifest
  run: aws s3 cp s3://kestrel-artifacts/dbt/manifest.json ./prod-manifest/

--defer means CI reads production data. Every ref() to an unbuilt model resolves to the production relation, so CI's role needs production read access. That is a governance decision to make deliberately — with a read-only role, masking policies on PII columns (Chapter 31 §31.6), and audit logging — rather than to discover when someone asks.

A fallback when the manifest is missing or stale. A first run, a rebuilt bucket, a manifest from a failed deploy. state:modified against a missing manifest selects nothing, and a CI job that tests nothing and passes is the worst outcome available:

if [ ! -f ./prod-manifest/manifest.json ]; then
  echo "no production manifest; falling back to a full build"
  dbt build            # slower, and it actually tests something
fi

⚠️ Failure Mode — what slim CI cannot see

state:modified+ selects modified models and their descendants. Four things fall outside that, and §27.2's four nightly-only catches were all of them:

A model connected by a hardcoded reference. Chapter 19 Case Study 1 — it is not a descendant, so it is not selected. The check that would find the problem is the one the problem disables.

A shared target. Two models writing the same table are not in each other's lineage.

A macro change. Modifying a macro changes every model that uses it, and whether state:modified catches that depends on your dbt version and configuration. Test it rather than assuming.

A change outside dbt. A source schema change, an Airflow DAG edit, a Python transformation. The dbt DAG does not know they happened.

Mitigations, in order of value:

• manifest_audit.py --hardcoded in CI (Chapter 19), which covers the first and is the reason that linter exists.
• A nightly full build, which is where the other three surface. Slim CI is a latency optimization, not a coverage one, and treating it as coverage is how the four got through.
• --select state:modified+ +exposure:* when the change touches anything a dashboard reads.

27.5 Environments, and Why Staging Is Hard

Application teams have dev, staging, and production, and staging is genuinely representative.

For data, staging has a dilemma with no clean answer:

Most mature data teams end up at the fourth, which is what --defer is, and the honest framing is that the industry replaced a staging environment with a permission grant.

What Kestrel runs, and the reasoning:

dev        per-developer schema, deferring to prod for unbuilt models
           (Ch. 19 §19.13) -- with masked PII and a 14-day drop policy

ci         ephemeral schema per pull request, deferring to prod,
           dropped on merge or after 3 days

prod       the thing

"staging"  does not exist as an environment. What it would have caught is
           covered by the nightly full build against prod's own data.

Deleting staging was a deliberate decision and the team's note is worth quoting: "we had a staging environment for eighteen months, it held a 5% sample, it never once caught something the slim build did not, and it cost $340 a month."

27.6 Versioning Three Things

Code is git, and is the easy one.

Schema is a migration, and needs the discipline application teams already have: forward-only, reviewed, and applied by the pipeline rather than by a person. Chapter 17's compatibility rules apply — an additive change is safe, a rename is not.

Data is the one with no good answer, and it is worth being precise about what "versioning data" can and cannot mean:

What you can version: the definition (code), the schema, the lineage, and — with Delta, Iceberg, or Hudi — the table's history (Chapter 10 §10.6).

What you cannot version is the answer to "what did this number say last March?" unless you kept it. Time travel expires; a table rebuilt with corrected logic no longer contains the wrong figure that a decision was made on.

Which produces one practice worth adopting: for any figure that goes into an external report or a regulatory filing, snapshot the value, not just the query. A small table of (metric, period, value, computed_at, code_version) is a few kilobytes a year and it is the only thing that answers "what did we say, and when?"

27.7 What a Data Deploy Actually Is

Deploying a data change is two operations that get conflated:

Ship the code. Merge, and the next scheduled run uses the new definition. This is software and it is solved.

Get the data into the state the new code implies. A backfill, a full refresh, a migration, a rebuild. This is the half that is unique, unrollbackable, and usually undocumented.

CODE                              DATA
merge the model change            fct_order_item still holds rows built
                                  by the OLD logic, for all of history
next run uses the new logic        ...and now history is inconsistent:
                                  yesterday's rows differ in definition
                                  from the day before's

Three deploy shapes, and every model change is one of them:

The failure is treating a definitional change as additive, which produces a table whose meaning changes at a date nobody recorded — Chapter 20 Case Study 1's "a trap with a timestamp on it," created by a deploy.

🔁 Idempotency Check — the deploy checklist for a definitional change

Every model pull request answers these, and the template makes them required rather than remembered:

```markdown

Deploy shape

• [ ] Additive / [ ] Definitional / [ ] Structural

If definitional or structural:

• [ ] Does history need rebuilding? rows: ______
• [ ] Rebuild command (with --dry-run): ______
• [ ] Is the rebuild idempotent? (Ch. 20 §20.3)
• [ ] What is the source retention? (Ch. 26 §26.5)
• [ ] Who is told, and when?
• [ ] Margin impact (measured on staging): ______ (Ch. 25 CS1) ```

The rebuild command with a dry run is the field that does the work. A change whose author cannot write the command has not thought about the data half, and that is the moment to find out — not at 05:00 when the numbers disagree with last week's.

And "does history need rebuilding" has a third answer that must be allowed: "no, and here is why the inconsistency is acceptable." Sometimes it genuinely is — a definition improved partway through a year, documented, with the date recorded. What is not acceptable is not deciding.

27.8 Rollback, and What Cannot Be Rolled Back

git revert is four minutes and it restores the code. It does nothing to the rows.

Four categories, in increasing order of difficulty:

The fourth is the one to design around. Chapter 23's quarantine, Chapter 19's exports, any reverse-ETL: once a number has left your system, no rollback exists, and the only available control is to not send it — which is what §26.8's "stale over wrong" is, at deploy time.

So the practice is to make the irreversible step last, and to put a check immediately before it:

# The export runs AFTER the assertions, not in parallel with them, and it
# is the last task in the DAG. Ch. 24 §24.6's structure, chosen for this.
build >> test >> reconcile >> export

27.9 Shadow and Blue-Green for Models

When a rollback is not available, do not deploy — run both and compare.

Shadow. Build the new version alongside the old, into a different table, and compare:

-- Chapter 18 Case Study 1's verification, as a deploy gate.
SELECT 'only_in_old' AS side, * FROM (SELECT * FROM fct_order_item
                                      EXCEPT SELECT * FROM fct_order_item__new)
UNION ALL
SELECT 'only_in_new', * FROM (SELECT * FROM fct_order_item__new
                              EXCEPT SELECT * FROM fct_order_item);

Expect a non-zero result and read it. A definitional change should produce differences; the gate is not "zero rows" but "the differences are the ones you intended, and only those."

Blue-green. Build into __new, assert, then swap. Chapter 20 §20.3's Strategy 4, used as a deploy mechanism rather than as an idempotency one:

ALTER TABLE gold.fct_order_item SWAP WITH gold.fct_order_item__new;

The old table remains for a day, which is the rollback that otherwise would not exist.

Both cost a full extra build, which is the honest trade: use them for definitional changes to tables that feed decisions, and not for a new column on a staging model.

27.10 Reviewing a Data Change

Two of this book's incidents were merged after review by competent engineers who had no way to see the defect: Chapter 19 Case Study 1's hardcoded reference (the diff showed a JOIN being added, and a JOIN being added is what a correct change also looks like) and Chapter 24 Case Study 1's two clocks.

Which is a claim about the limits of review, and it has a practical consequence: a data review should spend its attention on the things a diff cannot show, and delegate the rest to §27.2's linters.

Four questions worth a reviewer's time, none of which is answerable by reading the changed lines:

1. What is the deploy shape? §27.7. The single highest-yield review question, because the author has to have thought about the data half to answer it, and most of the damage in this book came from treating a definitional change as additive.

2. What is downstream? Not "what does this model do" but what breaks. dbt ls --select model+, or the pull-request comment in §27.13 — and the specific thing to look for is an exposure, because that is a person rather than a table.

3. Does the grain change? A change to GROUP BY, to a join's cardinality, or to a WHERE on a join key is a grain change until proven otherwise, and a grain change that nobody noticed is a fan-out (Chapter 6 §6.9, Chapter 20 Case Study 1).

4. What does this do on a re-run? Chapter 20 §20.3 and Chapter 24 §24.5. Retries are configured, so it will happen.

📐 Design Decision — what to review, and what to delegate to a machine

The temptation after an incident is to add a review checklist item. Chapter 19 Case Study 1 rejected exactly that, and the general rule is worth stating:

If a defect is invisible in the artifact under review, no amount of reviewer diligence is the fix. Either make it visible, or make it impossible.

Delegate to a linter	Keep for a human
hardcoded references (Ch. 19)	the deploy shape
wall-clock in a task (Ch. 24)	whether the definition is right
SUM(*_cents) without a cast (Ch. 22)	whether the grain changed
missing max_active_runs (Ch. 24)	whether a person downstream should be told
test coverage of the six (Ch. 23)	whether this is worth doing at all

The left column is everything mechanical, and every row of it exists because a competent reviewer missed it at least once in this book.

The right column is judgment, and it is what review is for. A reviewer whose attention is spent checking for hardcoded references has none left for "is this definition right?" — which is the question only they can answer.

The measurable version of this argument: after Kestrel moved the left column into CI, review comments per pull request fell from a median of 4 to 2, and the proportion that were about the model's meaning rather than its mechanics rose from about a quarter to about three quarters. Fewer comments, better ones.

27.11 Deploying the Orchestrator

A DAG deploy is not a model deploy, and the difference is that a bad DAG file affects everyone.

Chapter 24 §24.2: the scheduler parses every DAG file every thirty seconds. A syntax error, a slow import, or a module-level database call is not confined to your DAG — it degrades or breaks parsing for the whole installation.

Which makes the DAG deploy pipeline different in three ways:

Parsing is a gate, not a test. DagBag import errors and parse time (Chapter 24 §24.11) fail the build before anything is deployed, because the alternative is discovering it in the scheduler's log.

Deployment is usually a file sync, and syncs are not atomic. A DAG folder synced mid-parse can present a half-written file. Sync to a new directory and switch a symlink, or use a git-sync-style mechanism that swaps atomically — the same blue-green idea as §27.9, applied to a directory.

A DAG deploy changes the future, not the past. Merging a DAG with a different schedule does not re-run anything; merging one with a different task structure affects clearing and backfills of historical runs, which is Chapter 24 Case Study 1's territory.

⚠️ Failure Mode — the deploy that changed history's shape

Renaming a task is a one-line diff and it is the DAG change most likely to surprise you.

python - @task - def extract_orders(...): + @task + def extract_orders_v2(...):

Airflow's history is keyed on task_id. After that merge:

• Every historical run shows extract_orders as a removed task and extract_orders_v2 as never having run.
• Clearing a historical run re-runs extract_orders_v2 — the new code — against the old interval. Which is fine if §24.3 was followed and catastrophic if it was not.
• Any ExternalTaskSensor naming the old task now waits for something that will never appear (Chapter 24 §24.7).

A task rename is a structural change to a historical record, and it deserves the same treatment as a schema migration: deliberate, reviewed, and with a note in the deploy checklist saying what happens to history.

The cheap mitigation: treat task_id as an interface. Rename the function freely; keep the task_id stable with @task(task_id="extract_orders"). The one-line diff then genuinely is one line.

27.12 Secrets

Short, because the rules are the same as everywhere and the failure is common enough to state.

Never in the repository, and validate.py fails the build on a literal credential in a code sample. Never in profiles.yml (Chapter 19 §19.13), which is the most commonly committed secret in the data world because it lives next to the project and looks like configuration.

Prefer keyless authentication. GitHub Actions' OIDC federation to AWS, GCP, or Azure exchanges a short-lived token for a role, and there is no long-lived credential to leak, rotate, or find in a log:

permissions:
  id-token: write            # OIDC
  contents: read
steps:
  - uses: aws-actions/configure-aws-credentials@v4
    with:
      role-to-assume: arn:aws:iam::...:role/kestrel-ci
      aws-region: us-east-1

And the data-specific one: CI logs are readable by everyone with repository access, and a failed test that prints its failing rows prints production data into a log. --store-failures writes them to a table instead; make sure your CI does that rather than echoing them.

27.13 The Build That Must Not Be Slow

A pipeline slower than a developer's patience gets routed around — merged on red, re-run until green, or bypassed for "urgent" changes — and Chapter 19 §19.14 measured what that costs.

Four levers, in order of effect:

Slim CI. 22.0 minutes to 3.1. §27.4.

Order by yield. §27.2 — a bad change fails at second three, not minute eleven.

Cache aggressively. dbt deps, Python dependencies, and the compiled manifest. A cold dbt deps is 40 seconds on every run and is the same every time.

Parallelize the cheap half. Steps 1–4 of §27.2 need no warehouse, so they run concurrently with the slim build rather than before it — as long as the fast ones can fail the job independently, which is the detail that makes it worth doing.

💸 Cost Check — the pull request comment that changed behaviour

Chapter 25 Case Study 1's margin budget, implemented in CI. Every model pull request gets a comment:

text 📊 Data impact models modified 3 downstream models 9 exposures affected 2 (executive_daily_revenue, growth_weekly) deploy shape DEFINITIONAL — history rebuild required est. rebuild 6,483,117 rows, ~14 min margin impact +3m20s (remaining margin 3h26m) CI cost this run $0.41

Six lines, generated from the manifest and a run-record query, and the effect was not the one anyone predicted.

It has never blocked a change. What it did was change what authors do before opening the pull request: four authors in the first quarter found a cheaper approach, and two definitional changes were reclassified as additive once someone had to tick the box.

The "exposures affected" line produced the most conversation. Seeing "executive_daily_revenue" on your own pull request is a different experience from knowing abstractly that models feed dashboards, and three authors asked a stakeholder before merging — which had never happened before and was not requested by anyone.

The general principle: put the consequence where the decision is made. A dashboard nobody opens and a comment on the thing you are already looking at are the same information at completely different prices.

🎓 Interview Angle — "how do you test a data pipeline?"

The answer people give is "dbt tests," and that is one of four things, which is what the question is checking.

The strong answer separates the layers:

"Four different things, and they catch different failures. Unit tests on the logic, with invented rows — those run in CI in milliseconds and they catch a rule change even when today's data doesn't happen to contain the case. Assertions on the data, which run against real rows and catch drift. Integration: build the changed models and everything downstream against production-shaped data, which is where slim CI and --defer come in. And a shadow deploy for anything definitional, because the gate there isn't 'zero rows differ' — the differences are the point, and I have to express what 'intended' means as a predicate before I run it."

Four things that answer does. It separates logic from data. It names what unit tests catch that assertions cannot — a rule change with no matching data — which is the distinction most candidates miss. It mentions production-shaped data and the governance question that comes with it. And it ends on the definitional deploy, which is the hard case.

The follow-ups:

"What data do you test against?" — three sources, each with a blind spot (§27.3). The best answer mentions the pathological fixture: every row that has ever failed a test, accumulated. Very few candidates have built one and it is immediately compelling.

"Your CI passed but production broke. How?" — this is the question. A control that fails open: a selector that matched nothing, a lint step with || true, a manifest that was missing. A candidate who can name a specific fail-open control has debugged one.

"How long does your CI take?" — and the honest follow-up, "how long to a first failure?" Under twenty seconds is the target (Exercise 27.23a), because a pipeline that takes fifteen minutes to tell you about a syntax error is a pipeline people will push around.

And a good thing to volunteer: _built_by. "Every gold row carries the git sha that produced it, so 'which code made this row' is a query." It is two columns, it costs almost nothing compressed, and it is how you find out that a table contains two definitions (Exercise 27.24).

📏 Scale Note — CI time is a tax on every change, and it compounds

A pipeline's runtime is paid by every developer, every push, forever, and the arithmetic is worse than it looks.

text CI duration pushes/day engineer-hours/year waiting what people do ───────────────────────────────────────────────────────────────────────── 2 min 20 ~2.4 h wait 8 min 20 ~9.7 h context-switch 20 min 20 ~24.3 h push and leave 45 min 20 ~54.7 h batch changes, which makes each one riskier

The right-hand column is the real cost. At two minutes people wait and stay in context; at twenty they context-switch, and a context switch costs far more than the twenty minutes. At forty-five, people stop pushing small changes, and a large change is a harder review and a bigger blast radius — so a slow CI degrades correctness, not just velocity.

The ordering that fixes it is Exercise 27.23(a)'s: stages ordered by yield, cheapest first.

text stage time catches needs warehouse? ───────────────────────────────────────────────────────────────────────── 1 lint + format 3 s syntax, style no 2 dbt parse 8 s Jinja, ref() typos no 3 unit tests 6 s LOGIC no 4 structural checks <2 s hardcoded names, purity, sealed figures no 5 slim build + test 4 min everything else yes 6 shadow / diff, on request varies definitional changes yes

Stages 1 to 4 are 19 seconds and need no warehouse, which is the number in Exercise 27.23(a) — under twenty seconds to a first failure, and they catch the majority of what fails.

Two rules that keep it there:

Never put a slow check before a fast one that catches the same class. A twelve-minute build that fails on a typo is a twelve-minute lint step.

And measure time-to-first-failure, not total duration. They are different numbers and only the first one is felt.

🔐 Privacy & Governance — CI is a production read grant that nobody reviewed

Slim CI with --defer means the CI runner reads production data. That is what makes it work, and it is a grant with three properties that make it different from every other grant in the platform.

text a human's production access the CI role's production access ───────────────────────────────────────────────────────────────────────── granted to a named person granted to a WORKFLOW, which anyone who can push a branch can influence reviewed quarterly (ch 30) never reviewed, because it is "infrastructure" revoked on departure persists until somebody deletes the role logged as that person logged as `github-actions`, and the query log cannot say who caused it

Row 1 is the important one. A CI workflow runs code from a branch. Depending on how the workflow is triggered, a pull request from a fork can execute in a context that has production read access — which is a well-known supply-chain shape and is not usually thought about in a data context.

Four controls, and the first two are configuration:

Trigger production-touching jobs on pull_request_target or on protected branches only, never on pull_request from a fork.

Condition the OIDC trust policy on the branch and environment (Chapter 28 §28.9). A role assumable from any branch is a role assumable from a branch somebody just pushed.

Grant read on a masked view, not on the table. CI needs shapes and row counts; it does not need email. A ci_readonly role over a masking policy gives slim CI everything it uses.

And tag the CI role's queries so the warehouse's query log can attribute them. query_tag = 'ci:{{ pr_number }}' makes the log answer "which change caused this" — which is both a cost question (Chapter 33) and an access question, answered by one line.

The practical instruction: put the CI role in the quarterly access review, with the same justification field as a human's grant. Exercise 27.23(c) calls it a governance decision, and this is what that means in practice.

🏭 From the Pipeline — the lint step that had been passing for two years

A CI pipeline had six stages. Stage two was sqlfluff lint, and it had never failed.

The team took that as a sign of good discipline. During an audit somebody ran the linter by hand against a model they knew was badly formatted, and it reported eleven violations.

The CI step was:

yaml - name: Lint run: sqlfluff lint models/ || true # <- added during a migration, # in 2024, to unblock a release

Two years of a green step that could not go red. Nobody had added || true maliciously; it was added deliberately, during a migration, by somebody who intended to remove it that week.

The audit that found it was Exercise 27.18's, and it found four more:

text step fails open? how ──────────────────────────────────────────────────────────────────────── sqlfluff lint yes || true the security scan yes continue-on-error: true the freshness check yes skipped if the source table was absent `dbt build --select state:mod+` yes zero models, exit 0 the schema-drift check yes the baseline file was missing; it logged and returned success

Five of the six stages could not fail, and the CI badge had been green for two years.

Three things generalise.

|| true and continue-on-error are always added deliberately and never removed. They are the single highest-yield thing to grep for in any CI configuration, and the grep takes five seconds.

A check that skips when its input is missing is a check that fails open. The freshness check and the drift check both did the same thing — absence of the thing to check was treated as nothing to report — and both would have been correct to fail loudly.

And the deeper lesson is Exercise 23.20's: a control that exists and does not operate appears on every audit as present. The team's answer to "do you lint?" was yes, with a YAML block to point at, and it was true and useless. The only question that separates the two is whether it has ever failed — and if it has not, whether anybody has made it.

🧭 Version Note — what dbt added that changed CI, and what it did not

text version added what it changed in CI ───────────────────────────────────────────────────────────────────────── 0.18 state:modified slim CI became possible at all 1.0 --defer CI could build against production upstreams, so a partial build is coherent 1.5 model contracts + versions a schema can be ENFORCED at build time, so a breaking change fails in CI rather than downstream 1.8 UNIT TESTS logic testable with no data -- the first mechanism here that runs in milliseconds 1.9 microbatch backfills become parallel and each batch idempotent

The 1.8 row is the one that changed the shape of a good pipeline, because everything before it required a warehouse. Unit tests belong in stage 3 of Exercise 27.23(a)'s ordering, before anything connects, and they catch a class — a rule change with no matching data — that no data test can.

Model contracts are the underused one. A contract: {enforced: true} block with declared column types fails the build when a model's output shape changes, which is Chapter 17's producer-side enforcement applied to your own models. It costs a YAML block per mart.

What none of it changed: the four difficulties in §27.1, the three sources of test data, and the fact that a shadow deploy's gate is not "zero rows." Those are properties of transforming data rather than of a tool, and no version will address them.

And one thing got worse. As slim CI became standard, the failure mode of a missing manifest became more common and no less silent (§27.14). The feature that made CI fast is the feature that makes it pass having done nothing — which is why the four-line assertion is not optional.

🧪 Try It — grep your CI for controls that cannot fail

Five commands, thirty seconds, and every cohort finds something.

```bash

1. the classic

grep -rn "|| true" .github/ ci/ Makefile

2. the polite version

grep -rn "continue-on-error" .github/

3. a step that swallows a non-zero exit

grep -rnE "set +e|exit 0$" ci/ scripts/

4. a selector that can match nothing (§27.14)

grep -rn "state:modified" .github/ | grep -v "wc -l"

5. a check whose input might be absent

grep -rniE "if [ -f|test -f|--state" .github/ ci/ ```

Then, for each hit, ask Exercise 27.18's question: would its failure be distinguishable from having nothing to check?

Add the assertion that makes each one fail closed:

```bash N=$(dbt ls --select state:modified+ --state ./prod-manifest --resource-type model | wc -l) [ "$N" -gt 0 ] || { echo "selected 0 models -- refusing"; exit 1; }

FILES=$(git diff --name-only origin/main | grep -c '\.sql$' || true) [ "$LINTED" -ge "$FILES" ] || { echo "linted $LINTED of $FILES"; exit 1; } ```

The pattern is the same in both: a control asserts a floor on its own scope. "I examined N things, and N is greater than zero" is the minimum any check should report, and a check that reports only failures cannot be checked (Exercise 27.18's two passing rows).

27.14 The Kestrel Platform

🧱 Kestrel Platform — Increment 27: the pipeline

text .github/workflows/ ci.yml ← §27.2's six stages, ordered by yield nightly.yml ← the full build, and the manifest upload pr-report.yml ← §27.11's comment ci/ pr_report.py ← blast radius, deploy shape, margin impact fixtures/ pathological.sql ← §27.3's deliberate sample, clause 3 first .github/PULL_REQUEST_TEMPLATE.md ← §27.7's deploy-shape checklist

Eight things this increment must get right:

1. Stages ordered by yield, with steps 1–4 needing no warehouse. Under twenty seconds to a first failure.
2. The production job uploads manifest.json, and CI falls back to a full build if it is missing — because a CI job that tests nothing and passes is the worst outcome available.
3. --defer is configured, and the production read grant is documented with its masking policy and audit trail. It is a governance decision, not a task.
4. The pathological fixture set exists, and grows by one row per incident. §27.3's clause 3.
5. The pull-request template requires the deploy shape and, for definitional changes, the rebuild command with a dry run.
6. pr_report.py posts the six-line comment, including exposures and margin impact.
7. The export task is last in the DAG, after the assertions. §27.8.
8. OIDC, not long-lived keys, and --store-failures so CI logs never print production rows.

The exercise that matters is 27.23(d): take a defect from any case study in this book, add the row that caused it to fixtures/pathological.sql, and confirm the fixture fails against the pre-fix code and passes after. A fixture set built that way is the only regression suite that grows in the direction your system actually breaks.

27.15 Summary

Four ways this is harder than application CI/CD: you cannot easily test against realistic data · the build is slow and expensive · there is no rollback for data · staging is not representative in the one dimension that matters.

📐 They have no common fix, and three of the four resolve to "accept something." Data CI/CD is a set of trades; a team that has not named what it gave up gave up something anyway. The one thing not to trade is speed.

Order the pipeline by yield. Lint, parse, policy lints, unit tests — all four need no warehouse — then the slim build, then a full build nightly only. 🔎 Kestrel's policy lints caught fourteen failures at twelve seconds each, and the four caught only by the nightly build were all things slim CI structurally cannot see.

Test with all three: fixtures for logic, sampled production for shape, synthetic for known pathologies. ⚠️ Random sampling preserves the distribution and destroys the tail — a 1% sample has an 11.4% chance of containing a wholesale distributor. Sample deliberately, and build clause 3 first: every row that has ever failed a test, kept forever.

Slim CI needs three things: somewhere to keep the production manifest · a production read grant, decided deliberately · and a fallback to a full build when the manifest is missing, because a CI job that tests nothing and passes is the worst outcome available.

⚠️ Slim CI is a latency optimization, not a coverage one. It cannot see a hardcoded reference, a shared target, some macro changes, or anything outside dbt.

Staging has no clean answer, and the industry replaced it with a permission grant. Kestrel deleted theirs: eighteen months, a 5% sample, never once caught something the slim build did not, $340 a month.

Version code, schema, and — for anything in an external report — the value. A table of (metric, period, value, computed_at, code_version) is kilobytes a year and is the only thing that answers "what did we say, and when?"

A data deploy is two operations. Shipping the code is solved. 🔁 Getting the data into the state the new code implies is unique, unrollbackable, and usually undocumented — so the pull request requires the deploy shape and, for a definitional change, the rebuild command with a dry run. "No rebuild, and here is why the inconsistency is acceptable" is a valid answer; not deciding is not.

git revert restores code and does nothing to rows. Four categories, and data sent downstream cannot be rolled back at all — so make the irreversible step last, after the assertions.

Where rollback is unavailable, run both and compare. Shadow-build and diff; expect differences and read them — the gate is "only the ones you intended," not "zero." Blue-green swap keeps the old table for a day.

📐 Review the things a diff cannot show, and delegate the rest to linters. If a defect is invisible in the artifact under review, no amount of diligence is the fix. The four questions worth a reviewer's attention: the deploy shape · what is downstream (and which exposures) · whether the grain changed · what happens on a re-run. After Kestrel moved the mechanical checks into CI, review comments fell from a median of 4 to 2 and the share about meaning rather than mechanics rose from a quarter to three quarters.

⚠️ A DAG deploy affects everyone, because the scheduler parses every file every thirty seconds. Parse time is a gate, not a test; sync atomically; and a task rename is a structural change to a historical record — keep task_id stable and rename the function.

Secrets: never in the repository, never in profiles.yml, prefer OIDC, and --store-failures so a failing test does not print production rows into a log every developer can read.

💸 Put the consequence where the decision is made. A six-line pull-request comment — blast radius, exposures, deploy shape, margin impact — has never blocked a change, and made four authors find a cheaper approach and three ask a stakeholder before merging.

Chapter 28 goes one layer down: the infrastructure all of this runs on, and how to make it reproducible.

Key terms: lint · policy lint · unit test · review scope · task_id stability · fixture · deliberate sampling · slim CI · --defer · state comparison · manifest artifact · ephemeral environment · deploy shape · additive · definitional · structural · shadow deploy · blue-green swap · forward fix · OIDC · --store-failures