Interview preparation guide

Snowflake Interview Questions With Query and Architecture Exercises

Practice Snowflake interview questions by explaining the model, working through a small problem and checking failure cases. These original exercises test reasoning rather than recall of a claimed employer question list.

Scope and assumptions

The SQL exercise uses Snowflake QUALIFY and ROW_NUMBER. It needs an isolated Snowflake worksheet to execute; the expected result below is derived from the fictional rows.

Confirm the version, deployment model and permissions in the interview. The examples use fictional data and are deliberately small. A snippet demonstrates one idea; it is not a complete production system.

How are storage and warehouse compute separated?

A virtual warehouse supplies compute for queries while data storage is managed separately. Discuss workload isolation and cost as well as performance; increasing compute is not a substitute for checking a poor query.

How do you select the latest row per business key?

Partition by the key and order by a reliable timestamp, then add a deterministic tie-breaker. A maximum timestamp alone can return multiple rows when timestamps tie.

What would you check before resizing a warehouse?

Inspect query history, concurrency, scans and the workload’s requirements. Separate a single slow query from queuing pressure. Use measured comparisons and include cost in the decision.

What does a secure data model require?

Use appropriate roles and grants, and review which data a query exposes. Do not assume a warehouse boundary is a complete authorization boundary. Test access with a representative restricted role.

Worked exercise

Return the newest status for each ticket. The row ID breaks a tie if two events share the same timestamp.

WITH events AS (
  SELECT column1 AS ticket_id, column2 AS changed_at, column3 AS event_id,
         column4 AS status
  FROM VALUES
    (1, '2026-01-01 09:00:00', 10, 'open'),
    (1, '2026-01-01 10:00:00', 11, 'closed'),
    (2, '2026-01-01 09:30:00', 12, 'open')
)
SELECT ticket_id, status
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY ticket_id ORDER BY changed_at DESC, event_id DESC
) = 1
ORDER BY ticket_id;

Expected result and explanation

Ticket 1 is closed and ticket 2 is open. ROW_NUMBER selects one row within each ticket partition. The timestamp strings use a consistent sortable format for this small example; real schemas should use an appropriate timestamp type and explicit timezone policy.

Before changing the example, predict the result. Then vary one input or constraint and explain why the behavior should change. Keep the distinction between a measured result and an assumption clear.

Debugging and follow-up

If a query returns unexpected latest rows, inspect timestamp types, null handling, timezone and ties before blaming the warehouse. Add a tie case and a null case to the test dataset.

  • What evidence would distinguish your first explanation from an alternative?
  • Which boundary, permission or failure case should be checked?
  • How would you reproduce the issue without affecting live users?

Practice checklist

Explain one design decision aloud and support it with the worked example. Use the official documentation below to check version-specific behavior.

  • I stated the relevant version and environment.
  • I can explain the expected result and an edge case.
  • I can separate correctness, performance and security concerns.
  • I know which parts require a real runtime or service to verify.

Questions, answered

Are these questions used by a particular employer?

No. They are original practice questions. Employers can assess different tools, versions and levels of depth.

How should I verify the exercise?

Use an isolated local or sandbox environment with the stated prerequisites. Compare the expected result, then test an edge case. Do not run service commands against a shared or production system.

Sources and examples

ResumizeAI team. Examples are fictional teaching material, not customer results. Replace sample details with your own facts.

Your experience. A complete resume.

Build in Resumize with ATS-friendly templates, guided editing and a live preview. Start free, then upgrade when you need more.

Free account required. Free-plan PDF exports include a watermark.