Case Study 1: The First Data Hire

"We didn't need a data engineer. We needed someone to tell us which number was right." — a Kestrel executive, in the retrospective

Executive Summary

In early 2023, Kestrel Supply Co. had 1.1 million annual orders, three analysts, and a growing suspicion that its numbers did not agree with each other. It hired a data engineer to fix that. What the company thought it was buying was faster reports. What it actually needed — and what took eleven months to deliver — was a single defensible definition of revenue and a system that could produce it the same way twice.

This case study walks through that first year: what the analysts were doing before, what broke as volume grew, what the new engineer built first (and what they built first that they should not have), and how the company measured whether the hire was worth it.

The value here is not the architecture, which is deliberately unremarkable. It is the sequence. Most first data hires build the wrong thing first, and the reason is structural rather than personal: the visible pain is slow reports, and the actual problem is undefined semantics. Fixing the visible pain first makes the actual problem worse, because faster wrong numbers reach more people.

Skills applied: the six artifacts (§1.2); the accountability boundaries between roles (§1.4); Kestrel's scale and constraints (§1.5); and the argument that definition precedes code (§1.1, 🏭 From the Pipeline).

Background

Kestrel in early 2023. Roughly 1.1 million orders a year — about 46% of its FY2025 volume. PostgreSQL running the storefront. A BI tool connected directly to a read replica. Three analysts, each of whom had built up a personal library of SQL queries over two years.

That last detail is the whole case study in miniature.

Each analyst had a query that computed revenue. All three queries were reasonable. None of them matched, and each had been independently reviewed and approved by a different stakeholder at a different time:

Three different numbers. Three correct numbers. In a meeting, three people confidently contradicting one another with the same underlying data.

The trigger. In Q4 2022, the board asked why the revenue in the monthly operating review was 7.2% higher than the revenue in the year-end financial statements. Nobody could answer in the meeting. It took nine days to answer afterward, and the answer — "different definitions, both correct" — did not land well, because from the board's perspective it meant the company did not know its own revenue.

The company approved a data engineering hire in January 2023.

The Problem

The written job requisition said this:

Build automated data pipelines to reduce reporting turnaround from days to hours and eliminate manual data pulls.

That requisition describes a real pain. Analysts were spending an estimated 40% of their time on extraction and reshaping rather than analysis. Month-end close took nine business days, four of which were data assembly. Every ad-hoc executive question triggered a fresh hand-written query.

But the requisition describes a symptom, and building directly against it would have made the underlying condition worse. Consider what "automate the existing reports" actually means here: you take three mutually inconsistent definitions of revenue, encode each one into a scheduled pipeline, and deliver all three faster and to more people. You have now industrialized the disagreement. The next board question arrives sooner and the answer is more embarrassing, because now the contradiction is automated and has a systems diagram.

This is the central trap of a first data hire, and it is worth stating as a general rule:

When reporting is slow and inconsistent, fix consistency first. Speed applied to inconsistency is a force multiplier for the wrong thing.

The counter-argument is real and deserves a hearing. Consistency work is invisible for months. A new hire who spends their first quarter in definitional meetings and produces no pipeline looks, from the outside, like a bad hire — and the political capital to do the second quarter of work depends on being perceived as productive in the first. The engineer who insists on doing it in the right order may not survive long enough to finish.

The Analysis

The engineer's first two weeks were spent on an audit rather than a build. Three questions:

1. How many definitions of each core metric exist? They collected every query producing a number that anyone called revenue, orders, customers, or margin. The count:

Twenty-five distinct definitions of five metrics, one of them written down anywhere, and that one wrong.

2. What is the actual cost of the current state? Rather than argue qualitatively, they measured three things over four weeks:

• Analyst time on data assembly: tracked by self-report, 38% of hours. At three analysts, that is 1.14 full-time equivalents spent moving data rather than interpreting it.
• Rework: 11 of 34 delivered analyses in the period required revision after someone challenged a number. A 32% rework rate.
• Decision latency: median 6 days from executive question to answer. Two questions in the period were abandoned because the answer arrived after the decision.

Those three numbers did more to justify the following year of work than any architecture diagram could have.

3. Where does the data actually come from? A dependency map of every report back to source tables. It found something nobody expected: two dashboards were reading from a table that a departed contractor had populated by hand from a spreadsheet, last updated fourteen months earlier. Both dashboards were still in weekly use. Neither had a freshness indicator.

⚠️ Failure Mode — The stale table with no clock

A table that stops being updated does not look any different from a table that is being updated. It has rows. It joins. It renders. The only distinguishing feature is that its maximum timestamp stops moving, and nothing in a normal BI workflow shows you that.

This is the single cheapest data quality check in existence and one of the most commonly missing: for every table anyone depends on, assert that its maximum timestamp is within an expected window, and alert when it is not. Chapter 25 §25.3 makes it a first-class metric. If you take one operational practice from this chapter, take this one.

What they built, in order

The sequencing is the substance of this case study.

Months 1–2: the definition. Not code. A document — metrics.md — that defined each of the five contested metrics precisely, with named variants where variants were genuinely needed. The revenue entry ended up with three named measures rather than one, because the merchandising, finance, and growth definitions were all legitimate for their purposes:

• gross_revenue_cents — quantity × unit price, all orders regardless of status. Demand signal.
• net_revenue_cents — gross, less discounts, cancellations, and refunds, on shipped or delivered orders. The default. Everything says "revenue" means this unless it says otherwise.
• settled_revenue_cents — what the payment processor actually settled, net of chargebacks and fees. Reconciles to the bank.

The key move was not eliminating the variants. It was naming them, defining exactly one default, and making it socially unacceptable to say "revenue" without knowing which one you meant.

Getting sign-off took six weeks and four meetings, two of which were unpleasant. The finance controller and the head of growth disagreed about whether shipping revenue belonged in net. It was resolved by writing both down, defaulting to excluding it, and adding a separate shipping_revenue_cents column so the growth team could add it back explicitly. Nobody was fully satisfied, which is roughly the correct outcome for a definitional negotiation.

Month 3: one table. fct_order_item — one row per order line, with a documented grain and the three revenue measures as columns. Built with a hand-written Python script on a cron job, loading to a schema on the same PostgreSQL instance. No warehouse, no orchestrator, no cloud.

This is a deliberately unimpressive piece of engineering and it was the right call. It could be built in three weeks by one person. It demonstrated the definitions in something executable. And the migration to a real warehouse later was straightforward precisely because the model was already right — the hard part was already done.

Months 4–6: coverage, and the first real mistake. Four more fact tables and three dimensions. The mistake happened here: the engineer built a real-time streaming pipeline for the clickstream, because the growth team asked for real-time conversion tracking and it was a more interesting problem than the fifth fact table.

It took ten weeks. It was used for two dashboards. The freshness requirement, when someone finally asked the growth team directly, was hourly — they were making weekly decisions and looking at the dashboard on Monday mornings. An hourly batch job would have taken four days to build.

📐 Design Decision — "Real-time" almost never means real-time

When a stakeholder asks for real-time data, the useful follow-up is not "how fresh?" but "what decision will you make differently if it is one hour old instead of one minute old?"

The answers cluster into three groups, and only one of them is real:

• "None, but it feels better" — the most common answer by a wide margin. Build a batch job.
• "I would catch a problem sooner" — legitimate, and usually satisfied by monitoring and alerting rather than by a streaming pipeline. Chapter 25.
• "An automated system acts on it" — fraud scoring, inventory allocation, personalization, dynamic pricing. This is genuinely real-time and it is the case Chapter 29 is written for.

What you give up by defaulting to batch: you will occasionally have to rebuild something as streaming later, at roughly the cost of having built it as streaming initially. That is a real cost and it is smaller than paying it every time.

At Kestrel this cost ten weeks of the only data engineer's time in the year the company most needed foundational work. It is the single most instructive thing in this case study.

Months 7–9: tests and the boring stuff. Data quality checks. Freshness monitoring. The stale-table check from the ⚠️ callout above, applied to everything. Documentation of the model. Migration to a real warehouse. Airflow.

Months 10–11: the reconciliation. The last piece, and the one that changed how the company regarded the data team: a monthly automated comparison of net_revenue_cents from the warehouse against the payment processor's settlement report, with the variance broken down by known cause — timing differences, refunds in flight, currency conversion, processor fees.

The first run showed a 0.31% variance, fully explained by three timing rules. It has run monthly since.

The Decision

At the eleven-month review, the executive team decided to hire two more data engineers and formally establish a data team.

The case that persuaded them was not the architecture. It was four numbers, compared against the month-one baseline:

Note what is not in the table: rows processed, pipelines built, uptime, tools adopted. The engineer's own instinct was to present the architecture. Their manager insisted on presenting the four measures from the month-two audit instead.

That audit — two weeks that produced no code — is what made the whole year defensible. Without a baseline, the argument for the second and third hire would have been "things feel better," which does not survive a budget review.

What Happened

By FY2025, Kestrel had four data engineers, 2.4 million annual orders, and the platform you build in this book.

Three things from the first year proved durable:

The metric definitions survived every migration. Postgres to warehouse, hand-rolled Python to dbt, cron to Airflow. net_revenue_cents means today exactly what it meant in month two. It is the most valuable artifact produced in that year and it is a Markdown file.

The reconciliation caught real problems. Including, in March 2025, an 11.4% variance that turned out to be the duplicate-rows incident from §1.6 — though not for two weeks, because it was initially attributed to a definitional change. The reconciliation was pointing directly at the problem the whole time and nobody believed it. That is also a lesson: a check nobody trusts is a check nobody reads.

The streaming pipeline was decommissioned in 2024, replaced by a fifteen-minute batch job that nobody has complained about.

Lessons

1. Fix consistency before speed. Automating inconsistent definitions industrializes the disagreement.

2. Measure the baseline before you build. Two weeks of audit made a year of work defensible and funded three more roles. It is the highest-return two weeks in the story.

3. Name the variants; do not eliminate them. Three revenue definitions existed because three teams had genuinely different questions. The fix is naming, defaulting, and enforcing — not forcing a single answer that satisfies nobody.

4. The first system should be embarrassingly simple. A Python script on cron writing to a Postgres schema. The model was right, so the migration was easy. The reverse — sophisticated infrastructure over an unresolved model — is a much worse position and much more common.

5. "Real-time" is a request to interrogate, not fulfill. Ask what decision changes. Ten weeks went into a requirement that was actually hourly.

6. Every table anyone depends on needs a freshness check. Two live dashboards were reading fourteen-month-old hand-loaded data, and nothing in the system could have told anyone.

7. A check nobody trusts is a check nobody reads. The reconciliation flagged the duplicate-rows incident immediately and was disbelieved for two weeks.

Questions for Discussion

1. The engineer spent months 1–2 producing a document and no code. Defend that choice to a chief executive who is three months into a salary with nothing shipped. Now argue the opposite side — under what circumstances would building something visible first be the correct call?

2. Twenty-five definitions of five metrics existed. Is that a failure of the analysts, of management, of tooling, or is it the expected steady state of any company without a data team?

3. The streaming pipeline took ten weeks and served an hourly requirement. Whose failure is this: the engineer who built it, the growth team that asked for "real-time," or the manager who approved it? What single process change would have prevented it?

4. The four measures presented at the eleven-month review were self-reported analyst time, rework rate, decision latency, and close duration. Which is most vulnerable to being gamed, and how would you harden it?

5. The reconciliation showed a 0.31% variance that was "fully explained." How would you decide what variance is acceptable? What number would make you stop the month-end close?

6. Suppose Kestrel had instead hired an analytics engineer, or a data scientist, or a second BI developer. For each, describe how the first year would have gone differently and whether the board's original question would have been answered.

7. The metric definitions survived three complete technology migrations. What does that suggest about how you should allocate effort between modeling and tooling early in a platform's life — and what is the strongest argument against over-generalizing from this one case?