Snowflake Interview Questions

Analogy categoryAnalogies

Snowflake interviews for data engineering roles tend to probe three areas: architectural understanding (why separating storage and compute matters), practical SQL/feature fluency (Time Travel, cloning, streams/tasks), and judgment questions about cost and performance tradeoffs where there is rarely one 'correct' answer but interviewers want to see structured reasoning. Strong candidates explain not just what a feature does but when and why they'd reach for it over an alternative.

🏏

Cricket analogy: Like a selection panel testing a player's technique against both pace and spin in the nets, not just one bowling type, interviewers probe architecture, SQL fluency, and judgment separately.

Common Conceptual Questions

A frequent question is 'Explain how Time Travel and Fail-safe differ.' Time Travel lets you query, clone, or restore historical data for a configurable retention period (0-90 days depending on edition, default 1 day on Standard) using AT/BEFORE clauses or UNDROP, and it is user-accessible. Fail-safe is a separate, non-configurable 7-day period after Time Travel expires during which only Snowflake support can recover data for disaster recovery, and it exists purely as a last-resort safety net, not a feature you interact with directly.

🏏

Cricket analogy: Like a batsman reviewing yesterday's own footage anytime via a personal video library (Time Travel) versus needing to call the match referee for archived footage from years ago (Fail-safe).

Practical / Scenario Questions

A common scenario question is 'How would you quickly refresh a full-scale QA environment from production without doubling storage cost?' The expected answer is zero-copy cloning: CREATE TABLE/SCHEMA/DATABASE ... CLONE creates an instant, metadata-only copy that shares the same underlying micro-partitions with the source until either side modifies data, at which point only the changed partitions incur new storage. Another common scenario is diagnosing a slow query, where the expected approach is checking the Query Profile for spilling to local/remote storage, partition pruning ratios, and whether the warehouse is undersized for the data volume being scanned.

🏏

Cricket analogy: Like a broadcaster instantly replaying a delivery from the same shared master feed rather than re-recording it, zero-copy cloning shares underlying data until either side actually changes it.

Behavioral / Judgment Questions

Interviewers also ask judgment questions like 'A dashboard warehouse's cost tripled overnight — how do you investigate?' A strong answer walks through checking QUERY_HISTORY and WAREHOUSE_METERING_HISTORY for a spike in query count or a specific runaway query, checking if a new dashboard or scheduled job was recently added, verifying auto-suspend settings weren't accidentally changed, and confirming whether a resource monitor should have caught it, before proposing both an immediate fix and a preventive guardrail.

🏏

Cricket analogy: Like a coach reviewing match footage after a shocking batting collapse before making lineup changes, rather than reacting to the scoreline alone, root-cause investigation precedes any fix.

sql

-- Time Travel: query data as of one hour ago
SELECT * FROM orders AT(OFFSET => -3600);

-- Time Travel: restore an accidentally dropped table
UNDROP TABLE orders;

-- Zero-copy clone for an instant QA refresh
CREATE DATABASE qa_db CLONE prod_db;

-- Investigate a cost spike
SELECT warehouse_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -2, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY credits DESC;

When answering an open-ended Snowflake interview question, structure your answer as: state the relevant feature/mechanism, explain the tradeoff it exists to solve, then give a concrete scenario where you'd choose it over an alternative — this signals judgment, not just recall.

Interviewers assess architecture understanding, SQL/feature fluency, and cost-performance judgment as three distinct dimensions.
Time Travel is user-accessible historical data access (0-90 days); Fail-safe is a non-configurable 7-day, support-only recovery window.
Zero-copy cloning (CLONE) is the standard answer for instantly refreshing environments without duplicating storage upfront.
Diagnosing a slow query means checking the Query Profile for spilling, pruning ratio, and warehouse sizing, not guessing.
Diagnosing a cost spike means checking QUERY_HISTORY and WAREHOUSE_METERING_HISTORY for the specific driver before proposing a fix.
Strong candidates explain when and why to use a feature, not just what the feature does.
Behavioral answers should end with both an immediate fix and a preventive guardrail (e.g., a resource monitor).