DWH cost optimization for DE interviews

Why DWH cost shows up in DE interviews

Picture the scene. You're on an onsite at Snowflake, Databricks, or any company with multi-million-dollar warehouse bills — Stripe, Airbnb, DoorDash, Notion. The hiring manager slides into the real question: "Our Snowflake bill jumped 40% last quarter and the CFO wants it back under control. Walk me through how you'd attack it." That is a cost optimization prompt dressed as system design, and it separates engineers who only run queries from the ones who own a platform.

The reason this question is everywhere in 2026 is simple. Cloud DWH spend now shows up on quarterly earnings calls. Snowflake customers routinely report $50k-500k monthly bills, and the top decile of BigQuery customers crosses $1M per month. Finance teams stopped treating it as "compute" and started treating it as a budget line. Interviewers want to hear scan bytes, slot-hours, partition pruning, and tier policies — not generic talking points.

The structure below is the one I'd use on a whiteboard: cost drivers first, then five concrete levers with examples, then the traps that catch people who memorized the moves without ever cutting a real bill.

Cost drivers you need to name

Before you touch a single optimization, list the buckets. A senior DE answer starts with "compute, storage, egress, and metadata" — not because it's flashy, but because it forces the conversation onto numbers. Most engineers blur compute and storage together and get pushed off-balance when the interviewer asks where the dollars actually live.

The biggest mistake juniors make is sizing all four equally. On modern Snowflake, BigQuery, and Databricks workloads, compute is usually 70%+ of the bill, storage is cheap, and egress is a rounding error unless you've made a topology mistake. Lead with compute and you'll spend your interview minutes where they matter.

Scan reduction — the first lever

Every query in a columnar warehouse scans bytes, and you pay per byte scanned (BigQuery on-demand), per slot-hour (BQ Editions), or per warehouse-second (Snowflake). The cheapest query is the one that reads less data. Four tactics matter.

Project only what you need. SELECT * against a 200-column event table in BigQuery is a tax — the engine reads every column block from object storage. Replacing it with the eight columns the consumer actually uses cuts scan bytes by an order of magnitude on wide fact tables.

Push filters down. Predicates on partition columns prune entire partitions before any scan. Predicates on clustering keys prune micro-partitions on Snowflake or block ranges on Databricks. Predicates on neither force a full scan — which is why putting WHERE event_date = current_date() in a CTE after a join with a hundred-million-row dimension is the classic junior mistake.

-- Bad: scans full events table, filters after join
WITH joined AS (
  SELECT e.*, u.country
  FROM events e
  JOIN users u USING (user_id)
)
SELECT * FROM joined WHERE event_date = '2026-05-22';

-- Good: filter pruned at scan, then join
WITH recent AS (
  SELECT * FROM events
  WHERE event_date = '2026-05-22'
)
SELECT r.*, u.country
FROM recent r
JOIN users u USING (user_id);

Default dashboards to recent data. Looker, Tableau, and Mode default to "all time" unless you cap them. A dashboard scanning 18 months of events every open costs 18x what it should. Cap the range in the LookML or model layer.

Cache aggressively. Snowflake's result cache is free for 24 hours if underlying tables didn't change. BigQuery caches results for 24 hours per user per project. A dashboard rendering the same query 200 times a day should hit cache 199 times. If it doesn't, someone added current_timestamp() and broke the cache.

Partitioning and clustering

Partitioning is the load-bearing lever for tables above 100M rows. The pattern is the same across BigQuery, Snowflake, and Databricks Delta: partition by a low-cardinality, high-selectivity column — usually a date — and pick a clustering / cluster-by key on the next most-filtered column.

-- BigQuery
CREATE TABLE analytics.events
PARTITION BY DATE(event_ts)
CLUSTER BY user_id, event_name
AS SELECT * FROM raw.events;

-- Snowflake
CREATE TABLE analytics.events
CLUSTER BY (event_date, user_id)
AS SELECT * FROM raw.events;

The common over-partitioning trap: partitioning a table by hour when daily would do. BigQuery caps you at 4,000 partitions per table, and Snowflake's micro-partition layer already gives you fine-grained pruning under the hood. Hourly partitions on a five-year-old table will hit limits and produce slow metadata operations. Daily is the safe default — go to hourly only if your scan patterns demand it.

Materialized views and result cache

Pre-computing expensive aggregations is the highest-leverage move when the same heavy query runs hundreds of times per day. A daily MV that refreshes at 4am and serves every dashboard between 9am and 6pm pays for itself in hours.

ClickHouse's MV model is the most powerful but also the most footgun-prone — it fires on every insert into the source table, so a schema mismatch silently drops rows. You want a test that reconciles source and MV counts daily.

Storage tiering

Storage is rarely the biggest line, but tiering is the easy 60-80% win when a finance partner is breathing down your neck. The pattern is simple: hot data on warehouse-native storage, warm data on cloud object storage with external tables, cold data on archive.

Last 30 days   → hot tier (Snowflake / BigQuery native)
30-365 days    → warm tier (S3 / GCS via external tables)
> 365 days     → cold tier (Glacier / Coldline / Archive)

The trade-off is query speed. Hot scans cost more per byte but return in seconds. Warm scans through external tables run 2-10x slower. Cold tier retrieval takes minutes to hours and is meant for compliance archives, not interactive analytics. A clean retention policy with auto-promotion / demotion rules is the difference between $15k/month and $45k/month on a typical 50TB events table.

Warehouse and cluster scaling

Right-sizing is the lever that earns you the most political capital — it directly shrinks the bill, fast, with low engineering risk. Four tactics matter.

Auto-suspend. Snowflake idles a virtual warehouse after a configurable timeout. Set it to 60 seconds for interactive warehouses and 300 seconds for ETL warehouses. The most common waste pattern is a warehouse left at 10 minutes idle when usage is bursty — you pay for the long tail of nothing.

Right-size by workload. A Large warehouse running at 20% CPU for an hour is cheaper as a Medium running at 80% for the same hour, even though wall-clock might tick up slightly. Use the query profiler to read CPU and memory pressure, then drop a size.

Use spot / preemptible for batch. Databricks job clusters and EMR support spot instances at 60-90% discounts over on-demand. Backfills, batch transforms, and any restartable workload should default to spot. Interactive notebooks should not.

Commit for predictable spend. Reserved capacity (Snowflake capacity contracts, BigQuery Editions slots, AWS Savings Plans) trades flexibility for 30-60% discounts. Only commit to the floor — the baseline you're sure you'll hit every month. Above that, stay on-demand.

Common pitfalls

The first pitfall is optimizing the wrong layer. Engineers see a $200k bill and reach for partitioning the biggest fact table — even when 70% of spend is on a single dashboard re-running a CTE chain at every page load. Start with usage telemetry. Snowflake's query_history, BigQuery's INFORMATION_SCHEMA.JOBS_BY_PROJECT, and Databricks' query history all give bytes-scanned by query hash. Sort descending and you'll find your top three queries account for 50-70% of compute. Optimize those first.

The second pitfall is partitioning by the wrong column. Teams partition by user_id because users are how they think about the data, then discover dashboards filter by date 95% of the time and the partition does nothing. The interview answer: partition by your dominant filter column, not your join column. Use clustering for the join column on Snowflake / BigQuery, or secondary indexes on Postgres-class warehouses.

The third pitfall is MV refresh storms. A team builds 40 materialized views, all refreshing on the same hourly cron. At minute zero, the warehouse spikes to a Large for ten minutes, then drops to zero for fifty. Bill is double what right-sizing predicts. Stagger refreshes or use incremental MV where supported.

The fourth pitfall is trusting the autoscaler. Snowflake multi-cluster warehouses scale out automatically when queue length grows. Every additional cluster bills full per-second rate. For bursty workloads, the autoscaler happily spins up four clusters for two minutes and bills you for four. A scheduled scale-up before a known burst is often cheaper.

The fifth pitfall is over-collecting raw events. Many DWH bills are inflated 30-50% by event payloads no one queries. The fix is a deprecation cycle: every six months, find tables with zero reads in the trailing 90 days, then archive or drop. Teams routinely save 20-30% on storage with one afternoon of this work.

Related reading

• BigQuery for Data Engineering interviews
• Data tiering for Data Engineering interviews
• Materialized views for Data Engineering interviews
• Table partitioning in SQL
• DWH modeling for Data Engineering interviews

If you want to drill DE interview questions like this every day with real warehouse scenarios, NAILDD is launching with 500+ problems covering exactly this pattern.

FAQ

How do I estimate a DWH bill before I commit?

Pull a representative seven-day query workload from a sandbox or trial. Sum the bytes scanned (BigQuery) or warehouse-seconds (Snowflake), multiply by the public pricing per unit, then add 20-30% overhead for refreshes, dashboards, and exploratory work. Multiply by four for monthly. This gets you within roughly 20% of reality, which is enough for a budget conversation. Refine after the first month of real usage.

Is Snowflake or BigQuery cheaper?

It depends on workload shape. BigQuery on-demand wins for bursty, infrequent queries — you pay only for what you scan with no idle cost. Snowflake wins for sustained workloads with high concurrency, where warehouse-second billing lets you batch queries into fewer, larger compute slices. For predictable spend above roughly $30k/month, both have committed-capacity discounts that flatten the difference.

Does query optimization matter if I'm on flat-rate / reserved capacity?

Yes, just differently. On flat-rate, you're not paying per scan, but slow queries consume slots that block other queries. The bill is fixed, but the effective throughput drops. Senior teams treat slot-time as a budget the same way on-demand teams treat dollars — same optimization work, different unit.

When should I move off a managed DWH back to self-hosted?

Almost never for analytics. The break-even point — where ClickHouse, Trino, or DuckDB self-hosted beats Snowflake or BigQuery in total cost of ownership — usually requires a dedicated platform team of 3-5 engineers and a workload above $1M/year. Below that, the platform engineering tax outweighs the license savings. Above that, do the math carefully and include 24/7 oncall.

How do I show DWH cost wins on my resume?

Pick a single project, name the lever (e.g., "introduced daily MVs for top-5 finance dashboards"), and quote the dollar delta and percentage. "Reduced Snowflake spend on the finance domain from $48k to $19k/month — 60% reduction — by migrating top-5 dashboards to materialized views and right-sizing the analyst warehouse" lands better than a generic "optimized data warehouse costs" line. Interviewers will probe — make sure the number is real and you can defend the methodology.

How fast can a new DE drive measurable cost wins?

In the first 30 days, expect to find 10-20% in obvious waste: idle warehouses, runaway dashboards, broken result caches. The next 20-40% takes a quarter — retention policies, MV restructuring, freshness trade-offs. Beyond that, optimizations get architectural. Calibrate accordingly when an interviewer asks for a 90-day plan.