Snowflake Interview Questions and Answers: SQL and Scenarios
Table of Contents
Prepare to explain your decisions, not just recite definitions. These Snowflake interview questions and answers cover platform fundamentals, data loading, SQL, incremental pipelines, security, performance and production troubleshooting. The guide includes 80 distinct questions, with 15 SQL exercises and worked scenarios for freshers and experienced candidates.
By Brolly Academy Team. Updated 11 October 2026. Examples use synthetic practice data, not a customer deployment. Product-specific explanations link to Snowflake documentation; feature availability can depend on your edition, region and configuration.
Where should you start for your experience level?
- Freshers: Start with questions 1-20, then write the SQL in 41-50 without copying. Explain one small project from source data to a checked result.
- 2-4 years of experience: Add questions 21-40 and 51-70. Be ready to discuss incremental loads, permissions, data quality and how you investigated a failure.
- 5+ years of experience: Focus on architecture trade-offs, cost, observability, incident response and migration decisions in 56-75. Use evidence from work you actually performed.
- Interview tomorrow: Revise warehouse scaling, loading, MERGE, streams, recovery and access control. Practise two SQL problems and a two-minute project explanation before expanding the question count.
Experience bands are preparation paths, not a universal hiring syllabus. Read the job description first. A developer, administrator and data engineer can be tested on different parts of Snowflake.
- 1-20: Fundamentals and architecture
- 21-40: Loading, pipelines, security and performance
- 41-55: SQL exercises with expected results
- 56-70: Scenario-based interview questions
- 71-80: Project discussion, preparation and FAQs
Need guided labs before interview practice? Review the syllabus, training format and project coverage in our Snowflake Training in Hyderabad course. The answers and exercises below remain available without submitting an enquiry.
Snowflake interview questions for freshers: fundamentals and architecture
1. What is Snowflake, and how would you explain it in an interview?
Snowflake is a managed cloud data platform used to store, process, analyse and share data. For a data-engineering interview, begin with its separation of storage and compute: different workloads can query the same governed data using separate warehouses. Then give a concrete example, such as loading daily order files and building a sales reporting table. Avoid claiming that using Snowflake automatically makes every workload faster or cheaper.
2. What are the three main layers of Snowflake architecture?
For standard analytical workloads, explain database storage, query processing and cloud services. Storage holds compressed columnar data; virtual warehouses supply compute; cloud services coordinate activities such as authentication, metadata and query planning. Follow a query through those responsibilities instead of memorising three labels. For a visual explanation, see our Snowflake architecture guide. Separate compute reduces resource contention, but it does not remove all shared dependencies or concurrency issues.
3. What is a virtual warehouse? Is it where the data is stored?
A virtual warehouse supplies compute resources for operations such as SQL execution and bulk loading. It is not the permanent storage location for your database tables. For example, an ingestion warehouse and a BI warehouse can access the same tables while their compute is managed separately. In an answer, distinguish warehouse privileges, size, suspension and workload isolation from database and schema design.
4. What is the difference between scaling up and scaling out?
Scaling up increases the size of a warehouse and can help an individual resource-constrained query. Scaling out adds clusters in a multi-cluster warehouse to handle concurrency. A queue of many dashboard requests suggests a different investigation from one query that spills heavily. Multi-cluster support and configuration depend on edition. Neither option fixes an incorrect join or guarantees proportional speed improvement.
Reference: warehouse load and queuing.
5. What are auto-suspend and auto-resume?
Auto-suspend stops an idle warehouse after its configured idle period; auto-resume starts it when work requires it. The setting is a workload decision: rapid suspension saves idle compute but discards the warehouse data cache. Frequent dashboard requests and occasional development queries may need different settings. Explain how you would compare credits, cold-start latency and response times before selecting an interval.
Reference: warehouse cache and auto-suspension.
6. What is the relationship between databases, schemas and tables?
A database contains schemas, and a schema organises objects such as tables, views and stages. A fully qualified table name is DATABASE.SCHEMA.TABLE. An interview follow-up often tests session context: an unqualified name can resolve differently when the current database or schema changes. State the context explicitly in deployment scripts, and treat environment separation and permissions as design decisions rather than naming conventions alone.
7. What are micro-partitions and partition pruning?
Standard Snowflake tables organise data into micro-partitions and maintain metadata about their contents. Pruning uses that metadata to avoid scanning partitions that cannot satisfy a filter. A selective date predicate can therefore scan less data when the relevant values are well organised. Check partitions scanned in the query profile instead of asserting that every filter prunes efficiently. This explanation concerns standard tables, not every Snowflake table type.
Reference: micro-partitions and pruning.
8. When would you consider a clustering key?
Consider clustering when repeated selective queries on a large table scan too many partitions and the expected benefit justifies maintenance cost. Choose keys from observed query patterns, not simply the primary key or the column with the most distinct values. First compare scan behaviour, query latency and maintenance credits on representative workloads. Small tables and workloads that already prune well may not benefit from an explicit clustering key.
9. How do permanent, transient and temporary tables differ?
A permanent table is the usual choice when long-lived data needs recovery protection. A transient table persists across sessions but has no Fail-safe; use it only when the recovery trade-off is acceptable. A session-scoped temporary table disappears when its session ends and is useful for isolated work. Do not describe temporary data as free: it can consume storage while it exists. Choose the type from the data lifecycle, not just the loading method.
Reference: table types and recovery differences.
10. How is Time Travel different from Fail-safe?
Time Travel lets authorised users query or restore supported historical data within the configured retention period. Fail-safe is a separate Snowflake-managed recovery mechanism for eligible permanent data after that period; it is not a normal SQL-accessible backup. Temporary and transient tables do not have Fail-safe. Retention depends on table type, edition and settings, so an answer that promises 90 days for every table is incorrect.
References: Time Travel and historical storage and Fail-safe.
11. What is zero-copy cloning, and does a clone remain synchronised?
A zero-copy clone begins from a snapshot without immediately copying all underlying data. After creation, source and clone can change independently; the clone is not an automatically synchronised replica. New or retained micro-partitions can create storage costs as either side changes. A useful interview example is preparing an isolated test copy before a transformation change. See our zero-copy cloning walkthrough for the concept and use cases.
12. What is the difference between result reuse and warehouse caching?
Persisted query-result reuse can return an eligible previous result without rerunning the full query. A running warehouse can separately cache table data for subsequent queries. These mechanisms have different eligibility and lifecycle rules. A fast second run does not prove your SQL improved: compare equivalent data, warehouse conditions and cache settings. Result reuse still depends on access checks and unchanged relevant inputs; it is not public access to another user’s results.
References: persisted query results and warehouse data cache.
13. Does Snowflake use indexes or enforce primary keys?
Qualify the table type. Standard analytical tables do not use conventional user-created B-tree indexes; their primary-key, unique and foreign-key declarations generally do not enforce those relationships. Hybrid tables support indexes and enforced key constraints for operational workloads. Do not turn the standard-table rule into a claim that Snowflake never supports indexes. For a standard ingestion table, explicitly test duplicate keys rather than trusting a PRIMARY KEY declaration.
Reference: standard versus hybrid tables.
14. Is Snowflake only for OLAP, or can it support transactional use cases?
Standard columnar tables are designed for analytical workloads. Hybrid tables address supported operational use cases requiring low-latency indexed access and row-level concurrency. In an interview, describe the actual access pattern, transaction requirements, constraints and regional availability before choosing. Do not claim that every existing OLTP application can be moved unchanged onto standard warehouse tables.
Reference: hybrid-table availability and limitations.
15. What is a stage, and how do internal and external stages differ?
A stage identifies a file location used in loading or unloading. Internal stages use Snowflake-managed storage; external stages reference supported cloud storage. Internal stages include user, table and named stages. Creating a stage alone does not make files load automatically: a loading command or configured ingestion mechanism is still needed. In a project explanation, name the file location, access mechanism, file format and target table separately.
16. What does a file format object do?
A file format describes how staged data should be parsed, including its type and options such as delimiters, headers, quoting and compression. Reusing a named format helps keep loads consistent. For a CSV with quoted commas, an incorrect enclosure setting can produce a column-count error even when the file looks correct in a text editor. Examine representative source rows and validate parsing before accepting a load as successful.
17. What are VARIANT, OBJECT and ARRAY used for?
These types support semi-structured data. VARIANT can represent values with their associated types, including nested structures; OBJECT represents key-value structures and ARRAY represents ordered collections. Explain how you would extract known fields and cast them into stable reporting types. Keeping every field in raw JSON indefinitely can make downstream contracts unclear, so distinguish flexible ingestion from curated analytics.
18. How do you query JSON arrays using FLATTEN?
FLATTEN turns elements of an array or object into rows. A lateral use lets the expansion refer to the current parent row. Keep the parent business key, explicitly cast extracted fields, and check whether row multiplication is expected. Empty or missing arrays need a deliberate decision about preserving the parent row. Question 50 includes a small example whose two output rows can be inspected directly.
Reference: FLATTEN syntax and output.
19. How do a view and a materialized view differ?
An ordinary view stores a query definition rather than a separate precomputed result. A materialized view maintains results for supported definitions and can trade extra storage and maintenance work for faster reads. It is not automatically the best answer for every slow query. Explain the repeated query pattern, definition restrictions, expected change rate and measured benefit before recommending it.
Reference: choosing materialized views or pipeline tools.
20. Is a Snowflake schema the same as the Snowflake platform?
No. A snowflake schema is a dimensional modelling pattern in which dimension data is normalised across related tables. The Snowflake platform can store many models, including star and snowflake schemas. For an interview, state the fact-table grain first, then explain dimension relationships, join complexity and reporting needs. Confusing the product name with this modelling pattern makes an otherwise good answer difficult to follow.
Snowflake interview questions for experienced candidates: engineering and operations
21. When would you choose COPY INTO, Snowpipe or Snowpipe Streaming?
Use warehouse-based COPY INTO for a controlled file batch; consider Snowpipe when files arrive continuously and event-driven loading fits the requirement. Snowpipe Streaming ingests rows or events rather than relying on the same file-loading workflow. Choose using source format, acceptable freshness, volume, operational ownership and cost. “Real time” is not a sufficient requirement: ask how many seconds or minutes of delay the consumer can tolerate.
Reference: Snowpipe loading comparison.
22. How do you investigate a failed COPY INTO load?
Start with the actual error, affected files and target definition. Check parsing settings, column types, date formats, null handling and access to the stage. Use validation where supported to investigate before changing the load policy. Then reconcile input, loaded and rejected rows. ON_ERROR = CONTINUE is a business decision that can leave incomplete data; it is not a universal fix for a failing pipeline.
Reference: COPY INTO validation and error options.
23. Does Snowflake loading automatically prevent all duplicates?
No. File-load tracking is not the same as business-key deduplication. Renamed files, overlapping ingestion paths, expired load history or a forced reload can still require duplicate handling. Define a stable event or business key, retain source metadata and make downstream writes replay-safe. In an interview, distinguish loading the same file twice from receiving two legitimate events for the same customer.
Reference: FORCE and file-load behaviour.
24. What should you check before loading from Amazon S3 or Azure storage?
Confirm the approved storage location, identity or integration, permitted paths and cloud-side permissions. Then check Snowflake privileges, the stage URL, the file format and which files should be ingested. Do not paste access keys into interview scripts or repositories. A useful diagnosis separates cloud access failure from Snowflake object access failure and parsing failure; each has a different owner and remedy.
25. What is the difference between ETL and ELT in a Snowflake pipeline?
ETL transforms data before loading it into the destination; ELT loads data and transforms it within the destination platform. A Snowflake pipeline often keeps raw data and creates curated tables using SQL. Neither pattern removes the need for source validation, access controls or replay. Explain where transformations happen, why that location was chosen, and how you preserve traceability between source records and reporting outputs.
26. What does a Snowflake stream store?
A stream tracks an offset against a supported source and exposes changes since that position. It is not a second table containing a permanent copy of every source event. That distinction matters when explaining retention, replay and recovery. Select the stream type for the required change semantics, and preserve an independent event history if the business needs an immutable audit log.
Reference: stream offsets and change records.
27. Does selecting from a stream consume its records?
A SELECT by itself does not advance the stream offset. Consuming the stream through a qualifying DML transaction advances it when that transaction commits. This means a successful preview query is different from successful processing. Design the target write and offset advancement together, and give independent consumers separate streams when they need independent progress.
Reference: transactional stream consumption.
28. What is a task, and why might a newly created task not run?
A task executes SQL or a stored procedure on a schedule or an eligible trigger. Creation alone is not the whole deployment: review its suspended state, owner privileges, compute configuration, schedule or condition, dependencies and run history. State whether it uses managed serverless compute or an assigned warehouse. Avoid troubleshooting a task only with your interactive administrator role, because the task execution context can differ.
Reference: task setup and execution.
29. When would you use dynamic tables instead of streams and tasks?
For a supported declarative SQL transformation, a dynamic table lets you describe the desired result and freshness goal. Streams and tasks are useful when you need explicit procedural control, complex orchestration or custom side effects. Target lag is not the same as a guaranteed execution interval. Match the currently supported dynamic-table mode to the requirement instead of assuming that every pipeline uses the same refresh behaviour.
Reference: pipeline decision guide.
30. How does MERGE work, and what can make it unsafe?
MERGE matches a source dataset to a target and applies specified update, delete or insert actions. First define the target grain and make the source deterministic for that key. Multiple source rows can produce ambiguous updates or duplicate inserts. A stronger answer also considers stale events, null keys, retries and whether an unchanged row needs to be rewritten. Question 51 shows an explicit latest-row rule.
Reference: MERGE duplicate-source behaviour.
31. How would you implement SCD Type 1 and Type 2?
Type 1 overwrites the current attributes and does not retain earlier values in the dimension. Type 2 preserves versions with a business key, effective interval and usually a current-row indicator. For Type 2, close the previous interval and insert the new version consistently; handle same-day changes, late events and retries. Test that intervals do not overlap and that one business key has no more than one current row.
32. What is the difference between a stored procedure and a UDF?
A UDF is typically used to calculate a value within a query. A stored procedure groups an operation or workflow and is invoked with CALL. Procedure handlers support SQL through Snowflake Scripting, JavaScript, Python, Java and Scala, subject to their documented capabilities. Explain why you chose procedural logic instead of ordinary set-based SQL, and how you handle errors and execution rights.
References: stored procedures overview and supported handler languages.
33. What is Snowpark, and when would you use it?
Snowpark provides APIs for supported languages to work with Snowflake data and execution. It can be useful when a team needs reusable programmatic transformations or Python-oriented processing alongside SQL. Do not assume that collecting a large result into a client is equivalent to processing it in Snowflake. Describe the execution location, dependencies, data movement and operational constraints for your specific implementation.
34. How would you explain role-based access control?
Grant privileges to purpose-specific roles and assign those roles through an appropriate hierarchy. A reporting role may need warehouse usage, access to its database and schema, and SELECT on approved objects; it does not need broad administration. Explain both positive and negative tests: the role should read the permitted table and fail to read restricted data. Separate object ownership and grant administration from everyday application work.
Reference: access-control best practices.
35. Why can a user see a database but still fail to query a table?
Access is checked across multiple objects and the active execution context. Check the active role, relevant secondary roles, database and schema usage, table or view privileges, warehouse access and any policy affecting the result. Inspect the fully qualified object name to avoid diagnosing the wrong environment. Give the minimum missing grant through the authorised administrator; do not solve every error by assigning ACCOUNTADMIN.
36. How do masking policies and row access policies differ?
Masking controls how a column value is presented; row access policies control which rows are visible under their conditions. For example, one role may see a redacted email, while a regional role may see only its region’s customers. Test authorised and unauthorised identities, joins and downstream views. A policy is only as useful as its logic and the access paths that were actually tested.
Reference: policy-protected data sharing.
37. How does secure sharing differ from exporting a file?
A share exposes approved data objects for governed consumption rather than sending an unmanaged CSV copy. Explain the consumer, approved columns and rows, permissions, region constraints and how access will be revoked. Reader accounts serve supported consumers without their own full Snowflake account; the provider must consider the associated compute responsibility. Do not present internal departments and geographical teams as official technical share types.
Reference: reader-account configuration and cost ownership.
38. How do you investigate a slow query?
Capture the query ID and compare it with a known-good run under similar conditions. Separate compilation, queuing and execution time. Examine the query profile for excessive scans, join expansion, spilling and expensive operators; check changes in data volume or SQL. Change one variable at a time and compare both runtime and resource usage. A larger warehouse is a candidate experiment, not the diagnosis.
Reference: query workload investigation.
39. What is the difference between query tuning and cost optimisation?
Query tuning targets useful work completed efficiently; cost optimisation also examines idle warehouses, workload scheduling, storage retention and paid services. A faster query on a larger warehouse can still cost more overall. Establish a baseline with comparable data, output correctness, elapsed time and credits. Record the workload rather than publishing an unqualified claim such as “90% faster” without a reproducible comparison.
40. Can a resource monitor stop every type of Snowflake spending?
No. Resource monitors apply to warehouse-related consumption and their configured actions; they are not a universal cap for all serverless and AI services. Use the appropriate budgets, usage views and alerts for the services your account uses. Include thresholds, owners and response steps in the operating plan. Also allow for reporting and enforcement timing rather than promising an exact hard spending ceiling.
Reference: resource monitor scope and limitations.
Snowflake SQL interview questions with sample data and expected results
Use an authorised practice database, schema and warehouse. The fixture below creates session-scoped temporary objects named sf_iq_* and contains six synthetic source rows. Run it once in the same session as questions 41-52. The CREATE OR REPLACE statements replace those practice objects, so do not reuse production names. Warehouse use may consume credits.
Execution note: These are Snowflake SQL teaching examples. Questions 53-55 require your own objects, history or permissions. Expected results for the small fixture are provided for self-checking; the examples are not evidence of execution in your Snowflake account.
CREATE OR REPLACE TEMP TABLE sf_iq_orders (
order_id NUMBER, customer_id NUMBER,
order_date DATE, amount NUMBER(10,2),
status VARCHAR, updated_at TIMESTAMP_NTZ,
ingest_seq NUMBER
);
INSERT INTO sf_iq_orders VALUES
(101,1,'2026-01-02',100,'paid','2026-01-02 09:00:00',1),
(101,1,'2026-01-02',120,'paid','2026-01-02 10:00:00',2),
(102,1,'2026-01-03',80,'paid','2026-01-03 09:00:00',3),
(103,2,'2026-01-03',200,'paid','2026-01-03 11:00:00',4),
(104,2,'2026-02-01',50,'cancelled','2026-02-01 09:00:00',5),
(105,3,'2026-02-02',150,'paid','2026-02-02 09:00:00',6);
CREATE OR REPLACE TEMP VIEW sf_iq_latest AS
SELECT * FROM sf_iq_orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, ingest_seq DESC
) = 1;41. Find duplicate business keys before loading the reporting layer.
Expected result: order_id 101, row_count 2. This identifies repeated order keys, not necessarily identical rows. Ask whether the repeats represent source updates or accidental duplication before deleting anything. A duplicate-key check should also specify how null keys will be treated.
SELECT order_id, COUNT(*) AS row_count
FROM sf_iq_orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;42. Keep the latest record per order using QUALIFY.
Expected result: five orders, with order 101 retaining amount 120. The second ordering column is a deterministic tie-breaker in this practice dataset. In production, use a meaningful source sequence or an agreed conflict rule; an arbitrary tie-breaker may be repeatable without being correct. DISTINCT would not select the latest amount.
SELECT order_id, amount, updated_at
FROM sf_iq_orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, ingest_seq DESC
) = 1
ORDER BY order_id;43. Return the two highest-value paid orders per customer.
Expected result: customer 1 has orders 101 and 102; customer 2 has 103; customer 3 has 105. ROW_NUMBER limits the count to at most two rows per customer. If the interviewer wants all ties at second place, discuss RANK or DENSE_RANK instead and explain that more than two rows may be returned.
SELECT customer_id, order_id, amount
FROM sf_iq_latest
WHERE status = 'paid'
QUALIFY ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC, order_id
) <= 2
ORDER BY customer_id, amount DESC, order_id;44. Calculate monthly paid revenue without double counting updates.
Expected result: January 2026 = 400; February 2026 = 150. The raw table contains an earlier version of order 101, so summing raw rows would overstate revenue. State whether cancelled orders, refunds, tax and currency conversion are included before calling the result business revenue.
SELECT DATE_TRUNC('MONTH', order_date)::DATE AS month,
SUM(amount) AS revenue
FROM sf_iq_latest
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;45. Calculate a running revenue total over observed sales days.
Expected running totals: 120 on 2 January, 400 on 3 January and 550 on 2 February. This returns observed sales dates, not every calendar date. Join to a date spine first when the report needs days with no sales. The explicit window frame makes the accumulation rule clear.
WITH daily AS (
SELECT order_date, SUM(amount) AS revenue
FROM sf_iq_latest
WHERE status = 'paid'
GROUP BY order_date
)
SELECT order_date, revenue,
SUM(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_revenue
FROM daily
ORDER BY order_date;46. Find customers who have never placed a paid order.
Expected result: customer 4. NOT EXISTS expresses an anti-join without the null pitfalls of NOT IN when its subquery contains nulls. A customer with only cancelled orders should also qualify. Test that case as an extension instead of assuming every existing order is a paid order.
WITH customers AS (
SELECT column1::NUMBER AS customer_id
FROM VALUES (1),(2),(3),(4)
)
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM sf_iq_latest o
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
)
ORDER BY c.customer_id;47. Find the second-highest distinct paid order amount.
Expected result: order 105 with amount 150. DENSE_RANK answers second-highest distinct value and includes ties. ROW_NUMBER answers a different question: the second row in a chosen ordering. Clarify the tie requirement before coding, and explain that there is no result if fewer than two distinct values exist.
SELECT order_id, amount
FROM sf_iq_latest
WHERE status = 'paid'
QUALIFY DENSE_RANK() OVER (
ORDER BY amount DESC
) = 2
ORDER BY order_id;48. Calculate the change from the previous observed month.
Expected result: January has no previous value; February changes from 400 to 150, or -62.50%. NULLIF avoids division by zero. LAG here compares observed months; add a complete calendar when a missing month must be represented as zero rather than skipped.
WITH monthly AS (
SELECT DATE_TRUNC('MONTH', order_date)::DATE AS month,
SUM(amount) AS revenue
FROM sf_iq_latest
WHERE status = 'paid'
GROUP BY 1
), compared AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS previous
FROM monthly
)
SELECT month, revenue, previous,
ROUND(100.0 * (revenue - previous)
/ NULLIF(previous, 0), 2) AS change_pct
FROM compared
ORDER BY month;49. Handle malformed numeric values without hiding data-quality failures.
Expected classifications: 120.50 is valid; bad is invalid; the SQL null is missing. TRY conversion prevents a conversion exception, but the pipeline must still count, quarantine or resolve invalid records. Replacing every failed conversion with zero can silently change the business meaning.
WITH raw AS (
SELECT column1::VARCHAR AS amount_text
FROM VALUES ('120.50'),('bad'),(NULL)
)
SELECT amount_text,
TRY_TO_DECIMAL(amount_text, 10, 2) AS amount,
CASE
WHEN amount_text IS NULL THEN 'missing'
WHEN TRY_TO_DECIMAL(amount_text,10,2) IS NULL
THEN 'invalid'
ELSE 'valid'
END AS quality_status
FROM raw;50. Extract and flatten a JSON order.
Expected result: (201, A, 2) and (201, B, 1). The order key is intentionally repeated because the output grain is one item per order. Discuss missing arrays, invalid JSON and whether OUTER => TRUE is appropriate when a parent with no items must remain visible.
WITH raw AS (
SELECT PARSE_JSON(
'{"order_id":201,"items":[{"sku":"A","qty":2},'
|| '{"sku":"B","qty":1}]}'
) AS payload
)
SELECT payload:order_id::NUMBER AS order_id,
f.value:sku::VARCHAR AS sku,
f.value:qty::NUMBER AS quantity
FROM raw,
LATERAL FLATTEN(input => payload:items) f
ORDER BY sku;51. Write a replay-safe MERGE for the latest order state.
Expected target: five orders, with order 101 amount 120. Rerun only the MERGE and SELECT, not the CREATE OR REPLACE: the target should stay unchanged. This example assumes non-null keys/timestamps, a reliable sequence and one writer. Deletes, concurrent writers and events with conflicting ordering require additional design; MERGE alone does not establish those guarantees.
CREATE OR REPLACE TEMP TABLE sf_iq_target AS
SELECT order_id, amount, updated_at, ingest_seq
FROM sf_iq_latest
WHERE order_id = 102;
MERGE INTO sf_iq_target t
USING sf_iq_latest s
ON t.order_id = s.order_id
WHEN MATCHED AND (
s.updated_at > t.updated_at OR
(s.updated_at = t.updated_at
AND s.ingest_seq > t.ingest_seq)
) THEN UPDATE SET
t.amount = s.amount,
t.updated_at = s.updated_at,
t.ingest_seq = s.ingest_seq
WHEN NOT MATCHED THEN INSERT
(order_id, amount, updated_at, ingest_seq)
VALUES
(s.order_id, s.amount, s.updated_at, s.ingest_seq);
SELECT * FROM sf_iq_target ORDER BY order_id;52. Check referential integrity in a standard-table pipeline.
Expected result: order 105 points to missing customer 3 in this deliberately incomplete customer dataset. Decide whether to quarantine it, wait for a late dimension or use an agreed unknown member. Silently dropping the order from a report hides the quality problem.
WITH customers AS (
SELECT column1::NUMBER AS customer_id
FROM VALUES (1),(2)
)
SELECT o.order_id, o.customer_id
FROM sf_iq_latest o
LEFT JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;53. Validate a staged CSV before loading it.
This is an environment-dependent template, not part of the six-row fixture. Replace the example objects with your approved practice objects. For this simple supported load, validation returns detected parsing/conversion errors without loading rows; an empty result means no errors were found for the files checked. It does not prove completeness or business correctness. Transformed COPY statements have validation limitations.
-- Template: create the authorised stage and target first.
COPY INTO iq_db.iq_lab.orders_load
FROM @iq_db.iq_lab.orders_stage
FILE_FORMAT = (
TYPE = CSV
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
VALIDATION_MODE = RETURN_ERRORS;54. Query the state before an accidental update.
The expected result is the eligible historical state immediately before the identified statement. It depends on retained history and privileges; this template is not executable until the placeholder is replaced. Compare the result and restore into a separate recovery object before an approved production repair. If the history has expired, do not promise that this query can recover it.
-- Template: use a real UPDATE query ID and an eligible table.
SELECT order_id, amount
FROM iq_db.iq_lab.orders
BEFORE (STATEMENT => '<actual_update_query_id>');55. Inspect recent failed task runs.
Use an appropriate database context and a role authorised to see the relevant task history. Results depend on your environment; no failures in this returned window do not prove that every task ran. The result limit and time range can exclude events. Also check suspended tasks, skipped runs, owner privileges and downstream dependencies.
SELECT name, state, scheduled_time,
error_code, error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START =>
DATEADD('hour', -24, CURRENT_TIMESTAMP()),
RESULT_LIMIT => 1000
))
WHERE state = 'FAILED'
ORDER BY scheduled_time DESC;SQL references: QUALIFY, MERGE, FLATTEN, COPY INTO and task execution and monitoring.
For guided practice with loading, transformations and project explanation, compare the hands-on topics in our Snowflake course curriculum. Practise changing the input data and predicting the output before reviewing an answer.
Scenario-based Snowflake interview questions and answers
For a scenario answer, use this sequence: clarify impact and constraints, gather evidence, isolate the cause, make a controlled change, verify the result, and prevent recurrence. The cases below are training scenarios, not claims about Brolly Academy client incidents.
56. A dashboard that took 20 seconds now takes four minutes. What do you investigate?
First establish whether all users, one report or one time window is affected. Capture a slow query ID and compare it with a prior run: SQL text, data volume, warehouse, queue time and execution profile. If the delay is queuing, examine concurrency; if execution expanded, inspect joins, scans and spill. Check cache differences before comparing timings. Validate the chosen fix with the same result set and a representative workload, not just one warm-cache run.
57. An overnight load completed, but the dashboard is missing records. What next?
A green job status proves that the job finished under its rules, not that the dataset is complete. Reconcile expected files and source row counts against loaded, rejected and transformed rows. Compare source timestamps with reporting cut-offs and check filters, join losses and late arrivals. Find the first layer where records disappear. Backfill the specific gap using a replay-safe process, then add a completeness check that can fail or alert independently of job execution.
58. The same delivery arrived twice and totals increased. How do you repair it?
Pause or isolate the affected reporting update and identify the duplicate scope using source metadata and the agreed business key. Distinguish genuine repeated events from duplicate deliveries. Rebuild or correct only the affected keys or partitions through an approved repair, then reconcile totals. Prevent recurrence with stable ingestion identifiers, deterministic latest-state selection and a target write that can tolerate retries. Deleting every repeated customer ID would be wrong when a customer can place multiple orders.
59. MERGE fails because several source rows match one target row. What is your answer?
Inspect the source grain before changing any session parameter. Multiple records may represent duplicate delivery, updates or conflicting business events. Select one authoritative version using an agreed event timestamp and sequence, or aggregate only when the business meaning supports aggregation. Keep rejected conflicts for investigation. Rerun on a small controlled sample and verify both existing and new target keys. Allowing a nondeterministic result suppresses the symptom without deciding which value is correct.
60. An update changed every row instead of one customer. How would you respond?
Stop additional writes that would complicate recovery and preserve the query ID, time and impacted objects. Check available Time Travel history and inspect a pre-update snapshot. Prepare a recovery table or clone for comparison, reconcile the affected keys, and obtain approval for the repair. Consider downstream tables and reports that may already have consumed the bad values. Add row-count checks, transaction safeguards and review controls; do not run a second broad update based on a guess.
61. A stream became stale during a prolonged outage. Can you simply resume the task?
Not reliably. Inspect the stream state and whether the required change history is still available. Identify the last verified target checkpoint and reconstruct the missing interval from a retained raw source, audit log or suitable snapshot. Rebuild and reconcile the target before starting a new checkpoint. Explain how monitoring, retention choices and an explicit recovery plan would reduce future risk. A new stream alone does not recover the missing change history.
62. A scheduled task works manually but fails automatically. What would you check?
Compare the task execution context with the role and session that made the manual run succeed. Inspect task history, owner privileges, object names, warehouse configuration and dependencies. Check whether the task was resumed and whether its condition was satisfied. Fix the specific context difference, rerun safely and validate the target. Do not leave a broad administrator role as a permanent workaround; an interactive test should mimic the intended production permissions.
63. Snowpipe is configured, but newly uploaded files are not appearing. How do you debug it?
Trace one known file through the path: correct storage prefix, notification delivery, pipe state, access permissions, file-load history and parsing outcome. Inspect whether the file was previously registered rather than assuming another upload will trigger a new load. Compare event arrival time with processing time to identify the delay. Fix and reconcile the affected files before broad reprocessing, and retain source files until successful ingestion has been verified.
64. Cloud spending increased although row volume is stable. What evidence do you need?
Break down warehouse compute, idle time, serverless services and storage rather than comparing one total. Look for changed warehouse sizes, concurrency, task frequency, repeated full refreshes and longer historical-data retention. Compare equivalent periods and account for workload changes. Assign the increase to a measured cause before proposing suspension or downsizing. Use the appropriate cost controls for each service; a warehouse resource monitor does not cover every source of spending.
65. A dashboard is fast for one user but slow when 100 users open it. What changes?
The problem may be throughput and queuing rather than individual query execution. Inspect warehouse load and concurrency, then consider isolating BI from ingestion, optimising repeated queries or using suitable multi-cluster settings. Test a representative concurrent workload and measure response-time distribution, not only the fastest request. Scaling up may help some workloads, but it should not substitute for identifying the bottleneck and setting an acceptable cost per workload.
66. A source adds a field and changes the type of another. How do you prevent a broken pipeline?
Keep a clear contract between raw ingestion and curated tables. Capture the raw source and detect schema changes before promoting them to reporting. Decide which additions can be accepted automatically and which type changes require review. Quarantine incompatible rows with a reason, preserve source metadata, and test downstream consumers. Automatic schema evolution is a feature to configure deliberately, not permission for an upstream system to silently redefine business measures.
67. A late event arrives after a newer update. How should the pipeline behave?
Clarify whether the target stores current state or historical events. For current state, compare the source event timestamp and sequence against the stored version before overwriting it. For event history, retain the late event and recompute affected aggregates or effective intervals under an agreed rule. Distinguish source event time from ingestion time. Test out-of-order delivery, equal timestamps and repeat delivery; a pipeline that works only in arrival order is fragile.
68. A customer appears in an order before its dimension record arrives. What do you do?
Choose an explicit late-arriving-dimension policy. Depending on reporting needs, hold the order in a quarantine area, use an agreed unknown dimension member, or create a controlled inferred record that is enriched later. Preserve the original key and monitor unresolved items. Reconcile after the dimension arrives. An inner join that silently drops the order may make the dashboard look clean while understating the business result.
69. An analyst must see revenue but not customer email addresses. How do you implement that?
Start with a least-privilege reporting role and an approved presentation layer. Use the appropriate column policy or restricted view and verify behaviour as that analyst, not as an administrator. Check whether the analyst can reach an unprotected base table, alternate view or exported dataset. Test both permitted revenue analysis and denied sensitive access. Keep the access review and policy ownership explicit so future objects do not bypass the intended boundary.
70. How would you migrate an on-premises data warehouse to Snowflake?
Inventory dependencies, data volumes, SLAs, access rules and unsupported SQL assumptions before selecting an ingestion tool. Pilot one representative workload, reconcile counts and business totals, and compare performance and cost. Plan historical loading, incremental capture, security testing, cutover, rollback and operational ownership. Run parallel validation where appropriate. A credible answer names acceptance criteria and rollback triggers rather than treating migration as copying tables and declaring success.
Project discussion, interview preparation and common learner questions
71. How should you explain a Snowflake project in two minutes?
72. What follow-up questions should an experienced candidate prepare for?
Prepare to explain why you chose a particular load method, how you handled deletes and late records, who could access sensitive data, and how a retry avoided duplicate writes. Expect questions about testing, release review, monitoring, recovery and cost ownership. Use a specific example and identify your contribution accurately. If another team owned cloud networking or permissions, explain the boundary and how you collaborated instead of claiming responsibility for the whole platform.
73. What should a fresher include in a Snowflake portfolio?
Include a clear problem statement, a small permitted dataset, reproducible setup steps, SQL transformations, data-quality checks and expected outputs. Add a diagram that identifies source, raw, curated and reporting layers. Show one deliberately introduced failure, such as a duplicate source update, and how your tests detected it. Remove credentials and personal data. A small project that someone can inspect and reproduce is stronger than a list of tools with no working evidence.
74. How do you discuss a performance improvement credibly?
State the workload, data size, warehouse settings, cache conditions and correctness check before comparing results. Report the measurement method and limitations, including whether concurrency was represented. Separate your observed runtime improvement from any cost effect. Do not describe a tiny synthetic dataset as a production benchmark. If you have not measured the change, explain the hypothesis and test plan rather than supplying a made-up percentage.
75. What should you say when you do not know an answer?
State what you know, identify the uncertain part and explain how you would verify it. For example: “I have used streams with scheduled tasks, but not this trigger configuration. I would check the task history and current Snowflake documentation, then test it in a sandbox.” This is more useful than guessing a feature limit. Continue reasoning about failure handling and acceptance criteria without pretending to have experience you do not have.
76. How many Snowflake interview questions should I prepare?
There is no universal count that guarantees readiness. Use these 80 questions as a coverage checklist, but test whether you can write SQL, explain a project and diagnose unfamiliar variants. If you can recite 200 definitions but cannot explain why a MERGE duplicates data, you still have a practical gap. Prioritise the job description, your experience level and the topics where follow-up questions expose uncertainty.
77. Is SQL enough, or should I also know Python, dbt and a cloud platform?
SQL is a core skill for many Snowflake roles, but requirements vary. Compare the actual vacancy for expectations around Python, dbt, cloud storage, orchestration and deployment. First become comfortable with joins, window functions, transformations and data validation. Then learn the supporting tools used by your target role. Avoid presenting every tool mentioned in an advertisement as something you have used professionally.
78. Do I need Snowflake certification to get an interview?
Certification is not a universal hiring requirement. It can demonstrate structured study, but interviewers may still ask for SQL, project decisions and relevant experience. Check the vacancy rather than assuming an exam substitutes for practical work. Our Snowflake certification guide explains the preparation decision; use the official exam page for current objectives and policies.
79. How can I revise in seven days?
Day 1: architecture, table types and recovery. Day 2: loading and data quality. Day 3: solve questions 41-48 unaided. Day 4: JSON, MERGE and integrity checks. Day 5: streams, tasks, security and performance. Day 6: work through failure scenarios and explain one project aloud. Day 7: a mock interview and focused revision of weak areas. This is a revision plan for existing knowledge, not a promise to become job-ready from zero in a week.
80. How do I know whether I am ready to start applying?
You should be able to explain the target role, solve a small SQL problem with edge cases, describe one genuine project and investigate a failed load logically. Review relevant openings and note repeated skill gaps. See our Snowflake jobs in Hyderabad guide for role and application context. Apply to roles that fit your current evidence while continuing practice; neither this question list nor a course guarantees an interview or a job.
A practical mock interview and self-review checklist
- Explain: Describe architecture, warehouse scaling and recovery without reading definitions.
- Write: Solve deduplication and latest-state SQL, then predict the output when an extra update arrives.
- Debug: Work through a missing-record or duplicate-load scenario. State what you would inspect before changing anything.
- Defend: Explain a project decision, one alternative and why you did not choose it.
- Verify: Identify a correctness check, a permission test and a recovery step for your design.
For self-review, mark each topic as: can explain, can implement, can troubleshoot, or needs practice. This is a learning checklist, not an employer assessment or a predicted hiring score.
Continue learning Snowflake
If your gaps are in hands-on implementation, review Snowflake Training in Hyderabad at Brolly Academy for guided learning and project practice. If you already know the concepts, spend your next session changing the sample data, checking the results and practising your own explanations.
Related reading: architecture, zero-copy cloning, certification preparation and Hyderabad career opportunities.
Editorial note: This guide contains original training examples and links to official documentation. Product behaviour can change, so verify edition-specific features in the linked references. No question is represented as a leaked employer question, an exam dump or a guaranteed interview question.
Additional advanced Snowflake interview questions
The 80 core questions above remain unchanged. This additional section brings the guide to 92 questions, covering specialised decisions in storage, timestamps, transactions, performance, access, monitoring and recovery. Select topics relevant to the job description; these are original practice questions, not a claim about an employer’s actual interview.
81. How do external tables, Iceberg tables and native Snowflake tables differ?
Start with who owns the files, catalog and table maintenance. A conventional external table exposes files in an external stage through Snowflake metadata and is read-only. An Iceberg table uses the open Iceberg table format; capabilities depend on its catalog and storage configuration. A native Snowflake table uses Snowflake-managed storage and its supported platform behaviour.
Decision example: If several engines must use an existing lake dataset, investigate Iceberg interoperability, catalog authority and supported writes. If the requirement is ordinary warehouse reporting, compare that complexity with loading a native table. Do not claim every Iceberg configuration has identical feature support or that external storage is automatically cheaper.
Follow-up: Who refreshes metadata, controls access, removes obsolete files and pays transfer costs? Verify these responsibilities before choosing. References: external tables and Iceberg tables.
82. How would you choose TIMESTAMP_NTZ, TIMESTAMP_LTZ or TIMESTAMP_TZ?
TIMESTAMP_NTZ stores a wall-clock value without time-zone interpretation. TIMESTAMP_LTZ stores an instant internally in UTC and presents it using the session time zone. TIMESTAMP_TZ retains an instant with an offset, not the original named time zone and its future daylight-saving rules.
Scenario: Two offices report the same event under different local dates. First decide whether reporting groups by an absolute instant or by a business location’s calendar. Preserve the source’s named zone separately when the business needs that context, and make parsing and conversion explicit.
Test: Include a value with an explicit offset, a naive local value and a daylight-saving boundary. Compare the result in two session time zones. Never silently assume that a timestamp without an offset is UTC. Reference: Snowflake date and time types.
83. How are SQL NULL, JSON null and a missing JSON field different?
A SQL NULL represents a missing SQL value. A JSON null is an explicit value inside semi-structured data. A missing object field is not the same source event as a field explicitly set to JSON null, even when a later cast makes their outputs look similar. Use the appropriate predicates before flattening these distinctions into a reporting column.
Practice: The standalone teaching query below should classify the explicit null as JSON null and the absent key as SQL NULL. Run it in your own permitted environment; these expected values are not an execution claim.
SELECT
IS_NULL_VALUE(PARSE_JSON('{"email":null}'):email)
AS explicit_json_null,
PARSE_JSON('{}'):email IS NULL
AS missing_email;Follow-up: In an update feed, does a missing field mean ‘leave unchanged’ while an explicit null means ‘clear the value’? Define that contract before MERGE. Reference: IS_NULL_VALUE semantics.
84. Can you roll back a whole deployment containing DDL and DML?
Do not assume a BEGIN/ROLLBACK wrapper makes every deployment reversible. In Snowflake, a DDL statement implicitly commits an active transaction and runs as its own transaction. CTAS is also DDL. In addition, a failed DML statement does not necessarily roll back every earlier successful statement: the transaction and error-handling path matter.
Design answer: Separate schema changes from transactional data changes. Create prerequisite objects before beginning the intended DML transaction. Define which error triggers ROLLBACK and which changes require a separately approved recovery step.
Test: In a disposable lab, fail the second write deliberately and inspect both tables. Repeat the experiment with a DDL boundary and explain the difference. Use isolated connections for independent units of work. Do not claim that wrapping a stored-procedure call automatically fixes transaction scope. Reference: transaction and DDL rules.
85. When is search optimization more appropriate than a clustering key?
Search optimization maintains an additional access path for supported query patterns, including suitable selective lookups. Clustering improves how table data is organised for partition pruning. They solve overlapping but different problems and both can introduce maintenance costs.
Scenario: A support application repeatedly finds a few records by an identifier in a large table. Compare search optimization eligibility with the current scan profile. A reporting workload that repeatedly filters date ranges may call for a different clustering investigation. Do not enable either service only because the table has many rows.
Evidence: Benchmark representative predicates, result correctness, data-change rate, build/maintenance cost and end-to-end latency. Include both common and uncommon values. State the required edition and supported predicate types before recommending deployment. Reference: search optimization service.
86. Does Query Acceleration replace warehouse sizing or SQL tuning?
No. Query Acceleration can offload eligible portions of query processing to serverless resources. It does not repair an incorrect join, make every query eligible or eliminate the need to size and isolate warehouses appropriately.
Investigation: Identify whether the slow workload has eligible work, inspect the documented estimation and query-profile evidence, then compare a controlled run with acceleration enabled against a baseline. Keep SQL, data, cache conditions and concurrency comparable. Track the additional service credits as well as elapsed time.
Interview follow-up: If the query remains slow, distinguish queuing, compilation, unsupported work and another execution bottleneck. Explain the configured resource limit and who monitors spending. Recommend the service only when its measured benefit meets the workload’s latency and cost requirements. Reference: Query Acceleration Service.
87. Why might ACCOUNT_USAGE not show a query that just finished?
Metadata sources have different freshness, retention and visibility rules. ACCOUNT_USAGE is useful for historical, account-level analysis, but its views have documented latency. INFORMATION_SCHEMA views and table functions can support more immediate investigation within their own scope, time-window and result-limit constraints.
Scenario: An incident happened two minutes ago and a historical usage query returns no matching row. Check the specific view’s latency, your role and filters before concluding that the event did not occur. Correlate the query ID with the appropriate recent-history source.
Strong answer: Name the exact metadata source, explain its window and privileges, and distinguish ‘not visible here yet’ from ‘did not execute’. For a cost report, align time zone and billing period as well. Avoid memorising one latency number for every view. Reference: Account Usage and Information Schema comparison.
88. Do future grants fix access to tables that already exist?
Future grants define initial privileges for newly created objects of a specified type. They are not a retroactive grant on every existing table. A deployment may therefore need a deliberate existing-object grant and a future-object policy, alongside database, schema and warehouse access.
Scenario: A reporting role reads yesterday’s table but not a replacement created today. Inspect object identity, the replacement command’s grant behaviour and relevant future grants. When database- and schema-level future grants exist for the same object type, schema-level grants take precedence.
Validation: Test an existing approved table, a new approved table and a restricted table using the reporting role. In managed access schemas, account for centralised grant ownership. Do not solve a narrow failure by granting unrestricted access to every future object. Reference: future grants and managed access schemas.
89. When should a procedure use caller rights instead of owner rights?
A caller-rights procedure operates with the caller’s privileges. An owner-rights procedure generally uses its owner’s privileges and can delegate a controlled operation without granting the caller unrestricted underlying access. The execution context and supported session behaviour differ, so this is a security design choice, not just a syntax option.
Example: A maintenance operation may need narrowly delegated access, while a user-specific analysis should respect the user’s permitted data. Define what the procedure may accept and which objects it may touch. Validate inputs and avoid unrestricted dynamic object names that could expand delegated authority.
Tests: Run as a permitted caller, a caller lacking execution permission and a caller attempting an out-of-scope operation. Explain ownership and deployment review. Never assume the procedure is safe simply because users cannot query its underlying table directly. Reference: caller and owner rights.
90. How are replication, failover, RPO and RTO related?
Replication maintains supported secondary objects or data; failover is the controlled transition of service to a prepared secondary. RPO describes tolerable data loss measured in time, while RTO describes the target restoration time. A recent copy does not prove the application can reconnect and operate within either target.
Design answer: Identify data and account objects that must be replicated, refresh frequency, supported editions and regions, credentials, client connections, task ownership and external dependencies. Plan who authorises promotion and how duplicate processing is prevented.
Verification: Rehearse an approved non-production failover, check the last replicated business event, reconnect a client and validate permissions and scheduled processing. Measure the actual recovery outcome; do not promise zero data loss merely because replication is enabled. Failover-group and account-object capabilities are edition-dependent. Reference: replication and failover overview.
91. Why can a dbt incremental model still miss late updates or create duplicates?
An incremental materialization processes the rows selected by its model logic; it does not automatically define a correct business key or capture every late change. A watermark based only on the largest loaded timestamp can miss records arriving outside that boundary. A configured unique key also does not remove the need to validate source uniqueness and null handling.
Practical answer: Define the target grain, supported adapter strategy, deterministic source selection and a replay window appropriate to source behaviour. Explain how changed logic or historical corrections trigger a controlled rebuild or backfill rather than assuming the next incremental run repairs everything.
Tests: Replay the same batch, send an older update, change a previously loaded record and introduce a duplicate key. Compare the incremental result with a full recomputation on a small fixture. Reference: dbt incremental model configuration.
92. What would a safe Snowflake release and rollback checklist include?
Start with the intended change, its owner and acceptance criteria. Version SQL and configuration, review dependencies and permissions, and validate in a permitted non-production environment with synthetic or approved data. A successful script execution is only one checkpoint.
- Before release: compare schemas, grants, data contracts and task dependencies; agree the recovery point and decision owner.
- During release: control writers and scheduled work where necessary, apply changes in order and retain execution identifiers.
- Verify: reconcile rows and business totals, test allowed and denied access, confirm pipeline freshness and compare representative query behaviour.
- Recover: distinguish reversible configuration from committed data changes or external effects. Use the tested recovery procedure and reconcile replays before resuming consumers.
Follow-up: Explain what happens when a step succeeds but its acknowledgement is lost. Keep release state and evidence so a retry does not repeat a non-idempotent action. Check transaction boundaries before claiming the entire release can be rolled back.
More scenario-based Snowflake interview questions
93. A vendor file has a few bad rows and the whole nightly COPY load fails. What do you do?
For bulk COPY, ON_ERROR defaults to ABORT_STATEMENT, so one bad row stops the load (Snowflake documentation, October 2026). First see the errors without loading anything by running COPY with VALIDATION_MODE = RETURN_ERRORS. Then agree a rule with the business: skip bad files with SKIP_FILE, or load good rows with CONTINUE and route the rejects to an error table for the vendor to fix. Never switch silently to CONTINUE without a reject process, because rows would disappear without anyone knowing.
94. A developer pasted AWS access keys into a CREATE STAGE statement. How do you fix it?
Replace the keys with a storage integration, which lets Snowflake assume an IAM role in your AWS account so no secret keys live in Snowflake objects or scripts. Creating it needs ACCOUNTADMIN or a role with the CREATE INTEGRATION privilege. Run DESC INTEGRATION to get the IAM user ARN and external ID for the role trust policy, create the stage with STORAGE_INTEGRATION, restrict STORAGE_ALLOWED_LOCATIONS to the exact bucket path, and rotate the exposed keys at once.
95. Queries filter a 5 TB table on date, but the profile shows almost every micro-partition scanned. Why?
Pruning works only when the filter column is well grouped across micro-partitions. If data arrived out of order, each partition holds a wide range of dates. Check with SYSTEM$CLUSTERING_INFORMATION. Snowflake suggests clustering keys for very large tables that are queried often and updated rarely, with about 3 to 4 columns or expressions at most. Automatic Clustering runs serverless and costs credits, so confirm the query savings justify it before adding one.
96. One month-end query scans huge data while every other workload is fine. Do you resize the warehouse?
Not for one outlier. Query Acceleration Service offloads parts of eligible queries, such as large scans with selective filters, to serverless compute, and needs Enterprise Edition or higher (Snowflake documentation, October 2026). Check eligibility with SYSTEM$ESTIMATE_QUERY_ACCELERATION, then enable it on that warehouse with a scale factor cap so cost stays bounded. If the query is not eligible, run the job on a separate, larger warehouse that suspends right after.
97. A web app runs the same KPI query 500 times a day, and the data changes once a night. How do you cut cost?
Use the result cache. Snowflake keeps query results for 24 hours, and each reuse extends that up to 31 days after the first run. Reuse needs the exact same query text, unchanged underlying data and no non-reusable functions such as RANDOM. So make the app send one fixed query text, avoid adding timestamps to the SQL, and let the nightly load be the only thing that invalidates the cache.
98. A reporting warehouse is barely used but shows credits almost all day. What is wrong?
Someone may have set a very long auto-suspend, or a tool may be pinging the warehouse every few minutes. Estimate idle cost from WAREHOUSE_METERING_HISTORY by comparing credits attributed to queries with total compute credits. Lower auto-suspend for BI warehouses, find the tool sending keep-alive queries and point it at a small warehouse. Do not set it extremely low on a busy warehouse, because each resume bills at least 60 seconds and loses the local cache.
99. A developer dropped a production table 30 minutes ago. How do you recover it?
If the table is within its Time Travel retention, UNDROP TABLE restores it with its data. One trap: if a new table with the same name was created after the drop, UNDROP fails, so rename the new table first. After recovery, ask why a developer could drop production objects at all, and tighten ownership so production DDL runs only through deployment roles.
100. Audit needs a table as it was 20 days ago, and the account is on Standard Edition. What can you do?
Be honest about the limits. Standard Edition allows at most 1 day of Time Travel. Fail-safe gives 7 more days, but it is best-effort recovery by Snowflake Support only and is not a way to read history yourself. At 20 days the data is past both windows. Check backups or source systems, and propose prevention: Enterprise Edition with longer retention for critical tables, or scheduled snapshot clones.
101. QA needs a full copy of an 8 TB production database tonight on a tight storage budget. What do you suggest?
Use zero-copy cloning. A clone shares the original micro-partitions, so it is created quickly and costs no extra storage at first; storage grows only as either side changes data. Clone the database, mask sensitive columns before giving QA access, and set a short retention on the clone. Our zero-copy cloning guide explains the details.
102. Regional managers share one sales table but must see only their own region. How do you build it?
Use a row access policy, which returns TRUE or FALSE for each row based on the current role or user. For many regions, keep a small mapping table of role to region and have the policy look it up, so a new manager needs only a new mapping row, not a new view. Row access policies need Enterprise Edition or higher (Snowflake documentation, October 2026).
103. A developer asks for ACCOUNTADMIN "just for today" to meet a deadline. What is your answer?
Say no politely and fix the real gap. ACCOUNTADMIN is the top role and Snowflake advises granting it to very few people. Grant the missing privilege, such as CREATE SCHEMA on the dev database, to a custom functional role, and make sure custom roles roll up to SYSADMIN so administrators can still manage the objects. SECURITYADMIN manages grants, USERADMIN creates users and roles, and SYSADMIN creates warehouses and databases.
104. An ETL job still signs in as a user with a password. What should change?
Snowflake is ending single-factor password sign-in in phases, with the final phase scheduled for August to October 2026; Snowflake calls these dates estimates. Convert the account to a service user (TYPE = SERVICE, which cannot use passwords) and switch it to key-pair authentication with at least a 2048-bit RSA key. Rotate keys using the second key slot so the job never breaks.
105. A chain of five tasks keeps breaking and later steps run on old data. Can dynamic tables help?
Often, yes. A dynamic table stores the result of a SELECT and keeps it fresh to a TARGET_LAG (minimum 60 seconds), and Snowflake manages the dependency order (Snowflake documentation, October 2026). Replace transformation-only tasks with a chain of dynamic tables and set a lag the business actually needs. Keep tasks for procedural steps such as calling APIs or stored procedures. See our dynamic tables guide.
106. A small distributor needs its own daily sales but has no Snowflake account. How do you share it?
Create a reader account, which the provider account owns and pays for, and share a secure view filtered to that distributor. Only secure views can be added to a share; a standard view causes an error. Because you pay for the reader account compute, put a resource monitor on its warehouse. If the client later gets its own Snowflake account, switch to a normal share.
107. Your account is on AWS Mumbai and a partner runs Snowflake in a European region. Why does the direct share fail?
Direct shares work only within the same region. Across regions or clouds, publish a listing with cross-cloud auto-fulfillment, or replicate the database to an account in the partner region and share from there (Snowflake documentation, October 2026). Mention that replication and data transfer across regions add cost, and agree the refresh frequency with the partner.
108. A data scientist pulls 50 million rows into local pandas and runs out of memory. What do you advise?
Move the logic into Snowpark. Snowpark DataFrame operations are lazy: they are built on the client and sent to Snowflake as SQL only when an action such as collect runs, so the heavy work happens on Snowflake compute. Only the small final result should come back to the laptop. To keep pandas syntax, consider pandas on Snowflake, and check its current status in the docs before using it in production.
109. A Python stored procedure that trains a model keeps failing with memory errors. What do you change?
Run it on a Snowpark-optimized warehouse, which Snowflake offers for memory-heavy Snowpark and ML work, and keep the standard warehouse for SQL. Also check whether the code can sample or aggregate before training. For heavier or GPU workloads, Snowpark Container Services runs containers on compute pools inside Snowflake.
110. After a migration, the monthly revenue total in Snowflake differs slightly from the old system. How do you find the cause?
Compare at increasing detail: row counts per table, then sums per month, then per customer, until you find the rows that differ. Usual causes are data type and rounding differences (FLOAT against NUMBER), time zone handling, NULL versus empty string, trimmed spaces and duplicate loads. Fix the conversion rule, reload that slice, and keep the comparison as an automated check during the parallel run. Our Snowflake data types lab covers the type traps.
Related Courses
Brolly Academy Team
About the Author | Snowflake Interview Preparation
The Brolly Academy Team prepares practical learning guides on cloud data platforms, SQL and software development. This Snowflake guide brings together original practice questions, synthetic SQL examples and scenario-based explanations, with links to official documentation. Its focus is helping learners explain their decisions, verify results and prepare honestly for interviews.
Related Articles for Snowflake Interview Preparation
Related Courses for Your Snowflake Learning Path
Brolly Academy | Snowflake Guides
Snowflake Interview Questions and Answers: SQL and Scenarios
Prepare to explain your decisions, not just recite definitions.
These Snowflake interview questions and answers cover platform fundamentals, data loading, SQL, incremental pipelines, security, performance and production troubleshooting.
The guide includes 80 distinct questions, with 15 SQL exercises and worked scenarios for freshers and experienced candidates.
Share
- Brolly Academy Team
- Published:
- 45 min read (estimated)
