Case Study 2: The Serving Layer That Ate a Team

"Every request was reasonable. Every table was justified. Two years later we spent 60% of our capacity maintaining tables nobody could name the owner of."

Executive Summary

Between 2023 and 2025, Kestrel's data team accumulated forty-one gold-layer tables serving twenty-two dashboards. Each one was built for a good reason: a stakeholder needed a number, an existing table did not have it in the right shape, and building a purpose-built table was faster than modeling properly.

By early 2025 the team was spending an estimated 60% of its capacity on maintenance, a change to the definition of net_revenue_cents required touching fourteen objects, and three tables were discovered to disagree with each other about the same metric.

This case study is about the Transform/Serve boundary drawn too far upstream (§2.6) — the failure mode where you pre-aggregate per consumer instead of modeling once and letting consumers aggregate. It is the most common architectural failure in analytics platforms, it accumulates invisibly, and the individual decisions that produce it are each defensible.

It is also the counterweight to Case Study 1 in Chapter 1, which warned against under-serving. Both boundaries are wrong. This one shows the other side.

Skills applied: the Transform/Serve boundary (§2.6); transformation layers (§2.5); grain (§2.5); the software engineering undercurrent (§2.7).

Background

The starting state, mid-2023. The platform had a clean core: fct_order_item, fct_order, dim_customer, dim_product, dim_date. Five tables. A BI tool connected directly to them and analysts wrote their own aggregations.

The first crack. The merchandising team's category dashboard was slow — 40 seconds to load, because it scanned fct_order_item and joined three dimensions on every refresh. An engineer built gold.agg_category_daily: revenue, units, and margin by category and day, pre-aggregated. Load time dropped to under a second. Everyone was pleased.

That decision was correct. It is where the story starts going wrong that matters.

The pattern established. Over the next eighteen months the same move was made twenty-three more times:

Every request was reasonable. Every table was faster than the alternative. No single decision was wrong.

The Problem

By January 2025 the symptoms were unmistakable and the cause was not.

Symptom 1: maintenance ate the team. Four engineers, and roughly 60% of capacity going to maintaining existing tables — chasing failures, backfilling after upstream changes, and answering "why does this table say X when that one says Y."

Symptom 2: a definition change cost two weeks. Finance revised how shipping revenue was treated in net_revenue_cents. Fourteen objects referenced revenue in some form. Finding all fourteen took three days, because there was no lineage and the reference was sometimes a re-derivation rather than a join. Changing them took four. Reconciling the results took a week, because two of the fourteen had been computing it slightly differently all along and nobody had known.

Symptom 3: tables disagreed. During that reconciliation, three tables were found to report materially different figures for the same nominal metric:

All three had been in use for over a year. All three fed decisions. None had been wrong when it was built — each encoded the requesting team's definition at the time, and the definitions drifted apart afterward with nothing to hold them together.

⚠️ Failure Mode — Definition drift by pre-aggregation

When a metric is computed inside twenty different tables, it has twenty chances to drift. Drift does not happen at build time; every table is correct when it is written. It happens afterward, when the business definition changes and the change reaches fourteen of the twenty.

The symptom is not an error. It is two dashboards disagreeing, discovered in a meeting, by an executive, at the worst possible moment.

The structural fix is that a metric should be computed exactly once, in one place, and every consumer should reference that place rather than re-derive it. This is the argument for a semantic layer, for dbt metrics, or at minimum for a single documented view — Chapter 19 §19.7 and Chapter 30 §30.5.

The Analysis

The team ran an audit in February 2025. Four questions.

1. How many of the 41 tables are actually used? They instrumented the BI tool's query log for thirty days.

Sixteen tables — 39% — had not been queried once in a month. They were still being built nightly, still consuming compute, still breaking occasionally and being fixed.

2. What do they cost to build? Total nightly build time across all 41: 94 minutes of warehouse compute. The 16 unused tables accounted for 31 minutes.

💸 Cost Check — What the unused tables cost, and why that is the smaller number

Thirty-one minutes of nightly warehouse compute. On a Snowflake Medium warehouse at the frozen rate of $2.00/credit — a Medium is 4 credits/hour — that is:

$$\frac{31}{60} \times 4 \times \$2.00 = \$4.13 \text{ per night} = \$1{,}507 \text{ per year}$$

Fifteen hundred dollars. In a team whose fully loaded cost is comfortably over a million dollars a year, this is a rounding error, and presenting it as the headline finding would have been a mistake.

The real cost was the maintenance. Sixteen tables that break sometimes, that appear in the lineage graph, that a new engineer has to understand before changing anything, that show up in the catalog and make people wonder which one to use. Those are engineer-hours, and engineer-hours at a four-person team are the binding constraint on everything.

Estimated conservatively at two hours per table per quarter for incidental maintenance: $16 \times 2 \times 4 = 128$ hours a year, or about three working weeks. That is the number worth putting in a slide — and it is roughly 85× the compute cost.

Watch for this shape. The line item you can meter is rarely the expensive one.

3. How many distinct definitions of each core metric exist across the 41? Revenue: seven. Orders: four. Active customer: five. The exact situation from Chapter 1's Case Study 1, recreated inside the platform that had been built to fix it.

That is the finding worth sitting with. The platform solved the problem of analysts each having their own query by giving each analyst their own table, which is the same problem with better infrastructure and worse visibility. At least a query in a BI tool is visibly one person's; a gold table looks official.

4. What would it take to serve the same 22 dashboards from a modeled core? They prototyped: five fact tables, six dimensions, and a small set of genuinely expensive aggregates — three, not forty-one. The remaining dashboards would query the core directly.

The prototype held. The 40-second load time that started everything turned out to be a partitioning and clustering problem rather than an aggregation problem: fct_order_item was unpartitioned, and clustering it on (order_date, category_id) brought the same query to 2.4 seconds without any pre-aggregation at all.

The table that started the entire pattern should never have been built. The correct fix in 2023 was thirty minutes of storage-layout work, and nobody looked, because building a new table was the familiar move.

The Decision

A six-month consolidation, deliberately incremental. Four rules:

1. Delete the unused. All 16 never-queried tables were deprecated: renamed with a zz_deprecated_ prefix, left in place for thirty days, then dropped. Two turned out to be used by something the query log did not capture — a Python notebook and a scheduled export — and were restored. Fourteen were dropped.

The thirty-day rename is worth stealing. It converts "is anyone using this?" from an unanswerable question into a loud failure with a known cause and a one-command fix.

2. One definition, one place. Every metric defined once, in dbt, with every consumer referencing it. Chapter 19 §19.7.

3. A new aggregate table requires a measurement. Before building one, demonstrate that the query against the core model is genuinely too slow after partitioning and clustering have been addressed. Three of the next five requests were resolved by layout changes.

4. Every gold table has an owner and a stated consumer. In the catalog, enforced in CI. A table with no named consumer does not get built.

📐 Design Decision — Where to draw Transform/Serve, restated with both failures visible

Too far downstream (Chapter 1, Case Study 1): serve raw-ish tables, let every consumer aggregate. Result — the definition of revenue lives in nineteen BI queries maintained by five people, and three of them disagree.

Too far upstream (this case study): pre-aggregate per consumer. Result — forty-one tables, seven definitions of revenue, 60% of capacity on maintenance, and three of them disagree.

Both failures produce the same symptom. That is the trap: the natural fix for either one, if you have only experienced that one, is to move the boundary all the way to the other extreme.

The workable position: a well-modeled star schema at the boundary, a small number of aggregates justified by measurement, and exactly one place where each business metric is defined. What you give up: some queries will be slower than a bespoke pre-aggregate would make them, and you will occasionally have to defend that to someone watching a dashboard spin. That is the cost, it is real, and it is smaller than either alternative.

What Happened

Consolidation ran from March to September 2025. The result:

Same twenty-two dashboards, a third of the tables.

Two honest caveats, because the table above is the kind of before/after that invites over-generalization.

The consolidation itself consumed most of two engineers for six months. The 60%→25% shift recovered that within about a year, but the payback was not immediate and a team under acute delivery pressure could not have done it.

Three dashboards got slower. Load times went from under a second to between three and six seconds. Two teams accepted it. One escalated, and after measurement, that dashboard got one of the three retained aggregates. The escalation was the process working, not failing — it produced a measurement, and the measurement justified an exception.

Lessons

1. The Transform/Serve boundary fails in both directions, and both failures look identical from the outside: dashboards that disagree.

2. Every individual decision can be defensible while the aggregate is a disaster. No one built a bad table. The pattern was the problem, and patterns are invisible until someone counts.

3. Count your tables and their usage. 39% had not been queried in a month. Nobody knew, because nobody had asked.

4. The metered cost is rarely the expensive one. $1,507/year in compute against roughly three engineer-weeks a year in maintenance — an 85× ratio in the direction you cannot put on an invoice.

5. Check the storage layout before building an aggregate. The 40-second query that started everything was a partitioning problem. Thirty minutes of layout work in 2023 would have prevented forty tables.

6. Deprecate by renaming. Thirty days with a zz_deprecated_ prefix converts an unanswerable question into a loud, cheap failure.

7. A table with no named consumer should not exist. Ownership and a stated consumer, in the catalog, enforced in CI.

Questions for Discussion

1. The first pre-aggregated table was built for a genuine 40-second query. Given only the information available in 2023, what should the engineer have done differently — and what would have had to be true about the team's habits for them to do it?

2. Sixteen tables had not been queried in thirty days, and two of those were in fact used by systems the query log did not see. How would you design the usage audit to avoid that gap, and what would it cost?

3. The compute cost of the unused tables was $1,507/year and the maintenance cost roughly three engineer-weeks. The maintenance figure is an estimate and the compute figure is metered. How would you defend the estimate to a finance partner who prefers the number that comes from a bill?

4. Rule 3 requires a measurement before building an aggregate. Write that rule as a pull-request checklist item precise enough that it cannot be satisfied with a hand-wave.

5. Three dashboards got slower and one team escalated. Was accepting a 3–6 second load time the right call? What would change your answer — the audience, the frequency, the decision being made?

6. This case study and Chapter 1's Case Study 1 describe opposite failures with the same symptom. If you joined a company tomorrow and saw dashboards disagreeing, what three questions would tell you which failure you were looking at?

7. The consolidation took two engineers six months and paid back in about a year. Under what circumstances would you decline to do it, and what would you do instead?