Snowflake SQL & Data Engineering interview questions

The classic SQL screener at Snowflake and Meta. Everyone can recite the definitions, interviewers are listening for the one scenario where picking the wrong function silently corrupts your results.

The single most-reported live-SQL question in data-platform FDE screens. The pattern is standard, the points are in tie handling, the QUALIFY shortcut, and one aggregation trap most candidates miss.

Sessionization is gaps-and-islands with a twist, and it shows up in both SQL screens and Spark rounds at Databricks. The LAG-plus-running-SUM pattern here solves a whole family of interview questions.

The product-analytics staple that quietly tests event ordering, conditional aggregation, and cohort logic at once. Most candidates compute a funnel that double-counts, here's the version that survives interviewer scrutiny.

The triangle-shaped retention table every PM asks for, and a two-join SQL pattern interviewers love because it exposes grain mistakes instantly. Includes the right-censoring caveat that separates analysts from engineers.

Asked constantly because it tests whether you understand aggregation rather than syntax. The CASE-inside-aggregate idiom works on every engine, plus when to reach for Snowflake's PIVOT and its dynamic variant.

Snowflake's signature semi-structured question. Dot-notation, LATERAL FLATTEN for nested arrays, the casting traps, and the schema-on-read vs flattened-model judgment call interviewers really want to hear.

The Databricks signature triage question. Strong candidates don't list optimizations, they run a diagnostic decision tree: plan first, then scan, shuffle, join strategy, files. Here's the tree.

Snowflake's signature internals question, and the trap is in the second half: most candidates can define micro-partitions, but the scoring is on knowing the four cases where scaling up burns credits for zero speedup.

A warm-up that quietly reveals whether you understand why analytical engines are fast at all. Compression, projection, predicate pushdown, and the one workload where columnar is the wrong choice.

The question that simulates the actual FDE job: a customer's Snowflake bill just doubled. Scale-up vs scale-out is the easy half, the scoring is on workload isolation, auto-suspend, and proving which knob to turn.

The small-files problem is the #1 self-inflicted lakehouse wound FDEs find in the field. The causes are always the same three things, and the fix has a prevention half most candidates forget.

The dimensional-modeling staple. The trip fact table is the easy part, interviewers score grain declaration, the surrogate-key rationale, and how you handle the rider who changes cities. Here's the full shape.

Everyone can define SCD2; very few can write the MERGE that maintains it correctly. The expire-and-insert dance, the one-statement trick, and the late-arriving-change trap that breaks naive implementations.

The architecture question both Databricks and Snowflake ask, for opposite reasons. A vendor-neutral framework, what the Delta transaction log really does, and the honest convergence story that scores with both panels.

A requirements-interrogation question disguised as an architecture question. The first move isn't Kafka, it's a freshness-SLA conversation that usually deletes 90% of the cost. Here's the script strong FDEs run.

Sounds like a definition question; it's actually a judgment question. The economics that flipped the industry to ELT, and the four cases (starting with PII) where transforming before load is still non-negotiable.

The pipeline-design question that exposes whether you've ever been paged. One property, idempotency, solves reruns, late data, and backfills at once, and there are exactly three patterns that deliver it.

The integration question that decides data-platform FDE offers. Log-based vs query-based capture, why deletes break naive designs, the apply-merge that keeps order, and the snapshot step everyone forgets.

The question where data engineering meets the AI-native FDE job. Conversations, turns, LLM calls and tool calls each have their own grain, model them wrong and neither the cost dashboard nor the eval set can be built.

The capstone triage scenario data-platform FDEs get role-played through. A 9x blowup with an obvious-looking suspect, the scoring is on your diagnostic order, the questions you ask, and not stopping at the first plausible cause.

The two reported Snowflake SQL-screen warmups. Both look trivial; both have a silent-failure trap, integer division and divide-by-zero in one, the EXTRACT(MONTH) year-collision in the other.

The regression with no smoking gun, no deploy to blame, no single bad night. Staff-level triage is about reading the trend, not the last run, and the four slow-burn causes that a one-night comparison can't see.

The aggregate-restatement problem that breaks naive backfills: events arrive days late and out of order, so any partition you already published may be wrong. The staff answer is event-time partitioning plus bounded restatement, not a bigger watermark.

SCD2 tracks when an attribute was true; bitemporal also tracks when you knew it. The question separates engineers who can audit a number from those who quietly overwrite history when a correction arrives.

Basic salting assumes one side is a small dimension you can explode. When both sides are huge and skewed, exploding either one blows up the cluster. The staff move is to split hot keys out and handle them on a different plan entirely.

CDC design questions usually stop at the happy-path MERGE. This one stress-tests it: an at-least-once stream that double-delivers, hard deletes, soft deletes for audit, and a GDPR erasure that must physically purge even the bronze change log. The guarantees have to compose.

Streaming interviews separate people who have run a job at 3am from people who have read the docs. The hard parts are not the windowed aggregate, they are bounding state so the job does not die, and choosing what a late event does to an already-emitted result.

Two correct queries, two different numbers, one word. The fix is two certified definitions with different names, a bridge that closes to the dollar, and a rule for what the agent does with the bare word. The tempting join returns $9.0M.

The hardest 'standard' SQL interview pattern, asked everywhere from Meta to Palantir. There's a three-line trick that turns consecutive runs into a GROUP BY key, once you've seen it, you can't unsee it.

Join fan-out is the bug behind half of all 'the dashboard numbers are wrong' escalations an FDE will ever field. The mechanism, the 30-second detection query, and the three legitimate fixes.

A 40-year-old SQL classic that still filters out half of candidates, because of ties, NULLs, and departments with one employee. The interview-proof solution fits in six lines.

The most common bug in analyst SQL, and a favorite warm-up in Retool's debugging-flavored screens. One word's placement, ON vs WHERE, silently deletes your unmatched rows.

Every pipeline eventually double-loads data, so every FDE screen eventually asks this. There's one canonical idiom, plus a determinism detail that decides whether your dedupe is rerun-safe.

The verbatim Deployment Strategist case. The trap is accepting the premise, 'best for whom?' is the first scored move, and the second is designing a natural experiment from public data instead of hand-waving at 'analytics.'