Choosing a Warehouse
Match the engine to the economics and the team
Pick a warehouse on workload, latency, cost control, ecosystem, and team skills — not on marketing. Trade-offs: SaaS-elastic (Snowflake), serverless-scaling (BigQuery), AWS-native cluster (Redshift), open-source (DuckDB/ClickHouse/Trino).
What you will learn
- Score candidates against workload and cost-control criteria
- Separate marketing claims from architecture differences
- Prototype realistically before committing
What is this?
Warehouse choice follows requirements: query concurrency and freshness SLA, the cloud you live in, how cost must be controlled, the openness you need, and the operations skill you'll fund. Snowflake (elastic SaaS), BigQuery (serverless), Redshift (AWS cluster), and open engines (DuckDB, ClickHouse, Trino) each attack different corners of that space.
Why do you need it?
- Switching warehouses is expensive; choosing well on real criteria pays for years.
- 'Just family Snowflake' decisions often skip a cheaper, equally-good path.
- A prototype at scale beats a comparison PDF — but only if the prototype answers the right questions.
Mental model
Choosing a train, car, or bike for a commute
Choosing a warehouse
Trains (BigQuery) run on someone else's schedule and carry everyone; cars (Snowflake virtual warehouses) you start when and size as you like; bikes (DuckDB) are perfect local yesterday-and-tomorrow trips. Match the vehicle to the route.
How it works
- Snowflake: SaaS elastic compute with virtual warehouses, great isolation and ease, business-modeled pricing.
- BigQuery: serverless with slot/scan pricing — strong integration, no clusters to manage.
- Redshift: AWS-native MPP cluster with distkey/sortkey design, deep S3/KMS/Glue tie-ins.
- Open stack: DuckDB (local analytics), ClickHouse (fast OLAP columns on insert-heavy), Trino (distributed SQL over lakes).
- Scoring: concurrency mix, latency target, cost control, cloud fit, security certifications, migration effort.
A lean scoring matrix by requirement.
Examples
Three requirements, three answers
# Req A: self-serve BI, high concurrency, low ops -> Snowflake (virtual WHs + caching)# Req B: massive serverless analytics inside GCP, scan-model cost -> BigQuery# Req C: deep AWS, petabyte history, willing to tune clusters -> Redshift (or Spark+Duck)# Req D: an analyst's one-off on a laptop -> DuckDB (zero infra)The same 'warehouse' request yields a different 'right answer' depending on constraints.
In production
- A 40-analyst finance org picks Snowflake for elastic concurrency and easy time travel.
- A GCP-native product embraces BigQuery and pays per scan with partitioned marts.
- An AWS-only shop maintains Redshift with Spectrum for hot/warm/cold tiering.
- A small team embeds DuckDB in its internal tooling for zero-infra analytics.
Best practices
- Write down the decision criteria before demoing; vendors demo feature-parity better than 'the same as us at lower cost'.
- Prototype with realistic data volume and query mix, not sample dashboards.
- Model migration cost (SQL dialect, tooling, permissions) as a first-class criterion.
- Assign one owner to the scoring and revisit after SLOs actually bite.
Common mistakes
- Choosing solely on query latency demos while ignoring concurrency and cost curves.
- Assuming analysts' existing SQL transfers unchanged between engines.
- Under-weighting ops skill: a cluster you cannot maintain is a burden, not an advantage.
Debugging tips
- When a trial 'feels slow', re-run with the actual concurrency and part-size your production QPS uses.
- Compare the 95th percentile under load, not the first cold-query number (paper-cutware vs real).
- Audit the bill of the trial on realistic scans — cost surprises surface on day 30, not day 1.
Interview checkpoint
Quick quiz
Redshift's distkey/sortkey tuning happens at table design time and is hard to change later.
Which engine is the best first pick for an analyst doing one-off local analysis with zero ops?