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
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.
Two queries on the same 2 TB events table each return about 100 rows. One filters on event_date and takes 2 seconds. The other filters on user_id and takes 90 seconds on the same warehouse. Explain the difference with what you would see in the query profile.
MediumArchitecture, Warehouses and Micro-partitions
Sample Answer
Snowflake stores each table as micro-partitions of 50 to 500 MB of uncompressed data, and for each one it records every column's value range and distinct count. Before scanning, it compares the filter with those ranges and skips partitions that can't contain a match. Events are usually loaded in time order, so each partition covers a narrow date range and the event_date filter skips almost all of them. The same partitions each hold thousands of users, so every user_id range overlaps the filter and nothing is skipped. In the profile, the TableScan shows Partitions scanned against Partitions total: a handful out of tens of thousands for the first query, nearly all of them for the second. A bigger warehouse only scans the same partitions at a higher price.
You plan to add a clustering key to a 6 TB events table whose queries filter on country and on ranges of event_ts, a millisecond timestamp. What key do you define, how do you check that it works, and what does it cost?
HardArchitecture, Warehouses and Micro-partitions
Sample Answer
Define the key on expressions that keep ordering but cut cardinality, ordered from lowest to highest cardinality: ALTER TABLE events CLUSTER BY (country, TO_DATE(event_ts)). A raw millisecond timestamp is close to unique, so Snowflake can't group rows by it usefully, and Snowflake advises staying at three or four key columns. Check health with SELECT SYSTEM$CLUSTERING_INFORMATION('events'), which reports average clustering depth (lower is better) and a depth histogram, then confirm with Partitions scanned in real query profiles. Cost comes from two places. Automatic Clustering runs on serverless compute that bills credits whenever it reclusters, and the micro-partitions it rewrites are kept for Time Travel and Fail-safe, so storage grows on tables with heavy DML. Keys pay off on large tables that are queried often and changed rarely.
A Medium warehouse shows about 11 hours of running time per day, but the queries it ran add up to about 40 minutes of execution. What is going on, and how do you fix it without hurting the dashboards that use it?
EasyArchitecture, Warehouses and Micro-partitions
Sample Answer
Warehouses bill per second while running, with a 60-second minimum each time they start, and a Gen1 Medium costs 4 credits an hour. A warehouse created with SQL defaults to AUTO_SUSPEND = 600, so every dashboard query keeps it running for ten idle minutes. Queries spread across the day turn 40 minutes of work into most of a day of billed time. Lower AUTO_SUSPEND toward the real gaps between queries, and test whether a Small runs the same queries nearly as fast at half the rate. The trap is chasing the lowest possible value. Suspending drops the warehouse's local disk cache, so the next query reads from remote storage, and every resume pays the 60-second minimum again. Compare query times and PERCENTAGE_SCANNED_FROM_CACHE in QUERY_HISTORY before and after the change.
Cloud services credits on your bill used to be close to zero. This month they are about 25% of warehouse credits most days. What kinds of workloads cause that, and how do you find the source?
HardArchitecture, Warehouses and Micro-partitions
Sample Answer
Cloud services usage is billed only for the part of a day's usage that exceeds 10% of that day's warehouse usage, so a near-zero line is normal. A high ratio means the account does a lot of compiling, metadata and coordination work compared with scanning. Typical causes are thousands of tiny queries an hour from a BI tool or a polling script, very complex queries with long compile times, frequent SHOW and INFORMATION_SCHEMA calls, many single-row DML statements, and COPY commands that list stages holding huge numbers of files. Rank them in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY by CREDITS_USED_CLOUD_SERVICES, grouped by query type, user and warehouse. That view can lag by up to 45 minutes, and its per-query numbers don't include the daily 10% adjustment.
A filter WHERE order_date >= (SELECT MAX(load_date) FROM etl_watermark) makes a query scan every partition of orders. The same query with a literal date scans 2% of them. Why, and how do you rewrite it?
MediumArchitecture, Warehouses and Micro-partitions
Sample Answer
Pruning happens when Snowflake compares a predicate with each micro-partition's min and max values. The documentation is explicit that predicates containing a subquery aren't used for pruning, even when the subquery returns a single constant. The planner doesn't know the watermark's value when it decides which partitions to read, so it reads them all and filters afterwards. Compute the watermark first and pass it as a value: set a session variable, bind it as a parameter from the orchestrator, or use a Snowflake Scripting variable. Then confirm in the query profile that Partitions scanned dropped, rather than assuming it. The same check applies whenever a filter value comes from another table, which is common in incremental loads.
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.
The profile of a nightly aggregation shows 180 GB of bytes spilled to local storage and 60 GB spilled to remote storage. What does that mean, and what do you try, in order?
MediumQuery Performance, Caching and Warehouse Sizing
Sample Answer
An operator's intermediate data (usually a join's hash table, a large aggregation or a sort) didn't fit in the warehouse's memory. Snowflake wrote the overflow to the nodes' local disk, and when that filled up, to remote storage. Remote spilling is the expensive part. First shrink what reaches the operator: filter and project earlier, aggregate before joining, and check whether a join is multiplying rows. Next, process in smaller batches, such as a day at a time. Then size up. Each size step doubles the compute and gives more memory and local disk, so a query that spills on a Medium may fit on a Large and finish in well under half the time at a similar cost. Sizing up first hides a bad join.
A revenue query joins orders (5M rows) to order_status_history on order_id and reports totals 3.4 times too high. The profile's Join operator outputs 17M rows. Diagnose it and write the fix.
HardQuery Performance, Caching and Warehouse Sizing
Sample Answer
A join that outputs far more rows than it receives means the key isn't unique on one side. Each order has several status rows, so it is repeated once per status change and SUM(amount) counts it several times. Snowflake's profile documentation names this pattern: a Join producing many more rows than it consumes, often with high join time. Reduce the history to one row per order before joining, here the latest status, using QUALIFY. Then add a uniqueness check on the join key where the history table is built, so a new duplicate fails loudly. The trap is patching the symptom with DISTINCT, which may fix a count in one query while every sum stays inflated.
SQL
1WITH latest_status AS(2SELECT order_id,status3FROM order_status_history
4 QUALIFY ROW_NUMBER()OVER(5PARTITIONBY order_id ORDERBY changed_at DESC)=16)7SELECT s.status,SUM(o.amount)AS revenue
8FROM orders o
9JOIN latest_status s ON s.order_id = o.order_id
10GROUPBY s.status;
An engineer rewrites a dashboard query, runs it twice, and reports that it went from 40 seconds to 0.2 seconds. In production it is still slow after every hourly load. What fooled them, and how should they benchmark?
MediumQuery Performance, Caching and Warehouse Sizing
Sample Answer
The second run came from the result cache. Snowflake keeps a query's result for 24 hours after its last use, up to 31 days from the first run, and reuses it when the new query text matches exactly, the underlying data and micro-partitions haven't changed, the role can read the tables and the query has no non-reusable functions such as RANDOM() or UUID_STRING(). A reused result shows a single Query Result Reuse operator in the profile. The hourly load changes the table, so the first run after it executes for real. To benchmark, run ALTER SESSION SET USE_CACHED_RESULT = FALSE, decide whether you are measuring cold reads (suspend the warehouse first, which drops its local cache) or warm ones, and compare partitions and bytes scanned, which no cache can flatter.
A support tool looks up one customer at a time in a 3 TB interactions table by customer_email. Each lookup scans thousands of partitions and takes 30 seconds. The table is clustered by interaction_date for analytics. What do you propose?
MediumQuery Performance, Caching and Warehouse Sizing
Sample Answer
This is a selective point lookup on a column the table isn't ordered by, which is what the search optimization service is for. ALTER TABLE interactions ADD SEARCH OPTIMIZATION ON EQUALITY(customer_email) builds a search access path that lets Snowflake skip partitions that can't hold the value, without changing the clustering the analytics queries depend on. SHOW TABLES reports the build in its search_optimization_progress column. It needs Enterprise Edition. Building and maintaining the access path uses serverless credits plus storage, and enabling it on a multi-terabyte table can cause a short spike in credits, so estimate first with SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS. Configure the specific column rather than the whole table. For LIKE '%term%' searches, use ON SUBSTRING(col) instead of EQUALITY.
You move a 4-minute query from a Medium to a Large warehouse and it only drops to 3 minutes 45 seconds. Give the likely reasons, and say what you check next.
HardQuery Performance, Caching and Warehouse Sizing
Sample Answer
A size step doubles the compute, so it helps work that spreads across nodes: large scans, big joins and aggregations, and spills that now fit in memory. A query like that should finish in roughly half the time for about the same credits. When it doesn't, find where the time actually goes. QUERY_HISTORY splits it into COMPILATION_TIME, QUEUED_OVERLOAD_TIME and EXECUTION_TIME. Compilation runs in cloud services and doesn't shrink with warehouse size. Queuing is a concurrency problem. In the profile, check whether one operator dominates and whether the scan reads only a few partitions, which leaves little to parallelize, or whether a single join key holds most of the rows. Snowflake's own guidance is that larger isn't necessarily faster for small queries. Revert the size and fix the plan instead.
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.
A vendor's CSV feed sometimes contains a few malformed rows, and today the whole load aborts. The business wants good rows loaded and bad rows kept for review. How do you configure the load, and which default are you changing?
MediumLoading Data and Semi-structured Data
Sample Answer
Bulk COPY INTO defaults to ON_ERROR = ABORT_STATEMENT, so one bad row fails the statement. Snowpipe, by contrast, defaults to SKIP_FILE. Use ON_ERROR = CONTINUE to load every row that parses. SKIP_FILE_<num> or 'SKIP_FILE_<num>%' instead rejects a whole file past an error threshold, which is often safer, because a file with 40% bad rows usually means the format changed. Dry-run a new feed with VALIDATION_MODE = RETURN_ERRORS. After a real load, the VALIDATE table function returns the rows the last COPY rejected, so you can write them to a quarantine table and alert on the count. The trap is CONTINUE with no monitoring: rows go missing quietly and nobody notices until a total looks low.
Click events land in S3 as small JSON files every minute, and product wants them queryable within a couple of minutes. Compare a scheduled COPY, Snowpipe and Snowpipe Streaming for this, including how each is billed.
MediumLoading Data and Semi-structured Data
Sample Answer
A COPY scheduled every minute works, but each run resumes a warehouse and pays at least 60 seconds of it. Snowpipe with AUTO_INGEST = TRUE reacts to the bucket's event notifications and loads files on serverless compute, typically within a minute, billed per GB loaded with no warehouse. It keeps 14 days of load history per pipe. Snowflake suggests compressed files of roughly 100 to 250 MB, or about one new file a minute for slow sources. Snowpipe Streaming skips files entirely: an SDK or the Kafka connector writes rows through channels, data can be queryable in seconds, and named channels use offset tokens for exactly-once recovery. It is billed per uncompressed GB ingested. For minute-level files, Snowpipe fits. For sources that produce rows, use Streaming.
A VARIANT column v holds order events. SELECT v:customer.id FROM events WHERE v:status = 'shipped' returns nothing, although shipped orders are clearly in the raw files. Give the likely causes.
MediumLoading Data and Semi-structured Data
Sample Answer
Start with the path. Column names are case-insensitive, but element names in a path are case-sensitive, so v:status won't match a key written as Status. The field may also be nested, as in v:order.status, or sit inside an array that needs FLATTEN. Then check how the data was loaded. If each file is one JSON array and the file format lacks STRIP_OUTER_ARRAY = TRUE, a whole file becomes one row whose top level is an array, and every object path returns NULL. If a loader inserted the JSON as a string, v holds a string rather than an object, and you need PARSE_JSON. Once results appear, cast extracted values with ::STRING or ::NUMBER, and remember that a JSON null is stored as a VARIANT null, which differs from SQL NULL.
Each order document has an items array. Write a query that returns one row per line item, and explain what happens to orders whose items array is empty or missing.
EasyLoading Data and Semi-structured Data
Sample Answer
LATERAL FLATTEN turns elements into rows and returns the columns SEQ, KEY, PATH, INDEX, VALUE and THIS. For an array, INDEX is the element's position and VALUE is the element itself, which you cast field by field. The trap is the default OUTER => FALSE. An input that can't be expanded, because the array is empty or the path doesn't exist, produces no rows at all, so those orders vanish and order counts built on this query come out low. OUTER => TRUE keeps exactly one row for them, with NULL in INDEX and VALUE. Cast every extracted value with :: so the output has real types instead of VARIANT.
SQL
1SELECT2 o.v:order_id::NUMBER AS order_id,3 i.indexAS line_no,4 i.value:sku::STRING AS sku,5 i.value:qty::NUMBER AS qty,6 i.value:price::NUMBER(10,2)AS price
7FROM raw_orders o,8 LATERAL FLATTEN(INPUT => o.v:items,OUTER=>TRUE) i;
A 5 TB table stores events in a single VARIANT column. Filters on v:amount scan far more than filters on other fields, and date filters on v:event_ts are slow too. What is Snowflake doing under the hood, and when do you move fields into typed columns?
HardLoading Data and Semi-structured Data
Sample Answer
Snowflake extracts as many VARIANT elements as it can into columnar form, by default up to 200 per partition, and those scan much like normal columns. Elements are not extracted when they contain JSON null values or hold more than one type across rows, for example amount as a number in some events and a string in others. Queries on those must read the whole document. JSON also has no date type, so event_ts is stored as a string, which Snowflake documents as slower and larger than a native value. Move the fields you filter and join on into typed columns at load time and keep the raw VARIANT for the rest. Fix mixed types at the source, and use STRIP_NULL_VALUES when nulls only mean missing.
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.
Write a MERGE that applies a standard stream on customers to a replica table dim_customers, handling inserts, updates and deletes. What does an UPDATE look like in the stream?
HardStreams, Tasks, Dynamic Tables and Time Travel
Sample Answer
A standard stream returns the net change per row plus three metadata columns prefixed with METADATA$. ACTION is INSERT or DELETE, ISUPDATE marks rows that came from an UPDATE, and ROW_ID tracks a row over time. An UPDATE appears as a pair: a DELETE with the old values and an INSERT with the new ones, both flagged ISUPDATE = TRUE. Merge both halves naively and the same key matches two source rows, which Snowflake rejects as a nondeterministic merge under the default ERROR_ON_NONDETERMINISTIC_MERGE = TRUE. Drop the DELETE half of each update, then send true deletes to WHEN MATCHED ... DELETE. Inside a task, the offset advances only if the MERGE commits, so a failed run is retried with the same changes.
You create a task that runs that MERGE every five minutes with WHEN SYSTEM$STREAM_HAS_DATA('customers_stream'). A day later the replica hasn't changed at all. What did you forget, and what keeps the task cheap when there's nothing to do?
MediumStreams, Tasks, Dynamic Tables and Time Travel
Sample Answer
Tasks are created suspended. Nothing runs until ALTER TASK merge_customers RESUME. In a task graph, children must be resumed too, or you call SYSTEM$TASK_DEPENDENTS_ENABLE('root_task') to resume the root and its dependents together. TASK_HISTORY would have shown no runs at all. The WHEN condition keeps it cheap. It is evaluated in cloud services, without a warehouse, and the run is skipped when the stream is empty, with only a nominal cloud services charge. Omitting WAREHOUSE makes the task serverless (the owner needs the EXECUTE MANAGED TASK privilege), which avoids paying a 60-second warehouse minimum for a 5-second MERGE. By default OVERLAP_POLICY = NO_OVERLAP, so a scheduled run is skipped while the previous one is still going.
Your team keeps a revenue mart five minutes fresh with three stream-and-task pairs. A colleague proposes replacing them with dynamic tables. What changes, and what do you check before agreeing?
MediumStreams, Tasks, Dynamic Tables and Time Travel
Sample Answer
A dynamic table is a query plus a TARGET_LAG and a WAREHOUSE, and Snowflake works out the refresh schedule and the incremental logic. Chained dynamic tables refresh in dependency order, and an intermediate one can use TARGET_LAG = DOWNSTREAM. Lag is a freshness target rather than a schedule, and the minimum is 60 seconds. Check the refresh mode first. With the default REFRESH_MODE = AUTO, Snowflake picks INCREMENTAL or FULL once, at creation, and constructs such as EXCEPT or PERCENTILE_CONT push it to FULL. SHOW DYNAMIC TABLES explains the choice in refresh_mode_reason. A full refresh every few minutes on a big table can cost far more than the tasks did. Logic that needs procedural steps or exact timing still belongs in streams and tasks.
At 14:05 someone runs UPDATE orders SET status = 'void' without a WHERE clause. It is now 14:20 and new orders have kept arriving. How do you recover?
EasyStreams, Tasks, Dynamic Tables and Time Travel
Sample Answer
Use Time Travel. Find the bad statement's query ID in query history and read the table as it was just before it with BEFORE(STATEMENT => '<query_id>'), which is more precise than a timestamp or an OFFSET. Don't swap the whole table back, because that would lose the orders inserted since 14:05. Clone the old version and restore only status for the orders that existed then. If the app legitimately changed some statuses after 14:05, reconcile those separately. Time Travel works only within DATA_RETENTION_TIME_IN_DAYS, which defaults to 1 and can go up to 90 on Enterprise Edition for permanent tables. Transient and temporary tables get at most 1 day.
SQL
1CREATETABLE orders_before CLONE orders
2 BEFORE (STATEMENT =>'01bf3a2c-0002-7d41-0000-0012a7f3b2e1');34UPDATE orders o
5SETstatus= b.status6FROM orders_before b
7WHERE o.order_id = b.order_id;
A colleague says: "We have Fail-safe, so we don't need to worry about dropped tables after Time Travel runs out." Respond, and explain how zero-copy cloning fits into a safe migration plan.
MediumStreams, Tasks, Dynamic Tables and Time Travel
Sample Answer
Fail-safe is a 7-day, non-configurable period after Time Travel ends, but it exists for Snowflake's disaster recovery. You can't query it, recovery is best-effort through a support case, and it still costs storage. Transient tables, which dbt creates by default on Snowflake, have no Fail-safe at all. Within retention, UNDROP TABLE restores a dropped table. For a migration, clone first. CREATE DATABASE prod_backup CLONE prod is a metadata operation that shares the existing micro-partitions, so it's fast and adds storage only as either side changes. Know the rules. The cloned database itself doesn't inherit grants on the source database, though cloned child objects do. Tasks in the clone start suspended, pipes that reference internal stages aren't cloned, and unconsumed changes in cloned streams are inaccessible.
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
1GRANTUSAGEON WAREHOUSE bi_wh TO ROLE analyst;2GRANTUSAGEONDATABASE analytics TO ROLE analyst;3GRANTUSAGEONSCHEMA analytics.reporting TO ROLE analyst;4GRANTSELECTONALLTABLESINSCHEMA analytics.reporting TO ROLE analyst;5GRANTSELECTON FUTURE TABLESINSCHEMA analytics.reporting TO ROLE analyst;6GRANT ROLE analyst TOUSER new_analyst;
Design a role hierarchy for a team with raw, staging and mart schemas, data engineers who build everything, and analysts who only read marts. Where do your custom roles connect to the system roles?
MediumSecurity, Governance and Cost Control
Sample Answer
Use two kinds of roles. Access roles hold object privileges, for example MARTS_READ with USAGE and SELECT on the marts schema and RAW_RW with write privileges on raw. Functional roles match jobs: ANALYST receives MARTS_READ, DATA_ENGINEER receives the read-write roles for all three schemas, and users get only functional roles. Snowflake treats both kinds identically; the split is a convention that keeps grants auditable. Grant the top functional roles to SYSADMIN, as Snowflake recommends. Otherwise not even ACCOUNTADMIN can modify or drop the objects those roles own. Keep ACCOUNTADMIN to a small group of at least two people, and never make it anyone's default role. Managed access schemas move grant decisions from each object owner to the schema owner.
Support agents should see only the domain of a customer's email, a PII_READER role should see the full address, and everyone else a fixed mask. Write the masking policy and justify the role function you use.
MediumSecurity, Governance and Cost Control
Sample Answer
A masking policy is a schema-level object that receives the column value and returns what the session may see. It is attached per column and evaluated at query time, so one policy covers every table, view and tool that reads the column. It requires Enterprise Edition. Use IS_ROLE_IN_SESSION rather than CURRENT_ROLE() IN (...). CURRENT_ROLE() returns only the primary role, so a user who has PII_READER through the hierarchy or as a secondary role would be masked by mistake, while IS_ROLE_IN_SESSION checks the active primary and secondary roles and what they inherit. Masked values also flow into joins and GROUP BY. If analysts must join or count on email, mask with a deterministic hash such as SHA2(val) instead of a constant.
A SaaS company stores all tenants in one events table with a tenant_id column, and each tenant's analysts use their own role. Design row-level isolation, and name the performance and correctness traps.
HardSecurity, Governance and Cost Control
Sample Answer
Attach a row access policy that returns TRUE only for rows the session may see, driven by a mapping table from tenant to role. The policy body runs with the policy owner's privileges, so tenant roles never need access to the mapping table. Every query, view and BI tool on the table then gets the filter, unlike a WHERE in a view that someone can query around. Traps: each lookup adds work to every query, and Snowflake notes that simple expressions run faster than mapping-table subqueries, so keep the mapping small. Use IS_ROLE_IN_SESSION so inherited roles work, and give internal admins an explicit branch. Test as every role, because an empty result looks exactly like a correct one.
Finance wants a hard monthly cap of 5,000 credits, so you create an account-level resource monitor that suspends at 100%. Next month the bill is 6,200 credits and the monitor never fired. How is that possible, and what do you set up instead?
MediumSecurity, Governance and Cost Control
Sample Answer
Resource monitors only track and control warehouses. Serverless features such as Snowpipe, Automatic Clustering, search optimization and serverless tasks bill outside them, and so do AI services. Budgets are the tool for that spend. SUSPEND also waits for running statements to finish, so a long query can carry usage past the quota. Only SUSPEND_IMMEDIATE cancels them. Put a monitor on each warehouse with notify, suspend and suspend-immediate thresholds, add budgets for serverless spend, and lower STATEMENT_TIMEOUT_IN_SECONDS from its two-day default so a runaway query can't run for 48 hours. Monitors count the cloud services credits used by the warehouses they cover, without the daily 10% adjustment, so they can fire earlier than the bill suggests.
Your Snowflake bill doubled overnight and nobody launched anything new. Walk through how you find the cause in the first hour.
HardSecurity, Governance and Cost Control
Sample Answer
Split the bill by meter before guessing. SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY breaks credits down by SERVICE_TYPE, which separates warehouses from serverless features such as Automatic Clustering, Snowpipe and search optimization. If warehouses grew, WAREHOUSE_METERING_HISTORY shows which warehouse and which hours. QUERY_HISTORY for that warehouse then tells you whether there are more queries, longer queries, or a warehouse that stopped suspending. Usual culprits: a warehouse resized for a backfill and never sized back, AUTO_SUSPEND raised or turned off, a BI tool polling every minute, a task moved from hourly to every minute, a new clustering key reclustering a huge table, or a query that now spills. The metering views lag by up to 3 hours, so the latest hours may look low.
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.
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.
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.