Snowflake

Finance flagged that one virtual warehouse, WH_ANALYTICS, is the single biggest line on the Snowflake bill. It's sized X-Large, has AUTO_SUSPEND disabled (runs 24/7), and the query history shows it handles only a handful of queries an hour, none of them heavy. The team sized it XL "because dashboards felt slow once." The interviewer wants you to explain Snowflake's credit model, right-size this warehouse, and distinguish the two totally different scaling levers people confuse.

events is a 4TB, multi-billion-row table. A dashboard query filters WHERE event_date = '2026-06-22' — about 0.1% of the data — yet the Query Profile shows partitions scanned ≈ partitions total (it reads almost the whole table) and takes 90+ seconds. The table is loaded by a trickle of small MERGEs all day from many sources. The interviewer wants you to explain micro-partitions and pruning, why this table stopped pruning, and decide whether a clustering key is the right fix — including when it ISN'T.

A nightly aggregation query went from 4 minutes to 50 minutes. The Query Profile shows ~120GB spilled to local storage and ~800GB spilled to remote storage, and one Join node where rows-out is ~40× rows-in. The on-call engineer's fix was "bump the warehouse from L to 3XL," which helped a bit but the bill tripled. The interviewer wants you to read the Query Profile like a senior, explain what spilling means, and decide whether this is a "scale up" problem or a "rewrite the query" problem.

Your Snowflake bill went from ~₹8L/month to ₹24L in one quarter. It's a single account shared by five teams — data eng, analytics, two product squads, and a data-science group running notebooks — with a handful of shared warehouses and no chargeback. Nobody can say which team or workload drove the 3x, and there are no spend guardrails. The interviewer wants you to find the spend, attribute it, put hard guardrails in place, and design a governance model so this doesn't recur — using Snowflake's own metadata, not guesswork.

A raw landing table raw_customers is continuously loaded (Snowpipe) with CDC rows from an OLTP source — inserts, updates, and deletes. You need to keep a curated dim_customers dimension upserted in near real time without re-scanning the whole landing table every run. The mandate is to build it natively with Streams + Tasks, and to do it idempotently. The interviewer wants the full design — how the stream tracks changes, how the task schedules the MERGE, and the gotchas (stale streams, multiple consumers, empty runs) that sink naive implementations.