Case Study 2: Choosing a Warehouse

"Every vendor benchmark showed their product winning. All three benchmarks were honest. That should tell you something about benchmarks."

Executive Summary

In 2024, Kestrel evaluated Snowflake, BigQuery, and Redshift for its analytical warehouse. The evaluation took five weeks, produced a decision, and — more usefully — produced a method that the team has reused twice since.

The finding worth carrying is that performance decided nothing and cost decided nothing. All three were fast enough, and modelled cost came out within 22% of each other, which is well inside the error bars of a workload projection. The decision turned on operational fit — who could operate it, how it failed, and what leaving it would cost.

This case study also examines the two mistakes the team made during the evaluation, both of which are common: benchmarking with clean data, and modelling cost on today's volume.

Skills applied: the three architectures and their cost models (§8.5); cost modelling (§8.7); tool evaluation criteria (Chapter 5 §5.8); reversal conditions (Chapter 3 §3.7).

Background

The situation, mid-2024. Kestrel's analytics ran on a PostgreSQL read replica. The problems were concrete:

1. The morning dashboard refresh took 14 minutes and was competing with the extract.
2. Twelve analysts and BI users at 09:00 produced queue depth that made everything slow.
3. The clickstream — 14M events a day — had never been landed anywhere queryable.
4. The 6am SLA was being missed roughly twice a month.

Note that only problem 3 is about data volume. Two of the four are concurrency, which is exactly the pattern §8.9 describes and Chapter 5's Case Study 2 found: the honest reason to adopt a warehouse is usually concurrency and governance rather than scale.

Constraints: four data engineers. AWS was the existing cloud. No prior warehouse experience on the team. A budget expectation of roughly $60,000 a year, which was a number someone had said in a meeting rather than a modelled figure.

The Problem

The evaluation started badly, in a way worth reproducing because it is the default.

Week 1 was spent on vendor benchmarks. Each vendor publishes performance comparisons showing their product winning. The team read all three, found them contradictory, and spent three days trying to reconcile them.

They are not reconcilable, and they are also not dishonest. Each benchmark uses a workload, a data shape, a configuration, and a price point chosen by the vendor — and every product genuinely is fastest at something. Reading vendor benchmarks against each other is a category error, and recognizing that took the team most of a week.

Week 2's first benchmark was worse, because it was their own and it was wrong in a subtler way.

They loaded a clean, well-formed extract of fct_order_item — 6.5 million rows, perfectly typed, no nulls, sorted by date — and ran ten queries against each platform. All three performed well. The spread was under 30%.

⚠️ Failure Mode — Benchmarking with clean data

The clean-data benchmark measured almost nothing useful, and it took a second attempt to see why.

What clean data hides:

• Loading behavior. Real loads have type mismatches, encoding problems, unexpected nulls, and files of wildly varying size. How a platform fails a load — with a useful error and a quarantine, or with a truncated message and a partial write — is a daily operational property and it is invisible on clean data.
• Semi-structured handling. The clickstream is nested JSON with a schema that changes weekly. That was the workload with the most uncertainty and it was not in the benchmark at all.
• Concurrency. Ten queries run sequentially by one person is not twelve analysts at 09:00, which was two of the four presenting problems.
• Skew. The wholesale customer at 8% of order lines (Chapter 4, Case Study 1) is in the real data and was sorted away in the extract.

The rule from Chapter 5 §5.8, criterion 6: bring the messy table. The second benchmark used the raw clickstream with its actual schema drift, a deliberately malformed batch, and twelve simulated concurrent users. It produced a completely different ranking — and, more importantly, it produced findings rather than timings.

The Analysis

The benchmark that worked

Four workloads, chosen because each one exercised a property the team was uncertain about:

Three of four workloads did not differentiate. That is a normal and useful result, and it is where a lot of evaluation effort is wasted — teams keep benchmarking because benchmarking feels like progress.

Workload A did differentiate, and on a property nobody had listed as a criterion: what happens when a batch is malformed.

All three are defensible designs. Snowflake's is the one that suited a four-person team, because the failure is partial, visible, and diagnosable without re-running the load — and because "a batch was malformed at 03:00 and I need to know which rows and why, at 08:00" is a thing that happens weekly, not annually.

The cost model, and its honest error bars

Modelled at Kestrel's projected 2025 volume, using the frozen basis:

Within 22% top to bottom, and the team's own estimate was that the model's error bars were at least ±30% — because it depended on projected query volume, projected data growth, and assumed efficiency, none of which they could measure in advance.

💸 Cost Check — When the cost model does not decide

Three numbers spanning 22%, with error bars of ±30%. The intervals overlap completely.

The right conclusion is not "pick the cheapest." It is "cost does not decide this; use it as a constraint rather than a criterion." All three fit the $60,000 expectation. Move on.

This is the same finding as Chapter 3's Case Study 2, where cost analysis argued mildly against the chosen option and was right to be run anyway — running the numbers is how you find out whether they decide anything.

The two things the cost model genuinely produced, both more valuable than the ranking:

1. It found the sensitivities. Snowflake's cost was most sensitive to auto-suspend configuration, BigQuery's to query discipline, Redshift's to whether the reservation matched actual usage. Knowing which lever matters on each platform is worth more than knowing which total is lower, because the lever is what you will be pulling for the next four years.
2. It surfaced that Redshift's cost was fixed. $41,600 of it was committed whether or not it was used, which interacts badly with a business that has a 6.28× seasonal peak and a small team likely to misjudge sizing.

What actually decided it

Four factors, ranked by the weight the team gave them:

1. Operational fit for four engineers with no warehouse experience. Snowflake requires you to tune warehouse size and auto-suspend — two dials, both easy to reason about. Redshift requires distribution keys, sort keys, vacuum scheduling, and queue configuration — a genuinely larger surface that rewards expertise the team did not have. BigQuery requires the least tuning of all and demands the most query discipline, which is harder to enforce socially than a configuration setting.

2. Failure behavior on bad loads. Workload A.

3. Exit cost. Chapter 5 §5.8's criterion 2, asked deliberately at purchase. All three export to Parquet; none traps data catastrophically. Snowflake and Redshift store in proprietary internal formats but export cleanly; BigQuery the same. The mitigation the team chose was architectural rather than vendor-based: bronze and silver stay in Delta on object storage (Chapter 3's ADR-001), so only the gold layer is inside the warehouse and only the gold layer would have to be rebuilt. That decision reduced the exit cost of all three options and was made before the vendor choice.

4. Concurrency isolation. Snowflake's separate virtual warehouses per workload directly solve presenting problems 1 and 2 — the nightly transformation and the morning dashboards stop competing.

📐 Design Decision — Optimize the architecture to reduce the vendor decision's weight

The most reusable thing in this case study is a move the team made before evaluating vendors.

By deciding that bronze and silver live in open formats on object storage, and that only gold lives in the warehouse, they converted "which warehouse" from a decision about all their data into a decision about their serving layer — perhaps 8% of their bytes.

That is Chapter 3 §3.1's "push decisions down the reversal-cost ranking," applied deliberately. A warehouse migration went from a twelve-month project to an estimated six-to-eight-week one.

What it costs: an extra hop, extra latency on the bronze-to-gold path, and two storage systems to operate rather than one — the operational burden Chapter 3's ADR-001 records as its main negative consequence.

The general move: when a decision is hard and expensive, look for an architectural change that makes it cheap, and make that change first. It is usually available, it is usually cheaper than getting the hard decision right, and it survives being wrong.

The Decision

Snowflake, on operational fit and failure behavior, with cost as a constraint that all three satisfied.

The ADR recorded three reversal conditions, all observable:

• If gold-layer scanning exceeds 200 TiB/month, re-model against BigQuery capacity pricing — because the relative economics change at volume.
• If we hire two engineers with deep Redshift experience and the AWS commitment discount exceeds 30%, re-evaluate — because operational fit is a property of the team, not of the product.
• If warehouse cost exceeds $90,000/year, re-evaluate regardless of cause — a circuit breaker.

What Happened

Two years in, from the team's own review:

The cost model was wrong, in the expected direction. Actual first-year spend was $51,200 against a modelled $44,320 — 15.5% over, entirely from BI compute, because dashboard usage grew faster than projected. The model's error was within its own stated error bars, which is the most that can be asked of a projection.

The auto-suspend sensitivity mattered exactly as predicted. The first six months ran with a 60-minute auto-suspend inherited from a tutorial. Fixing it (§8.5) saved roughly $18,000 a year. The cost model had flagged this as the dominant lever and the team still got it wrong for six months, which says something about the gap between knowing a lever exists and pulling it.

Workload A's finding paid off repeatedly. Malformed clickstream batches occur roughly monthly. Each is a fifteen-minute investigation using the error table, rather than a re-run.

No reversal condition has fired. Gold-layer scanning is at 71 TiB/month. Nobody with Redshift expertise has been hired. Cost peaked at $61,400 annualized during the auto-suspend period and is now $53,000.

One thing they would do differently: the five weeks were too long. Three of the four workloads did not differentiate, and the team's judgment is that a two-week evaluation focused on failure behavior and operational surface would have reached the same answer. They kept benchmarking because benchmarking felt like rigour, and most of it measured nothing.

Lessons

1. Vendor benchmarks are not comparable to each other. Each is honest about a workload the vendor chose. Reading them against one another is a category error.

2. Benchmark with your messy data or do not benchmark. Clean data hides load failure behavior, semi-structured handling, concurrency, and skew — which is most of what differs.

3. Expect most workloads not to differentiate. Three of four did not. That is a finding; keeping going is how evaluations take five weeks.

4. Run the cost model, expect it not to decide, and keep the sensitivities. Knowing which lever dominates on each platform is worth more than knowing which total is lower.

5. Fixed committed cost interacts badly with a seasonal peak and an inexperienced team. Redshift's $41,600 was owed whether used or not.

6. Ask the exit-cost question at purchase, and then do better: change the architecture so the answer matters less. Keeping bronze and silver in open formats made the vendor decision an 8% decision.

7. Operational fit is a property of your team, not of the product. The reversal condition that names hiring is the honest form of this.

8. Knowing a lever exists is not pulling it. The cost model identified auto-suspend as dominant and the team still lost $18,000 a year to it for six months.

Questions for Discussion

1. Three of four benchmark workloads did not differentiate. How would you decide, in advance, which workloads are worth benchmarking? Is there a way to predict which will differentiate?

2. The cost model's intervals overlapped completely and the team used cost as a constraint rather than a criterion. When should a cost model decide a vendor choice? What would the numbers have to look like?

3. The architectural move — bronze and silver in open formats — reduced the vendor decision's weight before it was made. Find another decision in this book where the same move is available.

4. Snowflake's load failure behavior suited a four-person team. Describe the organization for which BigQuery's all-or-nothing load failure is the better design.

5. The team lost $18,000 a year for six months to a setting their own analysis had flagged as dominant. What process would close that gap? Would a checklist have worked?

6. Reversal condition 2 names hiring as a trigger. Is it reasonable for an architecture decision to depend on staffing? What are the risks of writing that down?

7. The evaluation took five weeks and the team believes two would have sufficed. Write the two-week version as a plan: what you do in each week, and what you deliberately skip.