Case Study 2: The Warehouse That Was a Copy

"It wasn't a warehouse. It was a second copy of the application database, with a slower query engine and none of the constraints."

Executive Summary

A subscription software company spent two years building what it called a data warehouse. It contained forty-one tables, all named after the source tables they were loaded from, all with the same columns, all refreshed nightly.

It worked. Analysts queried it, dashboards ran on it, and it kept analytical load off the application database — which was the original goal and which it achieved.

Then the application team split a table, and eleven dashboards, four dbt models, and one machine learning feature set broke in a single deploy. Fixing it took nine days. Six weeks later the same thing happened again with a different table.

This case study diagnoses mistake 8 from §6.9 — a model that mirrors the source — which the chapter calls the most common and hardest to argue against, because the copy works. It follows what it cost, how the team made the case for modeling properly, and the incremental migration that followed, including the parts that went badly.

Skills applied: semantic stability (§6.1); the four-step process (§6.3); grain (§6.3); conformed dimensions (§6.6); the eight mistakes (§6.9).

Background

The company. A subscription software business, about 120 employees, PostgreSQL application database, roughly 90 GB of analytical data.

How the "warehouse" was built. In 2023, analytical queries were slowing the application. The fix — correct, as far as it went — was to replicate the application tables into a separate warehouse nightly and point the BI tool at that instead.

The replication was a straightforward table-by-table copy. public.subscriptions became warehouse.subscriptions, with identical columns. Forty-one tables.

Why nobody modeled. Three reasons, all reasonable at the time:

1. It solved the presenting problem completely and in three weeks.
2. Analysts already knew the source schema and could keep using their existing queries unchanged — a real and immediate benefit, and the one that made the approach popular.
3. Nobody on the team had modeled a warehouse before, and the copy was obviously going to work.

What accumulated over two years. Analysts wrote queries against the copy. Those queries encoded business rules — which subscription statuses count as active, how to handle trials, what happens to a subscription that upgrades mid-cycle. Those rules ended up inside 200-plus BI queries and 30-odd dbt models rather than inside the warehouse, because the warehouse had no layer to put them in.

The Problem

2025-04-14. The application team deployed a refactor splitting subscriptions into subscriptions (identity and plan) and subscription_periods (billing periods, one row per period). It was a good change, driven by a real modeling problem in the application.

They announced it in the engineering channel a week ahead. Nobody on the data team was in that channel.

What broke:

Nine days to fix, most of it spent finding the affected objects, because there was no list of who read what. They found them by grepping the BI tool's query log for the column name and waiting for complaints.

The machine learning feature set is the expensive one. It did not break; it produced nulls, which the training pipeline handled by imputation, which degraded the model quietly. Three weeks and a retraining cycle before anyone connected the two events.

⚠️ Failure Mode — The blast radius you cannot measure

The nine days were not spent fixing things. They were spent finding things.

When consumers read source-shaped tables directly, every consumer is coupled to the source schema, and the coupling is invisible — it exists only as column names inside query text scattered across a BI tool, a dbt project, a notebook, and a feature pipeline.

Two consequences that are easy to underestimate:

You cannot estimate the cost of an upstream change. When product engineering asks "will this break anything?", the honest answer is "we don't know," which is not an answer that buys you a delay or a migration window.

The silent breakages are the expensive ones. Eleven dashboards failed visibly and were fixed in two days. One feature set produced nulls and cost three weeks plus a model retraining. Loud failures are cheap; the ratio here was roughly 1:10 in cost per incident.

A modeling layer converts this from an unbounded search into a bounded one: one file changes, and the interface downstream consumers depend on is unchanged. That is the property §6.1's callout describes, and this incident is the same event without it.

The Analysis

The nine days bought the team the credibility to propose something larger. They made the case with three artifacts.

1. A count of the business rules living in query text

They parsed the BI tool's saved queries and the dbt project, looking for repeated predicates:

Nine different SQL expressions for "churned this month." Some differed only in whitespace. At least four were semantically different — they disagreed about whether a downgrade counts as churn, whether the churn date is the cancellation date or the period end, and how to treat a customer who cancelled and resubscribed within the month.

Every one of those nine was in a dashboard someone used.

2. A worked example of one metric, computed three ways

They took a single month and computed churn using the three most common of the nine expressions:

A 0.7 percentage point spread on a metric the board tracked, and all three were in active use. This single table did more to authorize the work than the nine-day incident had.

3. A blast-radius estimate for the next refactor

Product engineering had two more refactors planned. The data team could not say what would break. They said so, in writing, and attached the nine-day incident. Naming an unquantifiable risk explicitly is more persuasive than estimating it badly — the inability to answer was the finding.

The Decision

An incremental migration, deliberately not a rewrite. Four principles:

1. The copy stays. Renamed to a raw schema, kept as the bronze layer, no consumer access. Nothing was deleted, which meant the migration could not break anything by removing something.

2. Model one business process at a time. Subscriptions first, because it carried the contested metrics. Then usage, then billing, then support.

3. Migrate consumers per-object, not per-schema. Each dashboard moved when someone had time to move it and verify the numbers matched. Numbers were expected to change — that was the point — and each change was documented and signed off by the metric's owner.

4. New objects may not read raw. Enforced by permissions, not by convention. This is the rule that made the migration finite rather than perpetual.

That fourth rule deserves emphasis. Most modeling migrations fail not because the modeling is hard but because the old path stays open, so new work keeps being built on the source-shaped tables and the migration never converges. Closing the old path by permission rather than by policy is what distinguishes a migration that ends from one that becomes a permanent two-system state.

📐 Design Decision — Strangler fig, not rewrite

The alternative was a clean rebuild: design the full dimensional model, build it, migrate everyone at once, delete the copy.

The case for a rewrite: faster to a coherent end state, no period of two competing schemas, no temptation to leave things half-migrated.

The case for incremental, which won: a rewrite requires the full model to be right before anything ships, and the team had never modeled a warehouse. Their first model would be wrong in ways they could only discover by using it. Incremental let them be wrong about subscriptions cheaply and apply the lesson to usage.

What incremental cost, and it was not small: eleven months of two schemas, during which every engineer had to know which one to use for what. Two dashboards were built against raw during the migration by contractors who did not know the rule — before the permission change closed it. And the team's own estimate is that a rewrite would have taken about seven months against the eleven the migration took.

They would choose incremental again, on the grounds that a seven-month rewrite that produced a wrong model would have cost more than the four extra months. That reasoning depends entirely on the team being new to modeling. An experienced team should probably rewrite.

What Happened

Eleven months. Four business processes modeled. Six fact tables, nine dimensions.

The next refactor is the number that mattered internally. Product engineering split the invoices table in early 2026. Two objects broke, both silver-layer models, both fixed the same afternoon. No dashboard, no dbt model downstream of silver, and no feature set was affected.

Three findings from the retrospective:

The metric consolidation was harder than the modeling. Choosing one of the nine churn definitions required a decision nobody wanted to own. It was eventually made by the chief financial officer, in a meeting, after the data team presented all nine with their results — and the team's view is that presenting nine was what forced it. Presenting three would have produced a debate; presenting nine produced agreement that the situation was untenable.

Two dashboards were built against raw during the migration. By contractors, who had not been told. This is what motivated the permission change, and it happened in month four — early enough to matter.

The ML feature set was rebuilt on the modeled layer and got better, not because the model was better but because the modeling process surfaced that two of its features had been computed from a subscription status that included trials, which nobody had intended. The modeling exercise found a bug that had been in production for a year, in a place nobody was looking.

Lessons

1. A copy of the source is not a warehouse. It works, which is why the mistake is common and hard to argue against, and it fails on the day the source is refactored.

2. Without a modeling layer, every consumer is coupled to the source schema, invisibly. The nine days were spent finding things, not fixing them.

3. Loud failures are cheap; silent ones are not. Eleven broken dashboards cost two days; one feature set producing nulls cost three weeks and a retraining. Roughly 1:10 per incident.

4. Count the spellings of your metrics. Nine expressions for "churned this month," at least four semantically different, all in active dashboards.

5. Show the metric computed three ways. A 0.7-point spread on a board metric authorized two years of work that an incident had not.

6. Naming an unquantifiable risk beats estimating it badly. "We cannot tell you what will break" was the finding.

7. Close the old path by permission, not by policy. Migrations that leave the source-shaped tables readable never converge.

8. Incremental over rewrite when the team is learning; rewrite when it is not. Eleven months against an estimated seven, and they would choose it again — because a fast wrong model costs more than four extra months.

9. Modeling finds bugs in places nobody is looking. A year-old feature bug surfaced because the process forces you to state what each column means.

Questions for Discussion

1. The original decision — copy the tables, three weeks, solves the problem — was reasonable given what the team knew. What would have had to be true for them to model properly in 2023, and was any of it achievable?

2. The data team was not in the engineering channel where the refactor was announced. Is communication the real fix here? Argue that it is not, and say what is.

3. Nine spellings of "churn." Design the check that would find this in a codebase you inherit, and estimate how many false positives it produces.

4. The chief financial officer chose the churn definition after seeing all nine. Was presenting nine manipulative, or was it the only honest presentation? What would you have done?

5. Two dashboards were built against raw by contractors in month four. Who failed — the contractors, their briefer, or the permission model? What is the general principle?

6. The team estimates a rewrite would have taken seven months against the migration's eleven, and would choose incremental again. Under what conditions does that reasoning flip?

7. The ML feature set had been computing from a status that included trials, for a year, and nobody noticed. What would have caught it independently of the modeling project? Is that check worth its cost?