Snowflake Software Engineer Interview Prep Guide

Focus most on Coding & Algorithms and System Design: you rated both 2/5, have no solved-question history yet, and Snowflake screens heavily probe graphs, parsing, scheduling, and distributed systems. Behavioral is the lightest review area: you rated it 3/5 and have already viewed behavioral content, so keep it to polished project stories rather than deep drilling. The Snowflake-specific emphasis is on DAG dependency reasoning, distributed control planes, query/storage metadata systems, resource governance, and SQL query planning/optimization. With one month until interview, spend the first two weeks closing algorithm gaps, the third week on Snowflake-style system design, and the final week on timed mixed practice plus behavioral polish.

Technical Screen — 66 min

Coding & Algorithms

• DAG Algorithms, Topological Sort, And Cycle Detection (Focus) — covered in depth under Onsite below.

• BFS, DFS, And Shortest Path Search (Focus) — covered in depth under Onsite below.

• Tree Algorithms, Traversal, LCA, And Dynamic Programming (Focus) — covered in depth under Onsite below.

• Parsing, Serialization, And Deserialization (Focus) — covered in depth under Onsite below.

• Greedy Scheduling And Resource Reuse (Focus) — covered in depth under Onsite below.

System Design

Focus area — You rated system design 2/5; Snowflake commonly probes reconciliation, leases, retries, and scheduler correctness.

What's being tested

Candidates must demonstrate designing a durable, fault-tolerant control plane and distributed scheduler: durable desired-state storage, reconciliation loops, leader election/lease semantics, safe handoff, idempotent execution, and observability. Interviewers probe correctness under failures (partitions, restarts, clock skew) and pragmatic tradeoffs (consistency vs availability, latency vs throughput) that a backend engineer would implement and operate.

Core knowledge

• Desired-state vs observed-state — store durable desired configuration in a strongly consistent store like `etcd`/`Postgres`; controllers continuously reconcile observed state to desired state using level-triggered loops rather than ad-hoc RPCs.

• Consensus & leader election — use Raft/Paxos via `etcd`/`ZooKeeper` for small-control-plane consensus/leader election; leader-only actions simplify correctness but require handling leader failover and leases.

• Lease-based ownership — implement shard/owner leases (TTL + heartbeats); pick TTL > 2× typical heartbeat RTT plus jitter. Lease renewal failures indicate takeover safety windows.

• Reconciliation loop pattern — controller reads desired state, computes diff, enqueues idempotent operations, and retries on errors; rate-limit with exponential backoff and jitter to avoid thundering herds.

• Idempotency & operation records — record each long-running operation with a unique operation id and terminal state in durable store; retries re-read outcome to avoid duplicate side effects.

• Distributed scheduling & sharding — shard job space (hash(job_id) mod N) or use consistent hashing; rebalancing during scale events must move lease ownership and migrate in-flight tasks safely.

• Time and clocks — coordinate scheduling using monotonic clocks for durations and synchronized wall clocks (`NTP`/`PTP`) for real-world timestamps; design tolerance for clock skew and late executions.

• Execution semantics — choose and document semantics: at-least-once is easiest (duplicates possible), at-most-once requires strict locking/coordination, exactly-once needs dedup + transactional side-effects or external idempotency primitives.

• Durable timers & missing-tick recovery — persist next-run timestamps in DB; on restart, scan overdue jobs and use leader/lease to dispatch them ensuring no silent task-drop if timer service dies.

• Failure modes & safety windows — define safe takeover windows: if lease TTL expires, a new owner can start tasks but must detect and either reattach to running tasks (via operation records) or assume they failed.

• Observability & SLOs — emit metrics (`p50`, `p99` scheduling latency, success rates), traces for reconciliation loops, and health checks; target meaningful SLOs for scheduling latency and success rate.

• Storage choices tradeoffs — `etcd`/`Postgres` for strong consistency and low cardinality control-plane state; `DynamoDB`/`Bigtable` for massive scale but eventual-consistency options require careful read-after-write logic.

Tip: model every stateful action as "write operation record" + "perform external side-effect" so restarts can reconcile from durable state.

Worked example — Design a Control Plane That Manages Cloud Clusters and Monitors Host Health

First 30 seconds: ask required APIs (create/resize/delete clusters, desired configuration fields, scale policies, health signals), expected QPS, and failure/consistency SLAs. Organize answer into three pillars: state management (durable desired-state store and operation records), reconciliation & autoscaling engine (periodic reconciler that computes diff, emits ops), and health monitoring & remediation (heartbeat, per-host probes, remediation actions like restart/replace). Use `etcd`/Raft for cluster metadata and operation journals to keep linearizability for create/delete semantics; store per-cluster lease and per-host health timestamps. Explicit tradeoff: choose strong consistency for cluster metadata (simpler correctness) at cost of higher write latency vs using eventual store for telemetry. Explain idempotency: every orchestration step has an operation id persisted, and workers are responsible for checking operation state before executing. Close by saying: if more time, detail shard assignment, slow-roll upgrades, and chaos-tests and add canary rollout logic.

A second angle — Design a Cron Job Scheduler

This is the same reconciliation + durable-state pattern but different constraints: high cardinality short-lived jobs, complex cron expressions, multi-tenant isolation, and strict timing. Shard schedule space by job_id into many partitions; each partition owner holds a lease and runs a local timer wheel reading persisted next-run timestamps. Ensure correct semantics for missed windows (backfill vs skip) and implement per-job concurrency limits and idempotency tokens for run attempts. Time skew and daylight-saving/time-zone handling are critical: parse cron into UTC next-run times and persist them. For reliable delivery, record run attempts in DB; use leader/lease handoff plus operation-resume logic to avoid duplicate runs across failovers.

Common pitfalls

Pitfall: Ignoring clock skew — designing timers only with wall-clock timestamps can easily lead to duplicated or missed runs when nodes disagree; use monotonic timers for intervals and synchronize wall-clock for scheduling.

Pitfall: Assuming single leader solves everything — leaders simplify coordination but you must design safe takeover: persist operation state and detect in-flight actions to avoid conflicting repairs.

Pitfall: Overselling "exactly-once" — candidates often promise exactly-once without specifying transactional side-effect guarantees; better to state achievable semantics (at-least-once with dedup keys, or idempotent actions) and how you'd implement them.

Connections

Interviewers may pivot to distributed consensus (Raft/Paxos), Kubernetes controller/operator internals, or workflow engines like `Temporal`/`Cadence` to discuss durable workflows and activity replay. They might also steer toward rate limiting and backpressure in schedulers.

Further reading

• In Search of an Understandable Consensus Algorithm (Raft) — core consensus algorithm to justify `etcd`/leader design.

• Kubernetes Controller Pattern (official docs) — practical reconciliation loop and controller best-practices.

Practice questions

Focus area — This is the most Snowflake-aligned design area, and your 2/5 system design rating warrants extra depth here.

What's being tested

Candidates must show practical distributed-systems engineering: designing scalable storage and query architectures, managing strong metadata and consistency, and trading off performance vs. cost. Interviewers probe how you partition data, maintain correctness (snapshots/transactions), surface metadata for planning, and propagate updates/evictions in a multi-tenant environment. Snowflake cares because these decisions determine ruler-scale query latency, storage efficiency, and operational complexity.

Core knowledge

• Content-addressable storage (CAS): store chunks by fingerprint (e.g., SHA-256) to enable deduplication; collisions negligible if fingerprint length $n\geq256$, collision prob $\approx 1/2^n$.

• Chunking strategies: fixed-size vs. variable-size (content-defined) chunking; variable chunking (e.g., Rabin fingerprints) improves cross-file deduplication but increases chunking CPU and metadata cardinality.

• Index & metadata services: separate metadata service that tracks fingerprints → locations, reference counts, and object manifests; must be highly-available and low-latency (cache hot prefixes).

• Reference management & GC: choose reference counting (immediate reclamation, distributed counters) or periodic garbage collection (mark-sweep with leases); reference counting scales poorly at extreme concurrency without sharding.

• Consistency & transactions: provide atomic commit of manifests + metadata update (use two-phase commit, or single-writer leases + idempotent operations) and support MVCC/snapshot isolation for queries reading stable views.

• Deduplication metadata scale: expect metadata entries ≈ number of unique chunks; for 1PB with 8KB avg chunk, ~1.3e8 chunks → store compacted bloom filters or partitioned key-value indices (e.g., LSM-based stores).

• Data layout & locality: use consistent hashing to distribute chunks across storage nodes; colocate index shards with compute cache to reduce lookup latency. Replicate metadata via consensus (Raft) for durability.

• Network & RPC design: use multiplexed RPC (gRPC) and batched lookups for manifest resolution; pipeline chunk fetches to hide latency. Limit synchronous cross-node ops on hot paths.

• Cache & eviction: multi-layer caching: client-side result cache, cluster SSD layer, and cold object store (S3); eviction policies combine recency and reference count/usage cost.

• Materialized view maintenance: for DAG-based views, store versioned intermediate results, use incrementality when possible, and invalidate downstream caches via a dependency graph (topological propagation).

• Query execution: split planning (cost-based optimizer) and execution (distributed operator graph) with shuffle-aware partitioning; model network as constrained resource and aim to minimize data movement.

• Cost & durability tradeoffs: choose erasure coding for storage cost-efficiency at higher CPU on reads; replication for low-latency reads and simpler GC.

Worked example — Design an object store with deduplication

Frame: ask expected SLAs (read/write latency), workload mix (many small files vs. large blobs), durability target (nines), storage backend (S3 vs raw disks), and per-tenant isolation requirements. Skeleton pillars: (1) chunking & fingerprinting pipeline that computes SHA-256 per chunk, (2) metadata index mapping fingerprints → locations + reference counts, (3) write path that atomically persists chunks and updates manifests, (4) read path that reconstructs objects from chunk pointers with caching, and (5) GC/compaction to reclaim unreferenced chunks. Explicit tradeoff: choosing reference counting gives prompt reclamation but requires strongly-consistent distributed counters (sharded updates + batching), whereas periodic GC (lease-based) simplifies writes at the cost of delayed reclaim and higher storage footprint. Close by noting operational concerns: monitor fingerprint index cardinality, handle hot-chunk hotspots (replicate or cache), and if time permitted add: cross-tenant dedupe policy, chunk-size auto-tuning, and multi-level metadata caches.

A second angle — Design cache for DAG-based query views

Same primitives apply but framed around compute results rather than raw bytes: represent each view node as a versioned artifact with a manifest linking to underlying shard files (which themselves may reuse deduplicated chunks). Key differences: invalidation is driven by upstream data changes and query semantics (append-only vs. arbitrary updates), so prefer incremental maintenance and dependency tracking. Use a dependency DAG to propagate invalidation and schedule recomputation; maintain both fine-grained (per-partition) and coarse-grained (full-view) artifacts to trade recompute cost vs. storage. For correctness, ensure readers obtain a consistent snapshot of the DAG—either via MVCC or via commit tokens—so cached intermediate artifacts map to the correct logical view version. Finally, optimize for small, frequent updates with delta encoding and a write-optimized store for intermediates.

Common pitfalls

Pitfall: confusing deduplication savings with real user-visible cost — dedupe reduces stored bytes but increases CPU, metadata size, and lookup latency; always quantify net cost versus naive replication.

Many candidates propose global, synchronous reference counting without addressing write contention. A distributed counter requires sharding and batching; otherwise metadata becomes the performance bottleneck under high ingest.

Pitfall: over-indexing metadata — storing per-chunk global indexes in memory without partitioning leads to OOM and GC pressure; instead partition and use compact probabilistic filters for fast negatives.

A communication mistake is skipping SLA clarifications: don't design a system assuming synchronous semantics if the product tolerates eventual visibility. State your consistency model and how it affects both user-visible correctness and operational complexity.

Pitfall: ignoring failure modes during compaction/GC — naive delete-on-zero can remove chunks concurrently referenced by a delayed writer; prefer atomic manifest reference updates and lease-based GC windows to avoid data loss.

Depth mistake: optimizing for average-case reading cost while neglecting pathological workloads (hot files or small-random-writes). Call out hot-spot mitigation (replication, LRU hot-cache, backpressure).

Connections

Interviewers may pivot to adjacent system topics such as query optimizers (cost models, cardinality estimation), storage engines (LSM vs B-tree, compaction strategies), or distributed transaction systems (two-phase commit, distributed snapshot algorithms). Be ready to map design choices across those domains.

Further reading

• Designing Data-Intensive Applications — Martin Kleppmann — comprehensive chapters on storage engines, consensus, and batch/stream processing relevant to these architectures.

• Bigtable: A Distributed Storage System for Structured Data (Google) — practical LSM and metadata partitioning patterns useful for chunk/index storage and tabletization.

Practice questions

Focus area — Multi-tenant resource isolation is central to Snowflake-style systems, and your system design rating indicates this needs focused review.

What's being tested

Interviewers probe your ability to protect shared services under concurrent, multi-tenant load: designing enforcement algorithms, choosing storage/coordination for correctness vs latency, handling runtime configuration changes, and proving scalability and testability. They want to see system decomposition (fast path vs control plane), clear invariants (hard vs soft limits, burst behavior), concurrency-safe primitives, and realistic operational concerns Snowflake cares about: tenant isolation, predictable latency (e.g., `p99`), and auditability.

Core knowledge

• Token bucket vs leaky bucket vs fixed-window vs sliding-window: know per-request semantics, burst handling, and cost/accuracy. Token bucket supports bursts; sliding-window gives precise short-window limits at higher memory cost.

• Per-tenant state model: a limiter keeps (last_timestamp, tokens) per key; state size ~two 8-byte fields; 10M tenants => ~160MB metadata plus overhead, so shard or evict cold tenants.

• Atomic enforcement primitives: use atomic increment/decrement or Lua scripts in `Redis` to implement read-modify-write in one network roundtrip; otherwise race conditions produce overconsumption.

• Distributed counters & consistency: strong correctness needs linearizable operations or single-writer sharding; eventual consistency can be acceptable for soft throttles but risks transient overuse.

• Runtime-updatable limits: store configs in durable store (`Postgres` or config service), publish updates via `Kafka`/pub-sub to edge nodes, and use local caches with TTL + version check to avoid restarts.

• Sharding and routing: consistent hashing routes tenant keys to limiter shards to avoid hot-spotting; maintain key ownership mapping and re-shard gracefully to minimize state migration.

• Clock & time handling: use monotonic clocks for token refill math; guard against clock skew between nodes when using local caches or lease-based tokens.

• Scaling designs: edge-enforced stateless checks (fast reject) with a central quota coordinator for long-term accounting, or fully-embedded state in edge with background reconciliation. For N requests/sec, choose in-memory `Redis` cluster for low-latency counters beyond ~100k keys per instance.

• Testing & verification: unit tests with time-mocking, deterministic fuzzing of race windows, integration stress tests with ramped QPS, and chaos tests (node fail, network partition) to validate guarantees.

• Metrics & observability: expose counters for allowed/denied, queue lengths, refill rates, `p50`/`p99` latencies, and per-tenant usage histograms for billing and debugging.

• Quota vs rate-limit semantics: quotas are cumulative (credits over billing period), rate limits are instantaneous windowed; implement quotas with durable ledgered updates (append-only or serializable DB writes) to avoid double-spend.

• Failure & retry semantics: design idempotency keys and backoff guidelines for clients; without idempotency, retries cause admission storms and require server-side mitigation (reject-with-backoff headers).

Worked example — Build a Tiered Rate Limiter With Runtime-Updatable Limits, Then Test and Scale It

First 30s: clarify scope — are limits per API key, per account, or both; window granularity (second/minute/hour); are limits hard (deny) or soft (throttle with queue)? Ask throughput targets and acceptable latency budget for the decision path. Skeleton: (1) Data model — per-tenant token-bucket state and tier mapping; (2) Enforcement plane — fast-path in-memory/`Redis` Lua script doing atomic token consume; (3) Control plane — durable config in `Postgres` with a `Kafka`-backed pub/sub to propagate tier changes; (4) Scaling — consistent hashing to shard tenants, local caches for hot tenants; (5) Testing/observability — unit tests with time mocking, and load tests with synthetic bursts. A key tradeoff: centralizing state yields strict correctness but increases latency and a single point of failure; local caches reduce latency but need reconciliation and accept temporary overuse. To close, state you’d implement a minimal prototype (token-bucket in `Redis` with Lua), add end-to-end load testing, and then iterate: add hierarchical quotas, burst smoothing, metrics and per-tenant throttling policies if time permits.

A second angle — Design a multi-tenant quota system

Quotas are longer-window, accounting-heavy problems: same building blocks apply but emphasis shifts to durable allocation, reconciliation, and billing. Use a fast in-memory layer (`Redis`) for immediate credit checks and a durable transactional store (`Postgres`) for audit and eventual consistency. Key differences: implement explicit credit allocation and reclamation, support partial allocations, and guarantee no double-spend — choose serializable transactions or per-tenant optimistic locking for correctness. For scale, batch ledger writes and reconcile periodically; expose per-tenant reports and alerts when usage approaches thresholds so clients can self-throttle rather than hit hard limits.

Common pitfalls

Pitfall: Designing around a single central store as the enforcement path — this simplifies correctness but creates latency and a hard availability dependency; interviewers expect a discussion of sharding, caching, and fallbacks.

Pitfall: Forgetting clock and time-source issues — using wall-clock time across distributed nodes without monotonic guards leads to negative token counts and incorrect refills; state this and prefer monotonic + server-side authoritative timestamps.

Pitfall: Only proving the design verbally without testing strategy — you must describe testability: deterministic unit tests (time travel), stress tests for bursts, chaos tests for partitions, and metrics that prove you meet `p99` and throughput SLAs.

Connections

Rate limiting and quotas often lead into adjacent topics: backpressure/admission control (how to slow producers upstream), fairness algorithms like weighted fair queuing, and billing/audit trails (how enforcement maps to chargeable usage). Interviewers may pivot to consistency models (strong vs eventual) or to operational concerns like monitoring `p99` and capacity planning.

Further reading

• Designing Data-Intensive Applications — Martin Kleppmann — strong chapters on distributed systems, consistency tradeoffs, and partitioning patterns.

• Token Bucket vs Leaky Bucket (engineering blogs) — practical explanation of algorithms and burst behavior with implementation notes.

Practice questions

Focus area — Because system design is 2/5, practice turning unreliable dependencies into reliable APIs with retries, idempotency, and observability.

What's being tested

Interviewers probe your ability to integrate and expose external services reliably: designing APIs and clients that tolerate flakiness, control resource usage, and fail predictably. Expect questions that check knowledge of timeouts, retries, idempotency, backpressure and isolation (bulkheads), and observable failure modes (p99 latency, success rate). Snowflake values engineers who can keep user-facing paths available and debuggable while respecting external SLA variability.

Core knowledge

• Retries: implement exponential backoff with jitter (e.g., base b, attempts n → delay ≈ b * 2^n ± jitter) and a capped max attempts to avoid cascading load.

• Idempotency: require idempotency keys for non-idempotent server actions; client SDK should attach Idempotency-Key and deduplicate on the server to avoid side-effect duplication.

• Circuit breaker: use circuit breaker (closed/half-open/open) to short-circuit calls after threshold failures; combine with health checks and cooldown windows to prevent retry storms.

• Bulkhead isolation: partition pools by external dependency or operation type (threadpool/connection pool) so one slow API cannot exhaust all resources; size pools using capacity planning.

• Timeouts vs deadlines: use per-call timeout shorter than user-facing deadline, and propagate a deadline header so downstreams can fail fast; avoid infinite blocking.

• Rate limiting: enforce token bucket or leaky bucket per-tenant and per-external-host; implement global and per-host quotas for crawlers or outward API calls.

• Caching & staleness: use read-through caches with TTL and stale-while-revalidate for soft degradation; consider cache invalidation costs — LRU works up to ~10M keys; beyond that use approximate structures or sharding.

• Backpressure & queues: buffer with bounded queues; apply admission control (reject or degrade) when queue length > threshold; Little’s law applies: $L = \lambda W$ to size pools.

• Observability: emit structured logs, metrics (request_rate, error_rate, p50/p95/p99), and distributed traces with a correlation id; alert on success-rate SLOs, not just latency.

• Async fallback & DLQ: convert sync calls to async via queues for brittle dependencies; failed items go to a dead-letter queue for investigation and replay.

• Security & token handling: cache third-party tokens, refresh proactively before expiry; on flaky token endpoints, use retry+Jitter and local validation (e.g., verify signatures) to reduce calls.

• Concurrency and deduplication for crawlers/search: normalize URLs, use a thread-safe dedupe store (Bloom filter + persistent set), and enforce per-host rate limits; maintain politeness via robots.txt parsing.

Worked example — Design a REST API Abstraction Layer

First 30s: clarify constraints — client language surface (multiple SDKs?), sync vs async usage, SLOs for latency and availability, and which external failure modes must be masked. Skeleton pillars: (1) client contract and typed models (SDK), (2) reliability layer (timeouts, retries, circuit breaker, idempotency), (3) observability & tracing (structured logs, metrics, traces), (4) security & versioning (auth, API versioning). A concrete design decision: choose where to put retry logic — client SDK or server gateway — tradeoff being duplication across clients vs centralized control; mitigate duplicate side-effects by requiring idempotency keys. Explicitly call out performance tradeoffs: aggressive caching reduces latency but may serve stale data; aggressive retries improve success-rate but risk overloading slow third-parties. Close by saying what you'd do with more time: add adaptive retry policies driven by runtime metrics, add chaos-testing for dependency behaviors, and provide SDK code-gen for consistent contracts.

A second angle — Design resilient auth with flaky third-party tokens

Frame as an external dependency problem where auth token issuance/validation is brittle. Apply the same primitives: cache tokens and their expiry, proactively refresh with jittered retries, and run a circuit breaker around the token service so authentication degrades predictably (e.g., allow limited grace period using cached tokens). For verification, prefer locally verifiable tokens (signed JWT) to avoid synchronous validation calls; if not possible, use asynchronous validation with short-lived allowlists. Key tradeoff: shorter TTLs improve security but increase load on the token issuer; choose TTL to meet both security and availability SLOs and document the acceptable failure modes (e.g., read-only degraded access).

Common pitfalls

Pitfall: Unbounded retries and blocking threads.
Unbounded retries (or very long blocking timeouts) exhaust threadpools and create cascading failures. Size threadpools, use non-blocking IO where possible, cap retries, and apply circuit breakers.

Pitfall: Designing retries without idempotency.
Retrying mutating operations without idempotency leads to duplicated side-effects. Require idempotency keys or switch such operations to an at-most-once server workflow with dedupe stores.

Pitfall: Focusing only on mechanisms, not SLOs.
Listing patterns like "use cache or circuit breaker" without quantifying SLOs (99.9% success, p99 latency) leaves the interviewer unsure about tradeoffs. State target SLOs and show how each mechanism supports them.

Connections

Interviewers often pivot to adjacent topics: distributed tracing and correlation-id propagation for end-to-end debugging, rate-limiting & quota enforcement for multi-tenant systems, or the tradeoffs between sync vs async integration and replay semantics (exactly-once vs at-least-once).

Further reading

• Release It! by Michael T. Nygard — practical resilience patterns, circuit breakers and bulkheads.

• Martin Fowler — Circuit Breaker — concise explanation of the pattern and common thresholds.

Practice questions

Focus area — Snowflake is database-adjacent; add focused practice on relational operators, join strategies, cost models, and execution-plan trade-offs.

What's being tested

Demonstrates reading and reasoning about a execution plan to find bottlenecks, choose better join strategies, and rewrite SQL for lower cost. Interviewers probe familiarity with the cost-based optimizer, cardinality estimates, and practical tools like `EXPLAIN` to validate fixes. At `Snowflake`, this maps to keeping queries predictable, reducing compute costs, and diagnosing skewed stages or bad statistics.

Patterns & templates

• Filter pushdown — apply WHERE predicates early; reduces rows shipped between stages and lowers memory/CPU usage.

• Projection pruning — select only needed columns to avoid unnecessary I/O and wide-row materialization.

• Join choice template — use hash join for large unsorted inputs if build-side fits memory; merge join for pre-sorted streams; nested-loop for tiny inner side.

• Join reordering / bushy plans — join smaller selective tables first to reduce intermediate cardinalities; optimizer cost = sum(plan costs).

• Use EXPLAIN / profile — inspect row counts, operator costs, and parallelism; compare estimated vs actual cardinalities to find bad stats.

• Rewrite anti/exists patterns — NOT EXISTS often outperforms NOT IN with NULLs; convert correlated subqueries to joins when safe.

• Window vs aggregation tradeoff — prefer GROUP BY for aggregates; use window functions like ROW_NUMBER() OVER (PARTITION BY...) only when per-row ordinal is required.

Common pitfalls

Pitfall: Trusting optimizer estimates — large discrepancies between estimated and actual row counts often indicate stale/missing statistics or data skew.

Pitfall: Adding indexes or clustering without measuring — unnecessary maintenance can increase write cost and not improve the hot query.

Pitfall: Overusing SELECT * — forces full-column materialization, hidden I/O, and wider shuffle/serialize costs.

Practice these

The practice cards below cover canonical variants — solve all of them and time yourself.

Practice questions

Onsite — 36 min

Coding & Algorithms

Focus area — You rated coding 2/5, and Snowflake often models permissions, services, and query dependencies as DAG problems.

What's being tested

Candidates must demonstrate correct application of topological sort over a directed acyclic graph (DAG), including deterministic ordering and reachable-node aggregation. Interviewers probe both cycle detection (detecting and reporting impossible orders) and efficient transitive aggregation (e.g., inherited permissions/roles) under time/space constraints.

Patterns & templates

• Kahn's algorithm (BFS in-degree queue) for O(V+E) topological orders; use a min-heap when lexicographic determinism is required.

• DFS with recursion stack for fast cycle detection and postorder topological output; track visit states {unseen, visiting, done}.

• Represent graphs with an adjacency list (Map<int, List<int>>) for memory-efficient traversal when E ~ V..10V.

• For transitive aggregation, propagate sets top-down (BFS) or merge child-to-parent using union-by-size to limit copying.

• Use a priority queue to produce deterministic alphabetical outputs when multiple nodes have zero in-degree.

• For permission models, treat "local deny" vs "inherited allow" by storing two flags per node and resolving precedence on aggregation.

• When scale is large (N > 1e6 edges), prefer iterative DFS/Kahn and avoid recursion to prevent stack overflow.

Common pitfalls

Pitfall: Forgetting disconnected components — run the algorithm starting from all zero in-degree nodes, not just an arbitrary root.

Pitfall: Returning any topological order when the problem requires deterministic lexicographic order — use a min-heap for zero in-degree selection.

Pitfall: Merging large sets naively per node — use union-by-size or bitsets when alphabet/domain is small to avoid O(N^2) behavior.

Practice these: the practice cards below cover the canonical variants — solve all of them and time yourself.

Practice questions

Focus area — Your coding rating is 2/5 with no solved history yet; graph traversal is a recurring Snowflake screen pattern.

What's being tested

Demonstrate graph traversal skills: model grids/structures as graphs, pick the right search (unweighted vs weighted), and implement correct distance/reachability. Interviewers probe correct state representation, complexity reasoning, and edge-case handling like obstacles, multiple sources, and cycles.

Patterns & templates

• BFS on unweighted graphs/grids — use deque, visited, and parent map; distances in O(V+E) time and O(V) space.

• Multi-source BFS — enqueue all sources with distance 0 to compute nearest-source distances in one pass.

• Dijkstra for non-negative weights — use priority_queue (min-heap); complexity O((V+E) log V).

• 0-1 BFS when weights are 0/1 — use deque to get O(V+E) time without a heap.

• A* heuristic search — admissible heuristic (e.g., Manhattan) to speed grid shortest-paths while preserving optimality.

• State representation — encode position and extra state (keys, direction) as tuples/ints; canonicalize to avoid duplicates.

• Path reconstruction — store parent[node] during search; reconstruct by backtracking to avoid re-traversal costs.

Common pitfalls

Pitfall: Treating a grid cell as visited before considering better paths — in weighted graphs you must allow distance relaxation (use Dijkstra).

Pitfall: Forgetting diagonal vs orthogonal neighbor rules — clarify movement model and neighbor generation deltas.

Pitfall: Not bounding memory for large N — represent visited compactly (bitset/flattened index) for big grids.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.

Practice questions

Focus area — Tree invariants and edge cases need deliberate practice given your 2/5 coding self-rating and one-month timeline.

What's being tested

These problems test mastery of tree traversal and connectivity reasoning (preorder listing, path queries, deletions) plus lowest common ancestor (LCA) and search-based dynamic programming over trees. Interviewers probe ability to transform parent/child representations into usable graphs, reason about component reattachment after deletions, and produce correct, efficient traversals under edge constraints.

Patterns & templates

• Build an adjacency list from parent pointers in O(n) time; detect roots by missing parent or indegree==0.

• Use dfs (recursive or iterative) for preorder with a running depth parameter to collect (id, depth) pairs in O(n) work and O(h) stack.

• Compute height with one postorder dfs returning max(child_heights)+1; reuse results for deletion queries to avoid recomputation.

• For shortest-path between nodes: find LCA via parent pointers, then reconstruct paths up-to-LCA and down, O(h) time; precompute binary-lifting for repeated queries in O(n log n) preprocess.

• For node-deletion with promotion: simulate local reconnection rules, then recompute component heights; prune search using subtree sizes and memoized heights to avoid exponential work.

• For enumerating valid deletion sets: apply backtracking with early pruning (skip symmetric deletions), deduplicate by canonical ordering, bound by combinatorial limits.

• Tip: prefer iterative dfs or explicit stacks for trees deeper than ~10^4 to avoid RecursionError; track parent pointers when reconstructing paths.

Common pitfalls

Pitfall: Assuming node values are unique — many variants allow duplicate values or missing nodes; use node IDs or handle absence checks explicitly.

Pitfall: Recomputing heights from scratch per deletion without memoization leads to O(n^2) or worse; cache subtree heights and update incrementally.

Pitfall: Not addressing recursion depth or stack memory on very deep trees; always discuss iterative alternatives or tail recursion limits.

Practice these — the practice cards below cover the canonical variants — solve all of them and time yourself.

Practice questions

Focus area — Snowflake asks production-style encoding and parser questions; your low coding rating makes robust edge-case practice high value.

What's being tested

These problems test stack-based parsing, robust tokenization, and unambiguous serialization/deserialization design under adversarial inputs. Interviewers are probing linear-time parsing patterns, correct handling of nesting/precedence, and safe encoding choices that preserve arbitrary bytes/characters.

Patterns & templates

• Stack-based bracket matching — push opening, on closing pop and compare; linear O(n) time, O(n) worst-case space, handle multiple bracket types.

• Shunting-yard / operator-precedence — convert infix to RPN for safe evaluation; implement precedence and left/right associativity explicitly.

• Recursive descent parser — small, readable parser for expressions with unary +/-, parentheses; good when grammar is simple and deterministic.

• Length-prefix (size + data) serialization — encode each string as <len>#<data> or binary 4-byte length, avoids escape complexities and preserves empties.

• Escape-based encoding — use only when stable delimiter sets; require robust escape rules and validate during decode to avoid ambiguity.

• DFS/BFS crawler with visited set — domain-filtered traversal, cycle avoidance via visited, respect polite limits (depth, rate).

• Tokenization first, parse second — separate lexical analysis from parsing to simplify unary vs binary operator resolution and variable substitution.

Common pitfalls

Pitfall: Treating unary - as binary; always detect operand absence left of operator and handle as unary with correct precedence.

Pitfall: Using simple delimiter split for serialization; strings may contain delimiter or be empty—prefer length-prefix.

Pitfall: Forgetting cycle detection in web crawl — omit a visited set and the crawler can loop indefinitely.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.

Practice questions

Focus area — Scheduling and resource reuse map closely to Snowflake backend themes, and your coding practice signals show room to build confidence.

What's being tested

Candidates must show proficiency with greedy scheduling and resource reuse: converting temporal/resource constraints into an algorithmic strategy that minimizes concurrent resource count. Interviewers probe ordering (sorting/sweep), reuse tracking (min-heap or multiset), and correctness under ties and edge cases like simultaneous end/start. For dependency-flavored variants, they expect topological ordering and cycle detection to layer execution before applying parallelism limits.

Patterns & templates

• Sort + sweep-line by start time, maintain active resource end-times in a min-heap (heapq); reuse when smallest end ≤ new start, O(n log n).

• Min-heap of end times: push trip/service end, pop while top ≤ current start; heap size = concurrent resources needed.

• Greedy assignment: always reuse earliest-finishing resource; proven optimal for interval partitioning (activity/interval scheduling duals).

• Kahn's algorithm for DAGs: compute in-degree, extract zero-degree nodes per layer to form parallelizable batches, O(V+E).

• Detect cycles with DFS (coloring) or Kahn; if cycle exists, no valid layered startup ordering.

• Concurrency bounding: after layering, treat each layer as set of independent tasks and schedule with a thread pool / semaphore, O(total tasks log k).

• Edge-case tie rules: define whether end==start allows reuse; implement consistent comparator to avoid off-by-one bugs.

Common pitfalls

Pitfall: Sorting only by start time and ignoring equal-time end/start semantics loses reuse opportunities or double-counts resources.

Pitfall: Using an unsized array or naive nested loops yields O(n^2) for large n; prefer heap-based O(n log n).

Pitfall: For dependency problems, returning any topological order without checking cycles will miss unsatisfiable inputs.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.

Practice questions