Snowflake AI Engineer interview questions

Continued pretraining adds domain knowledge that fine-tuning cannot, yet done carelessly it wrecks general ability. The signal is the replay ratio, learning-rate rewarming, and how you measure forgetting.

Both work, so the decision is made by four numbers: how many examples you have, how far the capability has to move, how many variants you must serve, and how many GPUs you own. The serving axis is the one candidates never mention.

Most staleness complaints are not model problems, and the engineer who says so first wins the round. Here is which changes belong in the index, which belong in the weights, and the lifecycle machinery that keeps months of refreshes from eroding the model.

Vector search over rows is the wrong tool when the user asks for a count or an aggregate. Interviewers want to see you retrieve the right schema, generate validated SQL, and know when to query the database instead of embedding it.

A Bloom filter answers 'have I seen this?' with a few bits per item rather than storing the key, swapping a rare false positive for a large memory saving. Here is the build, the sizing math, and where it pays off in dedup and serving.

Writing the regexes takes ten minutes. The half of the question that separates candidates is the evaluation: a detector that blocks 'ignore the noise in the data' has shipped a bug to every analyst using your product. Here is the layered detector and the harness that proves it works.

SCD2 tells apart engineers who have actually run pipelines from those who have only read about them. The answer rests on a single atomic MERGE that closes the prior row while opening the new one, and holds up when the job retries.

Sessionization tells apart candidates who reach for a self-join from those who know LAG plus a running sum. Below is the two-pass pattern that holds up across billions of events, along with the edge cases interviewers press on.

Pipelines fail and retry, so the real question is whether a retry corrupts your data. What interviewers look for is idempotent writes (not 'exactly-once delivery') alongside watermarks for late data. Here is how to keep re-runs safe without double-counting.

The Databricks-flavored performance question. What interviewers want is a candidate who heads straight for the usual suspects (skew, shuffle, spill) through the Spark UI rather than guessing. Here is the diagnostic order and the fixes that genuinely move the needle.

Top-N-per-group is the window-function question every data round asks, and the trap is RANK vs ROW_NUMBER vs DENSE_RANK. What interviewers want is picking the right ranking function for ties and understanding window frames. Here is the pattern and the tie nuance.

A hard streaming-systems question: dedup at high throughput while keeping state bounded. What interviewers reward is a tiered state design (a probabilistic filter ahead of durable state) plus watermark-driven eviction. Here is the architecture that does not OOM.

A pipeline-design question that rewards fitting the architecture to the freshness requirement rather than chasing real-time for its own sake. What interviewers want is the latency-vs-complexity tradeoff and an understanding of why Kappa arose to kill Lambda's dual codebase. Here is the decision.

A data-warehouse fundamentals question that tells apart people who model for analytics from those who only know OLTP normalization. What interviewers listen for is facts vs dimensions, the star schema, and why analytics denormalizes where transactional systems normalize.

What separates a query that scans a terabyte from one that scans a gigabyte is usually layout, not the engine. Interviewers listen for partition pruning, columnar formats, and the small-files problem.

Keeping a warehouse in sync with a live OLTP database is a CDC problem, and the naive 'full reload nightly' or 'query by updated_at' answers leave real gaps. The signal is log-based CDC and idempotent merges.

Bad data quietly corrupts everything downstream (dashboards, models, decisions), and 'it ran without error' is not the same as 'it's correct.' The signal is the categories of checks and failing loud at the boundary.

Query tuning is a core data-engineering skill, and what interviewers watch for is reading the execution plan before you change anything, then applying the right fix. Here is the diagnostic method that tells guessers apart from engineers.

Dedup is a daily data-engineering task and a frequent screen. The signal is ROW_NUMBER to keep the right record (not just DISTINCT), pinning down what 'duplicate' actually means, and handling near-duplicates at scale.

Hierarchies (org charts, bill-of-materials, category trees) call for recursion, and a self-join reaches only one level down. The signal is the recursive CTE with its anchor plus recursive members, and understanding how it terminates.

Pivoting (rows to columns) shows up constantly in analytics and reporting, and the portable trick is conditional aggregation, not a vendor PIVOT clause. The signal is the CASE-inside-aggregate pattern and knowing its one real limit.

Joins are the core of SQL, and the real signal is precise semantics: semi/anti joins (EXISTS / NOT EXISTS), the NOT IN null trap, and the fan-out bug that quietly doubles your sums.

The subtle part of rolling aggregates is the window frame clause, especially ROWS vs RANGE. The signal is the right frame for a moving window and the tied-rows gotcha that corrupts running totals.

Means mislead on skewed data like latency and spend, and SQL ships dedicated functions for the truth. The signal is PERCENTILE_CONT/DISC for exact quantiles, NTILE for bucketing, and why p50/p99 beat the average.

A new column is safe; a rename quietly corrupts every dashboard downstream. The signal is knowing which changes stay compatible, enforcing data contracts in CI, and running expand-contract migrations for the breaking ones.

You cannot wait for all the data, and buffering an unbounded stream will OOM. The signal is windowed joins with watermarks for stream-stream, lookup joins for stream-table, and a clean account of late and out-of-order events.

Set operations and NULL semantics catch out even senior engineers. The signal is UNION vs UNION ALL and its dedup cost, plus three-valued logic where NULL matches nothing, not even itself.

Materialized views swap storage and freshness for query speed by precomputing results. The signal is grasping the view-vs-materialized-view tradeoff and the refresh/staleness question.

Time-based analysis runs through all of analytics, and time zones and bucketing are the usual traps. The signal is DATE_TRUNC for bucketing, interval math, and storing UTC.

Correlated subqueries read naturally but may run once per row (O(n squared)), a classic slow-query cause. The signal is spotting them and rewriting as joins or window functions.

Reporting frequently needs subtotals and grand totals across several dimensions, and stitching that together with many UNIONs wastes work. The signal is GROUPING SETS, ROLLUP, and CUBE in one pass.

Exact COUNT(DISTINCT) over billions of rows has to track every unique value, so it turns slow and expensive. The signal is knowing when a sketch like HyperLogLog buys huge memory and speed wins for a sub-percent error budget.

A LATERAL join lets a FROM-clause subquery reference columns from the table ahead of it, which a normal join cannot. The signal is spotting the per-row use cases: top-N per group, table functions, unnesting arrays.

Modern warehouses store JSON natively, and querying it well sets strong data engineers apart from the rest. The signal is path access plus unnesting, and a clear call on when to flatten hot fields versus keep the schema-on-read flexibility.

WHERE cannot reference a window function, so people resort to wrapping queries in subqueries. QUALIFY filters on window output directly. The signal is explaining why WHERE fails and writing top-N-per-group cleanly.

MERGE does insert, update, or delete in a single statement, the foundation of incremental loads and slowly-changing dimensions. The signal is the matched/not-matched logic and what keeps a retry safe.

Modern table formats can query a table as of an earlier version or timestamp, the foundation of reproducibility, audits, and recovery. The signal is the versioned-snapshot mechanism and where it falls apart.

Consecutive-run problems (active streaks, uptime windows, price-stable periods) all boil down to one trick. Once the row-number difference clicks, they collapse to a window function and a group-by.

A dedicated vector database is not always required. When embeddings sit beside your relational data, nearest-neighbor in SQL makes filters and joins easy. Here is how, and the scale where it breaks down.

Building a training set from a billion-row table means sampling, and the naive 'ORDER BY random() LIMIT n' sorts the whole table. Stratified sampling takes even more care. Here are both, done efficiently.

Production drift monitoring frequently lives in the warehouse, not a fancy tool. Comparing two distributions in SQL with a metric like PSI is a few CTEs. Here is how to compute it.

Days with zero events simply don't show up in an event table, so a 7-day average quietly averages the wrong 7 rows. A date spine makes the missing days explicit. Here is the pattern.

The applied data-wrangling screen: here is a grubby database, turn out a clean analysis-ready table. The signal is profiling before transforming, doing set-based cleaning in SQL and row-level fixes in Python, joining without fanning out rows, and validating the output rather than trusting it.

An org-chart recursion walks a tree, but a general graph has multiple paths and back-edges. The signal is building up the visited path to prune cycles and ranking paths by cost to land the shortest one.

Wide tables with a column per period are easy to read and hard to query. The signal is unpivoting to long format with UNPIVOT or a UNION/cross-join trick, and knowing how NULLs and types bite you.

Two queries that look identical apart from ROWS vs RANGE return different numbers, and most candidates cannot explain why. The signal is knowing RANGE groups by value (peers) while ROWS counts physical rows.

The reflex is 'add an index,' but a good engineer reads the plan before touching anything. The signal is spotting the operator that dominates runtime, comparing estimated against actual rows, and knowing the seq-scan, bad-join, and spill patterns.

Pick the wrong partition key and you end up with millions of tiny files or lopsided giants. The signal is partitioning on a low-cardinality filter, clustering inside partitions on the next predicate, and keeping an eye on file size.

At-least-once creates duplicates and at-most-once drops data; everyone asks for exactly-once yet few can explain how Kafka provides it. The signal is idempotent producers, transactional read-process-write, and the read-committed isolation that binds them together.

Each of the three brings ACID to object storage, yet they were built to solve different problems. The signal is fitting the format to the workload (engine neutrality, Spark-native ACID, or streaming upserts) instead of naming a favorite.

Downstream pipelines shatter when an upstream team renames a column at 2am. The signal is a data contract: a versioned, enforced agreement on schema and semantics, verified at the producer before bad data spreads.

When a metric looks off, lineage points you to the upstream table at fault. The signal is separating table from column lineage and knowing the three capture methods: SQL parsing, runtime hooks, and metadata APIs.

Overlapping subscription or session windows have to collapse into clean periods, and a naive self-join runs quadratic. The signal is the gaps-and-islands trick: a running max end-time that marks where each new island begins.

Events arrive minutes or days after they happened, and a window that has already closed reports wrong counts. The signal is event-time vs processing-time, watermarks with allowed lateness, and how you fix already-emitted aggregates.

A backfill that double-counts or knocks over the live pipeline is worse than the bug it fixes. The signal is idempotent partition-scoped rewrites, keeping backfill compute isolated from live runs, and validating before the swap.

Pulling a scalar by path is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows plus a clear rule for when to flatten versus query in place.

Plenty of candidates know only 'use Debezium.' The signal is comparing three CDC mechanisms on how each captures deletes, loads the source, and preserves ordering, plus the snapshot-to-stream stitching that trips up real deployments.

Text-to-SQL is deceptively hard because correctness is all-or-nothing and the failure mode is a confident wrong number. What interviewers reward: schema grounding, query validation, and a safety layer, not merely 'prompt an LLM with the schema.' This lays out the production design.

Batch scoring a billion items is a throughput-and-cost problem, the reverse of low-latency serving. Every choice that harms latency works in your favor here. Here is how to design for dollars-per-million-documents.

'What was revenue by region last quarter?' looks like text-to-SQL, yet production analytics agents break on ambiguity, wrong joins, and confidently wrong numbers. Here is the architecture that makes the answers trustworthy.

Enterprise search lives or dies on permissions, freshness, and wiring into thirty messy SaaS sources; retrieval quality is table stakes. Learn how to fan out across connectors, enforce per-user access at query time, and blend lexical with vector search for results people trust.

Selling RAG as a product means wrangling messy ingestion, multi-tenant isolation, and per-customer index freshness, all behind a simple API. See the ingestion and query planes, how to keep tenants isolated, and how to hand customers eval and observability.

Raw clickstream lands in object storage and somehow turns into clean tables, dashboards, and ML features. The design questions are table format, the bronze-silver-gold layering, batch versus streaming, and how you handle schema drift and late data. Here is the lakehouse blueprint.

You cannot put a 2 TB dataset in git, and copying it per experiment bankrupts you. What matters is knowing how content-addressed versioning works and when file-level (DVC) versus branch-level (lakeFS) fits. Here is the decision.

Anyone can wire tasks into a DAG. What matters is the patterns that keep it correct under retries and backfills: idempotency, data-aware triggering, and the asset model. Here is what separates a flaky pipeline from a trustworthy one.

AI features create cross-tenant leak paths that classic SaaS isolation never had to reckon with: shared embeddings, shared caches, and fine-tunes that memorize. One leak ends an enterprise contract. Here is the isolation model.

Stripping direct identifiers protects nobody, because the identifying information was never in the name column. Here is how a linkage attack actually works, why k-anonymity and its patches keep failing, and the one defense that makes a promise it can keep.

A failed prod deployment stress-tests judgment under pressure. Interviewers want to see stabilize-first instincts, clean communication, and a root-cause fix that heads off a repeat. Here is the arc.

Building it is not the same as adopting it. Interviewers look for you to win a beachhead, prove value, and let advocates spread it, not mandate usage from the top. Here is the playbook.

Rescuing a churning account is diagnosis under pressure: find the real reason, fix what you can, and win back trust with action. Interviewers look for a method, not a discount. Here is the move.

An embedded customer engagement works only if the customer can run it without you. Interviewers look for enablement and a real handoff plan, not a dependency. Here is how to build self-sufficiency.

Security and compliance teams are gatekeepers, not adversaries. Interviewers look for you to bring them in early, satisfy real requirements, and turn a blocker into an ally. Here is the approach.