Troubleshooting, Monitoring, and Optimization — Databricks Data Engineer Associate Practice Quiz

Question 1

An analyst runs the same dashboard query dozens of times per hour against a large Delta table. Each execution rereads the same Parquet files from cloud object storage, and the Spark UI shows most of the stage time spent in remote reads rather than compute. The data changes only once per day after an overnight batch load. Which optimization will most directly reduce the repeated remote-read latency for these interactive queries?

Accepted Answer

Enable disk caching (Delta cache) on the cluster so frequently accessed Parquet files are stored on local SSDs of the worker nodes

Answer

Increase the number of shuffle partitions with spark.sql.shuffle.partitions to parallelize the reads

Answer

Run VACUUM on the table with a zero-hour retention to remove stale files and speed up scans

Answer

Repartition the DataFrame in memory before each query using repartition(200)

Question 2

A data engineer manages a Delta table that is continuously written to by a Structured Streaming job with a short micro-batch interval. Analysts complain that read queries against this table have become progressively slower over several days. In the Spark UI, they notice queries are launching thousands of tiny file-scan tasks. The table has no partitioning issues and query filters are appropriate. What is the most likely root cause and appropriate remediation?

Accepted Answer

The streaming job is producing many small files per micro-batch; schedule periodic OPTIMIZE to compact them into larger files.

Answer

The table is missing a Z-ORDER index on the join key; run ANALYZE TABLE to rebuild statistics.

Answer

Time travel history is accumulating; run VACUUM with a zero-hour retention to remove old versions and speed up reads.

Answer

The Delta transaction log is corrupted; recreate the table with CREATE TABLE AS SELECT to reset it.

Question 3

A data engineer manages a Lakeflow Declarative (DLT) pipeline that applies several data quality expectations across bronze and silver tables. After a production run, stakeholders report that some downstream metrics look off, and the engineer needs to determine how many records failed each expectation over the last several pipeline updates without manually re-running the pipeline. Which approach provides this historical data quality information most directly?

Accepted Answer

Query the pipeline's event log, filtering on the 'flow_progress' events to read the data quality metrics recorded for each expectation.

Answer

Open the Spark UI for the most recent run and inspect the Stages tab for records dropped during each transformation.

Answer

Run DESCRIBE HISTORY on each target Delta table to view the count of rows rejected by each expectation.

Answer

Re-run the pipeline in development mode and watch the notebook cell output for expectation violation counts.

Question 4

A data engineer needs to monitor a Lakeflow Declarative Pipeline (formerly Delta Live Tables) to report on how many records failed each expectation over the last several runs. Rather than manually inspecting the pipeline UI each time, they want to query this information programmatically to build an automated data-quality dashboard. What is the most appropriate source for these metrics?

Accepted Answer

Query the pipeline's event log, which records expectation results and data-quality metrics as structured events

Answer

Enable the Spark UI and scrape the SQL tab for expectation pass/fail counts after each run

Answer

Run DESCRIBE HISTORY on each target table to read the number of dropped rows per expectation

Answer

Parse the driver's stdout log files stored in the cluster log delivery location for expectation warnings

Question 5

A data engineer manages a large Delta table that is frequently queried with filters on different columns depending on the reporting period — sometimes by region, other times by product_category, and increasingly by sales_rep_id. The team currently uses static partitioning on region, but query performance has degraded and many small files exist within some partitions. They want a layout strategy that adapts to changing query patterns without requiring them to rewrite the table's partitioning scheme each time filter columns change. Which approach best addresses this requirement?

Accepted Answer

Enable liquid clustering on the table using CLUSTER BY on the frequently filtered columns, allowing the clustering keys to be changed over time without rewriting the table structure

Answer

Repartition the table using PARTITIONED BY on region, product_category, and sales_rep_id together to cover all filter combinations

Answer

Run OPTIMIZE with ZORDER BY on region only, since Z-ordering permanently fixes the optimal data layout for all future queries

Answer

Cache the entire table in memory using CACHE TABLE so that all filter queries read from memory regardless of the columns used

Question 6

A data engineer manages a large Delta table that is partitioned by `country`. Over time, most partitions have become very small because many countries have few records, while a handful of countries dominate the data. Query performance is inconsistent, and OPTIMIZE runs are slow. The team wants a single approach that avoids the small-file and skew problems of fixed partitioning while still improving data skipping on `country` and `event_date`. What should the engineer do?

Accepted Answer

Recreate the table using liquid clustering on `country` and `event_date` instead of partitioning

Answer

Add a second partition column on `event_date` to create more balanced partitions

Answer

Increase the number of shuffle partitions so OPTIMIZE writes larger files

Answer

Keep the partitioning and schedule VACUUM more frequently to remove small files

Question 7

A data engineer runs a nightly gold-layer job that performs large SQL aggregations and joins over Delta tables. The job meets its SLA but leadership wants faster runtimes without rewriting the transformation logic. The cluster currently uses a standard Databricks Runtime. Which change is most likely to accelerate this CPU-bound SQL and aggregation workload with minimal code changes?

Accepted Answer

Enable a Photon-enabled Databricks Runtime on the cluster

Answer

Increase the Delta log retention interval for the target tables

Answer

Switch all tables from managed to external storage

Answer

Disable Adaptive Query Execution to reduce planning overhead

Question 8

A data engineer notices that a Spark job processing a large join runs slowly. In the Spark UI, they open the stage details and see that 199 of 200 tasks completed in under 10 seconds, but one task ran for 12 minutes and shuffled far more data than the others. What is the most likely cause of this behavior?

Accepted Answer

Data skew, where one join key holds a disproportionately large number of records

Answer

The cluster has too few worker nodes to run all 200 tasks in parallel

Answer

The Delta table needs to be vacuumed to remove stale data files

Answer

Auto Loader checkpointing is slowing down the final task

Question 9

A nightly Databricks job that reads a large Delta table and performs several wide transformations fails intermittently. In the Spark UI, you notice a stage marked as 'Failed' where several tasks show status 'FAILED' with an error 'ExecutorLostFailure (executor 4 exited caused by one of the running tasks) Reason: Container killed by YARN for exceeding memory limits'. What is the most likely root cause you should investigate first?

Accepted Answer

Executors are running out of memory, likely due to data skew or excessive shuffle/spill for the wide transformations

Answer

The job cluster is running on an all-purpose cluster instead of a job cluster, causing memory contention

Answer

The Delta table has too few files, so partition pruning cannot occur

Answer

The checkpoint location for the streaming query has become corrupted

Question 10

A data engineer notices that a nightly Spark job is running much slower than usual. In the Spark UI's 'Executors' tab, they see that several executors show 'Task Time (GC Time)' where the GC Time is nearly 40% of the total task time. What is the most likely conclusion from this observation?

Accepted Answer

The executors are spending excessive time on garbage collection due to memory pressure, indicating they are under-provisioned or holding too much data in memory

Answer

The job is bottlenecked on network shuffle and needs more shuffle partitions

Answer

The data is heavily skewed across a single partition causing a straggler task

Answer

The Delta table has too many small files that need to be compacted with OPTIMIZE

Question 11

A data engineer notices that a nightly ETL job has slowed considerably. In the Spark UI, they observe that a single stage takes 90% of the total job time. Within that stage's summary metrics, the 'Shuffle Read Size' is very large (hundreds of GB) and the stage was triggered by a large groupBy aggregation across many columns. Task durations are relatively uniform (no single straggler). What is the most likely cause of the slowdown revealed by these metrics?

Accepted Answer

A wide transformation is forcing a massive data shuffle across the cluster during the aggregation

Answer

Data skew is concentrating records into one partition, creating a straggler task

Answer

The executors are running out of memory and repeatedly restarting

Answer

Too many small input files are causing excessive file-listing overhead before the stage begins

Question 12

A data engineer notices that a Spark job processing a large aggregation is running much slower than expected. In the Spark UI, they open the Stages tab and see that several tasks in the aggregation stage show large values under the 'Spill (Memory)' and 'Spill (Disk)' columns, while task durations vary only slightly. What does this most likely indicate, and what is an appropriate first action?

Accepted Answer

The executors do not have enough memory to hold shuffle/aggregation data, so it is being written to disk; increasing executor memory or reducing per-task data volume can reduce spill.

Answer

There is severe data skew, so the correct fix is to add a broadcast join hint to the aggregation.

Answer

The Delta table has too many small files, so running OPTIMIZE will eliminate the spill.

Answer

The job is failing due to a corrupted checkpoint, so the checkpoint directory must be deleted and the job restarted.

Question 13

A data engineer notices that a Databricks job has been running far longer than usual. Opening the Spark UI, they see one stage where 199 of 200 tasks completed quickly, but a single remaining task has been running for over 40 minutes with a very high 'Shuffle Read Size' compared to the other tasks. What is the most likely cause of this behavior?

Accepted Answer

Data skew is causing one partition to receive a disproportionately large share of the shuffled data

Answer

The cluster has too few executor cores to run all 200 tasks in parallel

Answer

The Delta table needs OPTIMIZE because it has too many small files

Answer

Photon is disabled, which slows down all aggregation tasks equally

Question 14

A data engineer calls .cache() on a DataFrame that is reused across several downstream transformations, but query times have not improved. They open the Spark UI to confirm whether the DataFrame is actually being cached in memory. Which tab in the Spark UI should they check to verify how much of the DataFrame is cached and where it is stored?

Accepted Answer

The Storage tab, which lists cached RDDs/DataFrames with their fraction cached and memory/disk usage

Answer

The Jobs tab, which shows the DAG of stages for each action

Answer

The Environment tab, which lists Spark configuration properties

Answer

The Executors tab, which shows per-executor task counts and shuffle metrics

Question 15

A data engineer runs OPTIMIZE with ZORDER BY on a large Delta table to improve query performance. Most dashboard queries filter on the customer_id column, but a smaller set of queries filter on region. The engineer writes: OPTIMIZE sales ZORDER BY (region, customer_id). After running it, queries filtering only on customer_id show little improvement in data skipping. What is the most likely explanation?

Accepted Answer

Z-ordering effectiveness decreases for columns listed later in the ZORDER BY clause, so customer_id benefits less than region because it is listed second.

Answer

ZORDER BY can only accept a single column, so listing two columns causes the second column to be ignored entirely.

Answer

Z-ordering only improves performance for the partition columns of the table, not for the columns named in the ZORDER BY clause.

Answer

OPTIMIZE must be run separately for each column, so a single command with two columns produces no data skipping benefit for either.

More Databricks Data Engineer Associate practice