Data engineer domain knowledge interview questions

As asked

You have a Spark job joining a 10TB fact table with a 200MB dimension table and the query plan shows a sort-merge join. Explain why a broadcast hash join would be more efficient here, how Spark decides which join strategy to use, and how you would force the broadcast join if Spark does not choose it automatically.

Sample answer outline

A broadcast join sends the entire small table to every executor, avoiding a shuffle of the large table entirely. Spark chooses broadcast joins when the smaller table is below spark.sql.autoBroadcastJoinThreshold (default 10MB). At 200MB, Databricks may not broadcast automatically. The candidate should mention the broadcast() hint in DataFrame API or SQL: SELECT /*+ BROADCAST(dim) */ ... or df.join(broadcast(dim_df), ...). They should note the risk: broadcasting too large a table causes OOM on executors. The tradeoff between shuffle cost (sort-merge) and memory cost (broadcast) should be explicit.

Expect these follow-ups

• How do you diagnose in the Spark UI whether a join became a sort-merge instead of a broadcast?
• What happens if you broadcast a table that is too large for executor memory?

As asked

Explain the bronze/silver/gold medallion data architecture. When is it the right pattern, and when is it overkill?

Sample answer outline

Bronze: raw ingested data, schema-on-read, no transformations. Silver: cleaned, conformed, deduplicated, joined with reference data. Gold: business-ready, often aggregated, modelled for specific consumer use cases. The point is to separate ingestion concerns from modelling concerns and to keep raw data immutable. It is overkill for small data volumes, or when there is only one downstream consumer, or when the source is already clean and stable. The hidden cost is storage duplication and three sets of tests to maintain.

Expect these follow-ups

• How does this compare to the data mesh approach?
• Where does dbt fit in this architecture?
• Why might you collapse silver and gold for some domains?

As asked

Databricks ships an execution engine called Photon alongside the standard Spark JVM execution engine. Explain what Photon is, why it exists, and in what workloads it gives the most benefit.

Sample answer outline

Photon is a C++ vectorized query execution engine that Databricks built to exploit CPU SIMD instructions and avoid JVM overhead for analytical SQL queries. It is most beneficial on scan-heavy, aggregation-heavy SQL workloads on Parquet or Delta files because columnar vectorization saturates modern CPUs far better than the row-at-a-time JVM Spark code path. It is less helpful for jobs dominated by Python UDFs or complex graph processing. Candidates should note that Photon integrates transparently through the Catalyst planner and falls back to Spark JVM for unsupported operations.

Expect these follow-ups

• In which situations would you expect Photon to give no improvement or even a slowdown?
• How does Photon relate to Apache Arrow and the broader move toward columnar formats in the data ecosystem?

As asked

A customer wants a single governance layer across multiple Databricks workspaces in different cloud regions. They also want to know which notebooks and jobs read from or write to a given Delta table. How does Unity Catalog address both of those requirements?

Sample answer outline

Unity Catalog provides a three-level namespace (catalog, schema, table) that is shared across all workspaces attached to the same metastore. Permissions are granted once at the catalog or schema level and apply everywhere. For lineage, Unity Catalog captures column-level and table-level lineage automatically from Spark, SQL, and Delta operations, storing which queries, notebooks, jobs, and users read or wrote each table. This lineage is queryable through the Unity Catalog UI and API without any manual tagging by engineers.

Expect these follow-ups

• How would you migrate a workspace from the legacy Hive metastore to Unity Catalog without breaking existing jobs?
• What access control model does Unity Catalog use, and how does it differ from table ACLs in the legacy metastore?

As asked

A data engineering team asks you whether to use Delta Live Tables or write their own Spark Structured Streaming pipeline. What are the concrete tradeoffs and when would each be the right choice?

Sample answer outline

DLT manages pipeline infrastructure, automatic checkpointing, error recovery, data quality expectations, and lineage automatically, reducing operational burden. It is best for teams that want declarative pipelines with enforced SLAs and quality rules and do not need fine-grained control over streaming state management. Hand-written Structured Streaming is better when you need custom state operations (flatMapGroupsWithState), non-Delta sinks, or very specific checkpoint and trigger control. DLT has higher abstraction cost: debugging is harder and the programming model is more constrained.

Expect these follow-ups

• How do DLT expectations work, and what happens to records that fail them?
• Can you mix DLT tables and regular Delta tables in the same Databricks workflow?

As asked

A Databricks SQL query goes from your SQL string to actual tasks running on executors. Walk me through the stages of the Catalyst optimizer: what transformations happen at each stage and what decisions are made along the way.

Sample answer outline

Catalyst works in four stages. Parsing turns the SQL string into an unresolved logical plan. Analysis resolves column names and types against the catalog, producing a resolved logical plan. Logical optimization applies rule-based rewrites: predicate pushdown, constant folding, column pruning. Physical planning converts the logical plan to one or more physical plans using strategies (e.g., choosing broadcast vs. sort-merge join based on table sizes), then the cost model selects the best. Code generation (Janino/WholeStageCodeGen) compiles the physical plan to JVM bytecode or hands it to Photon.

Expect these follow-ups

• Where in this pipeline does Adaptive Query Execution intervene, and what statistics does it use?
• How would you add a custom Catalyst rule to, for example, rewrite all calls to a deprecated UDF to a new one?

As asked

Databricks customers sometimes use RDDs in their Spark code. When is an RDD actually the right choice over a DataFrame or Dataset, and what are the performance implications of that choice?

Sample answer outline

RDDs are appropriate when the data is unstructured and cannot be represented as rows and columns, when you need fine-grained control over partitioning that the DataFrame API does not expose, or when you are implementing a graph algorithm that requires custom graph traversal. The performance implication of choosing RDDs over DataFrames is significant: DataFrames benefit from Catalyst optimization and Tungsten's off-heap memory management, which RDDs bypass entirely. Python RDDs also incur Python serialization overhead for each record. In most analytical workloads, DataFrames are 5 to 10x faster than equivalent RDD code.

Expect these follow-ups

• How does Tungsten's off-heap memory management reduce GC pressure compared to standard JVM heap usage?
• If you needed custom partitioning logic in a DataFrame pipeline, how would you achieve it without dropping to RDD?

As asked

A company is migrating their Databricks SQL workloads and is choosing between Serverless SQL Warehouses and classic (provisioned) SQL Warehouses. Walk me through the meaningful tradeoffs across cost, latency, scalability, and configuration control.

Sample answer outline

Serverless warehouses start in seconds (no VM provisioning) and bill per second of query execution, making them cost-effective for intermittent, unpredictable workloads. Classic warehouses have a higher cold-start time (2 to 5 minutes for VM provisioning) but offer more configuration control (worker type, driver size, init scripts, custom Spark configs) and can be cheaper for sustained, predictable workloads at full utilization. Serverless warehouses handle auto-scaling more smoothly because Databricks manages the pool. The main loss with serverless is the inability to run arbitrary init scripts or install native libraries on the cluster.

Expect these follow-ups

• In what scenario would a classic cluster still be the right choice even though serverless is generally simpler?
• How does the billing model difference between serverless (per second of query) and classic (per DBU-hour of cluster uptime) affect cost optimization strategies?

As asked

A data platform team wants to enforce data quality rules on all Delta tables ingested into the Lakehouse: null checks, referential integrity, value range checks, and freshness SLAs. How would you design and implement that framework on Databricks?

Sample answer outline

A strong answer starts with Delta Live Tables expectations for declarative quality rules on streaming or batch ingestion, showing how EXPECT, EXPECT OR FAIL, and EXPECT OR DROP work. For tables not in DLT, candidates should describe a validation layer using Great Expectations integrated with Databricks, or custom PySpark checks run as a post-ingestion step in a Databricks workflow. Freshness SLAs can be checked by comparing the max timestamp in the table against current time and alerting via Databricks SQL alerts or a webhook. Unity Catalog can enforce column-level policies. Candidates should address what happens on failure: quarantine to a bad_data table versus hard failure versus alerting only.

Expect these follow-ups

• How would you track quality metric trends over time to detect gradual degradation, not just threshold violations?
• What is your strategy for data quality checks that are too expensive to run on every record but too important to skip?

As asked

Your team currently runs Apache Airflow and is evaluating whether to migrate to Databricks Lakeflow Workflows for orchestrating their Spark and dbt jobs. What are the concrete differences, and when would you keep Airflow versus migrate to Lakeflow?

Sample answer outline

Lakeflow Workflows is deeply integrated with the Databricks platform: it can launch cluster-backed notebooks, dbt tasks, SQL tasks, and DLT pipelines from a single DAG with no external scheduler to maintain. It has first-class support for cluster reuse, repair run (restart from failed task), and native lineage to Unity Catalog. Airflow has a much richer ecosystem of operators (Kubernetes, Snowflake, Salesforce, etc.), is open-source with broader community support, and is easier to test locally. Lakeflow is the natural choice for teams that live entirely in Databricks. Airflow stays better when workflows span multiple systems or the team already has strong Airflow expertise and custom operators.

Expect these follow-ups

• How would you migrate an existing Airflow DAG with a DatabricksSubmitRunOperator to Lakeflow Workflows?
• What monitoring and alerting capabilities does Lakeflow Workflows offer that you would need to replicate in Airflow?