Databricks data interview pack

The Databricks loop

1. Lakehouse

   What _delta_log does on a failed write. OPTIMIZE vs VACUUM. Liquid clustering vs ZORDER.

2. Governance

   catalog.schema.table, grants, and why path-based tables have no lineage.

SQL (3)

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. 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.

Coding (10)

1. medium · Spark

   Driver vs executor. Where does your Python UDF actually run?

   The driver builds the plan. Executors run tasks on partitions. A Python UDF runs in a Python worker next to the executor — it is not “free SQL.” Prefer Spark SQL / built-in functions. collect() on a large frame is how you OOM the driver.

2. medium · Spark

   Why did a job that “worked in the notebook” fail as a scheduled job?

   Notebooks hide cluster size, caching, and an interactive driver. Jobs hit real data volumes, a smaller cluster, and no cached dataframes. Also: widgets and dbutils shortcuts that are not in the wheel. Always run the job definition on a sample of prod-scale data.

3. medium · Spark

   Kryo vs Java serialization. When do you care?

   When the UI shows time in serialization and shuffle bytes are huge. Register classes. It does not fix a bad join.

4. hard · Spark

   S3 vs HDFS locality. Why was the same job faster on HDFS?

   Object stores have weaker locality and more LIST/GET. File sizing and partition pruning matter more on S3.

5. easy · Spark

   foreach on the driver vs foreachPartition.

   Side effects belong on executors, idempotently. A driver for-loop over collect() is the anti-pattern.

6. easy · Spark

   Read an explain plan: how do you confirm a broadcast?

   BroadcastHashJoin (or AQE converted) in the physical plan / SQL tab. Do not guess from a hint you leftover last year.

7. hard · Spark

   AQE is on. You still got a 2 TB shuffle. What did AQE not fix?

   A missing broadcast because the small table was estimated huge, or a explode that created the 2 TB. AQE helps skew and coalesces — it does not rewrite a bad join key. Look at the SQL plan after AQE, not the one you hoped for.

8. easy · Spark

   UDF vs Spark SQL expr for a string normalize. Which do you merge?

   expr / built-in functions. Python UDFs kill Catalyst and Photon. If you must UDF, pandas/arrow vectorized, and measure. “It was faster on my laptop” is not a plan.

9. medium · Spark

   Job failed with metadata column _rescued_data full of JSON. What is Auto Loader telling you?

   The file schema drifted. Rescue is a quarantine, not a feature. Add the new fields or fail the pipeline. Dropping rescued_data is how you lose orders.

10. easy · Spark

    A notebook used display() and dbutils.fs. The scheduled job has neither. What was the real bug?

    The notebook was the application. Package a wheel/job, pass configs, and do not depend on interactive widgets. “Worked in the notebook” is the most common Spark incident report.

Data modeling (5)

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.

4. easy · Python & modeling

   pandas vs Spark vs SQL for a 40 GB join. How do you choose?

   If it already lives in a warehouse, write SQL (or dbt). Spark when the data is files on a lake and the job is distributed. pandas when it fits in memory on one box — and 40 GB does not. “I would load it into pandas and merge” ends the interview.

5. easy · Python & modeling

   Pickle a 20 GB frame to S3. What do you say?

   No. Parquet/Arrow with a schema. Pickle is a security and compatibility smell.

Orchestration (5)

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. hard · Airflow

   setup/teardown vs a hope that the cluster terminates.

   Airflow 2.7+ teardown runs even when the work fails. Use it for ephemeral clusters and temp schemas. Hope is not a task.

3. medium · Airflow

   deferrable sensors vs poking every 30s. What did you free?

   Worker slots. A poking sensor holds a slot for the whole wait. Deferrable releases the worker and waits in the triggerer. Still need a timeout and an SLA miss callback.

4. medium · Dagster

   What is a software-defined asset, in one sentence?

   A table (or file, or report) you declare with its dependencies; the scheduler materializes it. You are not scheduling a Python function that happens to write a table — you are scheduling the table.

5. easy · Dagster

   You still run Airflow for 40 vendor connectors and Dagster for marts. Is that a failure?

   No. Two orchestrators can be honest if ownership is split and you do not dual-schedule the same MERGE. A rewrite of every DAG is a year, not a sprint. Say the boundary.

Streaming (3)

1. easy · Kafka

   Kafka Connect vs a Flink job for the same JDBC sink. When is Connect enough?

   When the transform is light (rename, flatten) and you want offset management for free. Flink when you need event-time windows, exactly-once into Iceberg, or serious state. Do not write a Connect SMT that is actually a stream join.

2. 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.

3. easy · Apache Flink

   Flink SQL vs DataStream API for a 5-minute tumbling count. Which do you merge?

   Flink SQL if the window is standard and you want a table API. DataStream when you need custom timers, side outputs, or a sink the SQL connector cannot express. Do not write DataStream to look senior.

Warehouse (16)

1. medium · Recent signal, 2026-07-28

   Someone ran VACUUM 0 HOURS on a gold table. What is actually broken?

   In-flight readers and every snapshot older than now. VACUUM deletes files. OPTIMIZE does not. Restore is gone if the files are gone.

2. easy · Databricks

   When do you pick Delta on Databricks versus Iceberg on a shared lake?

   Delta is the default if Databricks is the compute and you want UniForm / deletion vectors / Liquid Clustering in that ecosystem. Iceberg if Trino, Snowflake, and Spark must read the same files without a vendor lock. Do not dual-write “just in case.”

3. medium · Databricks

   OPTIMIZE and ZORDER versus Liquid Clustering. What do you actually run weekly?

   OPTIMIZE compacts small files. ZORDER (or Liquid Clustering) colocates filter columns. VACUUM removes stale files after the retention window — and it is irreversible. Do not VACUUM 0 hours in prod. Quote the retention and the “time travel vs vacuum” trade.

4. easy · Databricks

   Unity Catalog vs Hive metastore. Why do interviewers care in 2026?

   UC is the governance plane: identities, row filters, lineage, a 3-level namespace. HMS is the legacy catalog. “We still have two catalogs” is a real migration answer. Do not claim ACLs on a path-based table you never registered.

5. easy · Databricks

   Job cluster vs all-purpose for a 20-minute twice-daily ETL.

   Job cluster that dies when the run ends. An always-on all-purpose box is an idle bill.

6. easy · Databricks

   UniForm vs dual-write Delta and Iceberg.

   UniForm (or one Iceberg table) with a single writer. Dual-write is how the two copies lie to each other.

7. hard · Databricks

   DLT expectations: warn vs fail vs drop.

   Fail or quarantine when the SLA cannot tolerate bad keys. Warn-only is a blog post. Drop is a product decision you must document.

8. medium · Databricks

   Instance pools — when are they worth idle VMs?

   When job start time is in the SLA and cold VM spin is 8 minutes. Size the idle count to the SLA, not to max theoretically.

9. medium · Databricks

   Liquid Clustering vs ZORDER on a 2 TB fact. What do you actually choose in 2026?

   Liquid Clustering when the table is Delta and you want ongoing layout without OPTIMIZE heroics. ZORDER is the older weekly job. Do not ZORDER every column “because Tableau.”

10. hard · Databricks

    A DLT pipeline with expect_or_drop on 12% of rows. Is gold trustworthy?

    No. You silently deleted a cohort. expect_or_fail for contracts; quarantine for the rest. Drop is for true garbage (malformed JSON), not “status we do not like.”

11. medium · Databricks

    Photon is on, the job is still slow. Name two reasons Photon is not the bug.

    A Python UDF that leaves Photon, a skew join, or tiny files. Photon does not fix a CROSS JOIN. Look at the Spark UI SQL tab before buying more workers.

12. easy · Databricks

    Unity Catalog volume vs a DBFS path in a notebook. What do you refuse to merge?

    Hard-coded dbfs:/ in production jobs. Volumes + UC grants are the paved road. DBFS mounts with a personal token are how you leak prod.

13. easy · Databricks

    Serverless SQL warehouse vs a job cluster for dbt on Databricks. How do you pick?

    SQL warehouse for BI/adhoc SQL; job cluster (or SQL warehouse with a scheduled dbt job) for ELT. All-purpose clusters for scheduled ETL are an idle-VM tax.

14. medium · Databricks

    You VACUUMed with a 7-day retention and a reader is on an 8-day-old notebook. What happens?

    The reader can fail on missing files. Time Travel is not a promise beyond retention. Tell analysts the retention SLA; do not VACUUM 0 HOURS to “save storage.”

15. 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.

16. 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.

Cloud (1)

1. easy · AWS

   S3 committers — why did a failed Spark write leave a partial prefix?

   The default committer can list/rename badly on S3. Use a magic committer and an idempotent overwrite of a partition.

System design (3)

1. hard · System design

   Two engines must share one lake table. What do you refuse to do?

   Dual-write CSV trees. Pick Iceberg (or UniForm) with a single writer. Two writers is how the copies lie.

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

   Fail the pipeline or open a ticket? Give a rule of thumb.

   Fail on grain/uniqueness/freshness that would ship a wrong number. Ticket (warn) on soft volume drift and new enum values you have not classified. If everything fails, nothing does — people --full-refresh and go home.

AI / ML (5)

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

   Chunk size 512 vs 2000 tokens. How do you choose without a vibe?

   An eval set: retrieval hit-rate at k, and answer faithfulness. Too small loses context; too large dilutes the embedding. Chunking is a modeling choice with a metric, not a default in a tutorial.

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. easy · AI / RAG / MLOps

   When is a feature store overkill?

   One model, one batch job, one gold table with a documented as-of join. A store pays off with many models, online serving, and reuse. “We bought Feast” is not a design.

5. 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

   How do you raise the bar for a junior who ships untested marts?

   Pair on the first unique/not_null tests, require them on every gold PR, and show a past fan-out. Do not take the keyboard forever. Interviewers want coaching plus a gate, not “I rewrote it Friday night.”

3. 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.

4. 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.

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/