Case Study 2: The Shadow Pipeline

"They had built a working pipeline in a spreadsheet, a Python script on somebody's laptop, and a Google Sheet with an hourly refresh. It had been running for seven months. It was more reliable than two of ours."

Executive Summary

Kestrel's marketing team built a parallel data pipeline. Not a spreadsheet — a real pipeline: a scheduled Python script pulling from the ad platforms' APIs, joining to an exported CSV of orders, and writing to a Google Sheet that fed their dashboards.

It ran for seven months before the data team knew it existed.

The discovery prompted the reaction it usually does — "they should have asked us" — and the investigation found something less comfortable:

their pipeline's uptime, 7 months            99.1%
our campaign_roi model's uptime, same period 96.4%
their time from request to a new metric      ~2 days
our time from request to a new gold model    9 days (median)

The shadow pipeline was faster and more reliable than the sanctioned one, and the reason was not that the marketing analyst was a better engineer. It was that they were the consumer.

This is the clearest evidence a central team ever gets that it is a bottleneck (§35.1), and Kestrel's response — neither shutting it down nor blessing it — is the transferable part.

Skills applied: the bottleneck signal (§35.1); data as a product (§35.5); self-serve as a response to demonstrated demand (§35.6); and why "they should have asked us" is the wrong first sentence.

Background

The marketing team's need was ordinary. They run campaigns across four ad platforms and want to know which ones produce revenue. That is gold.campaign_roi, which exists, is built by the data team, and reads gold.attribution and gold.daily_revenue.

Three things about it did not work for them.

It was daily, and campaign decisions are intraday. A campaign that is burning budget badly is worth knowing about at 11am, not tomorrow.

Adding a metric took nine days. Median, per §35.3's intake data. Campaign structures change weekly.

It did not include platform-reported spend, only Kestrel-side attribution — so it could tell you revenue by campaign and not cost, which is half of a return calculation.

They asked for all three. The requests were logged, prioritized reasonably against everything else, and were in the queue.

Then an analyst on the marketing team, who could write Python, built the thing they needed in a week.

The Problem

The discovery was accidental. A cost anomaly alert (Chapter 33 Case Study 2) fired on a warehouse role nobody recognized, doing hourly SELECTs against gold.fct_order_line.

The role belonged to a service account created eleven months earlier for a vendor evaluation (Chapter 30 Case Study 2's unattributable identities, arriving from the other direction) and reused, reasonably, by someone who needed credentials and found some.

What it was doing:

hourly:
  pull spend + impressions from 4 ad platform APIs
  SELECT order_id, customer_id, placed_at, net_revenue_cents,
         utm_campaign FROM gold.fct_order_line WHERE placed_at > ...
  join in pandas, on utm_campaign
  write to a Google Sheet
  -> 3 dashboards, 1 weekly email to the CMO

⚠️ Failure Mode — "they should have asked us" is factually wrong and strategically worse

They did ask. All three requests were in the queue, correctly prioritized, and had been for months. The sentence is available only to someone who has not checked, and Kestrel's platform lead checked before saying anything, which is the single best decision in this case study.

Even where it is true, it is the wrong first sentence, for three reasons:

  • It makes the conversation about compliance rather than about need. The team then defends the shadow pipeline instead of explaining what it does, and you lose the requirements.
  • It guarantees the next one is hidden. A shadow pipeline you know about is a manageable risk. A shadow pipeline built by people who have learned not to mention them is not, and the response to the first one determines which kind you get.
  • It ignores the evidence. Someone routed around you and built something that worked. That is data about your service, and it is the most expensive data you will ever be given for free.

The sentence Kestrel used instead, in the first message: "we found this and we're impressed it works — can you walk us through what it does, and what you needed that we weren't giving you?"

The marketing analyst's response included two requirements the original tickets had not captured, because a ticket is a specification and a walkthrough is a conversation (§35.1). One of them — that spend must be attributed to the campaign as of the day it was spent, not the day the order arrived — was a genuine modeling insight the data team had gotten wrong in gold.attribution.

The Analysis

The uncomfortable comparison, measured over the same seven months:

                              shadow pipeline    gold.campaign_roi
uptime                                  99.1%               96.4%
median latency to fresh data              1 h                26 h
time to add a metric                   ~2 days              9 days
metrics available                            14                   6
cost                                      ~$40/mo          ~$310/mo

Every column favors the shadow pipeline, and the honest analysis of why is the point of the case study.

🔎 Read the Plan — why the amateur pipeline beat the professional one

Not because the analyst is a better engineer. They are not, and the pipeline had real defects (below). Four structural advantages, none of which is about skill:

The builder was the consumer. No requirements gap, no ticket, no round trip. When the metric was wrong, the person who noticed was the person who could fix it, in the same afternoon. This is data mesh's entire thesis, demonstrated accidentally.

It served one consumer, so it could make expedient choices. No contract, no deprecation window, no other team's dashboard to break. gold.campaign_roi is slower to change because it is used by people who are not in the room — which is a cost of being a product, and is worth paying, and is not free.

Its correctness bar was different, and appropriately so. A campaign-pacing decision at 11am tolerates a 3% error; the board's revenue number does not. The shadow pipeline was optimized for a decision, and the gold model was optimized for a definition.

It had no on-call, no SLO, and no consequences for being wrong. Its 99.1% uptime was measured by the analyst noticing; the gold model's 96.4% was measured by Chapter 25's monitoring, which catches failures the analyst's method would not. The two numbers are not comparable and the team said so — but they also did not use that to dismiss the comparison, because the experienced reliability was genuinely better.

The generalizable finding: a shadow pipeline is usually better on the dimensions its builder cares about and worse on the dimensions nobody is measuring. The first list is short and visible; the second is long and invisible. Both facts are real, and a response that acknowledges only one of them will not be believed.

What was actually wrong with it, found in the walkthrough:

- joined on `utm_campaign` string; 11% of orders had a null or malformed
  value and were silently dropped  -> revenue understated by ~9%
- no dedup: an order edited after placement appeared twice
- the Google Sheet had a 5M-cell limit; it had been silently truncating
  the oldest 60 days for six weeks
- read `gold.fct_order_line` directly with a role that also had SELECT on
  `dim_customer`, including email  -> a privacy finding (Chapter 31)
- the script ran on a laptop

The 9% revenue understatement is the one that mattered, and nobody had noticed, because there was nothing to reconcile against — the shadow pipeline's numbers were never compared to the gold model's, by anyone, in seven months.

The Decision

Neither shut it down nor bless it. Three changes, over four months.

One: fix the gold model to serve the actual requirement. Hourly refresh for the campaign models, platform spend ingested, and the as-of-spend-date attribution the analyst had gotten right and the data team had gotten wrong. Six weeks.

Two: give the marketing team a sanctioned way to do what they were doing. A read-only role with column-level masking (Chapter 31 §31.6), a documented output port (§35.5), and a _scratch schema they can write to.

📐 Design Decision — sanction the pattern rather than the pipeline

The instinct is to replace the shadow pipeline with a supported one. Kestrel did that for the campaign use case, and it does not generalize, because the next need will arrive before the next gold model does.

What generalizes is sanctioning the pattern: a place where a consumer can build something temporary, safely.

text analytics_scratch schema - any analyst may create tables - read access to gold via a MASKED role (no raw PII) - 90-day retention, enforced - explicitly `internal` in the catalog (section 30.2): not searchable, not certified, and labelled "not for reporting" - a monthly report of what lives there, to the platform team

The last line is the one that makes it a control rather than a permission. The platform team reads the monthly list, and anything that has survived three months and has consumers is a candidate for promotion to a real model — which is how two of Kestrel's gold models were born.

This inverts the usual relationship. Instead of the shadow pipeline being a failure of governance, it becomes the intake mechanism: the scratch schema is where demand is demonstrated before the platform team invests in supply, which is §35.6's "build self-serve for capabilities whose users already exist and are already asking."

The honest cost: the scratch schema now holds 60-odd tables of varying quality, and someone will eventually query one and put it in a report. Kestrel's mitigations are the catalog tier, the naming convention, and the retention — and the team's assessment is that this risk is smaller than the risk of pipelines on laptops, which is a judgment rather than a proof.

Three: reconcile. Any scratch table that computes a metric which also exists in gold gets an automated comparison, and a difference above 1% is reported to both teams. This is what would have caught the 9%.

What Happened

Before After 4 months
Shadow pipelines known 0 1, then retired
Shadow pipelines existing ≥1 4, all in analytics_scratch
Campaign data latency 26 h 1 h
Metrics available 6 17
Revenue understatement in marketing's numbers ~9% 0, reconciled
Analyst PII access yes, unnoticed masked role
Scripts on laptops ≥1 0
Scratch tables promoted to gold — 2 in a year

Four shadow pipelines existed once it was safe to have them, which is the number that matters: the first one was not an anomaly, and three more surfaced within two months of the scratch schema existing. Two turned out to be genuinely valuable and were promoted; two were retired once the gold model covered them.

And the reconciliation check has fired eleven times in a year. Nine were the scratch table being wrong — usually a join or a filter — and twice it was the gold model, including one where a scratch table's simpler campaign attribution was correct and the gold model's was not.

🧱 Kestrel Platform — the shadow pipeline as a signal, not a violation

The lasting change is a reframing, and it is the one thing from this case study worth taking to any organization:

text OLD: a shadow pipeline is a governance failure. Find them. Stop them. NEW: a shadow pipeline is a measurement of your service. Find them. Make them safe. Read them as a backlog.

Kestrel now reports the count of scratch tables with more than one consumer as a platform metric, alongside the intake queue — because it is the same signal from the other side. A rising count means the platform is not meeting demand; a falling count means it is, or that people have stopped telling you.

The distinction between those two readings is the reason the scratch schema is sanctioned rather than tolerated. In a regime where shadow work is punished, the metric measures fear. In one where it is provided for, it measures need — and only one of those is useful.

Lessons

  1. The shadow pipeline was faster, cheaper, and experienced as more reliable than the sanctioned one. This is the clearest evidence a central team gets that it is a bottleneck.

  2. ⚠️ "They should have asked us" was factually wrong — all three requests were in the queue — and would have been the wrong first sentence even if true: it makes the conversation about compliance, it guarantees the next one is hidden, and it discards the evidence.

  3. The walkthrough produced two requirements the tickets had not captured, one of which was a modeling insight the data team had gotten wrong. A ticket is a specification; a walkthrough is a conversation.

  4. 🔎 The amateur pipeline won for four structural reasons, none about skill: the builder was the consumer · it served one consumer so it could be expedient · its correctness bar was appropriate to its decision · it had no on-call and no consequences.

  5. A shadow pipeline is better on the dimensions its builder cares about and worse on the ones nobody is measuring. Both facts are real, and a response that acknowledges only one will not be believed.

  6. It understated revenue by ~9% for seven months, and nobody noticed because nothing reconciled it against anything.

  7. 📐 Sanction the pattern, not the pipeline. A scratch schema with masked reads, enforced retention, an internal catalog tier, and a monthly report to the platform team — the last line is what makes it a control rather than a permission.

  8. This inverts the relationship: the scratch schema becomes the intake mechanism, where demand is demonstrated before the platform invests in supply.

  9. Reconcile anything that duplicates a gold metric. Eleven fires in a year: nine scratch errors, and twice the gold model was the wrong one.

  10. Three more shadow pipelines surfaced within two months of it being safe to have them. The first was not an anomaly.

  11. 🧱 Count scratch tables with more than one consumer as a platform metric. Rising means unmet demand; falling means met demand or that people stopped telling you — and only a sanctioned regime distinguishes those.

Questions for Discussion

  1. The platform lead checked the queue before responding. What would you have said if all three requests had genuinely never been made?

  2. The two uptime numbers were measured differently and are not comparable. How would you compare them fairly, and would the conclusion survive?

  3. The scratch schema holds 60 tables and someone will eventually put one in a report. Design the control that prevents it, and say what it costs.

  4. Two scratch tables were promoted to gold in a year. Is that a good rate? What would too many, or too few, indicate?

  5. The reconciliation found the gold model wrong twice. Should a scratch table's disagreement with gold be treated as a defect in the scratch table by default?

  6. The chapter argues shadow pipelines are a measurement of your service. Construct the case where they are genuinely just a governance failure.

  7. Marketing's need was intraday campaign pacing. Does that requirement, honestly examined, justify an hourly pipeline — or was Chapter 29 §29.11's four-question review the right response?