Databricks-DE-Pro Cost and Performance Optimization — 10 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?

Choose an answer to begin — your selection is scored in the full session.

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.