Case Study 1: Four Hypotheses in Ninety Minutes

"The dashboard is blank" is not a problem statement. It is a symptom with at least four causes, and the cost of guessing is a day.

Executive Summary

At 09:12 on a Thursday, Kestrel's head of marketing messaged the data team: the channel attribution dashboard was showing nothing for Wednesday. Every other dashboard was fine. The DAG was green.

This case study is a worked diagnosis. It follows an engineer using the lifecycle as a decision tree — four hypotheses, one per stage, each with a specific query that eliminates it — and finds the cause in ninety minutes. It then compares that against what the same investigation looked like the previous quarter, when a different engineer solved a similar problem in a day and a half by starting from the tool they knew best.

The engineering content is deliberately modest. The transferable thing is the search order, and the discipline of eliminating a stage rather than pursuing a hunch.

Skills applied: the lifecycle as a diagnostic (§2.1, §2.9); the six source-system questions (§2.2); watermark failure modes (§2.3); conforming and identity stitching (§2.5); the Transform/Serve boundary (§2.6).

Background

The dashboard. marketing_attribution shows, per acquisition channel per day, sessions, orders, revenue, and cost per acquisition. It reads one table, gold.fct_session_attribution, which is built nightly.

Its dependencies, five deep:

kestrel.clickstream.v1 (Kafka)
   └─▶ bronze.events                    (streaming consumer, 30s commits)
         └─▶ silver.events              (dedupe on event_id, type cast)
               └─▶ silver.sessions      (sessionize: 30-min inactivity gap)
                     └─▶ gold.fct_session_attribution   (join to orders, channel logic)

What was known at 09:12:

• Wednesday's rows: zero. Tuesday's: normal.
• The Airflow DAG kestrel_daily succeeded at 04:38, inside its window.
• The revenue dashboard, which reads gold.fct_order_item, was correct for Wednesday.
• Nobody had deployed anything since Monday.

That last pair is the interesting one. Order data was fine and session data was empty, which already rules out a large class of platform-wide causes and points at the clickstream branch. An engineer who noticed that at 09:12 has eliminated half the tree before running a query.

The Problem

Four hypotheses, one per stage, and the discipline is to check them in the order that eliminates the most possibility for the least effort — not in the order of what feels most likely.

The whole tree costs twelve minutes. That is the argument for doing it in order rather than guessing: the cost of not guessing is twelve minutes.

The Analysis

Hypothesis 1: Generate — eliminated in one minute

kafka-consumer-groups --bootstrap-server localhost:9092 \
  --describe --group bronze-events-consumer

GROUP                  TOPIC                    PARTITION  CURRENT-OFFSET  LOG-END-OFFSET  LAG
bronze-events-consumer kestrel.clickstream.v1   0          412883901       412883901       0
bronze-events-consumer kestrel.clickstream.v1   1          411920447       411920447       0
...
bronze-events-consumer kestrel.clickstream.v1   11         409113772       409113772       0

Offsets advancing across all twelve partitions, lag zero. Events were produced and consumed. Generate and — as it turns out — the Kafka hop of Ingest are both fine.

Hypothesis 2: Ingest — eliminated in one minute

SELECT event_date, COUNT(*) AS events
  FROM bronze.events
 WHERE event_date >= CURRENT_DATE - 4
 GROUP BY 1 ORDER BY 1;

 event_date  |  events
-------------+-----------
 2025-06-09  | 13,981,204
 2025-06-10  | 14,113,660
 2025-06-11  | 14,042,918     <- Wednesday. Normal.
 2025-06-12  |  5,240,881     <- today, partial

Fourteen million events landed on Wednesday, right in line with the 14,000,000/day baseline. Ingest is fine.

Two hypotheses eliminated in two minutes, and the problem is now known to be in Transform or Serve. This is the payoff of the search order: without it, an engineer who suspected a Kafka issue could have spent an hour on consumer group configuration before establishing that the data was already in the warehouse.

Hypothesis 3: Transform — where it was

Counting at every step down the chain:

SELECT 'bronze.events'    AS layer, COUNT(*) FROM bronze.events    WHERE event_date = '2025-06-11'
UNION ALL SELECT 'silver.events',   COUNT(*) FROM silver.events    WHERE event_date = '2025-06-11'
UNION ALL SELECT 'silver.sessions', COUNT(*) FROM silver.sessions  WHERE session_date = '2025-06-11'
UNION ALL SELECT 'gold.fct_session_attribution', COUNT(*)
            FROM gold.fct_session_attribution WHERE session_date = '2025-06-11';

 layer                        |   count
------------------------------+------------
 bronze.events                | 14,042,918
 silver.events                | 14,038,201
 silver.sessions              |  1,904,337
 gold.fct_session_attribution |          0     <- here

Sessions built normally — 1.9 million of them. The attribution model produced nothing.

🔎 Read the Plan — Counting at every layer is the highest-value five minutes in a data investigation

The query above is the single most useful diagnostic in this book, and it takes five minutes to write for any pipeline.

It converts "something is wrong somewhere in a five-stage chain" into "the failure is at exactly one edge," which is the difference between a search and a fix. Every layer boundary is a place where rows can silently disappear — a filter, a join, a cast that nulls, a partition predicate that does not match.

Write this query for your critical pipelines before you need it, put it in the runbook, and the next incident starts five minutes in rather than five minutes from starting. Chapter 26 §26.4 makes it a standing runbook artifact.

The attribution model, reduced to the relevant part:

SELECT s.session_id,
       s.session_date,
       c.channel_name,
       ...
  FROM silver.sessions s
  JOIN silver.channel_map c
    ON c.utm_source = s.utm_source
   AND c.utm_medium = s.utm_medium
   AND s.session_date BETWEEN c.valid_from AND c.valid_to

An inner join to a channel mapping table with validity windows. If no mapping row matches, the session is dropped.

SELECT MAX(valid_to) FROM silver.channel_map;
-- 2025-06-10

Every row in channel_map had valid_to = '2025-06-10'. The table is maintained by the marketing operations team through a quarterly upload, and the current version's validity window had expired at the end of Tuesday. Nobody had uploaded the next one.

Elapsed: forty minutes. The remaining fifty went to the harder question, which was not "what broke."

The Decision

The immediate fix was trivial: marketing operations uploaded the new mapping, the model was re-run, Wednesday backfilled in four minutes.

The interesting decision was what to change so this class of failure announces itself.

The team considered and rejected two options before choosing a third.

Rejected: change the inner join to a left join with an 'unmapped' default. This is the instinctive fix and it is worse than the disease. Sessions would flow through with channel 'unmapped', the dashboard would render, the numbers would be complete-looking and wrong, and nobody would notice for a month. It converts a loud failure into a silent one, which is the exact trade this book argues against everywhere. Blank is bad; plausibly-wrong is much worse.

Rejected: alert on zero rows in gold.fct_session_attribution. Correct, and too narrow. It catches this table and no other, and there are forty gold tables. Writing a bespoke alert per incident is how you end up with a hundred alerts, sixty of them stale, and a team that ignores all of them.

Adopted: three general controls.

1. Volume monitoring on every gold table, comparing today's row count against a trailing fourteen-day window and alerting outside a band. Zero rows is the extreme case; a 40% drop is the more common and more dangerous one. Chapter 25 §25.4.

2. Expiry monitoring on every reference table with a validity window. A daily check: does MAX(valid_to) extend at least seven days into the future? This is a generate-stage control catching a generate-stage problem — the source of the failure was a human process that stopped, not a system that broke.

3. A named owner and refresh cadence for every manually maintained table, recorded in the catalog. channel_map had neither. Chapter 30 §30.3.

📐 Design Decision — Fail loudly or fail plausibly

This fork appears constantly and the instinct is almost always wrong.

Fail loudly (inner join drops the rows): the dashboard is blank, someone complains within hours, the problem is fixed the same day. Embarrassing, visible, cheap.

Fail plausibly (left join with a default): the dashboard renders. Attribution silently reallocates to 'unmapped'. Decisions get made. Discovered in a month, if at all — and every analysis produced in that month is now suspect.

Choose loud. The cost of loud is measured in hours of embarrassment; the cost of plausible is measured in decisions.

What you give up: availability. A loud failure means the dashboard is genuinely unusable while you fix it, and there are contexts — a customer-facing status page, a real-time system — where degraded-but-available beats correct-but-down. Name which context you are in before you choose. In analytics, you are almost never in the second one.

What Happened

The three controls went in over two weeks. Volume monitoring on the forty gold tables fired eleven times in the first month; nine were true positives — three expired reference tables nobody knew about, four upstream schema changes, and two genuine data drops. Two were false positives from holiday traffic patterns, which led to the band being widened for known low-volume days.

A 75% true-positive rate on a new alert is unusually good, and the reason is that the check was general rather than targeted. The controls people build after an incident are usually too specific to the incident. Building the general version costs slightly more and catches the eight other things you did not know about.

The comparison the team drew afterwards was with a similar investigation the previous quarter, when fct_order_item had gone stale. That engineer had started from the orchestrator — reading Airflow logs, checking task durations, examining the scheduler — because Airflow was the tool they knew best. It was a day and a half before anyone counted rows at each layer, and the cause turned out to be an expired credential on the extract, visible in ten seconds from the ingest-stage count.

Same shape, ten times the cost. The difference was entirely search order.

Lessons

1. Locate the stage before choosing a tool. Four hypotheses, four queries, twelve minutes to eliminate the entire tree. Guessing costs a day.

2. Count rows at every layer boundary. The single highest-value diagnostic. Write it before you need it and keep it in the runbook.

3. Notice what is not broken. Order data was correct and session data was empty. That observation eliminated half the tree before the first query ran.

4. Prefer loud failure to plausible failure. The instinctive "fix" — a left join with a default — would have converted a one-day incident into a one-month one.

5. Build the general control, not the incident-specific one. "Alert on zero rows in this table" catches one thing. Volume monitoring across all gold tables caught nine real problems in a month.

6. Manually maintained tables need an owner and an expiry check. A human process that stops looks exactly like a system that works.

7. A data pipeline's dependencies include human processes. channel_map was uploaded quarterly by a person. That is a real dependency and it was not in any diagram.

Questions for Discussion

1. The engineer eliminated Generate and Ingest in two minutes, then spent forty on Transform. Was that the right allocation? Construct the case for checking Serve first, and say what information would make that the better opening move.

2. The team rejected the left-join fix as converting a loud failure into a silent one. Name a realistic Kestrel scenario where the opposite call — degrade gracefully rather than fail — is correct. What distinguishes the two cases?

3. Volume monitoring produced eleven alerts in a month, nine true. At what true-positive rate would you start considering the alert harmful rather than useful? Does the answer depend on who receives it and at what hour?

4. channel_map was maintained by a quarterly human upload with no owner recorded. How many such dependencies do you think a platform Kestrel's size has? How would you find them without waiting for each one to fail?

5. The previous quarter's investigation took a day and a half because the engineer started from the tool they knew best. Is that a training problem, a documentation problem, or a process problem? What single artifact would most reduce the gap?

6. The expiry check asks whether MAX(valid_to) extends seven days into the future. Why seven? Argue for a different number and say what it optimizes for.

7. Suppose the attribution model had used a left join from the start, and the incident was discovered a month later during a quarterly review. Write the first three paragraphs of that incident report. How does it differ in tone and content from the one actually written?