Snowflake logo

Snowflake Hard Interview Questions

5 hard-level practice questions for Snowflake technical interviews

Snowflake software engineer interviews cover algorithms, data structures, system design, and coding problems drawn from real interview rounds.

Software Engineer Backend Engineer Frontend Engineer Full Stack Engineer Mobile Engineer Data Engineer Data Scientist ML Engineer DevOps Engineer DevOps Engineer Product Manager SRE Security Engineer Engineering Manager Data Analyst UX/UI Designer QA Engineer
coding Hard Verified Question #1

1. Pipeline Throughput Optimizer


Category: Array coding problem

Question A message-processing pipeline consists of n services that must all be traversed in sequence. The pipeline's effective throughput is...

Input: Array
Output: Integer
Want to practise this one?
Create a free account to open the full question and practise Snowflake questions with AI.
Sign up free
coding Hard Verified Question #2

2. [OA] Bounded Transition Subsequence


Category: Array coding problem
You are compressing a color run sequence. Given an array of color IDs and an integer k representing the maximum number of allowed color changes,...
Input: Array
Output: Integer
coding Hard Verified Question #3

3. Inherited Access Rights


Category: String coding problem
You are building an access control system for a company with n departments (numbered 0 to n-1). Departments can inherit access rights from...
Input: List
Output: Computed result
coding Hard Verified Question #4

4. Three Frequency Assignment


Category: Graph coding problem
You are assigning radio frequencies to a set of communication towers. To avoid interference, no two towers that directly interfere with each other...
Input: Graph (nodes and edges)
Output: Computed result
coding Hard tree #1

1. [AI Enabled Coding] Query History Guardrail Evaluator


Scenario


Snowflake customers use virtual warehouses to execute queries and inspect execution history through QUERY_HISTORY. Build a prototype that processes exported query-completion records enriched with estimated compute credits, helping platform administrators detect warehouse bursts and account-wide credit spikes; these estimates are monitoring inputs, not a reproduction of Snowflake billing. You may use an AI assistant while coding, but must verify its implementation and explain its complexity.

Part 1 — Warehouse guardrails


  • Each input record is a map of strings containing query_id, account, warehouse, finished_at, and credits. finished_at is an integer Unix timestamp in seconds; credits is a nonnegative decimal string with at most two fractional digits.

  • Process records in their input order, which can differ from timestamp order. Insert the current record before evaluating its rules. Previously emitted results never change.

  • For a current timestamp t and window length w, include only records already processed whose timestamps satisfy t - w < finished_at <= t. Previously processed records with later timestamps are excluded.

  • Warehouses are identified by the pair (account, warehouse), not by warehouse name alone.

  • Default rule WAREHOUSE_BURST: alert if the current warehouse has more than 3 queries in the last 60 seconds.

  • Default rule WAREHOUSE_CREDITS: alert if the current warehouse has more than 10.00 estimated credits in the last 300 seconds.

  • Every record contributes to history, even if it triggers an alert. Evaluate all rules independently; equality with a limit does not trigger an alert.

  • Emit one result per record in input order. Sort triggered rule IDs lexicographically. For the examples, use { "query_id": ..., "alerts": [rule_id, ...] }.


Function signature:
def evaluate_queries(records: list[dict[str, str]]):

The return type is up to the candidate; the examples specify the required information and use one canonical JSON representation.

Example 1:


Input:
{
  "records": [
    {"query_id":"q1","account":"A","warehouse":"ETL","finished_at":"1000","credits":"4.00"},
    {"query_id":"q2","account":"A","warehouse":"ETL","finished_at":"1010","credits":"4.00"},
    {"query_id":"q3","account":"A","warehouse":"ETL","finished_at":"1020","credits":"3.00"},
    {"query_id":"q4","account":"A","warehouse":"ETL","finished_at":"1030","credits":"0.00"},
    {"query_id":"q5","account":"A","warehouse":"ETL","finished_at":"1090","credits":"1.00"}
  ]
}

Output:
[
  {"query_id":"q1","alerts":[]},
  {"query_id":"q2","alerts":[]},
  {"query_id":"q3","alerts":["WAREHOUSE_CREDITS"]},
  {"query_id":"q4","alerts":["WAREHOUSE_BURST","WAREHOUSE_CREDITS"]},
  {"query_id":"q5","alerts":["WAREHOUSE_CREDITS"]}
]

Explanation: Credit totals are 4.00, 8.00, 11.00, 11.00, and 12.00. At q4, all four queries are in (970, 1030], so both default rules fire. At q5, the burst window is (1030, 1090]: q4 is exactly on the excluded boundary, leaving only q5, while all five records remain in the 300-second credit window.

Part 2 — Caller-defined policies and diagnostic alerts


Account administrators now need policies spanning multiple warehouses, and other teams need to add policies without editing the evaluator. Extend Part 1 rather than replacing its default rules.
  • Accept an additional list of string-valued maps, each containing rule_id, scope, metric, window_seconds, and limit.

  • scope is warehouse or account. Account policies aggregate all warehouses in the current record's account. Warehouse policies retain Part 1's composite warehouse identity.

  • metric is count or credits. A policy fires exactly when its metric in (t - window_seconds, t] is strictly greater than limit. Count limits are nonnegative integer strings; credit limits are nonnegative decimal strings with at most two fractional digits.

  • Defaults remain active. Policy IDs are unique and cannot equal either default ID.

  • Return all triggered policies, sorted by rule_id. Each alert now includes rule_id, scope, observed, and limit. Serialize counts as integer strings and credits with exactly two fractional digits.

  • The design must support future additions of the same kind through policy data, without adding policy-specific branches. Discuss how the return type changes before implementing the extension.

  • Do not rescan an entity's complete history for each record or discard history merely because a later timestamp has been seen. Target O(n log n + n r log n + r log r) time or better, where n is the number of records and r is the total number of policies, including defaults. The full input batch is available for preprocessing, but future input records must not contribute early.


Function signature:
def evaluate_with_policies(
    records: list[dict[str, str]],
    policies: list[dict[str, str]]
):

Example 2:


Input:
{
  "records": [
    {"query_id":"a1","account":"A","warehouse":"ETL","finished_at":"100","credits":"6.00"},
    {"query_id":"a2","account":"A","warehouse":"BI","finished_at":"110","credits":"5.00"},
    {"query_id":"a3","account":"A","warehouse":"ETL","finished_at":"120","credits":"2.00"},
    {"query_id":"a4","account":"A","warehouse":"BI","finished_at":"130","credits":"6.00"}
  ],
  "policies": [
    {"rule_id":"ACCOUNT_CREDITS","scope":"account","metric":"credits","window_seconds":"300","limit":"12.00"}
  ]
}

Output:
[
  {"query_id":"a1","alerts":[]},
  {"query_id":"a2","alerts":[]},
  {"query_id":"a3","alerts":[
    {"rule_id":"ACCOUNT_CREDITS","scope":"account","observed":"13.00","limit":"12.00"}
  ]},
  {"query_id":"a4","alerts":[
    {"rule_id":"ACCOUNT_CREDITS","scope":"account","observed":"19.00","limit":"12.00"},
    {"rule_id":"WAREHOUSE_CREDITS","scope":"warehouse","observed":"11.00","limit":"10.00"}
  ]}
]

Explanation: Account credit totals are 6.00, 11.00, 13.00, and 19.00. At a3, ETL has only 6.00 + 2.00 = 8.00, but the account exceeds 12.00. At a4, BI has 5.00 + 6.00 = 11.00, so both the account policy and default warehouse credit policy fire. Neither warehouse has more than two queries.

Example 3:


Input:
{
  "records": [
    {"query_id":"e1","account":"A","warehouse":"ETL","finished_at":"200","credits":"6.00"},
    {"query_id":"e2","account":"A","warehouse":"BI","finished_at":"210","credits":"4.00"},
    {"query_id":"e3","account":"A","warehouse":"ETL","finished_at":"190","credits":"5.00"},
    {"query_id":"e4","account":"A","warehouse":"ETL","finished_at":"220","credits":"1.00"},
    {"query_id":"e5","account":"A","warehouse":"ETL","finished_at":"280","credits":"0.00"}
  ],
  "policies": [
    {"rule_id":"ACCOUNT_BURST","scope":"account","metric":"count","window_seconds":"60","limit":"2"},
    {"rule_id":"ACCOUNT_CREDITS","scope":"account","metric":"credits","window_seconds":"300","limit":"10.00"}
  ]
}

Output:
[
  {"query_id":"e1","alerts":[]},
  {"query_id":"e2","alerts":[]},
  {"query_id":"e3","alerts":[]},
  {"query_id":"e4","alerts":[
    {"rule_id":"ACCOUNT_BURST","scope":"account","observed":"4","limit":"2"},
    {"rule_id":"ACCOUNT_CREDITS","scope":"account","observed":"16.00","limit":"10.00"},
    {"rule_id":"WAREHOUSE_CREDITS","scope":"warehouse","observed":"12.00","limit":"10.00"}
  ]},
  {"query_id":"e5","alerts":[
    {"rule_id":"ACCOUNT_CREDITS","scope":"account","observed":"16.00","limit":"10.00"},
    {"rule_id":"WAREHOUSE_CREDITS","scope":"warehouse","observed":"12.00","limit":"10.00"}
  ]}
]

Explanation: At e2, account count 2 and credits 6.00 + 4.00 = 10.00 equal their limits, so neither account policy fires. At late-arriving e3, the previously processed timestamps 200 and 210 are greater than 190 and excluded: only e3 contributes, with count 1 and credits 5.00. At e4, account count is 4, account credits are 6.00 + 4.00 + 5.00 + 1.00 = 16.00, and ETL credits are 6.00 + 5.00 + 1.00 = 12.00, triggering three policies simultaneously; ETL count is exactly 3 and does not trigger its burst rule. At e5, (220, 280] contains only e5, but the 300-second credit totals remain 16.00 and 12.00.

Notes:


  • There are at most 200,000 records and 32 total policies, including defaults; an empty record list is valid. All input fields are valid, and query IDs are unique within the batch.

  • Timestamps are nonnegative integers, windows are positive integer seconds, and equal timestamps are allowed. Only records already processed, including the current record, count toward a result.

  • Use exact decimal arithmetic or integer hundredths for credits. These are monitoring alerts, not query rejection or warehouse suspension decisions.
Want to practise this one?
Create a free account to open the full question and practise Snowflake questions with AI.
Sign up free

Start practicing Snowflake questions

Sign up for free to access walkthroughs, AI-generated questions, and more.

Get Started Free