Cloud Data Warehouses — Interview Q&A
Q: Why is columnar storage so much better suited to analytical queries than the row-based storage a typical OLTP database uses?
A: An aggregate query (SUM, GROUP BY) typically only needs a few columns out of a wide table, but a row-based store has to read every column of every row from disk since a full row is stored contiguously — most of that I/O is wasted on columns the query never uses. Columnar storage stores each column separately, so the same query reads only the specific columns it actually needs, skipping everything else — for a wide table, this can be an order-of-magnitude reduction in real data read, which is the core reason data warehouses are built around columnar storage specifically for aggregate-heavy analytical workloads.
Q: How does partitioning actually reduce query cost and latency, concretely?
A: Partitioning splits a table into physically separate segments, most commonly by date — a query filtering on the partition column only reads the specific partitions matching that filter, not the entire table. For a multi-year table partitioned by day, a query for one specific day reads roughly one day's worth of data instead of scanning years of history. This matters doubly on pay-per-bytes-scanned pricing models, where partition pruning is a direct cost reduction, not just a speed improvement — an unpartitioned large table means every query, regardless of how narrow its actual date filter is, pays to scan the full historical dataset every time.
Q: What's the practical difference between partitioning and clustering, and how do you choose which columns to use for each?
A: Partitioning handles coarse-grained pruning — which large chunk of the table a query needs, almost always based on a date/timestamp column most queries filter on broadly. Clustering (or sort keys) handles finer ordering within a partition, for a second, typically higher-cardinality column that's also commonly filtered — co-locating rows with the same value physically so the engine can skip further within an already-pruned partition. The practical guidance: partition by the column that gives the broadest, most common filtering win, cluster by the next most commonly filtered high-cardinality column — clustering by a low-cardinality column provides little real benefit over no clustering at all.
Q: Explain storage-compute separation and why it's a meaningfully different operational model from a traditional fixed-cluster warehouse.
A: Storage-compute separation means the data itself and the processing power that queries it scale independently — multiple separate compute pools can query the exact same underlying stored data simultaneously, at different sizes, without contending for the same resources. In a traditional fixed-cluster model, a single heavy ad-hoc query competes for the same shared compute as every other query running against that cluster, which is a common, real source of performance degradation for unrelated workloads. With separation, a data science team's occasional heavy query can spin up its own dedicated, appropriately-sized compute pool without ever affecting a production BI dashboard's routine query performance running against separate compute over the same data.
Q: When would you query semi-structured JSON data directly versus flattening it into typed columns first?
A: Direct querying is the right fit for genuinely ad-hoc, infrequent exploration, or for an ingestion source with a schema that evolves or varies, where forcing a rigid schema upfront would break on every source-side change. But direct JSON parsing at query time is inherently slower than reading an equivalent pre-typed flattened column, so for a field that's queried frequently and repeatedly — a dashboard filtering on it regularly, not a one-off exploration — the sound pattern is landing the raw JSON as-is for flexibility, then flattening that specific field into a proper typed column during transformation (a dbt staging model), getting query performance where it actually matters while keeping ingestion flexible.
Q: Why would a query cost far more than expected even though it "looks" correctly filtered to prune partitions?
A: The filter might not actually be reaching the partition column the way it appears to in the SQL — a filter applied after a join, subquery, or view layer can defeat partition pruning even though a human reading the query would assume it's correctly scoped, since not every warehouse pushes a filter down through every possible intervening layer. The only reliable way to confirm pruning actually happened is checking the query's real execution plan or a dry-run bytes estimate before running it — assuming correctness from how the SQL reads, without verifying against the actual plan, is how an unexpectedly expensive query gets discovered only after the bill reflects it.
Q: How would you enforce that sensitive data (like SSNs or health records) is only visible to authorized roles, in a way that can't be bypassed by a specific BI tool or application?
A: Enforce it at the data layer itself, via the warehouse's own row-level security or dynamic column masking, rather than relying on every downstream application or BI tool to correctly implement its own access filtering. A masking policy tied to CURRENT_ROLE() (or equivalent) means the same underlying table returns fully unmasked data to an authorized compliance role and masked data to everyone else — automatically, consistently, regardless of what's querying the warehouse. Relying on application-layer filtering alone is a real, common gap, since it only takes one tool or one ad-hoc query bypassing that application logic to expose data that should have been restricted.