Snowflake Interview Questions

Dan Lee's profile image
Dan LeeData & AI Lead
Last update
Snowflake Interview Questions

Snowflake interviews for data engineering and analytics engineering roles rarely ask you to recite the three-layer architecture. They hand you a symptom instead: a dashboard that got slow, a bill that doubled, a CDC pipeline that drops updates, an analyst who can see a table in one session and not in another. The questions test whether you can trace that symptom to the mechanism underneath, whether that is pruning, warehouse memory, stream offsets or the role hierarchy.

Candidates slip in the same places. They reach for a bigger warehouse when the profile shows the query reading every partition. They assume a SELECT on a stream consumes it, that a resource monitor caps the whole bill, or that Fail-safe is a backup they can restore themselves. Each is a small misunderstanding with a real cost in production.

The 30 questions below are grouped into five areas: architecture and storage, query performance, loading and semi-structured data, change handling, and security and cost. Each answer gives the mechanism, the real parameter names and defaults, and the trap the interviewer is listening for. A prep plan, a short quiz and an FAQ close the page.

Intermediate30 questions

Snowflake Interview Questions

Practice questions on how Snowflake stores, prunes, loads, tracks and secures data, with answers built on real parameters, defaults and query profile evidence.

Data EngineerAnalytics EngineerData Platform EngineerData ArchitectBI Engineer

Architecture, Warehouses and Micro-partitions

Every Snowflake performance or cost question eventually lands on the architecture. Table data lives in the cloud object store, compute runs in virtual warehouses that you start, stop and resize, and a cloud services layer parses, optimizes and keeps the metadata. Interviewers start here because a candidate who puts work on the wrong layer will misdiagnose everything that follows.

The idea that matters most is pruning. Snowflake has no indexes in the usual sense. It keeps the min and max of every column for every micro-partition and skips partitions that can't match a filter, so the physical order of the data decides how much a query reads.

Architecture, Warehouses and Micro-partitions

A teammate says that when the team's only warehouse is suspended, nobody can run any query against Snowflake. Which part of that is wrong, and what does each layer of the architecture actually do?

EasyArchitecture, Warehouses and Micro-partitions

Sample Answer

Snowflake separates three layers. Storage holds tables as compressed, columnar micro-partitions in the cloud provider's object store. Compute is the virtual warehouse, a cluster that scans, joins and aggregates and is billed only while it runs. Cloud services handles authentication, parsing, optimization, transactions and the metadata catalog. Some queries never touch a warehouse. SELECT COUNT(*) FROM orders can be answered from micro-partition metadata, and the profile shows a Metadata-based Result. A repeated query whose result is still in the result cache is served by cloud services too. The trap is treating the warehouse as the database. It holds no data, so you can suspend it, resize it or give another team its own warehouse on the same tables without copying anything.

Learn this in the Snowflake course

Query Performance, Caching and Warehouse Sizing

Performance questions separate people who have tuned real workloads from people who have read the product page. The usual prompt is a slow query or a slow dashboard, and the interviewer wants a method: open the query profile, find the operator or wait that dominates, and fix that cause.

Hold on to one distinction. Scaling up (a bigger warehouse) gives one query more memory and parallelism, and scaling out (more clusters) gives more queries somewhere to run. Mixing the two up is the most common wrong answer in this area.

Query Performance, Caching and Warehouse Sizing

At 9:00 every Monday, dashboard tiles that normally load in 3 seconds take 40. The profile of a slow tile shows only 3 seconds of execution. Where did the rest of the time go, and what do you change?

EasyQuery Performance, Caching and Warehouse Sizing

Sample Answer

If execution took 3 seconds and the query took 40, it waited. QUERY_HISTORY shows this as QUEUED_OVERLOAD_TIME: the warehouse ran out of capacity when the whole team opened dashboards at once. Resizing is meant for heavy queries, not for concurrency. Make the warehouse multi-cluster (Enterprise Edition) with MIN_CLUSTER_COUNT = 1, MAX_CLUSTER_COUNT = 3 and the default STANDARD scaling policy, which adds clusters when queries start to queue. Credits follow the number of running clusters, so a Medium with three clusters can use up to 12 credits an hour during the peak and 4 the rest of the day. ECONOMY only starts a cluster when it expects at least six minutes of work for it, which saves credits and accepts some queuing.

Learn this in the Snowflake course

Loading Data and Semi-structured Data

Most data reaches Snowflake as files in a stage or as rows from a streaming client, and loading questions are about correctness more than speed. What happens when a load is rerun, when a file is partly bad, when the same file name shows up twice, or when a JSON field changes type between app releases.

The mechanism to know is load metadata. COPY INTO remembers which files it loaded into each table for 64 days, and Snowpipe keeps its own history per pipe for 14 days. Most duplicate and missing-row bugs come from not knowing which history applies.

Loading Data and Semi-structured Data

A nightly COPY INTO raw_orders FROM @orders_stage failed halfway through, and the on-call engineer reruns it. Will rows be duplicated? In which cases could they be?

EasyLoading Data and Semi-structured Data

Sample Answer

Normally not. Snowflake keeps load metadata on the target table for 64 days, recording which staged files were loaded, so the rerun skips files that already loaded and picks up the rest. Duplicates appear in specific cases. FORCE = TRUE ignores the metadata and reloads everything. A file copied or renamed to a new path counts as a new file. A file whose last-modified date is more than 64 days old, on a table whose initial load was also more than 64 days ago, has unknown status: COPY skips it by default, and LOAD_UNCERTAIN_FILES = TRUE loads it, possibly a second time. Bulk COPY and Snowpipe also keep separate histories, so neither knows what the other loaded. Keep the downstream MERGE idempotent anyway.

Learn this in the Snowflake course

Streams, Tasks, Dynamic Tables and Time Travel

Change handling gets the most interview time in Snowflake data engineering loops, because a CDC pipeline exercises everything at once: incremental reads, deletes, retries, ordering and recovery. Expect to write the MERGE, not just name the objects.

Keep one idea straight. A stream is an offset, not a queue of copied rows. It records a point in the table's version history, returns the net changes since that point, and moves forward only when a DML statement that reads it commits.

Streams, Tasks, Dynamic Tables and Time Travel

A stream orders_stream on orders feeds an hourly task. An analyst debugging the pipeline runs SELECT * FROM orders_stream several times, and another team adds a task that also reads orders_stream in an INSERT. What happens to each consumer?

MediumStreams, Tasks, Dynamic Tables and Time Travel

Sample Answer

The analyst's queries are harmless. A stream's offset advances only when the stream is used in a DML statement whose transaction commits, such as INSERT, MERGE or CTAS. A plain SELECT never moves it. The second task is the real problem. Whichever task commits first advances the shared offset, and the other then sees only changes made after that point, so both silently miss rows. Every consumer needs its own stream, and streams store only an offset, so an extra one costs almost nothing. Watch staleness too. If a stream isn't consumed within the table's retention period, Snowflake extends retention for it up to MAX_DATA_EXTENSION_TIME_IN_DAYS (14 by default). Past STALE_AFTER the stream is stale and must be recreated.

Learn this in the Snowflake course

Security, Governance and Cost Control

Security questions test whether you understand that access in Snowflake is layered: a role needs privileges on the warehouse, the database, the schema and the object, and policies can still hide rows or values after all of that succeeds. Cost questions test whether you know where credits are spent and which control covers which meter.

The idea that matters most is the role hierarchy. Privileges go to roles, roles are granted to other roles and to users, and a session acts with its primary role plus any secondary roles. Most "it works for me but not for them" bugs start there.

Security, Governance and Cost Control

A new analyst with role ANALYST gets "does not exist or not authorized" on analytics.reporting.daily_revenue, which a colleague can query. Walk through what you check and grant.

EasySecurity, Governance and Cost Control

Sample Answer

Snowflake reports a missing privilege as a missing object, so check the whole chain. The role needs USAGE on a warehouse, USAGE on the database, USAGE on the schema and SELECT on the table. The user must hold the role and actually be using it, as primary or secondary role. SHOW GRANTS TO ROLE for both roles shows the difference. Add future grants so next week's tables are readable too. They apply only to objects created afterwards, and when schema-level and database-level future grants exist for the same object type, the schema-level ones win and the database-level ones are ignored for that schema. Existing tables need the ALL TABLES grant as well.

SQL
1GRANT USAGE ON WAREHOUSE bi_wh TO ROLE analyst;
2GRANT USAGE ON DATABASE analytics TO ROLE analyst;
3GRANT USAGE ON SCHEMA analytics.reporting TO ROLE analyst;
4GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reporting TO ROLE analyst;
5GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.reporting TO ROLE analyst;
6GRANT ROLE analyst TO USER new_analyst;
Learn this in the Snowflake course

How to Prepare for Snowflake Interviews

Read a query profile before you suggest anything

Most performance questions are won or lost in the first minute: the candidate who says "bigger warehouse" before looking at partitions scanned, spilling and join row counts loses the room. Practice reading profiles until you can name the dominant operator from a screenshot, using Reading the query profile, then run the full method on Fix a slow query from its profile.

Learn the defaults that interviewers quote back

Know these without looking: AUTO_SUSPEND = 600 for warehouses created with SQL, 1 day of Time Travel by default, 7 days of Fail-safe, 64 days of COPY load metadata against 14 for Snowpipe, ON_ERROR = ABORT_STATEMENT for bulk loads, and 14 days of stream retention extension. Virtual warehouses and Time Travel and Fail-safe cover the ones that matter most.

Write the CDC pipeline from memory

Be able to write a stream, a MERGE that handles the DELETE and INSERT halves of an update, and a task with SYSTEM$STREAM_HAS_DATA on a whiteboard, then explain what happens when the task fails halfway. Streams and change tracking covers the offset rules and Build a CDC pipeline with streams and tasks walks through the full design.

Practice cost questions with real meters

Cost questions reward people who know which view to open: METERING_DAILY_HISTORY by service type, then WAREHOUSE_METERING_HISTORY, then QUERY_HISTORY. Learn which features are serverless and which controls cover them in Credits, auto-suspend and resource monitors, then rehearse the investigation in The bill doubled overnight.

Rehearse access design out loud

Security rounds are design conversations, so practice explaining access roles versus functional roles, why custom roles roll up to SYSADMIN, and why policies should use IS_ROLE_IN_SESSION. Work through Roles and access control and Masking and row access policies, then try Design multi-tenant access as a timed mock.

How Ready Are You for Snowflake Interviews?

1 / 6
Architecture

A dashboard query with a date filter scans 48,000 of 50,000 micro-partitions. What do you do first?

Frequently Asked Questions

What Snowflake topics come up most in data engineering interviews?

Four areas dominate: performance diagnosis from the query profile (pruning, spilling, exploding joins), change handling with streams, tasks and dynamic tables, loading with COPY and Snowpipe, and cost control with warehouses and credits. Security design with roles and policies is common for platform and senior roles. Expect scenarios rather than definitions, such as a slow dashboard, a doubled bill or a CDC pipeline that drops updates, and expect to write the SQL for the fix.

Do I need a SnowPro certification for Snowflake interviews?

No. Certification can help a resume get past a filter, but interviewers test whether you can reason about a real workload: why a query scans every partition, why a stream missed rows, why a resource monitor didn't stop the bill. Someone who has tuned a few real queries and built one CDC pipeline usually does better than someone who has memorized feature lists. Spend your time on hands-on practice and on explaining tradeoffs out loud.

How long does it take to prepare for Snowflake interview questions?

If you already write SQL daily, two to three weeks of focused practice is usually enough: one week on architecture, pruning and the query profile, one on loading and streams and tasks, and a few days on security and cost. Starting from little warehouse experience, plan on five to six weeks. Whatever the timeline, finish by working timed design cases out loud, because that is the format most loops use.

Are Snowflake questions asked in data analyst interviews?

Usually lightly. Analyst interviews focus on SQL, and Snowflake comes up as the dialect: QUALIFY, FLATTEN on JSON, date functions and window functions. Analytics engineers and BI engineers get more, especially on performance, clustering and cost, because they own models that run every hour. Data engineers and platform engineers get the full range, including CDC design, Snowpipe, governance and credit control.

What level of SQL do I need for Snowflake interviews?

Comfortable intermediate SQL plus a few Snowflake-specific pieces. You should write window functions, QUALIFY, MERGE with several WHEN clauses, LATERAL FLATTEN over arrays, and Time Travel queries with AT or BEFORE without looking them up. You should also be able to read a query plan and say which join or scan is the expensive one. Writing correct SQL quickly matters more than knowing rare functions.

Do Snowflake interview questions overlap with general data warehousing questions?

Heavily. Columnar storage, partition pruning, star schemas, slowly changing dimensions, incremental loads and workload isolation apply to every cloud warehouse, and interviewers often start there before moving to Snowflake specifics. Learning the general mechanism first makes the Snowflake version easy to remember: micro-partitions are pruning with automatic partitions, and multi-cluster warehouses are workload isolation with a credit price.

Dan Lee's profile image

Written by

Dan Lee

Data & AI Lead

Dan is a seasoned data scientist and ML coach with 10+ years of experience at Google, PayPal, and startups. He has helped candidates land top-paying roles and offers personalized guidance to accelerate your data career.

Connect on LinkedIn