Snowflake Applied AI Engineer interview questions

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

Vector search over rows is the wrong tool when the user asks for a count or an aggregate. The signal is retrieving the right schema, generating validated SQL, and knowing when to query the database instead of embedding it.

A Bloom filter answers 'have I seen this?' using a few bits per item instead of storing the key, trading a rare false positive for a huge memory win. Here is the build, the sizing math, and where it pays off in dedup and serving.

SCD2 separates people who have run pipelines from people who have read about them. The whole answer hinges on one atomic MERGE that closes the old row and opens the new one, and stays correct when the job retries.

Sessionization separates people who reach for a self-join from people who know LAG plus a running sum. Here is the two-pass pattern that scales to billions of events, plus the edge cases interviewers push on.

Pipelines fail and retry; the real question is whether a retry corrupts your data. The signal is idempotent writes (not 'exactly-once delivery') plus watermarks for late data. Here is how to make re-runs safe without double-counting.

The Databricks-flavored performance question. The signal is going straight to the usual suspects (skew, shuffle, spill) via the Spark UI, not guessing. Here is the diagnostic order and the fixes that actually 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. The signal is choosing the right ranking function for ties and knowing window frames. Here is the pattern and the tie nuance.

A hard streaming-systems question: dedup at high throughput with bounded state. The signal is a tiered state design (probabilistic filter in front of durable state) plus watermark-driven eviction. Here is the architecture that does not OOM.

A pipeline-design question that rewards matching the architecture to the freshness requirement, not chasing real-time for its own sake. The signal is the latency-vs-complexity tradeoff and knowing why Kappa emerged to kill Lambda's dual codebase. Here is the decision.

A data-warehouse fundamentals question that separates people who model for analytics from people who only know OLTP normalization. The signal is facts vs dimensions, the star schema, and why analytics denormalizes where transactional systems normalize.

The difference between a query that scans a terabyte and one that scans a gigabyte is usually layout, not the engine. The signal is partition pruning, columnar formats, and the small-files problem.

Syncing a warehouse with a live OLTP database is a CDC problem, and the naive 'full reload nightly' or 'query by updated_at' answers have real holes. The signal is log-based CDC and idempotent merges.

Bad data silently poisons everything downstream (dashboards, models, decisions), and 'it ran without error' is not '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 the signal is reading the execution plan before changing anything, then applying the right fix. Here is the diagnostic method that separates guessers from engineers.

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

Hierarchies (org charts, bill-of-materials, category trees) need recursion, and a self-join only goes one level deep. The signal is the recursive CTE with its anchor plus recursive members, and knowing how it terminates.

Pivoting (rows to columns) is constant 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 heart 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 silently doubles your sums.

The subtlety in rolling aggregates is the window frame clause, especially ROWS vs RANGE. The signal is the correct frame for a moving window and the tied-rows gotcha that corrupts running totals.

Means lie on skewed data like latency and spend, and SQL has 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 silently corrupts every dashboard downstream. The signal is knowing which changes are 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 clear story for late and out-of-order events.

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

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

Time-based analysis is everywhere in analytics, and time zones and bucketing are the common traps. The signal is DATE_TRUNC for bucketing, interval math, and storing UTC.

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

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

Exact COUNT(DISTINCT) over billions of rows tracks every unique value, so it gets 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 before it, which a normal join cannot. The signal is recognizing the per-row use cases: top-N per group, table functions, unnesting arrays.

Modern warehouses store JSON natively, and querying it well separates strong data engineers 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.

You cannot filter on a window function in WHERE, so people wrap queries in subqueries. QUALIFY filters window results directly. The signal is knowing why WHERE cannot do it and writing top-N-per-group cleanly.

MERGE handles insert-or-update-or-delete in one statement, the backbone of incremental loads and slowly-changing dimensions. The signal is the matched/not-matched logic and what makes a retry safe.

Modern table formats let you query a table as of a past version or timestamp, the backbone of reproducibility, audits, and recovery. The signal is the versioned-snapshot mechanism and where it breaks.

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

You don't always need a dedicated vector database. When embeddings live next to your relational data, nearest-neighbor in SQL makes filters and joins trivial. Here is how, and the scale where it breaks.

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 needs more care still. Here are both, done efficiently.

Production drift monitoring often 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 appear in an event table, so a 7-day average silently 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, produce 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 instead of trusting it.

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

Wide tables with a column per period are painless to read and painful 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 except for ROWS vs RANGE return different numbers, and most candidates cannot say why. The signal is knowing RANGE groups by value (peers) while ROWS counts physical rows.

Everyone says 'add an index,' but a strong engineer reads the plan first. The signal is finding the dominant operator, comparing estimated vs actual rows, and recognizing the seq-scan, bad-join, and spill patterns.

Partition on the wrong key and you get millions of tiny files or skewed giants. The signal is partitioning on a low-cardinality filter, clustering within partitions on the next predicate, and watching file size.

At-least-once duplicates and at-most-once loses data; everyone wants exactly-once and few can explain how Kafka delivers it. The signal is idempotent producers plus transactional read-process-write plus the read-committed isolation that ties them together.

All three give you ACID on object storage, but they were built for different problems. The signal is matching the format to the workload (engine neutrality, Spark-native ACID, or streaming upserts) rather than naming a favorite.

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

When a metric looks wrong, lineage tells you which upstream table to blame. The signal is distinguishing table vs column lineage and knowing the three ways to capture it: SQL parsing, runtime hooks, and metadata APIs.

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

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

A backfill that double-counts or takes down the live pipeline is worse than the original bug. The signal is idempotent partition-scoped rewrites, isolating backfill compute from live runs, and validating before swapping.

A flat path extract is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows and a clear rule for when to flatten vs query in place.

Most candidates only know 'use Debezium.' The signal is comparing three CDC mechanisms by how they capture deletes, load the source, and handle ordering, plus the snapshot-to-stream stitching that trips up real deployments.

Text-to-SQL is deceptively hard because correctness is binary and the failure mode is a confident wrong number. The signal is schema grounding, query validation, and a safety layer, not just 'prompt an LLM with the schema.' Here is the production design.

Batch scoring a billion items is a throughput-and-cost problem, the opposite of low-latency serving. Every choice that hurts latency helps you here. Here is how to design for dollars-per-million-documents.

'What was revenue by region last quarter?' sounds like text-to-SQL, but production analytics agents fail 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 connecting to 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.

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

Raw clickstream lands in object storage and somehow becomes 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. The signal 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. The signal 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 open cross-tenant leak paths that classic SaaS isolation never had to think about: shared embeddings, shared caches, and fine-tunes that memorize. One leak ends an enterprise contract. Here is the isolation model.

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

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

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

A forward-deployed engagement succeeds only if the customer can run it without you. Interviewers want 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 want to see you engage them early, meet real requirements, and turn a blocker into an ally. Here is the approach.

Applied AI rarely waits for certainty, and companies (Amazon's Bias for Action, NVIDIA's speed) test whether you can act decisively without freezing or being reckless. The signal is a reversible, well-reasoned call with a plan to validate. Here is the arc that lands.

A sudden 22-point AUC jump is a gift and a warning. Junior candidates celebrate; strong ones get suspicious and know exactly which leakage checks to run before anything ships.

Moderation is a multi-stage classification problem with brutal tradeoffs: false negatives cause real harm, false positives censor legitimate users, and the adversary adapts. The signal is the tiered pipeline, per-severity precision/recall calibration, and human-in-the-loop. Here is the design.

Applied AI is half translation. Interviewers ask this to see whether you can drop the jargon, frame tradeoffs in business terms, and earn trust with people who control the roadmap. Here is how to show that skill in the answer itself.

Imbalance shows up in fraud, churn, and abuse, and the naive answer (oversample, done) leaks data and inflates offline metrics. The signal is preferring cost-sensitive learning, fixing the metric, and resampling correctly. Here is the full toolkit with tradeoffs.

This is two questions hiding as one: how would you have caught it before full rollout, and how do you debug it now. Answer both and you signal senior judgment. Here is the staged-rollout and root-cause playbook.

Extreme class imbalance plus a hard latency budget plus an adversary who adapts. The signal is handling imbalance honestly, choosing the operating point from costs, and designing for the feedback loop. Here is the end-to-end design.

This question is not about whether you failed; everyone has. It is a test of ownership, debugging rigor, and honesty under pressure. Here is the structure that turns a failure story into a hire signal, and the traps that turn it into a flag.