Ten Snowflake questions data engineers and developers should prepare, what each one tests, short answers checked against the docs, and a worked example.
Snowflake interview questions for data engineer, analytics engineer and developer roles come back to four areas: architecture, performance (pruning, clustering, caching, warehouse sizing), pipelines (COPY, Snowpipe, streams and tasks) and governance (Time Travel, masking, sharing). Expect follow-ups beyond definitions, such as diagnosing a slow query and saying what the fix costs in credits. Below are ten questions worth preparing, each with what it tests and an answer checked against Snowflake's documentation.
For how this fits into the whole loop, including the data modeling and pipeline design rounds, see our guide to passing the data engineer interview.
First, which kind of Snowflake interview is it?
Most readers are interviewing for a job that uses Snowflake. If you are interviewing at Snowflake itself, it publishes its engineering process: a 30-minute screen, 60-minute technical interviews (coding or system design), then a panel of three to five 60-minute sessions, sometimes with a 30-minute Tech Talk. It says the process "can take up to two to four weeks" and that you may be asked to do at least one interview in person.
Its January 2026 careers post encourages using AI to prepare, but bans it in assessments, take-home projects and technical exercises, and says coding interviews happen "without the use of any coding or AI assistants." For the coding rounds there, see our guide to Snowflake LeetCode interview questions.
Architecture
1. Explain Snowflake's architecture.
Tests: why the design matters, not just the layer names.
Answer: Three layers: storage, compute and cloud services. Data sits in one central columnar store; queries run on virtual warehouses, independent compute clusters; cloud services handle metadata, security and optimisation. Snowflake calls this a hybrid of shared-disk and shared-nothing designs. The consequence: warehouses do not affect each other, so ELT jobs and dashboards can each get their own, sized to fit.
2. What are micro-partitions, and how does pruning work?
Tests: where Snowflake's speed comes from.
Answer: Tables are split into micro-partitions of 50 to 500 MB of uncompressed data, with metadata on each. Queries use that metadata to skip partitions that cannot match the filter: in the docs' example, one hour out of a year of data would ideally scan 1/8760th of the table. A filter that contains a subquery cannot be used for pruning.
3. When would you add a clustering key?
Tests: cost judgement.
Answer: Rarely, and after measuring. Snowflake suggests it for multi-terabyte tables whose queries have slowed or whose clustering depth is high, with three or four columns at most. Say the trade-off: clustering consumes credits and adds storage. A weak answer adds clustering keys to every big table.
Performance
4. A dashboard query got slow. Walk me through it. See the worked example below.
5. Scale up or scale out?
Tests: which problem each solves.
Answer: Resize the warehouse (scale up) for slow, complex queries and large loads. Add clusters with a multi-cluster warehouse (scale out, Enterprise Edition) when many users queue at once. In auto-scale mode, the Standard policy starts clusters to avoid queuing; Economy lets queries wait to save credits.
6. How does the result cache work?
Tests: knowing when it misses.
Answer: A persisted result is kept for 24 hours, reset each time it is reused, up to 31 days from the first run. Reuse needs identical query text (a change of case or a table alias breaks it), unchanged data, no functions such as RANDOM or UUID_STRING, and a role with access to every table queried.
Pipelines
7. COPY INTO or Snowpipe?
Tests: latency, cost and duplicates together.
Answer: Bulk COPY runs on a warehouse you size, when you run it, as one transaction per statement. Snowpipe is serverless, triggered by a cloud storage event or a REST call, and typically loads within a minute of learning about a file, billed per GB loaded. The detail that impresses: a pipe remembers processed files for 14 days, so a file re-staged under the same name after that can load twice. Snowflake recommends files of roughly 100 to 250 MB compressed, since a load cannot run more operations in parallel than it has files.
8. Build an incremental pipeline with streams and tasks.
Tests: change data capture and its gotchas.
Answer: A stream records row changes with METADATA$ACTION and METADATA$ISUPDATE (an update appears as a delete plus an insert). Its offset advances only when a DML statement consumes it; querying it alone does not. A task runs the merge, and SYSTEM$STREAM_HAS_DATA skips runs when nothing changed:
CREATE OR REPLACE STREAM orders_stream ON TABLE raw_orders;
CREATE OR REPLACE TASK merge_orders
WAREHOUSE = transform_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO orders t
USING (
SELECT order_id, status, updated_at
FROM orders_stream
WHERE METADATA$ACTION = 'INSERT'
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.status = s.status, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, updated_at)
VALUES (s.order_id, s.status, s.updated_at);
ALTER TASK merge_orders RESUME; -- tasks are created suspendedThen name the gaps yourself. This version ignores deletes; ask whether that matters. The QUALIFY line answers the classic "deduplicate this table" question in one clause. A stream goes stale, and must be recreated, if its offset falls outside the source table's retention period. If the logic fits in one SELECT, a dynamic table with a target lag is the declarative alternative.
9. Query nested JSON.
Tests: semi-structured data.
Answer: Store it in a VARIANT column, use path notation, and explode arrays with FLATTEN:
SELECT e.payload:user_id::STRING AS user_id,
i.value:sku::STRING AS sku
FROM events e,
LATERAL FLATTEN(input => e.payload:items) i;Governance and recovery
10. Someone dropped a table and someone else wants partner access to PII. What do you do?
Tests: recovery and security basics.
Answer: Time Travel keeps history for 1 day by default; on Enterprise Edition, permanent objects can keep up to 90 days, transient and temporary ones 0 or 1. UNDROP TABLE restores a dropped table, and CLONE ... AT(OFFSET => ...) gives you a copy as of an earlier moment. After that comes a 7-day Fail-safe that only Snowflake Support can recover from, so do not say you can query it. For the partner, Secure Data Sharing copies no data, is read-only, and the consumer pays for its own compute. A masking policy (Enterprise Edition) on the PII columns masks values at query time by role. Direct shares work only within one region.
Worked example: the slow dashboard query
Say a dashboard query went from seconds to over a minute. A strong answer opens the Query Profile and checks the four problems Snowflake's docs list:
- Inefficient pruning. Compare partitions scanned with partitions total. If it scans nearly everything, the filter does not match how the data is stored; fix the filter before considering a clustering key.
- Spilling. Bytes spilled to local or remote storage mean intermediate results did not fit in memory: use a larger warehouse or smaller batches.
- Exploding joins. A join emitting far more rows than it reads is often caused by a missing join condition.
- UNION without ALL. An extra aggregate removing duplicates you never had.
Then say how you would measure the fix and what it costs. That last step is what makes an answer sound senior.
A two-week preparation plan
- Days 1–3: architecture, and read the Query Profile of real queries in any account you can use.
- Days 4–7: build question 8 end to end (stage, COPY, stream, task), then rebuild it as a dynamic table and compare.
- Days 8–10: roles, one masking policy, a clone with Time Travel and an UNDROP.
- Days 11–14: answer each question aloud in under two minutes, and prepare one project story with a number in it (runtime, credits saved). Brush up SQL with our SQL interview questions for 3 years' experience, and see the dbt interview questions if the role uses dbt.
Where Verve fits
Attempt each SQL problem yourself first. Afterwards, Verve AI's Coding Copilot can read a problem from a screen capture, answer in SQL, and run Optimize or Edge Case actions to compare against your version. Use it to practise and to compare against your own answer, not as a substitute for your own work.
Sources
- Engineering hiring stages, durations, "two to four weeks", in-person interview: https://careers.snowflake.com/us/en/gethired
- AI policy for applications, assessments and coding interviews (20 January 2026): https://careers.snowflake.com/us/en/blogarticle/ai-cheat-sheet-how-and-when-to-use-ai-in-your-snowflake-interview
- Interview steps vary by role; virtual and in-person interviews: https://careers.snowflake.com/us/en/blogarticle/navigating-the-data-slopes-applying-and-interviewing-at-snowflake
- Three layers, hybrid shared-disk and shared-nothing, independent warehouses: https://docs.snowflake.com/en/user-guide/intro-key-concepts
- Micro-partition size, 1/8760 pruning example, subquery predicates, clustering depth: https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions
- When to cluster, multi-terabyte guidance, 3–4 columns, credit and storage cost: https://docs.snowflake.com/en/user-guide/tables-clustering-keys
- Multi-cluster warehouses for concurrency, Enterprise Edition, scaling policies, resizing for slow queries: https://docs.snowflake.com/en/user-guide/warehouses-multicluster
- Result cache retention (24 hours, 31 days) and reuse conditions: https://docs.snowflake.com/en/user-guide/querying-persisted-results
- Snowpipe triggers, serverless billing, latency, 14-day load metadata, single-transaction COPY: https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro
- File size recommendation (100–250 MB compressed) and parallelism: https://docs.snowflake.com/en/user-guide/data-load-considerations-prepare
- Stream metadata columns, offset behaviour, staleness: https://docs.snowflake.com/en/user-guide/streams-intro
- Tasks created suspended, ALTER TASK RESUME, SYSTEM$STREAM_HAS_DATA: https://docs.snowflake.com/en/user-guide/tasks-intro
- Dynamic tables and target lag: https://docs.snowflake.com/en/user-guide/dynamic-tables-intro
- QUALIFY with ROW_NUMBER: https://docs.snowflake.com/en/sql-reference/constructs/qualify
- FLATTEN and LATERAL: https://docs.snowflake.com/en/sql-reference/functions/flatten
- Time Travel retention by edition, AT/BEFORE, UNDROP: https://docs.snowflake.com/en/user-guide/data-time-travel
- Fail-safe 7 days, recovery by Snowflake Support only: https://docs.snowflake.com/en/user-guide/data-failsafe
- Cloning, including Time Travel clones: https://docs.snowflake.com/en/user-guide/object-clone
- Secure Data Sharing: no copy, read-only, consumer compute, reader accounts, same-region direct shares: https://docs.snowflake.com/en/user-guide/data-sharing-intro
- Dynamic Data Masking, applied at query time, Enterprise Edition: https://docs.snowflake.com/en/user-guide/security-column-ddm-intro
- Query Profile common problems (pruning, spilling, exploding joins, UNION without ALL): https://docs.snowflake.com/en/user-guide/ui-query-profile
- Coding Copilot screen capture, SQL output, quick actions: https://docs.vervecopilot.com/features/coding-copilot
Ryan
CEO of Verve AI









