Databricks-DE-Pro Cost and Performance Optimization — All Questions With Answers

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?

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.

Question 16

An enterprise data team runs a large nightly batch job using a standard all-purpose cluster. The job frequently fails due to cloud provider spot instance pre-emptions and takes over four hours to complete. How should the engineer refactor this architecture for maximum cost efficiency and reliability?

Accepted Answer

Convert the workload to use a Databricks Job cluster configured with spot instances and automatic fallback to on-demand.. Migrating the workload from an all-purpose interactive cluster to a Databricks Job cluster running on spot instances with an automatic fallback mechanism ensures cost-effective batch execution. Job clusters consume lower DBU rates than all-purpose clusters, and spot instances drastically reduce infrastructure costs while fallback guarantees completion despite cloud provider interruptions.

Answer

Provision a larger all-purpose cluster with double the worker nodes to brute-force execution speed.

Answer

Upgrade the cloud provider virtual machine family to the latest generation without changing cluster types.

Answer

Increase the Apache Spark executor memory fraction and decrease shuffle partition counts.

Question 17

Refer to the exhibit. A data engineer creates an instance pool to reduce cluster startup times for development teams. However, finance reports indicate unexpected cloud infrastructure charges. Based on the configuration shown in the exhibit, what is the primary driver of these unexpected costs?

Accepted Answer

The min_idle_instances setting maintains running virtual machines continuously, incurring persistent infrastructure costs.. Instance pools maintain idle virtual machines ready for immediate attachment to clusters, drastically reducing startup latency. However, setting min_idle_instances to 2 ensures that two instances are running constantly even when no clusters are active, incurring continuous cloud provider infrastructure charges and idle DBU fees that accumulate rapidly.

Answer

The max_capacity limit is set too low, forcing teams to provision multiple competing instance pools.

Answer

Instance pools do not support Spot instances, forcing all pool-backed clusters to run expensive on-demand VMs.

Answer

The selected node_type_id is optimized for storage rather than compute, causing inflated licensing surcharges.

Question 18

A data engineer is optimizing a Delta Lake table that experiences high read latency due to many small files. Which command should be executed to physically reorganize the data layout to improve query performance?

Accepted Answer

OPTIMIZE table_name. The OPTIMIZE command is the primary tool for compacting small files into larger, efficiently sized files in Delta Lake. This improves read performance by reducing metadata overhead and enabling efficient data skipping. Compaction is a critical maintenance task for tables with frequent streaming or small-batch writes, ensuring that downstream analytical queries remain performant and cost-effective by reducing the number of I/O operations required during data retrieval.

Answer

VACUUM table_name

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS

Answer

REORG TABLE table_name APPLY (PURGE)

Question 19

A Databricks SQL warehouse is experiencing high costs due to idle resources. Which TWO configurations should be implemented to effectively manage and reduce warehouse costs?

Accepted Answer

Set the auto-stop duration to a very low value, such as 1 minute, for serverless SQL warehouses.. Auto-stop and serverless scaling are the primary levers for cost control in Databricks SQL. Auto-stop terminates warehouses when no queries are running, preventing billing for idle time. Serverless SQL warehouses provide faster startup times and more granular scaling, allowing users to configure aggressive auto-stop durations without compromising user experience. Together, these configurations ensure that compute resources are only consumed when active query processing is required, directly minimizing unnecessary cloud spending.

Answer

Enable multi-cluster load balancing to ensure all queries are executed on the largest instance type.

Answer

Configure SQL warehouse scaling to use the maximum cluster size at all times to avoid resizing overhead.

Answer

Disable the query cache to ensure all results are freshly computed for accurate billing metrics.

Question 20

Refer to the exhibit. An engineer has configured the cluster settings as shown. What is the expected impact on the Delta table's performance and write operations?

Accepted Answer

Read performance will improve over time as files are automatically compacted and optimized for downstream queries.. The settings `optimizeWrite` and `autoCompact` are crucial for maintaining healthy Delta tables. `optimizeWrite` rearranges data for optimal file size before writing, while `autoCompact` automatically merges small files after writes. These configurations reduce the need for manual maintenance, lower storage costs, and ensure read queries perform optimally by preventing file fragmentation. This proactive approach is standard practice for high-performance Delta Lake implementations that minimize manual intervention.

Answer

Write latency will increase, but read performance will remain unaffected.

Answer

Storage costs will increase significantly due to the creation of temporary duplicate data files.

Answer

The cluster will fail to start because these configurations are deprecated in recent Databricks runtimes.

Databricks-DE-Pro Cost and Performance Optimization — All Questions With Answers

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

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?

Exhibit

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

Refer to the exhibit. A data engineer creates an instance pool to reduce cluster startup times for development teams. However, finance reports indicate unexpected cloud infrastructure charges. Based on the configuration shown in the exhibit, what is the primary driver of these unexpected costs?

Exhibit

A data engineer is optimizing a Delta Lake table that experiences high read latency due to many small files. Which command should be executed to physically reorganize the data layout to improve query performance?

A Databricks SQL warehouse is experiencing high costs due to idle resources. Which TWO configurations should be implemented to effectively manage and reduce warehouse costs?

Refer to the exhibit. An engineer has configured the cluster settings as shown. What is the expected impact on the Delta table's performance and write operations?

Exhibit

A data engineer is designing a pipeline and notices that the cost of processing is unexpectedly high during development. Which action provides the most immediate cost reduction when using Databricks?

Which THREE techniques are recommended for improving the performance of Spark SQL joins on large Databricks tables?

An organization wants to monitor and limit the spend of their Databricks SQL warehouses. Which feature is most appropriate for setting alerts when costs exceed a certain threshold?

A data team is using Liquid Clustering on a Delta table. How does this feature improve performance compared to traditional Z-Ordering or Partitioning?

Which THREE actions can help reduce the 'shuffle' operations in a Spark job?

A team has a large Delta table that is rarely updated. What is the most cost-effective way to store this data while maintaining the ability to query it with Databricks SQL?

A Spark job is failing with an OutOfMemoryError (OOM) during a group-by operation on a skewed key. What is the most effective way to resolve this?

Which property should be configured to allow Databricks to automatically optimize the size of files during write operations in Delta Lake?

Which metric should a data engineer prioritize when investigating a slow-running query in the Databricks SQL query history?

Exhibit

A team is building a streaming pipeline that processes millions of events per second. They are using Structured Streaming with a Delta Lake sink. What is the most effective way to optimize the performance and cost of this write-heavy workload?

A data scientist reports that their notebook takes 20 minutes to initialize, even when the cluster is running. What is the most likely reason for this high initialization time?

Databricks-DE-Pro Cost and Performance Optimization — All Questions With Answers

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

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?

Exhibit

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

Refer to the exhibit. A data engineer creates an instance pool to reduce cluster startup times for development teams. However, finance reports indicate unexpected cloud infrastructure charges. Based on the configuration shown in the exhibit, what is the primary driver of these unexpected costs?

Exhibit

A data engineer is optimizing a Delta Lake table that experiences high read latency due to many small files. Which command should be executed to physically reorganize the data layout to improve query performance?

A Databricks SQL warehouse is experiencing high costs due to idle resources. Which TWO configurations should be implemented to effectively manage and reduce warehouse costs?

Refer to the exhibit. An engineer has configured the cluster settings as shown. What is the expected impact on the Delta table's performance and write operations?

Exhibit

A data engineer is designing a pipeline and notices that the cost of processing is unexpectedly high during development. Which action provides the most immediate cost reduction when using Databricks?

Which THREE techniques are recommended for improving the performance of Spark SQL joins on large Databricks tables?

An organization wants to monitor and limit the spend of their Databricks SQL warehouses. Which feature is most appropriate for setting alerts when costs exceed a certain threshold?

A data team is using Liquid Clustering on a Delta table. How does this feature improve performance compared to traditional Z-Ordering or Partitioning?

Which THREE actions can help reduce the 'shuffle' operations in a Spark job?

A team has a large Delta table that is rarely updated. What is the most cost-effective way to store this data while maintaining the ability to query it with Databricks SQL?

A Spark job is failing with an OutOfMemoryError (OOM) during a group-by operation on a skewed key. What is the most effective way to resolve this?

Which property should be configured to allow Databricks to automatically optimize the size of files during write operations in Delta Lake?

Which metric should a data engineer prioritize when investigating a slow-running query in the Databricks SQL query history?

Exhibit

A team is building a streaming pipeline that processes millions of events per second. They are using Structured Streaming with a Delta Lake sink. What is the most effective way to optimize the performance and cost of this write-heavy workload?

A data scientist reports that their notebook takes 20 minutes to initialize, even when the cluster is running. What is the most likely reason for this high initialization time?