Snowflake questions test whether you understand what you pay for and why a query is fast or slow: storage versus compute, how pruning works, and which features keep pipelines cheap and correct. Here are 10 questions that come up in data-engineer interviews, each with a model answer. Product details were checked against Snowflake's documentation as of November 2026.
1. Explain Snowflake’s architecture.
Level: Easy. What the interviewer is testing: whether you understand the core design, not just the marketing line.
Three layers that scale independently. Storage: all table data in compressed, columnar micro-partitions in cloud object storage, managed by Snowflake. Compute: virtual warehouses, clusters of compute that run queries; many can read the same data at once without contention. Cloud services: the control layer for authentication, metadata, query parsing and optimisation, and transaction management. Separation means you pay for storage and compute separately and can give each team its own warehouse.
2. What are micro-partitions and how do they speed up queries?
Level: Medium. What the interviewer is testing: micro-partitions and pruning.
Snowflake automatically divides table data into micro-partitions, each holding roughly 50 to 500 MB of uncompressed data, stored columnar. For every micro-partition it records metadata such as the min and max value of each column. When a query filters on a column, Snowflake compares the filter with that metadata and skips partitions that cannot match; this is pruning. It works best when similar values sit together, which depends on load order or clustering. (Snowflake docs, November 2026.)
3. When should you define a clustering key, and how do you check whether a table needs one?
Level: Medium. What the interviewer is testing: clustering keys and when they pay off.
Most tables are fine without one. Consider a clustering key on very large tables (multi-terabyte) where queries filter or join on a few columns and the natural load order does not match. Check with SYSTEM$CLUSTERING_INFORMATION('table', '(col)'), which reports clustering depth: the average depth of overlapping micro-partitions for those columns. Lower is better, and the docs caution that depth is not a precise measure; query performance (partitions scanned versus total) is the real test. Automatic Clustering maintains the key in the background and consumes credits, so weigh the benefit against that cost.
4. What is the difference between scaling up and scaling out a warehouse?
Level: Medium. What the interviewer is testing: concurrency versus size.
Scaling up (a bigger size) speeds up a single complex query. Scaling out (multi-cluster warehouse, an Enterprise-edition-and-above feature) adds clusters to serve more concurrent queries and reduce queueing; it does not speed up one query. Diagnose first: slow single query with spilling points to size; many queries waiting in a queue point to multi-cluster or workload separation.
5. What caches exist in Snowflake and how do they affect benchmarking?
Level: Medium. What the interviewer is testing: caching layers.
Result cache: an identical query on unchanged data can return a stored result (for a limited time) without using a warehouse. Warehouse local disk cache: data files read recently stay on the warehouse’s local storage until it suspends. Metadata cache answers some queries (such as simple counts or min/max) from metadata. When benchmarking, results are misleading unless you account for these; test cold (new or resumed warehouse, caching disabled for the session) and warm separately.
6. Explain Time Travel and fail-safe. What retention can you set?
Level: Medium. What the interviewer is testing: Time Travel.
Time Travel lets you query, clone or restore data as of an earlier point within the retention period, using AT/BEFORE with a timestamp, offset or statement id, and UNDROP to restore dropped objects. The default is 1 day on all accounts. On Enterprise edition and above, permanent tables, schemas and databases can be set up to 90 days; transient and temporary objects are limited to 1 day. After retention ends, data in permanent tables moves to fail-safe for 7 days, during which only Snowflake can recover it; transient and temporary tables have no fail-safe. Longer retention means more storage cost. (docs.snowflake.com, November 2026.)
SELECT * FROM orders AT(OFFSET => -60*5); -- five minutes ago SELECT * FROM orders BEFORE(STATEMENT => '<query_id>'); -- before a bad update UNDROP TABLE orders; 7. What is zero-copy cloning and what are its limits?
Level: Easy. What the interviewer is testing: zero-copy cloning.
CREATE TABLE t2 CLONE t1 (also for schemas and databases) creates a new object that points at the same micro-partitions; no data is copied until either side changes, so clones are fast and initially free of extra storage. Good for dev/test copies and pre-release backups. After changes the two diverge and storage is billed for the changed partitions. Clone references still follow retention and privilege rules, so check what the clone includes.
8. What is a stream in Snowflake? How is it used with tasks?
Level: Medium. What the interviewer is testing: change capture.
A stream records change data capture information for a table: which rows were inserted, updated or deleted since the last time the stream was consumed. Reading a stream inside a DML statement (for example MERGE into a target) advances its offset when the transaction commits. A task runs SQL (or calls a procedure) on a schedule or trigger; tasks form DAGs. The classic pattern: a task runs every few minutes, checks SYSTEM$STREAM_HAS_DATA('my_stream'), and if true merges the stream into the target. Watch for stale streams: a stream goes stale when its offset falls outside the table’s data retention period. For an unconsumed stream, Snowflake extends a shorter retention up to MAX_DATA_EXTENSION_TIME_IN_DAYS (14 days by default); after that the stream goes stale and you must recreate it, so consume streams well within that window.
CREATE OR REPLACE TASK merge_orders WAREHOUSE = etl_wh SCHEDULE = '5 MINUTE' WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') AS MERGE INTO orders_clean t USING orders_stream s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET t.amount = s.amount WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (s.order_id, s.amount); 9. What is a dynamic table? How does it compare with streams and tasks?
Level: Medium. What the interviewer is testing: declarative pipelines in Snowflake.
A dynamic table materialises the result of a SELECT and keeps it fresh: you declare the query and a target lag, and Snowflake tracks dependencies and schedules refreshes. The minimum target lag is 60 seconds, and TARGET_LAG = DOWNSTREAM lets an intermediate table refresh only when something downstream needs it. Refresh modes: INCREMENTAL (process only changes), FULL (recompute everything), AUTO (choose at creation depending on whether the query supports incremental). Streams and tasks give you imperative control (you write the merge and the schedule); dynamic tables are simpler when the logic is a query. Choose streams and tasks for custom logic or side effects, dynamic tables for declarative transformations. (docs.snowflake.com, November 2026.)
CREATE OR REPLACE DYNAMIC TABLE daily_revenue TARGET_LAG = '15 minutes' WAREHOUSE = transform_wh AS SELECT order_date, SUM(amount) AS revenue FROM orders_clean GROUP BY order_date; 10. The Snowflake bill doubled. How do you investigate and control it?
Level: Medium. What the interviewer is testing: cost governance.
- Find who and what: use account usage views (such as warehouse metering and query history) to rank warehouses and queries by credits.
- Check warehouses for no auto-suspend, oversized warehouses on small jobs, and multi-cluster set too high.
- Look at serverless features that bill separately (Automatic Clustering, Snowpipe, materialised views, search optimisation, serverless tasks).
- Look at storage: long Time Travel on churning tables, forgotten clones, large transient staging data.
- Put resource monitors on warehouses to notify or suspend at thresholds, and set statement timeouts.
Fix the biggest items first, then set budgets and alerts so you hear about the next spike early.
How to practise these
On a free trial account: create a small table, look at SYSTEM$CLUSTERING_INFORMATION, query it with AT(OFFSET => -60), drop it and UNDROP it, then put a stream on it and consume the stream in a MERGE. Explaining what you saw beats reciting definitions.
Written with AI assistance by ATS Data Labs. Key facts were checked against Snowflake's documentation in November 2026; Snowflake changes limits and names, so confirm details in the current docs before relying on them.
