Nobody Referenced That Column

The warehouse-cost review turned up a wide table. silver_listing carries condition_grade_raw — the grade the seller typed when they listed the item, eight values from NWT down to unrated — and right next to it condition_grade, the five-value canonical version built when the silver layer was cleaned up (Lesson 11’s rule R3). Two columns saying the same thing. Ticket DATA-1187: retire the old one.

The engineer does the careful things. git grep condition_grade_raw returns 8 files, 7 of them downstream. He reads all 7. They all look fine. And rather than drop the column and break something, he keeps the name and points it at the canonical value, with a comment:

-- DATA-1187: condition_grade_raw is deprecated. Keep the name, serve the
-- canonical value, so nothing downstream has to change in this release.
case when trim(seller_grade_input) in ('NWT','NWOT') then 'NEW'
     when trim(seller_grade_input) in ('A','A-')     then 'EXCELLENT'
     when trim(seller_grade_input) in ('B+','B')     then 'GOOD'
     when trim(seller_grade_input) = 'C'             then 'FAIR'
     else 'UNKNOWN' end as condition_grade_raw,

CI is green: 43 tests, 0 failures. Compiled schema identical — same names, same order, same types, 0 differences. Parquet on the bronze table drops from 2,587,736 to 2,566,565 bytes (0.82%), exactly the saving the ticket asked for. Merged on 2026-07-14.

34 days later a seller emails support: her new-with-tags coat was discounted three days after she listed it, and the price the app suggested was lower than the one it suggested for the same coat in June.

“What depends on silver_listing.condition_grade_raw?” is one question with four answers.

After the release, 17 models produced different output. That set is exactly the 17 models on the column-level path — no misses, no false alarms. Table-level lineage named 24 and over-predicted by 7 (70.8% precision). Text search named 7 and missed 9 models entirely.

The where-used explosion

* = reads the column · · = table-reachable only · | = the file names the column

| * silver_listing                      condition_grade_raw
    * int_listing_enriched              condition_grade_raw
|     * int_listing_daily_snapshot      condition_grade_raw
|       * fct_listing_daily             condition_grade_raw
          · mart_inventory_health       —
            · rpt_inventory_health      —
|       * mart_pricing_input            condition_grade_raw +2
          * rpt_markdown_queue          markdown_hold_days +1
          * rpt_price_ladder            premium_multiplier +1
|     * dim_listing                     seller_stated_grade
        * fct_sale                      seller_stated_grade
          * fct_return                  seller_stated_grade
            * mart_returns_analysis     seller_stated_grade
              * rpt_ops_returns         seller_stated_grade
          * mart_marketing_audience     seller_stated_grade
          · mart_finance_gmv            —
            · rpt_exec_weekly           —
        · fct_price_change              —
|     * mart_grade_mix                  condition_grade_raw
|       * rpt_grade_mix_daily           condition_grade_raw
|     * mart_seller_scorecard           nwt_share
        * rpt_seller_league             nwt_share
      · int_seller_activity             —
        · dim_seller                    —

Where used, in full

The no rows are the 11 dependent columns living in a file that never mentions condition_grade_raw.

int_listing_enriched is a convenience view everything in the gold layer builds on: select l.*, s.seller_tier, … from silver_listing as l. Fifteen columns ride through it under their own names.

To expand a star you have to know what columns the table has, and that is not in the SQL — it is in the catalog. Hand a parser only the model files and it resolves 5 of this view’s 20 output columns, then reports that nothing downstream depends on the column under review. Not an error, not a warning: zero — which is worse than useless, because zero looks like an answer.

A SELECT * in the middle of a DAG is not a style preference. It converts column-level lineage into table-level lineage for everything downstream of it — and table-level lineage is precisely the answer nobody can act on.

It is also why lineage is a build artifact rather than a text search. dbt’s manifest.json, the warehouse’s INFORMATION_SCHEMA plus its query log, or a SQL parser given a schema: any of the three can answer the question. Nothing that reads files without a catalog can.

The shim preserved the column’s name, its type and its not-null property.

• not_null(condition_grade_raw) — the only test on the column — still true.
• The other 42 tests never touch it. 0 of 43 failed.
• Compiled column set and types: 0 differences. A data contract describing shape (Lesson 08) cannot see this, because the shape did not change.
• Row count on silver_listing: 400,000 before, 400,000 after.
• accepted_values on the raw column: did not exist. Two tests sit on condition_grade, the column the team had just built. One sits on the deprecated column, and it is not_null.

A deprecated column loses its tests long before it loses its consumers. Had accepted_values existed with the eight seller-stated grades it would have failed 400,000 rows of 400,000 in the first CI run — the entire table.

Except that it depends how the shim is written. accepted_values fails rows whose value is outside the list; it has nothing to say about values that have gone missing.

The more carefully the migration is written, the less detectable its residue. Variant B is a smaller error than variant A and strictly harder to find — it passes the one test that would have caught A on day zero.

17 of 43 models produce different output — 1 silver, 2 intermediate, 4 gold, 5 marts, 5 reports. 7 of them also change row count.

The project has 37 columns denominated in euros. 35 are identical to the cent under both variants: GMV €4,847,793.52, commission €1,066,514.5744, listed value €14,998,249.80, fct_sale 130,698 rows, the weekly exec report to the cent.

That is arithmetic, not luck: a sum over a fact table cannot see how the facts are labelled. Nothing here touched a quantity — it changed a category — so every financial monitor in the company was structurally incapable of moving. The two €-columns that did move are the two computed from the grade rule.

A seller-quality metric read 0.0000% for all 24,000 sellers for 34 days. Lesson 12’s rule: a suspiciously round result is a bug hypothesis before it is a finding — and here it was a bug, for a month, on a dashboard someone owns.

The pricing service reads mart_pricing_input every morning. Two of its columns are grade rules: a 12% premium on the suggested list price for seller-stated-new stock (6% for A−), and a 14-day hold before anything is marked down. After the shim no listing is premium, so every suggestion came out 10.71% low and every hold went to zero.

• 7,665 listings, worth €359,155.72 at the price they should have carried, were listed during the 34 days.
• €7,177.29 given away on 1,353 sales by the morning the seller’s email arrived.
• A further €3,386.59 on 702 sales in the 22 days after the fix — the wrong number is written onto the listing and correcting the model does not re-price the stock.
• €10,563.88 over 2,055 sales · €310.70 a day · €113,406.36 a year had nobody written in.
• 5,683 of those listings are still live, carrying €270,439.80 of stock at a price that was never right.

A pipeline bug becomes a business fact the moment a consumer writes down what it read. Fixing the model fixed the model; the prices stayed wrong, and €3,386.59 of the bill arrived after the fix was deployed.

Lesson 06’s band test, applied honestly to the composition metric, fires once and then goes quiet: a trailing 90-day min–max envelope contains 0.00% from the day after the release, so the release day is 1 of 57 days outside — in a channel that had already produced 16 crossings across 583 pre-release days (2.74%). The envelope poisons itself with the very event it exists to flag. Against a fixed reference window the same metric is outside on 57 of 57 days. The instrument was never the band; it was the reference.

A sigma I am not going to publish. The generator’s daily premium share has sd 0.363%, putting the release day at z = −96.2 — a property of deterministic synthetic data, not of the world. A real day of about 606 listings at p ≈ 0.349 has a binomial sd of 1.937%, i.e. z ≈ −18.0. Neither is the honest statement, which needs no sigma: a share that sat between 34.061% and 36.398% for 730 days read 0.000%.

The rung worth building is A8, and it is not a test — it is a build step. Everything below it answers “did something change?”. Only A8 answers “what will this change?”, which is the question the reviewer was trying to answer with git grep in the first place.

Eleven shapes so far: the event (06–10), the constant (11), the drift (12), the unwatched (13), the reversible (14), the unreproduced (15), the bundled (16), the transient (17), the extremal (18), the referential (19), the plural (20). The twelfth is the first that is not about testing at all.

The remote: the symptom surfaces in a file that did not change. rpt_ops_returns is 6 hops from the edit and contains no token the pull request touched; 9 of the 16 affected models never mention the column by name. No diff to review, no blame to read, no owner who sees the change go past. And unlike every earlier shape the answer was fully determined before anything ran — statically, in the code, at review time. Nothing was uncertain. It was simply never computed.

Which is why this is the only failure shape whose fix is a build artifact rather than a test. It generalises straight out of the warehouse: renaming an event property, changing an enum in an API response, moving a column between marts, retiring a dashboard field, or changing a metric’s date basis (Lesson 20 — a metric definition is a node in this graph too).

Every earlier failure shape is about a test not noticing. This one is about a review not being able to: the blast radius was knowable, completely and cheaply, and no artifact in the toolchain computed it.

1. “If I delete a column, what tells us what breaks — and is it a build artifact or a person?” The honest answer is often “a person, from memory”. That is the gap.
2. “How many SELECT * models sit between our sources and our marts?” Each one is a place where column-level lineage degrades to table-level. One query against the repository, and nobody has run it.
3. “Which of our columns get renamed mid-pipeline, and do we know the alias chains?” A rename is exactly where text search stops and lineage has to take over.
4. “Which nodes in our lineage graph are outside the warehouse?” The pricing service, the reverse-ETL destinations (13), the BI layer, the erasure job (14), the metric definitions (20). If they are not declared as exposures they are not in the graph — and that is where the money was.
5. “When we deprecate a column, do we add a test to it or take one away?”

Everything here comes out of one script: 43 SQL models, 400,000 deterministic listings, sqlglot 30.18.0 for the lineage, DuckDB 1.5.5 for the three builds. It ships in full inside the artifact (§11 there). Run it, then:

1. Break the star. Rewrite int_listing_enriched to list its 20 columns explicitly instead of l.* and re-run. The catalogue-less answer goes from 0 to the true 20. That is the whole cost and the whole benefit of the star, measured.
2. Add the missing test. Put accepted_values with the eight raw grades on silver_listing.condition_grade_raw and re-run both variants. Watch it catch A completely and B not at all.
3. Write the monitor. One query: for every text column in every model, count distinct values and compare with yesterday. 13 columns contract. Now decide what you would do with that alert at 07:00 on a Tuesday — that is the real design problem, not the query.
4. Do the review properly. Print the 20-row where-used table above and ask, of each row, what the column is for. Two of them are pricing rules.

One sentence: a text search answers “where is this name written”, and the question you actually asked was “what will be different tomorrow if I change this”. Those two answers differed by 9 whole models and €10,563.88.

One rule for change reviews: break things loudly on purpose. Dropping the column outright would have failed the 7 models that name it at compile time, in one CI run, taking their whole subtree down with them — loudly, completely, for nothing. The compatibility shim was written to be kind and converted a loud, cheap, complete failure into a quiet, expensive, partial one. A shim is only compatible if it preserves the type, the domain and the meaning; the first two are testable and the third is not.

How these numbers were made

A 43-model warehouse project (8 raw sources, six layers) written as plain SQL; 400,000 listings and 133,458 sales generated by modular arithmetic on the row id, with a weekday factor and a growth trend so daily volume is never flat and no random seed exists. Lineage is computed by sqlglot: the project is qualified in topological order so stars expand against the accumulated schema, then each model’s per-column edges are composed into a transitive closure. The no-catalog figure is the same code with the schema withheld from the starred model. All 43 models are then built three times in DuckDB — as-is, variant A, variant B — and every column of every model is compared on row count, type, distinct count, sum and a hash of its distinct values, with a relative tolerance on float sums because floating-point addition is order-dependent and would otherwise report phantom changes. The 43 tests are executed as SQL, not described.

Asserted rather than claimed: that the set of models whose output changed is exactly the set on the column-level path, and that GMV, commission and listed value are identical to the cent in all three builds. Deliberately not done: deriving any wall-clock time, and publishing the z-score of the generator’s own synthetic variance. Money depends on one assumption stated in the open — that a listing which sold inside its hold window would have sold anyway at the price the rule intended.

Reproducibility: the generator was extracted from the rendered artifact, run in a clean directory, and diffed against the published figures key by key — 18 top-level keys, 3,484 scalar values, 0 differences.