The first Snowflake question in most data-engineering loops is some version of “walk me through the architecture,” and the answer that clears it is not “storage and compute are separate.” Every candidate says that. What the interviewer is listening for is what that separation buys you: you can point a 4X-Large warehouse at a nightly load and a tiny warehouse at the BI dashboards, both hitting the same table, with no copies and no contention. Two teams can query the same data at the same time on their own compute and never block each other.
Snowflake has three layers, and naming them cleanly signals you have run it rather than skimmed the docs. Data lives in cloud object storage (S3, Azure Blob, or GCS) as compressed, columnar files you never touch directly. Compute is the virtual warehouses you spin up and size. The cloud services layer sits above both and handles metadata, query planning, transactions, and security, and it is the piece people forget because you never provision it. That services layer is also the answer to a favorite follow-up: how does Snowflake skip most of a table before scanning any data? The metadata it keeps is what makes that possible.
Micro-partitions and why pruning is the whole game
Snowflake stores every table as micro-partitions: immutable files of roughly 50 to 500 MB of uncompressed data, written in columnar format, created automatically as you load. You do not manage them. For each micro-partition, Snowflake records the range of values per column (the min and max), a distinct-value count, and other metadata. When you filter WHERE event_date = '2026-07-01', the planner reads that metadata first and drops every micro-partition whose stored date range cannot contain July 1. That is partition pruning, and it is the mechanism behind almost every performance answer you will give.
A strong candidate ties this straight back to query design. If your filters line up with how the data was naturally loaded, usually by time, pruning is excellent and you barely think about it. The trouble starts when you filter on a column scattered evenly across every micro-partition, like a random user ID in a table loaded by date. Now the min/max ranges overlap on every file, nothing prunes, and Snowflake reads the whole table. That is the moment clustering enters the conversation.
When a clustering key earns its credits, and when it just burns them
Clustering reorganizes a table’s micro-partitions so rows sharing a key value sit together, which tightens those min/max ranges and lets pruning do its job. You define it once:
ALTER TABLE events CLUSTER BY (event_date, customer_id);and the automatic clustering service keeps it sorted in the background as new data arrives. The gotcha interviewers probe is the assumption that clustering always helps. It does not. Automatic clustering spends credits every time it reshuffles partitions, so on a table with heavy, constant churn it can cost more than the queries it speeds up. It pays off on large tables (hundreds of gigabytes and up) queried often with predictable filters on the clustering key. Cluster a small table, or a column nobody filters on, and you are paying to sort data for no return.
Order matters too, and this is where people slip. Put the lower-cardinality column first so it groups broadly, then the higher-cardinality one to sort within each group. And you check the result rather than guessing:
SELECT SYSTEM$CLUSTERING_INFORMATION('events', '(event_date, customer_id)');The output reports average overlap and depth per micro-partition. High overlap means the key is not helping and you should rethink it before switching on a service that bills you to maintain it.
Scale up or scale out, the question people get backwards
Give someone this scenario: the nightly transform is slow, and separately, at 9 a.m. fifty analysts hit the dashboards and everything crawls. Same fix? No, and mixing them up is the fastest way to look junior.
A slow single query is usually a memory problem. Resizing the warehouse up, say Medium to Large, doubles the compute and the memory, which matters because Snowflake spills to disk when a query outgrows RAM. You see it in the query profile as “Bytes spilled to local storage,” and worse, “Bytes spilled to remote storage,” which means it exhausted the local SSD and started writing to object storage mid-query. Remote spilling is very slow and is often the real reason a join drags. Sizing up, or cutting the data the query touches, is the answer there.
The 9 a.m. crush is a concurrency problem, and a bigger warehouse does nothing for it. What you want is a multi-cluster warehouse: same size, but Snowflake spins up extra clusters as the queue grows and retires them when demand falls.
CREATE WAREHOUSE bi_wh
WAREHOUSE_SIZE = 'SMALL'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 4
SCALING_POLICY = 'STANDARD'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;Scale up for the size of one query. Scale out for the number of queries. Saying that plainly, with the spilling detail attached, is what separates a real answer from a memorized one.
Where the credits actually go
Snowflake bills compute in credits, and a virtual warehouse spends them by size for as long as it runs. Each step up the ladder doubles the rate.
| Virtual warehouse size | Credits billed per hour | Compute relative to X-Small | Workload it fits |
|---|---|---|---|
| X-Small | 1 | 1x | Dashboards, small lookups, dev work |
| Small | 2 | 2x | Light transforms, BI concurrency |
| Medium | 4 | 4x | Routine ELT jobs |
| Large | 8 | 8x | Heavier batch loads and joins |
| X-Large | 16 | 16x | Large nightly builds |
| 2X-Large | 32 | 32x | Big backfills, wide aggregations |
| 3X-Large | 64 | 64x | Very large one-off reprocessing |
| 4X-Large | 128 | 128x | Rare massive parallel loads |
Billing is per second with a 60-second minimum every time a warehouse resumes, and the dollar cost is credits times your per-credit rate, which depends on edition and cloud region, so check your own contract rather than quoting a figure in the room. Sizes keep doubling past the table, up to 6X-Large for the rare monster job.
Cost questions almost always arrive as a mystery to solve: “our bill tripled last month, where do you look first?” The strongest answers start with the warehouses that never sleep. A warehouse with AUTO_SUSPEND set high, or set to never, keeps billing while idle, and that single misconfiguration explains more surprise bills than anything else. Set auto-suspend to 60 seconds on most warehouses and it stops charging you a minute after the last query lands.
There is a real tradeoff to name, and it earns points: suspending a warehouse clears its local SSD cache, the copy of recently read micro-partitions it keeps so it can skip re-reading from storage. Suspend too aggressively and you lose that cache, so repeated queries re-fetch from object storage and run slower. For a warehouse serving steady dashboard traffic, a slightly longer suspend keeps the cache warm and can be the cheaper choice overall. After idle warehouses, look at oversized ones (an X-Large running a job a Medium would finish), automatic clustering on churny tables, and SELECT * on wide tables, which throws away the columnar advantage.
Resource monitors are the guardrail worth raising before you are asked. You set a credit quota on a warehouse or the account, and Snowflake notifies, then suspends, when it is crossed, which is how you stop a runaway backfill from quietly burning a month’s budget over a weekend.
The caches, and the result cache trap
Snowflake has three caches, and interviewers like the last one because it has sharp edges. The metadata cache answers things like COUNT(*) straight from the services layer without waking a warehouse. The local disk cache is the warm-SSD copy already mentioned. The result cache holds the actual output of a query for 24 hours and can return it with no compute at all, which sounds like free speed until you learn what invalidates it.
The result cache only fires when the new query text matches the previous one almost exactly, the underlying data has not changed, and the query holds no non-deterministic functions. Drop a CURRENT_TIMESTAMP() or a RANDOM() into it and every run is unique, so the cache never hits. Change one part of the SQL, even whitespace in some cases, and you miss. Candidates who know the result cache exists but cannot say why their “cached” dashboard keeps spending credits are the ones who have read about Snowflake without fighting with it, and that gap is exactly what these rounds are built to find.
Drill the patterns next:
