Snowflake data interview pack

The Snowflake loop

1. Internals

   Micro-partitions, clustering vs Search Optimization, Query Profile nodes.

2. Gen AI specialty

   Cortex COMPLETE in ELT vs a BI explore. Analyst vs Search. Token cost per row.

SQL (4)

1. hard · SQL

   NULL = NULL is unknown. How does that bite a MERGE or an anti-join?

   NOT IN with a NULL in the list returns empty. Anti-joins should be NOT EXISTS or a LEFT JOIN … WHERE right.key IS NULL. MERGE match conditions that compare nullable keys silently miss rows. Say this out loud; it is a common take-home trap.

2. medium · SQL

   QUALIFY vs a CTE for window filters — when do you use each?

   QUALIFY is cleaner in Snowflake/BigQuery when the dialect has it. A CTE is portable and easier to debug. Either is fine; filtering rn in WHERE on the same SELECT is not.

3. medium · SQL

   A query is correct but scans last year. How do you prove partition pruning?

   Read the plan / bytes scanned / partitions assigned. Add the partition column to the filter (or clustering). EXPLAIN or the warehouse profile — not a guess.

4. easy · SQL

   FILTER (WHERE status = 'paid') on an aggregate vs a CASE. Why mention it?

   FILTER is readable and often plans the same as SUM(CASE WHEN …). Interviewers want you to know both, and that MySQL/SQL Server need CASE. Portability is a real answer.

Data modeling (3)

1. medium · dbt

   ref() vs source() — why does the distinction matter in an incident?

   source() is the raw relation you do not own; ref() is a model in the project DAG. Breaking a source contract is a producer problem. Breaking a ref is your DAG. Tests on sources catch upstream silence; tests on marts catch your grain. Mixing them hides who pages whom.

2. medium · dbt

   Who owns grants after dbt build?

   The warehouse role dbt uses must be able to GRANT. Configure grants on the model so BI is not broken every rebuild. “We click in the UI” does not survive CI.

3. easy · dbt

   A package from dbt Hub pins an old adapter. How do you not freeze upgrades?

   Pin versions, read changelogs, run slim CI on the package bump in a branch. “Latest” on main is how Friday night breaks. Vendored macros you do not understand are worse than a package.

Orchestration (2)

1. hard · Airflow

   data_interval_end vs execution_date. Which one do you use for a daily partition?

   In 2.x, a 00:00 DAG run for 2026-08-28 covers [28, 29). The partition you load is usually data_interval_start (the logical date). Using “today” or pendulum.now() inside the task is how you reload the wrong day after a retry.

2. medium · Airflow

   A DAG import queries Snowflake to build task lists. Why did the scheduler restart loop?

   Parse-time I/O. Top-level queries fail or hang, and every parse hits the warehouse. Build the list in a task, use a catalog, or static YAML.

Streaming (2)

1. medium · Apache Flink

   Flink or Spark Structured Streaming?

   Flink for low-latency keyed state and true event-time. Spark for micro-batch ETL that happens to read Kafka. If the SLA is warehouse minutes, you may not need either — Dynamic Tables / DLT might be enough.

2. hard · Apache Flink

   Exactly-once into Kafka is on; the warehouse sink is at-least-once MERGE. What do you tell the stakeholder?

   End-to-end is the weakest sink. The MERGE must be idempotent on a primary key. “Flink is exactly-once” without a sink contract is marketing.

Warehouse (17)

1. medium · Recent signal, 2026-08-18

   A hiring manager asks you to put Cortex COMPLETE in a Looker explore. What do you say?

   No. Materialize the label in ELT once, version the prompt, and let BI read a column. Dashboard refresh is a token bill and a non-deterministic metric.

2. medium · Snowflake

   Someone DELETED gold.orders. What do you do in the first ten minutes?

   Query History for the last good timestamp. SELECT count(*) … AT (TIMESTAMP => …). CLONE to a _fix table and diff. Do not UNDROP as the first move if writers are live — you can race a reload. Time Travel is a restore window, not a backup policy. Fail-safe is Snowflake-only and not queryable.

3. medium · Snowflake

   Streams and Tasks vs an external orchestrator. How do you choose?

   Streams + Tasks are fine for a few in-Snowflake incremental loads. Once you have 30 jobs, cross-system dependencies, or a human on-call rotation, put Airflow / Dagster on top and keep Tasks as the SQL worker. Do not invent a second orchestrator inside Snowflake “just because it is native.”

4. medium · Snowflake

   Search Optimization vs a clustering key — one sentence each.

   Clustering helps range/filter scans on poorly ordered columns. Search Optimization helps highly selective point lookups. They cost extra credits. Do not buy both “just in case.”

5. medium · Snowflake

   Zero-copy clone of prod for a destructive test. What do you warn finance?

   Clone is cheap at t0. Storage grows as either side writes. Time Travel on the clone is not a backup of prod. Drop the clone when the test ends.

6. easy · Snowflake

   A share vs an export. When is export still right?

   Share when both sides are Snowflake and you want live data. Export when the consumer is not Snowflake, needs a file SLA, or cannot be a reader account.

7. hard · Snowflake

   Dynamic Tables vs Streams + Tasks. Pick one and defend it.

   Dynamic Tables when the graph is SQL and you want a lag target. Streams+Tasks when you need custom Task logic or an external orchestrator already owns the clock. Do not run both on the same table without a reason.

8. medium · Snowflake

   SnowPro Specialty: Gen AI — where does COMPLETE belong?

   In incremental ELT that writes a versioned column. Not in a Looker explore, not on every dashboard refresh, and not on raw PII.

9. easy · Snowflake

   Cortex Analyst returns doubled revenue. First check?

   The semantic model grain and joins — same fan-out you would debug in SQL. A bigger warehouse will not fix a bad metric definition.

10. medium · Snowflake

    Auto-suspend 60s and a dashboard that ticks every 90s. What happens to the bill?

    The warehouse resumes almost every tick. You pay the resume (and the minimum billed window) all day. Either lengthen suspend, use a dedicated XS that stays warm, or materialize the tile.

    Hint: Resume cost, not bytes scanned.

11. medium · Snowflake

    A task graph and Airflow both schedule the same MERGE. Failure mode?

    Two clocks, duplicate work, lock contention, and “who pages.” Pick one orchestrator. Snowflake Tasks are fine for SQL-only graphs; Airflow owns when humans and sensors exist.

12. hard · Snowflake

    Secure view vs masking policy vs row-access policy. One sentence each.

    Secure view hides the SQL and blocks some optimizations. Masking rewrites a column per role. Row-access filters rows. Do not stack all three “for security theater.”

13. easy · Snowflake

    DIRECTORY tables and a stage full of JSON. Why is SELECT $1:id from the stage a bad gold pattern?

    Stage queries are ingest, not a mart. COPY or Snowpipe into VARIANT bronze, then typed silver. BI on a stage has no Time Travel you own and no tests.

14. medium · Snowflake

    Query Acceleration Service vs a larger warehouse. When do you buy QAS?

    Spiky, scan-heavy queries that overflow a well-sized warehouse. If every query is slow, size or rewrite first. QAS is not a clustering key.

15. hard · Snowflake

    ACCOUNTADMIN ran a Cortex call on a PII column. What was missing besides “don’t”?

    A masking policy that still applies to functions, a dedicated role for LLM jobs, and an allow-list of columns. Cortex is another consumer of the column, not a bypass.

16. medium · ClickHouse

    ClickHouse or Snowflake for product analytics? Pick and defend.

    ClickHouse for high-QPS event analytics and wide denormalized facts. Snowflake for governed marts, mixed workloads, and BI that needs roles/Time Travel. Many shops: CH for product, warehouse for finance.

17. hard · PostgreSQL

    NULL in a unique column. What happens?

    Postgres unique allows multiple NULLs (until NULLS NOT DISTINCT). Warehouse dialects differ. CDC + MERGE on a nullable key silently drops or dupes. Name the constraint.

System design (3)

1. medium · System design

   Cost doubled and nobody knows which team. What was missing in the design?

   Tags / chargeback on compute and storage, query tags from dbt/jobs, and an owner on every gold table. Architecture without FinOps is a slide.

2. medium · System design

   Multi-tenant SaaS analytics. How do you isolate?

   Tenant_id on every fact, row policies or separate schemas for large tenants, and never a shared “admin” role that can SELECT * across tenants. Compaction and clustering on tenant + date.

3. easy · System design

   Cost attribution: five teams, one Snowflake account. Minimum viable design?

   One warehouse (or tag) per team, resource monitors, and QUERY_HISTORY grouped by warehouse/role. Shared XS for ad-hoc with a tight auto-suspend. Without tags, FinOps is a spreadsheet of villains.

Data quality (2)

1. hard · Data quality

   dbt tests vs Great Expectations vs Monte Carlo.

   dbt for model grain you own. GE/Soda for richer expectations and non-dbt paths. A vendor when silent breakage across hundreds of tables is the incident. Start with tests; buy when misses are expensive.

2. medium · Data quality

   Schema drift: a new column in JSON bronze. Auto-add to gold or fail?

   Land in bronze/VARIANT automatically; fail or ticket before it becomes a typed gold column. Auto-promoting unknown fields is how PII appears in BI. An allow-list is a valid contract.

AI / ML (4)

1. hard · AI / RAG / MLOps

   When is “just use the warehouse LLM function” the wrong answer?

   When you need a specific model, tight latency, or tool-use the warehouse cannot host. Cortex/BigQuery ML are ELT steps, not every product.

2. hard · AI / RAG / MLOps

   Tenant A can retrieve Tenant B’s chunks. Walk the failure.

   You embedded without tenant_id in the filter/metadata, or the query path does not apply the ACL. Metadata filter at retrieval is mandatory. “The prompt said not to” is not an ACL.

3. medium · AI / RAG / MLOps

   You version prompts in git. The warehouse COMPLETE call still used v1. Why?

   The job pins a prompt table/version at runtime and nobody bumped it, or COMPLETE inlines a string in SQL. Treat prompts like dbt models: version column, PR, and an eval before promoting.

4. medium · AI / RAG / MLOps

   Embeddings in Snowflake vs a dedicated vector DB. One sentence for each.

   Warehouse when retrieval is batch/ELT and ACL already lives in SQL. Dedicated DB when you need low-latency ANN and the app is the product. Hybrid: warehouse is source of truth, index is a derived store with a rebuild job.

Behavioral (4)

1. hard · Behavioral

   Warehouse cost doubled and leadership asked you in a meeting. Walk the room through it.

   Do not guess. Query history, warehouse resume count, a clustering job, a Cortex or BI refresh loop. Show credits by warehouse and by user, then one change you made (auto-suspend, a smaller size, materialize the explore). Chargeback tags if you have them. “We will migrate to Spark” without a number is a fail.

2. easy · Behavioral

   You disagreed with a senior engineer on lake vs warehouse. How did it end?

   A spike: one workload, cost, and a consumer. Data, not identity. If you lost, say what you learned (maybe the warehouse was right for BI). If you won, say the rollback plan. Ego is the junior tell.

3. medium · Behavioral

   How do you say no to ACCOUNTADMIN for an analyst “just this once”?

   Give a timed break-glass with logging, or a narrower role that unblocks the task. Explain blast radius (UNDROP, shares, billing). If leadership overrules, write the exception. Permanent ACCOUNTADMIN is how incidents become existential.

4. easy · Behavioral

   Tell me about a time you reduced warehouse cost without a hero rewrite.

   Concrete: auto-suspend, a clustering key on a real filter, killing a 5-minute dashboard tick, or a warehouse per team. Percent and dollars. “We migrated everything to X” is not the only story — small levers count.

SQL coding labs

Problem statements only. Run them against tests on DataLane; solutions live in the pad.

1. medium · about 12 min · tables: events

   Traffic source from JSON payload

   events.payload is raw JSON with source and campaign keys. Break activity down by source without unloading the column into a staging table first — this is the semi-structured question every warehouse screen has now.

   https://www.datalane-data.blog/coding/problems/event-source-breakdown/