Databricks-DE-Pro Cost and Performance Optimization Practice Test 2 — 15 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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.

Question 6

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?

Accepted Answer

Utilizing Spot instances for non-critical development and test clusters.. Choosing the right instance type and using Spot/Preemptible instances is a foundational cost-optimization strategy. Spot instances leverage unused cloud capacity at significantly lower prices compared to On-Demand instances. While they can be reclaimed by the cloud provider, using them for development, testing, or fault-tolerant batch jobs is a highly effective way to slash cloud infrastructure spending without altering the underlying code or business logic of the data pipeline.

Answer

Switching to a more expensive instance type to complete the job faster.

Answer

Enabling Photon acceleration for all development workloads.

Answer

Increasing the number of workers in the cluster to handle more data in parallel.

Question 7

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

Accepted Answer

Broadcast the smaller table in a join to avoid shuffling large datasets.. Optimizing joins is critical to performance as they are often the most resource-intensive operations in Spark. Techniques like broadcasting small tables, using bucketing to avoid shuffles, and ensuring data is properly partitioned help the Spark engine execute joins efficiently. By minimizing the amount of data moved across the network (shuffling) and maximizing local processing, jobs complete faster and consume fewer compute resources, leading to a more performant and cost-effective overall data architecture.

Answer

Increase the number of partitions to the maximum possible value to ensure maximum parallelism.

Answer

Use Cross Join for every join operation to ensure no data rows are missed.

Question 8

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?

Accepted Answer

Implement Budget Policies in the Databricks account console to set alerts on usage.. Databricks provides budget policies and administrative controls to manage costs. Setting up budget alerts via the Databricks account console allows administrators to define spending limits and receive notifications when consumption approaches these levels. This proactive monitoring is essential for governance, ensuring that cost overruns are identified early and addressed by the relevant teams, preventing unexpected bills and promoting responsible use of compute resources across the organization.

Answer

Enable auto-termination on every individual query executed.

Answer

Use the Databricks Billing System to set a hard limit that automatically shuts down the account.

Answer

Manually check the Spark UI after every query to calculate the cost.

Question 9

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

Accepted Answer

It automatically adjusts the data layout based on changing query patterns without manual maintenance.. Liquid Clustering provides dynamic data layout optimization that automatically adapts to query patterns without requiring manual maintenance or complex partition strategies. Unlike static partitioning, which can lead to data skew or file management issues, Liquid Clustering reorganizes data based on actual usage, significantly reducing the burden on engineers. It is a more flexible, modern solution that simplifies performance tuning while ensuring that read queries remain fast across varying data access patterns.

Answer

It eliminates the need for any clustering keys by automatically indexing every column.

Answer

It forces all data to be stored on a single partition to maximize local cache hits.

Answer

It replaces Delta Lake's file management, making the OPTIMIZE command unnecessary.

Question 10

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

Accepted Answer

Use broadcast joins for small tables to keep the join operation local.. Shuffling is the most expensive operation in Spark because it involves network I/O and data serialization across worker nodes. Reducing shuffles is achieved by minimizing the volume of data shuffled (e.g., using broadcast joins), pre-partitioning data (e.g., using bucketing), or performing operations that keep data local to the worker. These strategies ensure that data is processed in place, dramatically reducing execution time and cluster resource usage for complex data transformations.

Answer

Increase the number of shuffle partitions significantly for small datasets.

Answer

Always use 'repartition()' before any operation to ensure even data distribution.

Question 11

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?

Accepted Answer

Store the data in Delta format on cloud object storage.. Storing data in Delta format on cloud object storage (like S3 or ADLS) provides the best cost-to-performance ratio. Databricks SQL can query this data directly without needing to load it into a proprietary data warehouse. By using object storage, you only pay for the storage used, and because the data is rarely updated, the overhead of maintenance is minimal, making it the ideal cost-optimized storage pattern.

Answer

Load all data into a high-performance in-memory database.

Answer

Replicate the data into multiple cloud regions for higher availability.

Answer

Convert the table to a legacy Hive format for better compatibility.

Question 12

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?

Accepted Answer

Use the 'salting' technique by adding a random prefix to the skewed key.. Data skew occurs when one key contains a disproportionate amount of data, causing one task to process significantly more than others. Salting (adding a random prefix to the key) breaks this concentration, distributing the data evenly across the cluster. This prevents individual tasks from crashing due to memory limits, allowing the job to complete successfully and efficiently by balancing the workload across all available workers.

Answer

Increase the executor memory size for all workers in the cluster.

Answer

Remove the group-by clause and process the data using a UDF.

Answer

Reduce the number of executors to force the job to run sequentially.

Question 13

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

Accepted Answer

spark.databricks.delta.optimizeWrite.enabled. The `spark.databricks.delta.optimizeWrite.enabled` property is essential for write-time optimization. It dynamically groups data before writing to storage, ensuring that the created files are of optimal size. This prevents the small-file problem from occurring in the first place, reducing the need for post-write maintenance and improving read query performance significantly. It is a proactive performance optimization that is highly recommended for high-frequency write workloads.

Answer

spark.sql.shuffle.partitions

Answer

spark.databricks.io.cache.enabled

Answer

spark.sql.autoBroadcastJoinThreshold

Question 14

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

Accepted Answer

The total execution time and breakdown of the query stages.. The 'Total Time' metric is decomposed into wait time, compilation time, and execution time. Identifying the execution time bottleneck helps determine if the issue is compute-bound, I/O-bound, or due to slow data retrieval. This information is vital for deciding whether to optimize the query code, adjust partitioning, or scale the warehouse, ensuring that engineering efforts are targeted at the right performance issues to maximize impact.

Answer

The number of users logged into the workspace at the time.

Answer

The color of the query status light in the UI.

Answer

The name of the user who submitted the query.

Question 15

A data engineering team experiences massive compute waste because interactive development notebooks are frequently left running overnight by engineers. As a Databricks administrator, which configuration should you implement at the cluster policy level to automatically mitigate this financial exposure without disrupting ongoing development work?

Accepted Answer

Set a mandatory auto_termination_minutes rule in the cluster policy enforcing a maximum idle timeout threshold for all user-created clusters.. Enabling automatic cluster termination based on an idle timeout is the most direct policy enforcement mechanism to eliminate compute waste from forgotten interactive sessions. This setting continuously monitors the execution state of the driver and worker nodes, shutting down the cluster safely once the specified threshold is crossed, which drastically reduces cloud infrastructure spending while preserving user autonomy during active hours.

Answer

Configure a maximum worker node limit constraint within the compute policy that prevents scaling beyond two nodes.

Answer

Enforce a strict maximum instance lifetime limit that terminates the cluster regardless of whether interactive queries or jobs are running.

Answer

Disable the ability for standard users to attach notebooks to interactive clusters, forcing all execution through scheduled jobs.