Snowflake Interview Questions (2026): What They Actually Ask

Snowflake has three independent layers. Database storage holds table data as compressed, columnar micro-partitions in cloud object storage. Compute is a set of virtual warehouses: clusters that read that shared storage but don't own any of it. Cloud services sits on top and handles authentication, metadata, query parsing and optimization, and access control for the whole account. Because storage and compute scale separately, a dashboard team and an ETL job can run on different warehouses against the same tables without competing for the same compute.

interviewing.io notes that Snowflake's questions expect more database knowledge than the average company. This one sets up most of the product questions on this page, since pruning, multi-cluster scaling and zero-copy cloning all follow from it. Connect the separation to one concrete consequence instead of reciting the diagram.

ROW_NUMBER gives every row a distinct number, even rows with identical values. RANK gives tied rows the same rank and then skips, so two rows tied at 1 push the next row to 3. DENSE_RANK gives tied rows the same rank without skipping, so the next row gets 2. For scores 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4; RANK gives 1, 2, 2, 4; DENSE_RANK gives 1, 2, 2, 3.

TechPrep names Department Top Three Salaries as an example of the simpler SQL questions, and it turns on exactly this choice: "top three salaries" usually means three distinct salary values, which is DENSE_RANK, while ROW_NUMBER = 1 silently drops ties.

SELECT department_id, employee_id, salary
FROM employees
QUALIFY DENSE_RANK() OVER (
  PARTITION BY department_id
  ORDER BY salary DESC
) <= 3;

Aced lists this among the behavioral questions candidates faced, and TechPrep describes the values interview as firmly focused on ownership. Snowflake's own description of Own It reads: "Take initiative and ownership. Be responsible." So pick a project where you set the direction, not one where you owned a slice of someone else's plan.

Be specific about what ownership meant: deciding requirements, making technical calls, unblocking other people, and answering for the result, including the parts that went wrong. TechPrep warns that vague team-level answers score poorly; say what you did, decided or built. Have an answer ready for "what would you do differently now?"

Aced reports this prompt from Snowflake loops. Snowflake's Own It value gives you the structure almost word for word: if you make a mistake, "own it, fix it, learn from it and move on." Name the mistake plainly, explain how you contained or fixed it, and describe the change you made so it couldn't happen the same way again.

The last part is what makes it an ownership answer rather than a story about a bad week. Answers that spread the blame, even partly, work against the value being tested.

Another prompt Aced reports. It maps to Make Each Other The Best, which Snowflake describes as being inclusive and collaborative, offering and asking for help, listening, and giving and asking for constructive feedback. Show what you did to build the working relationship, not only how the task got done.

Concrete beats general. "We communicated well" earns little. "Before the first working session I wrote down our assumptions and asked their lead which constraints we had missed" shows the behavior the value describes.

Aced lists this alongside the unfamiliar-team question. The trap is answering with a project where teamwork was pleasant but not necessary. Pick one where the outcome depended on skills or access you didn't have, and explain how you got them: who you asked, what you offered in return, and how credit was shared at the end.

Snowflake's value text includes "Give praise and celebrate success." Naming what a specific colleague contributed, and how you made that visible, fits it better than a generic "great team effort."

Aced lists this, together with "Tell me about yourself" and "What are you looking for in your next role?", as typical of the first call, which Snowflake says may be with your future manager. A manager will listen for a reason tied to the team's work, not to the brand.

Prepare one product-level reason (a feature or problem area you have used or want to work on) and one role-level reason (the kind of systems you want to build). Aced suggests knowing where the platform is heading, such as Cortex AI, if your background fits it.

Snowflake says its four-stage engineering process can take two to four weeks. Aced gives the same range and adds that senior and staff loops can run four to six weeks; TechPrep says two to six weeks. After the panel, Snowflake says the debrief is typically within a few days. If you haven't heard back within 30 days of applying, Snowflake's page suggests the role wasn't a match.

It helps more than at most companies. interviewing.io says that because Snowflake is a database company, many questions need more database knowledge than average, and Aced says nearly every question tests how you reason about data. Language expectations depend on the team: Aced reports SQL, Python and Java for platform and data teams, and strong C++ for core database and query-engine teams.

A practical minimum before the panel: the storage, compute and cloud services split, micro-partitions and pruning, virtual warehouses, and Time Travel and cloning. All four are covered by questions on this page.

interviewing.io puts the coding questions at around a LeetCode medium, with a Snowflake-specific twist and a focus on database internals. TechPrep says the rounds skew harder than average, with graphs, dynamic programming and concurrency, and describes its online assessment as two to three Medium to Hard problems in 90 to 120 minutes. Aced calls the technical screen the hardest round, because prompts that look simple get harder through follow-ups about how data is processed, stored and moved.

The common thread across all three: clarify the requirements first. Aced's tip is blunt: you can lose the round by solving the wrong problem.

TechPrep says SQL appears across several stages, including the online assessment and sometimes the phone screen, even for backend roles, with a range from simple queries to rolling averages and window functions, plus semi-structured data such as JSON extraction. Aced says SQL knowledge is critical for the role.

Cover window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, rolling frames), date-gap filling, MERGE, recursive CTEs, and two Snowflake features you won't find in standard ANSI SQL: QUALIFY and LATERAL FLATTEN on VARIANT columns.

Not in the official outline. Snowflake's page describes screens, technical interviews and a panel, and notes that some teams and roles add steps. A prep guide on techinterview.org says some senior and staff roles get a 4 to 6 hour take-home on a realistic engineering problem; none of the other sources we checked mention one. Ask your recruiter after the first screen exactly which steps your loop includes.

Snowflake says a 30-minute Tech Talk presentation may be part of the panel depending on the level and role. interviewing.io describes a 30-minute presentation of a past project for mid-to-senior (IC3 and IC4) and principal (IC5 and above) candidates, usually to several engineers from the team. TechPrep says it is followed by about 30 minutes of questions on trade-offs, decisions and ownership.

Treat the talk as the setup for the questions. Pick a project where you made real decisions under real constraints, and be ready to defend the ones you'd change now.

Snowflake decides, and it has published guidance: its hiring page links to a Snowflake article on how and when to use AI in your Snowflake interview. The wording implies the answer depends on the round. We could not load the article's text on 2 October 2026, so we won't paraphrase rules we haven't read. Read it from the hiring page and ask your recruiter, for each round, whether AI tools are allowed and which ones. Use them only where Snowflake says you can.

Aced reports that some 2026 candidates had an AI-conducted screening interview early in the process, with no live engineer, covering their experience, recent projects and the challenges they worked through. It says Snowflake has not confirmed it as a standard step, and that candidates named HackerRank's AI interviewer as the tool, which is also unconfirmed.

If you get one, prepare as you would for a hiring manager call: structured answers about a recent project, your specific contribution, and a technical decision with its trade-offs.

A virtual warehouse is a cluster of compute that runs queries, loads and DML. Sizes run from X-Small upward, and each step up doubles the compute. Many warehouses can point at the same data without affecting each other, and each is billed only for the credits it uses while running.

Auto-suspend stops a warehouse after a period of inactivity, so it stops consuming credits; auto-resume starts it again when a query arrives. The follow-up worth knowing: Snowflake's docs say each running warehouse keeps a cache of table data it has read, and that cache is dropped when the warehouse suspends. A very short auto-suspend on a bursty workload saves credits but makes the first queries after each resume read from remote storage again. It's a trade-off between credit spend and cache hits, not a setting to minimise blindly.

All three store data in the same micro-partition format, so query performance is the same. What differs is protection and lifetime, which drives storage cost. Permanent tables get the Time Travel period your edition allows (up to 1 day on Standard, up to 90 days on Enterprise and above) followed by 7 days of Fail-safe, during which Snowflake can recover data that you can no longer query. Transient tables have no Fail-safe period, and Time Travel of at most 1 day.

Temporary tables exist only for the session that created them and also have no Fail-safe. Transient tables suit staging and intermediate data you can rebuild from source, because you don't pay Fail-safe storage for it. A detail from the docs worth mentioning: if a transient table is cloned from a permanent one and still shares micro-partitions with it, those shared bytes only enter Fail-safe when the transient clone is dropped.

CLONE creates a copy of a database, schema or table almost instantly, because micro-partitions are immutable. Snowflake copies no data blocks at clone time. It creates new metadata that points at the same micro-partitions the source uses, so the time to clone depends on metadata, not on table size.

The clone only uses its own storage once it diverges. An update to the clone writes new micro-partitions that belong to the clone alone, and the original is untouched. That makes a full-size dev copy of production cheap to create, and you pay only for what changes. Combined with Time Travel, you can clone a table as it was before a bad deploy using an AT or BEFORE clause.

A stage is a named location for files before or after loading. Internal stages are storage Snowflake manages (user, table or named stages). External stages point at storage you own, such as an S3 bucket, Azure container or GCS bucket, usually through a storage integration.

COPY INTO is batch loading: you run it, it loads the files in the stage, and it keeps load metadata so re-running it doesn't load the same files twice. It suits data that arrives in batches. Snowpipe is continuous loading: an event notification tells it a file has landed, and it loads the file with compute Snowflake manages. Update your cost answer if you learned the old model: Snowflake's docs now say Snowpipe is billed as a fixed credit amount per GB loaded, replacing the former mix of compute time and a per-file charge. Text files are charged on their uncompressed size.

Aced lists this among questions candidates reported from the technical screen. Walk the string and keep a set of characters seen so far; return True the moment you see a repeat. That is O(n) time and O(k) space, where k is the alphabet size, so it is effectively constant space for ASCII.

Expect follow-ups, since that's where Aced says the screen gets harder: what if the input is a stream too large for memory (the set is still bounded by the alphabet), what about Unicode (decide whether "é" written as one or two code points counts as the same character), and can you do it with no extra space (sort, then compare neighbours, at O(n log n)).

def has_duplicate(s: str) -> bool:
    seen = set()
    for ch in s:
        if ch in seen:
            return True
        seen.add(ch)
    return False

assert has_duplicate("snowflake") is False
assert has_duplicate("warehouse") is True
assert has_duplicate("") is False

TechPrep says concurrency is a recurring theme in the onsite coding rounds and that you may be asked to implement thread-safe data structures. A bounded producer-consumer queue is the standard warm-up. Use a deque, one lock, and two condition variables on that lock: producers wait while the queue is full, consumers wait while it is empty, and each side notifies the other after a successful operation.

Explain why the waits sit inside while loops (spurious wakeups, and another thread may have taken the slot first), and what you'd add next: timeouts on put and get, and a close() that wakes all waiters so threads can exit cleanly.

from collections import deque
import threading

class BoundedQueue:
    def __init__(self, maxsize: int):
        self._queue = deque()
        self._maxsize = maxsize
        self._lock = threading.Lock()
        self._not_full = threading.Condition(self._lock)
        self._not_empty = threading.Condition(self._lock)

    def put(self, item):
        with self._not_full:
            while len(self._queue) >= self._maxsize:
                self._not_full.wait()
            self._queue.append(item)
            self._not_empty.notify()

    def get(self):
        with self._not_empty:
            while not self._queue:
                self._not_empty.wait()
            item = self._queue.popleft()
            self._not_full.notify()
            return item

q = BoundedQueue(2)
out = []
consumer = threading.Thread(target=lambda: [out.append(q.get()) for _ in range(5)])
consumer.start()
for i in range(5):
    q.put(i)
consumer.join()
assert out == [0, 1, 2, 3, 4]

Aced reports this prompt, worded that openly, from Snowflake's technical screen. The open wording is the test: ask what the stream contains, what the class must answer, and how much memory you may use before writing anything. A common concrete version is a fixed-capacity window over the most recent values that reports their average.

A fixed-size circular buffer gives O(1) add and O(1) average if you maintain a running sum. Interviewers watch the wraparound arithmetic and what happens before the buffer is full. Likely follow-ups: make it thread-safe, report the maximum as well (a monotonic deque), or handle values arriving out of order by timestamp.

class WindowAverage:
    def __init__(self, capacity: int):
        self.buf = [0.0] * capacity
        self.cap = capacity
        self.count = 0
        self.head = 0  # index of the next write
        self.total = 0.0

    def add(self, value: float) -> float:
        if self.count == self.cap:
            self.total -= self.buf[self.head]
        else:
            self.count += 1
        self.buf[self.head] = value
        self.total += value
        self.head = (self.head + 1) % self.cap
        return self.total / self.count

w = WindowAverage(3)
assert [w.add(v) for v in [3, 6, 9, 12]] == [3.0, 4.5, 6.0, 9.0]

interviewing.io lists Decode String as a common question from Snowflake interviewers on its platform, and has a recorded Snowflake-interviewer replay of it; it notes the data comes from mock interviews, so the real round may differ. Use a stack: accumulate digits into a count, push the current string and count on an opening bracket, and on a closing bracket pop them and append the current string repeated count times.

The stack holds the context from before the current bracket pair, not the content inside it. Multi-digit counts such as 12 are the usual bug.

def decode_string(s: str) -> str:
    stack, cur, num = [], "", 0
    for ch in s:
        if ch.isdigit():
            num = num * 10 + int(ch)
        elif ch == "[":
            stack.append((cur, num))
            cur, num = "", 0
        elif ch == "]":
            prev, k = stack.pop()
            cur = prev + cur * k
        else:
            cur += ch
    return cur

assert decode_string("3[a2[c]]") == "accaccacc"
assert decode_string("2[ab]3[c]") == "ababccc"
assert decode_string("12[x]") == "x" * 12

TechPrep names a concurrent LRU cache as an example of the thread-safe structures Snowflake asks for. Combine a hash map (key to node) with a doubly linked list ordered from most to least recently used. get moves the node to the front; put inserts at the front and evicts from the tail when over capacity. Both are O(1).

For the concurrent version, the simplest correct answer is one lock around every operation, since even get mutates the list. Then discuss the cost: one lock serialises all access. Sharding the cache by key hash into N independent LRUs, each with its own lock, cuts contention at the price of approximate global LRU order.

import threading

class Node:
    def __init__(self, key, val):
        self.key, self.val = key, val
        self.prev = self.next = None

class LRUCache:
    def __init__(self, capacity: int):
        self.cap = capacity
        self.map = {}
        self.head, self.tail = Node(0, 0), Node(0, 0)
        self.head.next, self.tail.prev = self.tail, self.head
        self.lock = threading.Lock()

    def _remove(self, node):
        node.prev.next, node.next.prev = node.next, node.prev

    def _add_front(self, node):
        node.next, node.prev = self.head.next, self.head
        self.head.next.prev = node
        self.head.next = node

    def get(self, key):
        with self.lock:
            node = self.map.get(key)
            if node is None:
                return -1
            self._remove(node)
            self._add_front(node)
            return node.val

    def put(self, key, value):
        with self.lock:
            if key in self.map:
                self._remove(self.map[key])
            node = Node(key, value)
            self.map[key] = node
            self._add_front(node)
            if len(self.map) > self.cap:
                lru = self.tail.prev
                self._remove(lru)
                del self.map[lru.key]

c = LRUCache(2)
c.put(1, 1); c.put(2, 2); c.get(1); c.put(3, 3)
assert c.get(2) == -1 and c.get(1) == 1 and c.get(3) == 3

Aced reports this from the Snowflake technical screen ("given an array of intervals and a new interval, adjust the array to fit the new interval"). Do it in one pass: copy intervals that end before the new one starts, merge every interval that overlaps it by widening its start and end, then copy the rest. O(n) time.

Clarify before coding whether touching intervals such as [1,2] and [2,3] count as overlapping, and whether the input is guaranteed sorted. A data-flavoured follow-up: the intervals are time ranges in a file too large for memory. The same single pass works as a stream.

def insert_interval(intervals, new):
    out, i, n = [], 0, len(intervals)
    start, end = new
    while i < n and intervals[i][1] < start:
        out.append(intervals[i]); i += 1
    while i < n and intervals[i][0] <= end:
        start = min(start, intervals[i][0])
        end = max(end, intervals[i][1])
        i += 1
    out.append([start, end])
    out.extend(intervals[i:])
    return out

assert insert_interval([[1, 3], [6, 9]], [2, 5]) == [[1, 5], [6, 9]]
assert insert_interval([[1, 2], [3, 5], [6, 7], [8, 10]], [4, 8]) == [[1, 2], [3, 10]]

This is interviewing.io's example of a Snowflake coding question, and it is deliberately loose. Pin down the format, the aggregation (count per key, sum per key, top N), and the file size. Then stream the file line by line instead of loading it, aggregate into a dictionary, and use a heap for the top N, which costs O(n log k) instead of sorting everything.

Follow-ups move toward data systems: malformed lines (count and skip them, don't crash), a file larger than one machine can process in time (split by byte range, aggregate per chunk, merge the partial counts), and a key space too large for memory (hash-partition keys to disk, then aggregate each partition).

import heapq
from collections import Counter

def top_endpoints(lines, k=3):
    counts, bad = Counter(), 0
    for line in lines:
        parts = line.split()
        if len(parts) < 3:
            bad += 1
            continue
        counts[parts[2]] += 1  # format: <ts> <level> <endpoint> ...
    return heapq.nlargest(k, counts.items(), key=lambda kv: kv[1]), bad

logs = ["1 INFO /a", "2 INFO /b", "3 WARN /a", "garbage", "4 INFO /c", "5 INFO /a"]
top, bad = top_endpoints(logs, 2)
assert top[0] == ("/a", 3) and bad == 1

interviewing.io has a recorded replay of a Snowflake engineer interviewing a candidate on an ID generator. Start with requirements: unique across machines, roughly time-ordered or not, 64-bit or not, and expected rate per machine. A common answer packs a millisecond timestamp, a worker ID and a per-millisecond sequence into 64 bits, the scheme Twitter popularised under the name Snowflake IDs (no relation to the company).

The details interviewers probe: the sequence overflowing within one millisecond (wait for the next one), the clock moving backwards (refuse to issue IDs or wait until it catches up), how worker IDs are assigned uniquely, and thread safety inside one process.

import threading, time

class IdGenerator:
    EPOCH_MS = 1_700_000_000_000

    def __init__(self, worker_id: int):
        assert 0 <= worker_id < 1024  # 10 bits
        self.worker_id = worker_id
        self.last_ms = -1
        self.seq = 0
        self.lock = threading.Lock()

    def next_id(self) -> int:
        with self.lock:
            now = int(time.time() * 1000)
            if now < self.last_ms:
                raise RuntimeError("clock moved backwards")
            if now == self.last_ms:
                self.seq = (self.seq + 1) & 0xFFF  # 12 bits
                if self.seq == 0:
                    while now <= self.last_ms:
                        now = int(time.time() * 1000)
            else:
                self.seq = 0
            self.last_ms = now
            return ((now - self.EPOCH_MS) << 22) | (self.worker_id << 12) | self.seq

g = IdGenerator(7)
ids = [g.next_id() for _ in range(10000)]
assert len(set(ids)) == 10000 and ids == sorted(ids)

TechPrep names Design Hit Counter as an example of Snowflake's low-level design questions, which it says focus on components that are thread-safe, memory-efficient or built for high throughput. The memory-efficient answer keeps one bucket per second rather than one entry per hit: a deque of (second, count) pairs, dropping buckets older than 300 seconds.

Then cover what the interviewer will push on: very high hit rates (buckets keep memory at 300 entries regardless of traffic), many threads (a lock, or per-thread counters merged on read), and many machines (each counts locally and a reader sums them, accepting a slightly stale total).

from collections import deque
import threading

class HitCounter:
    WINDOW = 300

    def __init__(self):
        self.buckets = deque()  # (second, count)
        self.total = 0
        self.lock = threading.Lock()

    def _evict(self, now):
        while self.buckets and self.buckets[0][0] <= now - self.WINDOW:
            self.total -= self.buckets.popleft()[1]

    def hit(self, ts: int):
        with self.lock:
            if self.buckets and self.buckets[-1][0] == ts:
                sec, cnt = self.buckets.pop()
                self.buckets.append((sec, cnt + 1))
            else:
                self.buckets.append((ts, 1))
            self.total += 1
            self._evict(ts)

    def get_hits(self, ts: int) -> int:
        with self.lock:
            self._evict(ts)
            return self.total

h = HitCounter()
for t in (1, 2, 3, 3, 300):
    h.hit(t)
assert h.get_hits(300) == 5 and h.get_hits(301) == 4 and h.get_hits(605) == 0

TechPrep gives Design In-Memory File System as its other low-level design example. Model it as a tree of nodes: each node is either a directory (a map from name to child) or a file (a content buffer). Path operations split on "/" and walk from the root, creating directories on mkdir and files on first write.

Points that earn credit: ls on a file path returns just the file name; ls output is sorted, so either sort on read or keep children in a sorted structure; appending to large files should not copy the whole content each time (store a list of chunks); and for the concurrency follow-up, a lock per directory node lets writers in different subtrees proceed in parallel, with locks always taken from root to leaf to avoid deadlock.

QUALIFY filters on the result of a window function, the way HAVING filters on aggregates. Snowflake evaluates it after window functions are computed, so you can keep the latest row per key without wrapping the query in a subquery or CTE.

QUALIFY is not part of standard ANSI SQL, which is why it is a quick way to show you have worked in Snowflake. Mention the tie case: if two rows share the same event_ts, ROW_NUMBER picks one arbitrarily, so add a tiebreaker column to the ORDER BY if the result must be repeatable.

SELECT user_id, event_id, event_ts, event_data
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id, event_id
  ORDER BY event_ts DESC, ingest_id DESC
) = 1;

TechPrep says Snowflake tests semi-structured data handling such as JSON extraction and nested data. Use LATERAL FLATTEN to turn an array inside a VARIANT into one row per element. FLATTEN returns columns including SEQ, KEY, PATH, INDEX, VALUE and THIS; you mostly use VALUE with colon path notation and a cast.

Two details worth saying out loud: cast money to NUMBER with a scale rather than FLOAT, and use OUTER => TRUE if rows whose array is empty or missing must still appear in the output.

SELECT
  o.order_id,
  f.value:product_id::STRING      AS product_id,
  f.value:quantity::INTEGER       AS quantity,
  f.value:price::NUMBER(12, 2)    AS price
FROM orders o,
LATERAL FLATTEN(input => o.order_data:line_items) f
WHERE f.value:price::NUMBER(12, 2) > 100;

TechPrep mentions rolling averages among the harder SQL problems. A window of ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is only correct if every date has a row, so build a date spine first and left join the activity to it, treating missing days as zero.

A Snowflake-specific catch: SEQ4() inside GENERATOR is not guaranteed to be gap-free, so derive the day offset with ROW_NUMBER() instead. Say which definition of the average you chose for the first six days of the range (a partial window, as below, or NULL until seven days exist).

WITH spine AS (
  SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2026-01-01'::DATE) AS d
  FROM TABLE(GENERATOR(ROWCOUNT => 365))
),
daily AS (
  SELECT event_date AS d, COUNT(DISTINCT user_id) AS dau
  FROM events
  GROUP BY event_date
)
SELECT
  s.d,
  COALESCE(daily.dau, 0) AS dau,
  AVG(COALESCE(daily.dau, 0)) OVER (
    ORDER BY s.d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS dau_7d_avg
FROM spine s
LEFT JOIN daily ON daily.d = s.d
ORDER BY s.d;

Snowflake stores tables as micro-partitions: immutable, compressed, columnar units, each holding roughly 50 to 500 MB of uncompressed data. For each micro-partition it keeps metadata, including the range of values in each column. When a query filters on a column, Snowflake uses that metadata to skip micro-partitions that cannot contain matching rows. This pruning is why the same query can be fast on well-ordered data and slow on randomly ordered data.

The follow-up is clustering. Defining a clustering key asks Snowflake to keep rows with similar key values in the same micro-partitions, which narrows the value ranges and improves pruning for filters on that key. Mention the cost too: automatic reclustering uses credits, so it pays off on large tables that are filtered on the key far more often than they are written.

Time Travel lets you query, clone or restore data as it was at an earlier point, within the table's retention period (up to 1 day on Standard edition, up to 90 days on Enterprise and above). You use AT or BEFORE with a timestamp, offset or query ID, and UNDROP restores dropped tables, schemas and databases.

The reason it is cheap: DML never overwrites a micro-partition. It writes new micro-partitions and updates the metadata to point at them, so the old versions are still there until retention expires. Reading the past means reading an older metadata version, not replaying a log. The trade-off to mention is storage: high-churn tables with long retention keep many superseded micro-partitions, which you pay for.

MERGE INTO the target USING the source ON a matching key; WHEN MATCHED THEN UPDATE the changed columns, WHEN NOT MATCHED THEN INSERT the new row. It is the usual way to write an idempotent daily load: running the same MERGE twice on the same source leaves the target in the same state, which a plain INSERT does not.

The question interviewers follow with: what if the source has two rows for the same customer_id? Then one target row matches several source rows and the outcome is not well defined. Deduplicate the source first, for example with QUALIFY ROW_NUMBER() = 1 per key, as below.

MERGE INTO customers AS t
USING (
  SELECT * FROM staged_customers
  QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1
) AS s
  ON t.customer_id = s.customer_id
WHEN MATCHED THEN
  UPDATE SET t.email = s.email, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, created_at)
  VALUES (s.customer_id, s.email, s.updated_at);

Snowflake's documented syntax is WITH [RECURSIVE] name AS (anchor_clause UNION ALL recursive_clause). The anchor selects the starting employee; the recursive clause joins the CTE back to the employees table to walk up one manager per iteration, and stops when an iteration returns no rows.

The risk to raise unprompted: bad data with a cycle (A manages B, B manages A) makes the query loop. Snowflake's docs say such a query keeps running until it times out under STATEMENT_TIMEOUT_IN_SECONDS or is cancelled. Guard against it with a depth limit in the recursive clause, as below.

WITH RECURSIVE mgmt_chain AS (
  SELECT employee_id, manager_id, name, 1 AS depth
  FROM employees
  WHERE employee_id = 501

  UNION ALL

  SELECT e.employee_id, e.manager_id, e.name, mc.depth + 1
  FROM employees e
  JOIN mgmt_chain mc ON e.employee_id = mc.manager_id
  WHERE mc.depth < 50
)
SELECT * FROM mgmt_chain ORDER BY depth;

Resizing a warehouse (scaling up) gives each query more compute, which helps large, slow queries. A multi-cluster warehouse (scaling out) adds more clusters of the same size when queries start queueing, then shuts them down as load drops. Scaling up makes one query faster; scaling out lets more queries run at once. Multi-cluster warehouses are an Enterprise Edition feature.

The scaling policy decides how eagerly clusters are added. Standard favours starting clusters to avoid queueing. Economy, in the docs' words, conserves credits by keeping running clusters fully loaded rather than starting more, which can mean queries queue for longer. Mixing up "bigger warehouse" and "more clusters" is the common wrong answer, because they fix different bottlenecks.

Persisted query results are kept for 24 hours, and each reuse resets that period, up to 31 days from the first run. A new query reuses a result only if, among other conditions in the docs: the query text matches the earlier one exactly, the table data behind it has not changed, the query has no non-reusable functions such as RANDOM or UUID_STRING, it uses no external functions or hybrid tables, and the role running it has the required privileges.

"Exactly" is the detail candidates get wrong. Snowflake's docs show that adding a table alias or writing the keywords in lowercase is enough to prevent full reuse. Reuse is on by default and can be turned off with the USE_CACHED_RESULT parameter, which you should do when benchmarking warehouse performance.

TechPrep lists pushing back on decisions you believed were wrong among the ownership prompts. The value that fits best is Integrity Always, which Snowflake describes as speaking up candidly even when it is uncomfortable, and which ends with "Commit fully when decisions are made."

That last sentence is the shape of a strong answer: you made the case with evidence rather than intuition, you listened, and once a decision was made you committed to it, even if it went against you. Say what the decision-maker's reasoning was. "I disagreed and eventually let it go" without that reasoning sounds like neither conviction nor commitment.

Aced lists customer focus among what interviewers look for, and Put Customers First is the first of Snowflake's values: listen to customers, understand their needs and pain points, and focus on what matters to them. An internal stakeholder can be the customer if you don't have external-customer stories.

The arc that works: the customer asked for X, you found out what their workflow actually needed, and you built Y, which solved the real problem more simply. The technical decision should change because of what you learned. If it didn't, it is a delivery story, not a customer story.

TechPrep names Merge k Sorted Lists as an example of the difficulty candidates report. Push the first element of each list into a min-heap as (value, list index, position), pop the smallest, and push the next element from the same list. That is O(N log k) time for N total elements, and the heap never holds more than k entries.

The follow-up fits Snowflake's data focus: what if k is very large, or the lists are files on disk? The heap stays O(k) in memory, which is how external merge sort combines sorted runs. If k is too large even for that, merge in rounds, combining groups of lists and then merging the results.

import heapq

def merge_k_sorted(lists):
    heap = [(lst[0], i, 0) for i, lst in enumerate(lists) if lst]
    heapq.heapify(heap)
    out = []
    while heap:
        val, i, j = heapq.heappop(heap)
        out.append(val)
        if j + 1 < len(lists[i]):
            heapq.heappush(heap, (lists[i][j + 1], i, j + 1))
    return out

assert merge_k_sorted([[1, 4, 5], [1, 3, 4], [2, 6], []]) == [1, 1, 2, 3, 4, 4, 5, 6]

Another problem TechPrep names. Build a trie from the dictionary, then run DFS from every cell, following only paths that exist in the trie. When you reach a node that ends a word, record the word and clear the marker so it isn't reported twice. Mark cells as visited during the DFS and restore them on the way back.

Without the trie, you would search for each word separately, repeating the same board walks for every shared prefix. With it, a path is abandoned as soon as no word starts with it. A further optimisation is to prune trie nodes whose children have all been found, so later searches skip dead branches.

TechPrep names this one too. Sort jobs by end time. For each job, either skip it or take it and add its profit to the best total from jobs that end at or before its start, found by binary search. Keep a list of (end time, best profit so far) that only grows when the best profit improves, so it stays sorted on both fields.

The binary search is where off-by-one errors happen. bisect_right with (start, infinity) finds the last entry whose end time is at most start, so a job ending exactly when another starts counts as compatible. Confirm that rule with the interviewer.

import bisect

def job_scheduling(start_time, end_time, profit):
    jobs = sorted(zip(end_time, start_time, profit))
    dp = [[0, 0]]  # [end_time, best_profit]
    for end, start, p in jobs:
        i = bisect.bisect_right(dp, [start, float("inf")]) - 1
        if dp[i][1] + p > dp[-1][1]:
            dp.append([end, dp[i][1] + p])
    return dp[-1][1]

assert job_scheduling([1, 2, 3, 3], [3, 4, 5, 6], [50, 10, 40, 70]) == 120
assert job_scheduling([1, 2, 3, 4, 6], [3, 5, 10, 6, 9], [20, 20, 100, 70, 60]) == 150

Reads don't block writes. Because micro-partitions are immutable, a write creates new micro-partitions and a new table version; a query that started earlier keeps reading the version it began with. Writes against each other are a different story, and Snowflake's transactions docs are specific: UPDATE, DELETE and MERGE statements hold locks that generally prevent them from running in parallel with other UPDATE, DELETE and MERGE statements on the table. Most INSERT and COPY statements only write new partitions, so they can often run in parallel with each other.

A blocked statement waits for the lock or times out. LOCK_TIMEOUT controls how long, and its default is 43,200 seconds (12 hours). Hybrid tables are the exception: they lock individual rows. The docs also warn that overlapping scoped transactions in stored procedures can deadlock if they touch the same table. A good answer covers both halves, readers versus writers and writers versus writers, rather than stopping at "it's MVCC."

TechPrep gives "a key-value store with transactional semantics" as an example of Snowflake's concurrency-flavoured coding. Keep the committed data in a dict and each open transaction as a layer of pending changes on a stack. Reads check layers from the newest down, then the base. Deletes need a tombstone so a delete inside a transaction hides the committed value. Rollback pops a layer; commit folds a layer into the one below, or into the base for the outermost one.

Then the concurrency discussion: with one global lock this is correct but serial. With per-client transactions you need isolation rules, such as snapshot reads plus a check at commit time that no key you wrote changed since you began, which is optimistic concurrency, versus locking keys on first write. Say which conflicts each approach detects.

import threading

_TOMBSTONE = object()

class KVStore:
    def __init__(self):
        self.base = {}
        self.layers = []
        self.lock = threading.RLock()

    def get(self, key):
        with self.lock:
            for layer in reversed(self.layers):
                if key in layer:
                    v = layer[key]
                    return None if v is _TOMBSTONE else v
            return self.base.get(key)

    def set(self, key, value):
        with self.lock:
            (self.layers[-1] if self.layers else self.base)[key] = value

    def delete(self, key):
        with self.lock:
            if self.layers:
                self.layers[-1][key] = _TOMBSTONE
            else:
                self.base.pop(key, None)

    def begin(self):
        with self.lock:
            self.layers.append({})

    def rollback(self):
        with self.lock:
            if not self.layers:
                raise RuntimeError("no transaction")
            self.layers.pop()

    def commit(self):
        with self.lock:
            if not self.layers:
                raise RuntimeError("no transaction")
            top = self.layers.pop()
            target = self.layers[-1] if self.layers else self.base
            for k, v in top.items():
                if v is _TOMBSTONE and target is self.base:
                    self.base.pop(k, None)
                else:
                    target[k] = v

kv = KVStore()
kv.set("a", 1)
kv.begin(); kv.set("a", 2); kv.begin(); kv.delete("a")
assert kv.get("a") is None
kv.rollback(); assert kv.get("a") == 2
kv.commit(); assert kv.get("a") == 2 and kv.layers == []

A prep guide on techinterview.org says Snowflake's applied coding round can involve database primitives such as implementing a hash join or a sort operator, and TechPrep lists join algorithms and query execution among the internals worth studying. Build a hash table on the smaller input (build side) keyed on the join column, then stream the larger input (probe side) and emit a row for every match.

Follow-ups: the build side doesn't fit in memory (partition both inputs by a hash of the key, spill partitions to disk, then join partition by partition, which is a Grace hash join); NULL keys never match in an inner join; skewed keys with millions of rows on both sides blow up the output, so detect hot keys and handle them separately; and why a hash join beats nested loops for equality joins but can't do range conditions.

from collections import defaultdict

def hash_join(build, probe, build_key, probe_key):
    table = defaultdict(list)
    for row in build:
        k = row[build_key]
        if k is not None:
            table[k].append(row)
    for row in probe:
        for match in table.get(row[probe_key], ()):
            yield {**match, **row}

customers = [{"cid": 1, "name": "A"}, {"cid": 2, "name": "B"}, {"cid": None, "name": "X"}]
orders = [{"oid": 10, "cust": 1}, {"oid": 11, "cust": 1}, {"oid": 12, "cust": 3}]
joined = list(hash_join(customers, orders, "cid", "cust"))
assert [r["oid"] for r in joined] == [10, 11] and joined[0]["name"] == "A"

TechPrep lists MVCC, LSM trees versus B-trees, columnar storage, query planning and vectorized execution as database internals that are "all fair game," with deeper questions for Core Database teams. A B-tree updates pages in place, giving fast point reads and range scans with predictable latency, at the cost of random writes and write amplification from page splits. An LSM tree buffers writes in memory, flushes them as sorted immutable files, and merges them in the background, so writes are sequential and fast while reads may check several files (bloom filters help) and compaction costs I/O.

Tie it back: write-heavy ingestion and key-value stores favour LSM; read-heavy OLTP with range scans favours B-trees. An analytical warehouse like Snowflake uses neither as its main table format: immutable columnar micro-partitions with per-partition metadata for pruning, which shares the LSM idea of never modifying files in place.

techinterview.org lists this as a Snowflake system design prompt. Build on immutable files. A table is a versioned list of file references held in a metadata store. Each DML commit writes new files and atomically publishes a new version that adds the new files and drops the replaced ones from the list. A query at time T reads the version current at T. Old files are kept until no version within the retention window references them, then garbage-collected.

A clone is a new table whose first version copies the source's file list, so nothing is copied except metadata. Later writes to either table create files that only that table references. The hard parts to discuss: reference counting or mark-and-sweep across tables that share files, so garbage collection never deletes a file a clone still uses; metadata store scale and consistency, since every query reads it; and how retention settings interact when a clone outlives its source.

The same techinterview.org prompt extends to cross-region replication. With immutable files, replication means copying new files and then the metadata version that references them, in that order, so the secondary never points at a file it doesn't have. Replicate on a schedule: the interval sets how much data you can lose (RPO), and the time to promote the secondary and redirect clients sets recovery time (RTO).

Discuss the consistency choice explicitly. Asynchronous replication keeps writes fast, but readers on the secondary see data that is up to one interval old. Synchronous replication would add cross-region latency to every commit. For context, Snowflake's own product replicates databases to read-only secondaries in other regions or clouds that are refreshed from the primary, which matches the asynchronous model; say that the internals you describe are your design, not Snowflake's implementation.

A stream records an offset into a table's change history and exposes the rows changed since it was last consumed, with METADATA$ACTION, METADATA$ISUPDATE and METADATA$ROW_ID columns. An update appears as a DELETE and an INSERT pair with ISUPDATE set to TRUE. A task runs SQL on a schedule. Consuming the stream in a DML statement, such as the MERGE below, advances its offset when the transaction commits.

The detail that separates a real answer: a stream becomes stale if its offset falls outside the source table's data retention period. Snowflake extends retention for unconsumed streams up to MAX_DATA_EXTENSION_TIME_IN_DAYS (14 days by default), but once a stream is stale its unconsumed changes are gone and you must recreate it. Check STALE_AFTER with SHOW STREAMS and schedule the task well inside that window. Adding WHEN SYSTEM$STREAM_HAS_DATA to a scheduled task means it only runs when the stream has new data, as in Snowflake's own task examples.

CREATE OR REPLACE STREAM orders_stream ON TABLE raw_orders;

CREATE OR REPLACE TASK merge_orders
  WAREHOUSE = etl_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  MERGE INTO orders t
  USING (SELECT * FROM orders_stream WHERE METADATA$ACTION = 'INSERT') s
    ON t.order_id = s.order_id
  WHEN MATCHED THEN UPDATE SET t.status = s.status
  WHEN NOT MATCHED THEN INSERT (order_id, status) VALUES (s.order_id, s.status);

ALTER TASK merge_orders RESUME;

This is interviewing.io's example of a Snowflake system design question: take in events that happened at a certain time, organise them so you can retrieve them quickly and insert new ones quickly, and keep track of the trade-offs. Buffer incoming events in memory and append them to a write-ahead log for durability, flush in batches into files partitioned by time bucket, and keep a small index of each file's minimum and maximum timestamp so a range query opens only the files that overlap.

The trade-offs to name: late-arriving events land in old buckets (accept a small overlap, or compact later); bigger batches mean faster ingest but staler reads; and many small files hurt reads, so compaction merges them. This is close to how Snowflake prunes micro-partitions by min/max metadata, and saying so shows you know the product you would be building.

techinterview.org lists this prompt ("a query result cache that handles 1M queries/sec across 10K customers"). Start with the key: tenant ID, a normalised query text, and the versions of every table the query reads. Including table versions makes invalidation automatic, since a write produces a new version and old keys simply stop matching. Tenant isolation is not optional; two tenants with identical SQL read different data.

Then scale and correctness: a local cache on each coordinator for hot results plus a shared distributed tier; size limits and eviction; results that must never be cached (non-deterministic functions, per-user security policies). A useful contrast: Snowflake's own result cache skips normalisation entirely. Its docs require an exact text match, so a lowercase keyword or a new alias misses the cache. Explain why a real system might accept that (simplicity, no risk of a wrong match) and what normalising would buy.

TechPrep names a distributed rate limiter as a typical Snowflake system design prompt. Limit per tenant on two axes: request rate and an estimate of the compute each query will use. A token bucket per tenant handles bursts well. The distributed problem is sharing the bucket across many gateway nodes: a central store with atomic operations is exact but adds a network hop to every request, while giving each node a share of the tenant's budget and rebalancing periodically avoids the hop but allows small overruns.

State the choice: for most workloads a small overrun is acceptable and cheaper than coordination on every query; where a limit protects shared capacity, enforce it exactly. Also cover what clients see when limited (a clear error and a retry-after hint) and how the limiter behaves if its store is unavailable (fail open or closed, and why).

TechPrep lists a metadata service for a cloud warehouse among Snowflake's system design prompts. Given a table name, the service must return its schema, its current version and the files in that version, quickly, because every query needs this before planning starts. Partition the metadata by tenant so one tenant's heavy DDL or cloning can't slow lookups for others, and cache on query coordinators, since a table's metadata rarely changes between queries seconds apart.

The hard part is consistency while things change. Pin a metadata version when a query is planned and read that version through execution, so a concurrent rename, drop or write can't change the answer partway through. Also discuss how commits update the version atomically and how cached entries are invalidated when they do.

Aced reports this as a real Snowflake system design question and says data security and governance is a recurring theme, with follow-ups on database internals. Scope it first: who the users are, what is sensitive, and which rules apply. Then the parts: classification of sensitive columns, role-based access control, column masking and row-level filtering evaluated at query time, encryption at rest and in transit with key management, and an audit log of who read what.

Push into the internals, since that's where the follow-ups go: masking must be applied inside the query plan so cached results and clones don't leak unmasked data; policies should attach to data (a column or tag) rather than to each query; and the audit trail must be tamper-evident and queryable without becoming a bottleneck on the read path.

Aced reports this two-part prompt from Snowflake's system design round. Keep the first half brief: upload to object storage, an asynchronous transcoding pipeline, metadata in a database, video served through a CDN. Spend the time on what the second half asks for: the cache layer for metadata and hot content.

Decide what to cache (video metadata, view counts, recommendations), the pattern (cache-aside with TTLs is the usual default), and what happens on a viral video: many requests missing at once and hitting the database together. Prevent that with request coalescing, so one request fills the cache while the others wait for it. Then implement the core in code, for example an LRU with TTL, and explain eviction and invalidation when a video's metadata changes.