How do you optimize a data warehouse for billions of rows?

What's really being asked

Whether you understand that cloud data warehouses bill for scanned bytes and compute time, not just storage, and that query performance at the billion-row tier is driven by physical layout rather than SQL tuning alone. Interviewers want to see you reason about partition pruning, clustering, pre-aggregation, caching layers, and workload management as a coordinated system instead of isolated knobs.

The full answer

Four distinct layers in order. First, schema optimization: partition tables by date or event time so dashboards only scan relevant shards, and cluster by high-cardinality filter columns like user_id or event_type to co-locate similar rows and reduce bytes read. Second, pre-aggregation: build materialized views or scheduled rollups that collapse billions of raw events into daily or hourly aggregates, eliminating joins and window functions at query time. Third, acceleration: leverage result caching for repeated dashboard queries, use BI Engine or an in-memory acceleration layer for sub-second slices, and add search indexes on text-heavy filter fields if the platform supports it. Fourth, cost governance: move from on-demand to reserved capacity or slot-based billing when query volume is steady, use autoscaling for bursty BI workloads, and enforce query timeout limits or maximum bytes billed to prevent runaway scans.

The mistakes people make

Proposing to add more warehouse nodes or increase instance size without changing table design, which linearly increases cost. Suggesting indexes in the traditional OLTP sense, which most columnar warehouses do not use for raw event tables. Recommending caching alone as the primary fix, which ignores data layout and still incurs full scan costs on cache misses. Proposing to shard into hundreds of tiny tables manually, which breaks partition pruning and complicates querying.

What usually comes next

How would you choose between partitioning by ingestion time versus event time when backfills are common? When would a materialized view hurt performance or freshness? How do you handle a dashboard that needs both real-time and historical data in the same chart? What monitoring signals tell you whether to cluster a column or leave it unclustered?

A concrete example

A BigQuery events table with twelve billion rows per month and a dashboard filtering on event_type and date. Partition by _PARTITIONDATE and cluster on event_type and user_id. Create a materialized view grouped by date and event_type that refreshes every hour. The dashboard queries the materialized view instead of the raw table, scanning under fifty megabytes instead of multiple terabytes. Enable BI Engine cache for the top ten dashboard tiles. Switch the project to flat-rate reservations with baseline slots and autoscaling max to cap spend during traffic spikes.