Snowflake Interview Questions for Data Engineers

Snowflake interview questions tend to circle two themes. The first is money: Snowflake bills compute separately, by the second, for warehouses that start and stop, so candidates are expected to reason about what a workload costs. The second is layout: data lives in micro-partitions, and how well they are clustered decides how much each query reads. This guide works both with numbers, using Snowflake's documented rules and a local model on real taxi data, then covers Time Travel and cloning, which come up in nearly every Snowflake interview.

import numpy as np
import pandas as pd

CREDITS_PER_HOUR = {"X-Small": 1, "Small": 2, "Medium": 4, "Large": 8}   # Gen1 warehouses, from Snowflake's documentation


def billed_seconds(query_starts, query_seconds, auto_suspend):
    """Per-second billing with a 60-second minimum each time the warehouse resumes."""
    total, running_until, resumed_at = 0.0, None, None
    for start in query_starts:
        if running_until is None or start > running_until + auto_suspend:   # suspended: resume and start a new bill
            if resumed_at is not None:
                total += max(60, running_until + auto_suspend - resumed_at)
            resumed_at = start
        running_until = max(running_until or 0, start + query_seconds)
    return total + max(60, running_until + auto_suspend - resumed_at)


print("SF1 an illustrative dashboard: one 20-second query every 5 minutes for 8 hours")
starts = np.arange(0, 8 * 3600, 300)
for suspend in (60, 300, 600):
    secs = billed_seconds(starts, 20, suspend)
    print(f"   auto-suspend {suspend:>3} s: billed {secs / 3600:.2f} h -> {secs / 3600 * CREDITS_PER_HOUR['X-Small']:.2f} X-Small credits")

print("\nSF2 a bigger warehouse for a job that scales perfectly: same credits, less waiting")
for size, minutes in (("X-Small", 16), ("Small", 8), ("Medium", 4)):
    print(f"   {size:<8} {minutes:>2} min -> {minutes / 60 * CREDITS_PER_HOUR[size]:.3f} credits")

print("\nSF3 clustering depth and pruning on real pickup times, 40 micro-partitions")
ts = pd.read_parquet("yellow_tripdata_2024-01.parquet", columns=["tpep_pickup_datetime"]).tpep_pickup_datetime
ts = ts[(ts >= "2024-01-01") & (ts < "2024-02-01")].to_numpy()
day = (np.datetime64("2024-01-15"), np.datetime64("2024-01-16"))
for name, order in (("load order of the file", ts), ("shuffled, as after unordered loads", np.random.default_rng(0).permutation(ts))):
    parts = np.array_split(order, 40)
    lo, hi = np.array([p.min() for p in parts]), np.array([p.max() for p in parts])
    probes = np.sort(ts)[:: len(ts) // 200]                       # 200 points across the month
    depth = np.mean([np.sum((lo <= x) & (hi >= x)) for x in probes])
    scanned = int(np.sum((lo < day[1]) & (hi >= day[0])))
    print(f"   {name:<36} average depth {depth:5.1f} | a one-day query scans {scanned:>2} of 40 partitions")

SF1 an illustrative dashboard: one 20-second query every 5 minutes for 8 hours
   auto-suspend  60 s: billed 2.13 h -> 2.13 X-Small credits
   auto-suspend 300 s: billed 8.01 h -> 8.01 X-Small credits
   auto-suspend 600 s: billed 8.09 h -> 8.09 X-Small credits

SF2 a bigger warehouse for a job that scales perfectly: same credits, less waiting
   X-Small  16 min -> 0.267 credits
   Small     8 min -> 0.267 credits
   Medium    4 min -> 0.267 credits

SF3 clustering depth and pruning on real pickup times, 40 micro-partitions
   load order of the file               average depth   2.8 | a one-day query scans  3 of 40 partitions
   shuffled, as after unordered loads   average depth  39.6 | a one-day query scans 40 of 40 partitions

SF1. How is Snowflake compute billed, and why does auto-suspend matter?

Warehouses consume credits while running: 1 credit per hour for X-Small, doubling with each size, billed per second with a 60-second minimum each time the warehouse starts (Snowflake: warehouses). Auto-suspend decides how long an idle warehouse keeps running. For an illustrative dashboard that runs a 20-second query every 5 minutes for 8 hours, a 60-second auto-suspend billed 2.13 hours, while 300 seconds billed 8.01 hours: the warehouse never got the chance to suspend, because the next query arrived first. Same queries, nearly four times the bill. The trade-off is that a suspended warehouse loses its local cache, so a very short suspend can slow the next query.

SF2. Is a bigger warehouse more expensive?

Not necessarily. A job that scales perfectly costs the same credits on any size: 16 minutes on X-Small, 8 on Small and 4 on Medium all came to 0.267 credits, because each size doubles both speed and rate. Real jobs rarely scale perfectly, but the point stands: size up to finish heavy jobs sooner, and size down or suspend when work is light. For many concurrent users rather than heavy queries, multi-cluster warehouses add clusters instead of size.

SF3. What are micro-partitions and clustering depth?

Snowflake splits tables into micro-partitions of 50 to 500 MB of uncompressed data and records value ranges for each, so queries can prune partitions that cannot match. Clustering depth is the average number of micro-partitions whose ranges overlap at a point; the smaller, the better clustered (Snowflake: micro-partitions). In the model, built on real January 2024 pickup times from New York's yellow taxis (NYC TLC: trip record data), trips kept in their natural load order had an average depth of 2.8 on pickup time, and a one-day query touched 3 of 40 partitions. Shuffled, as after many unordered loads and updates, depth rose to 39.6 and the same query touched all 40.

What to say: data loaded in time order is naturally well clustered on time. Define a clustering key only for large tables whose queries filter on a column the natural order does not follow, because automatic reclustering costs credits.

SF4. What are Time Travel and zero-copy cloning?

Time Travel lets you query, clone or restore data as it was within a retention period, and UNDROP brings back dropped objects (Snowflake: Time Travel). Cloning creates a copy of a table, schema or database that shares the underlying storage until either side changes (Snowflake: cloning):

SELECT COUNT(*) FROM trips AT(OFFSET => -60 * 30);      -- the table as it was 30 minutes ago
CREATE TABLE trips_dev CLONE trips;                      -- a copy for testing, sharing storage
UNDROP TABLE trips;                                      -- restore a table dropped by mistake

Interviewers ask how you would use them: recovering from a bad update, building a test environment from production data without copying it, or comparing a table before and after a load.

SF5. How does Snowflake separate storage and compute?

Data sits in central storage, and any number of warehouses can read it independently, so an ETL warehouse and a BI warehouse do not compete for resources. Each warehouse is billed only while it runs. This is the answer behind many design questions: give heavy loads and interactive users separate warehouses, and size each for its own pattern.

SF6. What is the result cache?

When the same query runs again and the underlying data has not changed, Snowflake can return the persisted result instead of recomputing it, and the cache expires after 24 hours (Snowflake: persisted results). For dashboards that repeat identical queries, this can mean no warehouse time at all for the repeats. Mention that small changes to the query text, or any change to the data, prevent reuse.

SF7. How does Snowflake handle JSON?

With the VARIANT type, which can hold any value, including nested arrays and objects (Snowflake: semi-structured data). Load raw JSON into a VARIANT column, query fields with path notation, and use FLATTEN to turn arrays into rows. A good answer adds when to stop: frequently queried fields are better extracted into typed columns for pruning and clarity.

How to answer Snowflake questions

• Turn workloads into credits: size, running seconds, the 60-second minimum and auto-suspend.
• Prefer natural clustering from load order before paying for clustering keys.
• Know the recovery tools: Time Travel, UNDROP and clones.

The platform is introduced in Snowflake explained. Comparable design questions for other warehouses are in Redshift interview questions and BigQuery interview questions, and platform-neutral ones in data warehouse interview questions.

Cloud warehouses are part of the Data Engineering course.

Sources

• Overview of warehouses (Snowflake documentation) — Snowflake
• Micro-partitions and data clustering (Snowflake documentation) — Snowflake
• Understanding and using Time Travel (Snowflake documentation) — Snowflake
• Cloning considerations (Snowflake documentation) — Snowflake
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• Using persisted query results (Snowflake documentation) — Snowflake
• Introduction to semi-structured data (Snowflake documentation) — Snowflake