Databricks-DE-Pro Cost and Performance Optimization — Timed 15 Questions

Question 1

A data engineering team manages a Delta Lake table that is frequently updated and queried by multiple downstream jobs. Users report that queries are increasingly slow over time. Inspection shows thousands of small JSON files in the storage location due to streaming appends. Which optimization technique should the data engineer apply to restore query performance cost-effectively?

15 minute time limit — choose an answer to begin.

Accepted Answer

Run the OPTIMIZE command regularly to compact small data files into larger, optimized blocks, followed by targeted Z-Ordering.. Delta Lake tables store data in Parquet format, not JSON. While the transaction log (_delta_log) contains JSON files, the 'small file problem' that impacts query performance and is resolved by the OPTIMIZE command refers to the data files (Parquet). Using 'JSON' in the stem is technically incorrect for a Delta Lake data storage description and would confuse a professional-level candidate.

Answer

Execute the VACUUM command daily with a zero-hour retention period to aggressively purge all old data files immediately.

Answer

Increase the driver node memory size on the accessing clusters to cache the large catalog of small file metadata.

Answer

Switch the table storage format from Delta Lake to standard Apache Parquet to bypass the transaction log overhead.

Question 2

To optimize performance for a table that is frequently filtered by a date column, what is the best strategy?

Accepted Answer

Use Liquid Clustering on the date column to eliminate the need for manual partitioning.. Databricks now recommends Liquid Clustering for all new Delta tables as it replaces manual partitioning and Z-Ordering. It provides better performance, simplifies data layout, and allows for flexible clustering keys without rewriting data. While partitioning by date (Option C) is a valid legacy technique, it is no longer the 'best strategy' in the context of modern Databricks features (DBR 13.3+).

Answer

Partition by the high-cardinality ID column to ensure unique distribution.

Answer

Partition the table by the date column to enable efficient partition pruning.

Answer

Disable all partitioning and rely on the default Z-Order optimization.

Question 3

A data engineer observes that a Delta table containing 50TB of data is experiencing slow scan performance. The table is partitioned by 'date', but many queries filter by 'region' and 'customer_id'. Which optimization strategy should the engineer implement to improve query performance with minimal overhead?

Accepted Answer

Apply Z-Ordering on 'region' and 'customer_id'.. Z-Ordering on 'region' and 'customer_id' organizes data files to colocate related information within existing partitions. By physically clustering data based on these high-cardinality columns, the Delta Lake engine can maximize data skipping during scans. While Z-Ordering requires an OPTIMIZE command to rewrite data files, it is the most effective strategy for high-cardinality columns where partitioning (Option A) would lead to the 'small file problem' and excessive metadata overhead. This technique is essential for large datasets where partitioning alone does not sufficiently narrow down the search space.

Answer

Increase the number of partitions to include 'region' and 'customer_id'.

Answer

Convert the table to a Parquet format without Delta features.

Answer

Enable liquid clustering on the existing table immediately.

Question 4

Refer to the exhibit. A data engineer is running a heavy join operation on a cluster with the provided configuration. Despite the auto-scaling being set to a maximum of 8 workers, the join operation is consistently spilling to disk. What is the most likely cause?

Accepted Answer

The cluster is using too many workers, causing overhead.. Spilling to disk during a heavy join operation typically occurs when the shuffle data exceeds the available memory per executor, often related to insufficient memory-optimized instance types, improper memory fractions, or inadequate shuffle partitions, rather than the Spark cache consuming memory (which would actually be evicted under memory pressure if properly managed).

Answer

The Spark cache is enabled, consuming memory meant for shuffles.

Answer

The auto-scaling configuration is preventing the cluster from scaling up.

Answer

The shuffle partitions are too high, causing small file overhead.

Question 5

Which THREE actions should be taken to minimize the cost of running a Databricks Job that processes large volumes of intermittent data?

Accepted Answer

Use spot instances for fault-tolerant, non-time-critical jobs.. To optimize costs for intermittent, large-volume workloads, you should: 1) Use Job clusters, which have a significantly lower DBU rate than All-Purpose (interactive) clusters. 2) Use Spot instances for workers to reduce infrastructure costs by up to 90%. 3) Enable auto-scaling so the cluster size adjusts to the data volume, ensuring you do not pay for idle resources during periods of low activity.

Answer

Always use All-Purpose clusters to ensure high availability.

Answer

Set the maximum DBU limit on the workspace to zero.

Question 6

A data engineering team runs a nightly batch job that performs a full outer join between a 5 TB fact table and a 200 GB dimension table. The job runs on a job cluster with Photon enabled. The team wants to reduce the amount of data shuffled across the network during the join. Which configuration change should they apply to the fact table DataFrame before the join?

Accepted Answer

Use `repartition` on the fact table by the join key before the join.. Repartitioning both DataFrames by the join key before the join ensures that matching keys reside on the same partition, eliminating the need for a shuffle during the join. This is particularly effective for large joins where broadcasting is not possible. The other options either do not address shuffle reduction or are impractical for large tables.

Answer

Use the `broadcast` hint on the dimension table DataFrame.

Answer

Use `repartition` on the fact table to increase the number of partitions to match the cluster's total cores.

Answer

Use `bucketBy` on the fact table and dimension table by the join key and save them as bucketed tables.

Question 7

A data engineer manages a Databricks job that runs daily on a job cluster and processes a 2 TB Delta table. The job performs a large shuffle and writes the result back to the same table using MERGE. Analysis shows the shuffle read is 1.5 TB and the shuffle write is 1.5 TB. The engineer wants to reduce shuffle overhead and improve performance. Which configuration change should the engineer make?

Accepted Answer

Enable adaptive query execution and set spark.sql.adaptive.coalescePartitions.enabled to true.. Adaptive query execution (AQE) optimizes shuffle partitions dynamically by coalescing small partitions after the shuffle stage. This reduces the number of tasks and the amount of shuffle data read and written. Enabling AQE with partition coalescing directly targets the large shuffle overhead described. Other options either increase overhead or do not address the root cause.

Answer

Increase spark.sql.shuffle.partitions from 200 to 2000.

Answer

Set spark.sql.autoBroadcastJoinThreshold to -1 to disable broadcast joins.

Answer

Increase the cluster size by adding more worker nodes with more memory.

Question 8

A data engineer is using Databricks SQL to query a large Delta table that is frequently updated with small appends. The engineer notices that queries are slow and the table has many small files. The engineer wants to improve query performance and reduce storage costs without changing the table schema. Which action should the engineer take?

Accepted Answer

Run OPTIMIZE on the table to compact small files.. OPTIMIZE compacts small files into larger ones, which improves query performance by reducing the number of files scanned and can reduce storage costs by eliminating metadata overhead. It is the direct solution for a Delta table with many small files. Other options either do not address the issue or are not applicable to existing files.

Answer

Enable auto compaction on the table.

Answer

Repartition the table by a high-cardinality column.

Answer

Run VACUUM on the table to remove old files.

Question 9

A data engineer is optimizing a Databricks SQL warehouse that runs a mix of short ad-hoc queries and long-running analytical queries. The warehouse is currently configured with a fixed size and auto-stop after 10 minutes. Users report that short queries sometimes wait for cluster startup, and long queries compete for resources, causing delays. The engineer wants to improve performance and reduce cost. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Enable serverless SQL warehouses to reduce startup time and improve elasticity.. Serverless SQL warehouses and intelligent workload management with auto-scaling both provide elasticity and reduce startup latency. Serverless eliminates cluster startup time and scales to zero, while intelligent workload management dynamically allocates resources and queues queries to prevent contention. Together, they improve performance for mixed workloads and lower cost by avoiding over-provisioning and idle time.

Answer

Increase the warehouse size to accommodate peak concurrency.

Answer

Configure the warehouse to use Photon and increase the auto-stop timeout to 60 minutes.

Answer

Create separate warehouses for short and long queries, with different sizes and auto-stop settings.

Question 10

A data engineer is analyzing a Databricks SQL warehouse that runs a dashboard with multiple queries. The warehouse is configured with auto-stop after 10 minutes of inactivity. The dashboard is accessed sporadically throughout the day, with periods of high activity and long idle times. The engineer wants to reduce cost without impacting user experience. Which action should the engineer take?

Accepted Answer

Enable the serverless SQL warehouse option.. Serverless SQL warehouses provide instant startup and auto-scaling, eliminating idle time costs and cold starts. They bill only for query execution time, making them ideal for sporadic workloads. This directly reduces cost while maintaining or improving user experience, unlike adjusting auto-stop thresholds or cluster size, which involve trade-offs.

Answer

Increase the auto-stop threshold to 30 minutes to avoid cold starts.

Answer

Configure the warehouse to use a smaller cluster size.

Answer

Decrease the auto-stop threshold to 5 minutes to save on idle time.

Question 11

A data engineer is optimizing a Databricks job that reads from a large Parquet table stored in cloud object storage. The job performs a full scan of the table for each run, and the engineer wants to reduce the amount of data read and improve performance. The engineer decides to convert the table to Delta Lake. Which two actions should the engineer take to further reduce data read and improve performance? (Choose two.)

Accepted Answer

Partition the Delta table by a low-cardinality column used in filter predicates.. Partitioning by a low-cardinality filter column and Z-ORDER clustering on a high-cardinality filter column both enable Delta Lake's data skipping, reducing the amount of data read during queries. These are complementary techniques: partitioning for coarse-grained skipping and Z-ORDER for fine-grained skipping. The other options either do not reduce read volume or are redundant.

Answer

Increase the spark.sql.files.maxPartitionBytes to read larger chunks.

Answer

Set the table property delta.autoOptimize.optimizeWrite to true.

Answer

Convert the table to a Delta table and run OPTIMIZE with Z-ORDER on a filter column.

Question 12

A data engineer is tuning a large Spark SQL join on Databricks that processes 20 TB of fact data joined to a 50 GB dimension table. The job uses a broadcast hash join because the dimension table is under the default auto-broadcast threshold, but the driver is running out of memory and the job is slow. The engineer wants to improve performance and stability without increasing driver size. Which two actions should the data engineer take? (Choose two.)

Accepted Answer

Repartition the fact table by the join key before the join and ensure the dimension table is also repartitioned by the join key.. The root cause is attempting to broadcast a 50 GB dimension, which forces the driver to collect and distribute a large dataset, leading to OOM and slow execution. Disabling broadcast for this join by lowering the auto-broadcast threshold allows Spark to use a distributed sort-merge join. Repartitioning both sides by the join key co-locates matching keys and further improves the join by balancing work across executors. Together these actions remove driver memory pressure and improve performance without increasing driver size.

Answer

Increase spark.sql.shuffle.partitions to a very high value such as 10000 to spread the join across more tasks.

Answer

Enable adaptive query execution (AQE) and set spark.sql.adaptive.autoBroadcastJoinThreshold to a value larger than 50 GB.

Answer

Cache the 50 GB dimension table in memory on the executors using CACHE TABLE and rely on the broadcast join to use the cached data.

Question 13

A data engineer is configuring a Databricks job cluster for a nightly ETL job that processes data in a single pass. The job reads from cloud storage, transforms the data, and writes to a Delta table. The engineer wants to minimize cost while ensuring the job completes within its SLA. Which cluster configuration is most appropriate?

Accepted Answer

Use a job cluster with a fixed number of workers based on the expected workload.. Job clusters are designed for automated workloads and are automatically terminated after job completion, eliminating idle costs. A fixed number of workers sized appropriately provides predictable performance without the overhead of autoscaling. Other options either use unsuitable cluster types or introduce unnecessary cost and complexity.

Answer

Use an all-purpose cluster with autoscaling and a long auto-stop timeout.

Answer

Use a SQL warehouse with auto-scaling and Photon enabled.

Answer

Use a job cluster with autoscaling from 2 to 20 workers and a short auto-stop timeout.

Question 14

A data engineer is tasked with reducing compute costs for an interactive SQL analytics workspace that runs sporadic, highly unpredictable queries. The jobs experience cold start delays and occasional out-of-memory errors due to sudden concurrency spikes. Which TWO strategies should the engineer implement to balance cost efficiency and performance?

Accepted Answer

Migrate interactive SQL workloads to Databricks Serverless Compute to dynamically scale resources and eliminate idle billing.. Implementing serverless compute eliminates idle cluster costs while absorbing concurrency spikes instantly through elastic scaling. Enabling Photon on standard clusters provides vectorized query execution that speeds up scans and aggregations, reducing runtime and lowering total execution costs. Together, these choices optimize both resource utilization and user concurrency responsiveness.

Answer

Configure single-node clusters with maximum autoscaling limits to handle unpredictable concurrency peaks without cluster management overhead.

Answer

Provision pools of pre-warmed idle driver nodes to ensure zero-second startup latency for all analytical queries.

Answer

Disable automatic cluster termination and keep all worker nodes running 24/7 to guarantee immediate resource availability.

Question 15

A data engineer is designing an ETL pipeline processing high-frequency streaming data into Delta tables on Databricks. The pipeline experiences frequent small file creation and high metadata overhead, degrading query performance. Which optimization technique should the engineer implement to resolve this issue?

Accepted Answer

Enable spark.databricks.delta.optimizeWrite.enabled and spark.databricks.delta.autoCompact.enabled.. Implementing automated compaction via optimized write and Auto Compact merges small files during writes, maintaining optimal file sizes around 128MB. This technique is critical for streaming workloads because frequent micro-batches naturally produce excessive tiny files that severely degrade both metadata listing times and subsequent read scan performance across production environments.

Answer

Increase the Delta table version retention period to keep historic snapshots longer.

Answer

Enable predictive optimization to automatically manage compaction and vacuum operations.

Answer

Switch the table format from Delta to Apache Parquet to leverage native cloud storage indexing.