Courseiva

CCNA Cost and Performance Optimization Questions

33 questions · Cost and Performance Optimization · All types, answers revealed

Practice these questionsDomain overviewAll questions
1
Multi-Selecthard

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?

Select 2 answers
A.Configure single-node clusters with maximum autoscaling limits to handle unpredictable concurrency peaks without cluster management overhead.
B.Migrate interactive SQL workloads to Databricks Serverless Compute to dynamically scale resources and eliminate idle billing.
C.Provision pools of pre-warmed idle driver nodes to ensure zero-second startup latency for all analytical queries.
D.Enable Photon acceleration on clusters executing heavy relational scans and complex analytical joins.
E.Disable automatic cluster termination and keep all worker nodes running 24/7 to guarantee immediate resource availability.
AnswersB, D

Serverless SQL warehouses scale automatically with query concurrency and bill only while active, directly addressing the sporadic, unpredictable workload and idle-cost constraint. Cold starts and out-of-memory errors from concurrency spikes are absorbed by dynamic resource provisioning rather than fixed cluster sizing.

Why this answer

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.

Exam trap

Candidates often choose manual cluster scaling policies or standard instance types, failing to recognize that serverless compute and Photon are designed for unpredictable, high-concurrency analytical workloads.

Practice this question →
2
MCQmedium

A data engineering team runs a nightly batch job on a Databricks job cluster. The job reads a large Parquet dataset, performs transformations, and writes the result to a Delta table. The cluster is configured with autoscaling from 4 to 16 workers and uses the default Spark configuration. The team observes that the job runs for 2 hours, but the cluster's CPU utilization is only around 30% throughout the run. They want to reduce cost without increasing runtime. Which action is most likely to achieve this?

A.Cache the Parquet dataset in memory before performing transformations.
B.Reduce the number of workers in the cluster to match the observed CPU utilization.
C.Switch the job to use Photon and increase the driver node size.
D.Enable auto-optimize shuffle and set spark.sql.shuffle.partitions to a higher value.
AnswerB

Since CPU utilization is low, the cluster is over-provisioned relative to the workload's actual parallelism. Reducing the maximum worker count lowers cost while likely maintaining similar runtime, because the job is not CPU-bound and additional workers are idle. This directly aligns compute resources with the workload's needs without changing the job logic or risking performance regression.

Why this answer

The cluster is over-provisioned because CPU utilization is low, indicating the job is not CPU-bound. Reducing the number of workers aligns compute with actual demand and lowers cost without increasing runtime. Other options either add overhead or target the wrong bottleneck, such as shuffle partitioning or driver size, which are not indicated by uniformly low CPU usage.

Exam trap

The trap here is assuming that more workers always improve performance, when in fact low CPU utilization signals over-provisioning and reducing workers is the cost-effective fix.

Practice this question →
3
MCQmedium

A data engineer manages a Delta table that is used for both batch analytics and frequent small updates from a streaming job. The table is not partitioned, and the engineer notices that queries are slowing down as the table grows. The engineer wants to improve query performance without changing the table schema or partitioning strategy. Which action should the engineer take?

A.Increase the cluster size used for all queries against the table.
B.Convert the table to a Parquet table and use partitioning by date.
C.Run OPTIMIZE with Z-ORDER on a commonly filtered column.
D.Enable change data feed on the table and run VACUUM with a shorter retention period.
AnswerC

Z-ORDERing during OPTIMIZE co-locates related data in the same files, improving data skipping for queries that filter on the Z-ORDERed column. This reduces the amount of data scanned and speeds up queries, especially for large tables with frequent updates. It does not require schema changes or partitioning, and it can be scheduled regularly to maintain performance as new data arrives.

Why this answer

OPTIMIZE with Z-ORDER improves data skipping by clustering similar data together, which directly speeds up queries that filter on the Z-ORDERed column. It works without schema changes or partitioning and is compatible with frequent updates. Other options either do not address query performance or introduce trade-offs that harm Delta Lake capabilities.

Exam trap

The trap here is thinking that VACUUM or adding compute solves query slowdowns, when the real fix is optimizing data layout with Z-ORDER to enable efficient data skipping.

Practice this question →
4
MCQhard

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?

A.The max_capacity limit is set too low, forcing teams to provision multiple competing instance pools.
B.Instance pools do not support Spot instances, forcing all pool-backed clusters to run expensive on-demand VMs.
C.The min_idle_instances setting maintains running virtual machines continuously, incurring persistent infrastructure costs.
D.The selected node_type_id is optimized for storage rather than compute, causing inflated licensing surcharges.
AnswerC

min_idle_instances keeps that number of virtual machines powered on at all times, even when no clusters are running. Those idle instances bill continuously for cloud infrastructure, which is the persistent cost driver behind the unexpected charges shown in the exhibit.

Why this answer

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.

Exam trap

Candidates often blame 'cluster size' or 'DBU usage' for costs, missing the specific detail that 'min_idle_instances' forces the cloud provider to keep virtual machines running 24/7.

Practice this question →
5
MCQhard

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

A.It eliminates the need for any clustering keys by automatically indexing every column.
B.It automatically adjusts the data layout based on changing query patterns without manual maintenance.
C.It forces all data to be stored on a single partition to maximize local cache hits.
D.It replaces Delta Lake's file management, making the OPTIMIZE command unnecessary.
AnswerB

Liquid Clustering is designed to be adaptive. By monitoring how data is accessed, it automatically reclusters data to optimize for the most frequent queries. This removes the need for manual Z-Ordering or re-partitioning, ensuring that the table remains performant over time as query patterns evolve in production environments.

Why this answer

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.

Exam trap

Candidates frequently confuse Liquid Clustering with traditional partitioning, incorrectly believing it requires manual maintenance or specific column definitions to be set up by the user for every query pattern change.

Practice this question →
6
MCQmedium

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?

A.Increase the Delta table version retention period to keep historic snapshots longer.
B.Enable predictive optimization to automatically manage compaction and vacuum operations.
C.Enable spark.databricks.delta.optimizeWrite.enabled and spark.databricks.delta.autoCompact.enabled.
D.Switch the table format from Delta to Apache Parquet to leverage native cloud storage indexing.
AnswerC

Enabling optimized writes and Auto Compact forces Spark to shuffle data to achieve well-sized files prior to writing and automatically triggers a compaction pass when small files are detected. This directly targets the root cause of metadata bottlenecks in high-frequency streaming architectures.

Why this answer

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.

Exam trap

Many candidates select periodic manual OPTIMIZE commands rather than automatic write-time solutions, missing the proactive nature required for high-frequency streaming workloads.

Practice this question →
7
MCQeasy

A data engineer is reviewing a Databricks job that runs on a job cluster and reads a large Delta table. The engineer notices that the job takes a long time to start because the cluster is provisioned from scratch each time. The engineer wants to reduce the startup time without increasing cost significantly. Which action should the engineer take?

A.Use a larger driver node to speed up initialization.
B.Enable cluster pools and configure the job to use a pool.
C.Set the cluster's Spark configuration to enable adaptive query execution.
D.Increase the cluster's autoscaling maximum to allow more nodes.
AnswerB

Cluster pools maintain a set of idle, ready-to-use instances that can be quickly allocated to clusters. Using a pool for the job cluster reduces startup time because the instances are already provisioned. It can also reduce cost by sharing idle instances across multiple clusters, though there is a small cost for maintaining the pool.

Why this answer

Cluster pools keep a set of idle instances ready, so job clusters can start almost instantly by borrowing from the pool. This reduces startup time significantly. The other options either do not address startup time or could increase cost without solving the problem.

Exam trap

The trap here is confusing cluster startup time with job execution time; features like AQE or larger drivers improve execution but not provisioning.

Practice this question →
8
MCQmedium

Your organization runs numerous batch data engineering pipelines using standard Databricks jobs. Finance reports indicate that compute costs are inflated due to cluster startup times and rigid over-provisioning. Which optimization approach provides the best balance of cost savings and execution reliability for scheduled production batch jobs?

A.Keep using interactive all-purpose clusters but implement notebook-level python scripts to manually stop clusters when jobs finish.
B.Refactor all batch jobs to execute exclusively on Serverless SQL Warehouses regardless of workload dependencies.
C.Migrate scheduled pipeline execution to Databricks Jobs compute leveraging job clusters configured with spot instances for workers.
D.Reduce the executor memory allocation below default recommendations to force Spark to spill data to disk more frequently.
AnswerC

Databricks Jobs compute bills at a significantly lower rate than all-purpose compute. Configuring job clusters with spot instance workers leverages spare cloud capacity at steep discounts, while job orchestration automatically provisions and terminates clusters per run.

Why this answer

Transitioning scheduled production batch pipelines from interactive all-purpose clusters to Databricks Jobs compute with spot instance integration delivers massive financial savings. Jobs compute provides lower compute unit pricing compared to interactive workspaces, while spot instances discount infrastructure further, and robust retry mechanisms handle any cloud-level pre-emptions gracefully.

Exam trap

Candidates often suggest interactive clusters to avoid configuration complexity. They ignore that interactive clusters bill at higher rates and stay running, failing to leverage the cost-effective nature of job-specific compute.

Practice this question →
9
Multi-Selecthard

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

Select 3 answers
A.Use broadcast joins for small tables to keep the join operation local.
B.Increase the number of shuffle partitions significantly for small datasets.
C.Bucket tables to ensure co-location of data for joins and aggregations.
D.Always use 'repartition()' before any operation to ensure even data distribution.
E.Filter data before performing expensive transformations or joins.
AnswersA, C, E

Broadcasting small tables sends the data to all nodes, allowing the join to happen locally. This prevents the large table from being shuffled across the network, which is the primary driver of performance degradation in joins. It is a highly effective way to eliminate unnecessary network traffic.

Why this answer

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.

Exam trap

Candidates often suggest increasing cluster resources (like memory or CPU) as a fix for shuffle issues, rather than focusing on architectural changes like broadcasting or bucketing to avoid the shuffle entirely.

Practice this question →
10
MCQmedium

Refer to the exhibit. An administrator reviews the cluster configuration JSON for an all-purpose interactive development cluster used by data engineers. Based on Databricks cost and performance optimization best practices, which specific parameter in this configuration represents the highest risk for unnecessary financial expenditure?

A.The spark_version string specifies an ML runtime version containing unnecessary packages for general data engineering workloads.
B.The node_type_id specifies i3.xlarge storage-optimized instances which are entirely unsupported for running standard Delta Lake queries.
C.The auto_termination_minutes parameter is set to 120, allowing interactive compute to remain idle and bill at all-purpose rates for two full hours.
D.The autoscale max_workers limit is capped at 20, which will instantly crash any query exceeding ten gigabytes of data volume.
AnswerC

Idle interactive compute bills at the higher all-purpose DBU rate, so a 120-minute auto_termination window leaves two hours of chargeable inactivity per session. Shortening it to 10–30 minutes directly satisfies the stem's cost-optimisation constraint, since idle time, not cluster size, drives the unnecessary expenditure here.

Why this answer

The exhibit shows an all-purpose cluster configured with a 120-minute auto-termination timeout and heavy ML-runtime packages on storage-optimized instance types. Setting auto-termination to 120 minutes (2 hours) of inactivity causes extreme financial waste when developers walk away. Interactive all-purpose clusters should typically timeout after 30 to 60 minutes of inactivity to optimize cost recovery.

Exam trap

Candidates often focus on instance types or library versions. They fail to notice that an idle cluster running for two hours is a massive, avoidable cost leak in an interactive development environment.

Practice this question →
11
MCQhard

A data engineer maintains a Delta Lake table that stores 5 years of order data. Analysts frequently query the most recent 90 days, but compliance requires that older data remain queryable. The table is currently partitioned by order_date and has 200,000 small files because data arrives continuously via Structured Streaming. Queries on the last 90 days are slow and expensive. Which combination of actions will most effectively reduce query cost and improve performance for the recent-data queries?

A.Run OPTIMIZE with Z-ORDER BY order_date on the entire table and set delta.autoOptimize.optimizeWrite to true.
B.Enable Delta Lake column mapping and rewrite the table with generated columns for order month.
C.Run OPTIMIZE on the partitions covering the last 90 days to compact small files, and configure the streaming writer with a longer trigger interval and optimized writes to produce larger files.
D.Run OPTIMIZE on partitions covering the last 90 days, then use Z-ORDER BY order_date on those partitions, and enable optimized writes for the streaming ingestion.
AnswerC

Compacting the hot partitions reduces the number of files that queries must open, which directly lowers scan cost and improves performance for recent-data queries. Increasing the streaming trigger interval and enabling optimized writes reduces the rate of small-file creation going forward. This targets both the existing small-file problem and its root cause while leaving cold historical data untouched, which is the most cost-effective strategy.

Why this answer

The dominant problem is the 200,000 small files created by continuous streaming ingestion. Queries on the last 90 days must open many tiny files, which drives up overhead and cost. Compacting only the hot partitions with OPTIMIZE reduces file count where it matters most, and adjusting the streaming writer to produce larger files prevents the problem from recurring.

Z-ORDER on a partition key that is already the partition column adds little value, so focusing on compaction and ingestion file sizing is the correct optimization.

Exam trap

The trap here is reaching for Z-ORDER on the partition column, which is already used for partitioning and therefore provides minimal additional data-skipping benefit.

Practice this question →
12
Multi-Selectmedium

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

Select 3 answers
A.Broadcast the smaller table in a join to avoid shuffling large datasets.
B.Increase the number of partitions to the maximum possible value to ensure maximum parallelism.
C.Bucket the tables on the join key to enable sort-merge joins without shuffling.
D.Use Cross Join for every join operation to ensure no data rows are missed.
E.Filter data as early as possible in the pipeline before performing the join.
AnswersA, C, E

Broadcasting sends a copy of the smaller table to every worker node, allowing the join to be performed locally without shuffling the larger table. This eliminates the expensive network I/O associated with data shuffling, which is the most common cause of performance degradation in large-scale distributed join operations.

Why this answer

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.

Exam trap

Candidates often confuse shuffle reduction techniques, choosing generic caching or increasing partition counts without realizing that broadcasting small tables and bucketing are the direct structural methods to eliminate shuffle overhead during joins.

Practice this question →
13
MCQmedium

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?

A.The cluster is using spot instances.
B.The notebook is installing many libraries using %pip install.
C.The cluster has too many workers allocated.
D.The user is using the wrong Databricks Runtime version.
AnswerB

Notebook-scoped library installations trigger a package resolution and installation process every time the Spark context starts or resets. For large sets of dependencies, this can take a long time. Moving these to cluster-level libraries ensures they are pre-installed on every node, which drastically reduces the notebook's initialization time.

Why this answer

High initialization time in a running cluster is frequently caused by excessive libraries being installed at the notebook level via '%pip install'. Every time the notebook is attached or the context is refreshed, these libraries must be re-resolved and installed, which adds significant overhead. Pre-installing these libraries at the cluster level via 'cluster-scoped libraries' allows them to be available immediately upon startup, eliminating this redundant installation phase and significantly speeding up the initialization process.

Exam trap

Candidates often blame cluster startup times or network latency, forgetting that executing '%pip install' dynamically inside a notebook triggers redundant library resolution and installation loops.

Practice this question →
14
MCQeasy

A data engineer notices that a Databricks SQL warehouse used for executive dashboards runs 24/7 but is only actively queried during business hours. The warehouse is a Pro-sized warehouse with auto-stop set to 10 minutes. The team wants to reduce cost without affecting dashboard availability during business hours. Which action should the data engineer take?

A.Enable serverless compute for the warehouse and set the auto-stop to 1 minute.
B.Create a schedule to stop the warehouse outside business hours and start it before the business day begins.
C.Add a second warehouse for the executive dashboards and route queries to it only during business hours.
D.Change the warehouse to a Classic-sized warehouse and increase the auto-stop to 30 minutes.
AnswerB

A scheduled stop/start targets the root cause: the warehouse is idle but running outside business hours. Stopping it overnight and on weekends eliminates compute charges during those periods, while starting it before business hours ensures dashboards are available when needed. This is the most direct and effective cost reduction for a predictable usage pattern.

Why this answer

The warehouse is billed for the time it is running, so the largest saving comes from eliminating the predictable idle window outside business hours. Scheduling a stop at the end of the day and a start before business hours ensures the warehouse is available when dashboards are used and incurs no compute cost overnight and on weekends. Auto-stop alone does not help if the warehouse is kept running or restarted by sporadic queries, and resizing or adding warehouses does not target the idle period.

Exam trap

The trap here is focusing on auto-stop or warehouse size, which only affect the period after the last query, instead of scheduling the warehouse to be off during the known idle window.

Practice this question →
15
MCQmedium

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.Increase the trigger interval to 1 hour to batch writes.
B.Disable checkpointing to reduce the write latency.
C.Force a global sort before writing to the Delta table.
D.Enable 'delta.autoOptimize.optimizeWrite' and 'delta.autoCompact'.
AnswerD

These features automatically manage file sizes during writes and consolidate them after writes. This eliminates the small file problem inherent in streaming workloads, ensuring that the cloud storage layer remains efficient. This is the industry-standard approach for maintaining Delta Lake performance and keeping storage costs optimized for streaming.

Why this answer

For high-volume streaming, the 'optimizeWrite' and 'autoCompact' features are critical. 'optimizeWrite' ensures that data is written in optimal file sizes (typically 128MB) before committing, which prevents the creation of small files in the cloud storage. This reduces the burden on the file system and improves downstream read performance. Combined with 'autoCompact', the table remains performant for analytical queries without manual intervention, saving compute cycles and maintenance time.

Exam trap

Candidates often choose manual 'OPTIMIZE' commands or manual file management instead of leveraging built-in Delta features, failing to realize that streaming pipelines require automated, continuous file maintenance to prevent significant performance degradation.

Practice this question →
16
MCQhard

A data engineer is tuning a Spark job that reads from a Delta table and writes to another Delta table. The job uses a groupByKey operation followed by an aggregation. The engineer notices that the job is spilling to disk during the shuffle and taking a long time. The engineer wants to reduce shuffle spill and improve performance. Which action is most likely to help?

A.Increase the number of shuffle partitions to reduce the amount of data per partition.
B.Increase the executor memory and enable off-heap memory for the shuffle.
C.Replace groupByKey with reduceByKey or aggregateByKey to perform map-side aggregation.
D.Set spark.sql.shuffle.partitions to a very high value and enable adaptive query execution.
AnswerC

groupByKey shuffles all key-value pairs without map-side aggregation, causing large data transfers and spill. reduceByKey or aggregateByKey perform partial aggregation on the map side before shuffling, significantly reducing the amount of data shuffled and the memory pressure. This directly reduces spill and improves performance for aggregation workloads, and it is a best practice in Spark.

Why this answer

Replacing groupByKey with reduceByKey or aggregateByKey enables map-side aggregation, which reduces the volume of data shuffled and thus reduces spill and improves performance. Other options either add overhead, do not address the root cause, or are costly workarounds that do not fix the fundamental inefficiency.

Exam trap

The trap here is assuming that more partitions or more memory will solve shuffle spill, when the real fix is reducing the amount of data shuffled by using map-side aggregation.

Practice this question →
17
MCQeasy

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?

A.Switching to a more expensive instance type to complete the job faster.
B.Enabling Photon acceleration for all development workloads.
C.Utilizing Spot instances for non-critical development and test clusters.
D.Increasing the number of workers in the cluster to handle more data in parallel.
AnswerC

Spot instances offer significant cost savings compared to On-Demand instances. By using them for development or non-time-critical processing, you can reduce the infrastructure bill by up to 80%. This is the most effective immediate strategy for cost management without impacting the actual logic or performance of the pipeline.

Why this answer

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.

Exam trap

Test-takers often recommend rewriting application logic or optimizing code, overlooking infrastructure choices like Spot instances that provide immediate, zero-code cost reduction.

Practice this question →
18
MCQhard

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

A.spark.sql.shuffle.partitions
B.spark.databricks.delta.optimizeWrite.enabled
C.spark.databricks.io.cache.enabled
D.spark.sql.autoBroadcastJoinThreshold
AnswerB

This setting enables the optimize-write feature, which automatically compacts data into optimal file sizes during the write process. By doing this upfront, it prevents the creation of numerous small files, which is a major performance bottleneck for read-heavy analytical workloads on large Delta tables.

Why this answer

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.

Exam trap

Students often confuse Auto Compact with Optimize Write, or suggest running manual OPTIMIZE commands instead of configuring the automatic write-time property.

Practice this question →
19
Multi-Selecthard

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?

Select 2 answers
A.Set the auto-stop duration to a very low value, such as 1 minute, for serverless SQL warehouses.
B.Enable multi-cluster load balancing to ensure all queries are executed on the largest instance type.
C.Configure SQL warehouse scaling to use the maximum cluster size at all times to avoid resizing overhead.
D.Implement SQL query history monitoring to identify and optimize long-running or resource-intensive queries.
E.Disable the query cache to ensure all results are freshly computed for accurate billing metrics.
AnswersA, D

Serverless SQL warehouses support very fast startup times, making a 1-minute auto-stop duration feasible. This minimizes the period that the warehouse remains active while idle, ensuring that billing stops almost immediately after the last query finishes, which is highly effective for reducing costs in environments with intermittent usage.

Why this answer

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.

Exam trap

Exam takers often recommend cluster scaling down or increasing instance sizes to reduce SQL warehouse costs, overlooking that serverless auto-stop duration and query optimization are the primary levers.

Practice this question →
20
MCQmedium

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?

A.Configure a maximum worker node limit constraint within the compute policy that prevents scaling beyond two nodes.
B.Enforce a strict maximum instance lifetime limit that terminates the cluster regardless of whether interactive queries or jobs are running.
C.Set a mandatory auto_termination_minutes rule in the cluster policy enforcing a maximum idle timeout threshold for all user-created clusters.
D.Disable the ability for standard users to attach notebooks to interactive clusters, forcing all execution through scheduled jobs.
AnswerC

Mandating an idle termination timeout via cluster policies automatically reclaims compute resources whenever user activity ceases for a designated duration. It strikes the ideal balance by allowing full scaling flexibility during work hours while shutting down idle infrastructure automatically.

Why this answer

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.

Exam trap

Candidates frequently choose 'cluster termination settings' at the workspace level or assume manual user training is sufficient, failing to realize that cluster policies are the mandatory enforcement mechanism.

Practice this question →
21
MCQmedium

A data engineering team is running a nightly batch job on a Databricks job cluster that processes a 10 TB Delta table. The job reads the entire table, performs transformations, and writes results to another Delta table. The team notices that the job takes 4 hours and consumes significant DBUs. They want to reduce runtime and cost without changing the business logic. The table is partitioned by ingestion date, but queries often filter on a high-cardinality column 'customer_id'. Which optimization technique is most appropriate to improve performance and reduce cost?

A.Enable Delta Lake caching by running CACHE TABLE on the source table before the job.
B.Increase the cluster size by adding more worker nodes to the job cluster.
C.Optimize file layout using Z-ORDER BY on the 'customer_id' column.
D.Convert the table to a Parquet table and use partition pruning on 'customer_id'.
AnswerC

Z-ORDER BY clusters data on the 'customer_id' column, improving data skipping for queries that filter on that column. This reduces the amount of data read during the nightly job, leading to faster runtime and lower DBU consumption. It is a cost-effective optimization because it reorganizes existing data without changing business logic, and the benefits persist across runs until the data is rewritten.

Why this answer

Z-ORDER BY on 'customer_id' colocates related data, enabling Delta Lake's data skipping to prune files during reads. This reduces I/O and compute, directly lowering runtime and DBU cost. Other options either increase cost, are impractical, or degrade performance.

The optimization is persistent and does not require changes to business logic, making it ideal for this scenario.

Exam trap

The trap here is assuming that adding more cluster resources or caching will solve performance issues without addressing data layout, which is often the root cause.

Practice this question →
22
MCQeasy

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

A.The number of users logged into the workspace at the time.
B.The total execution time and breakdown of the query stages.
C.The color of the query status light in the UI.
D.The name of the user who submitted the query.
AnswerB

The execution time breakdown helps isolate which part of the query is causing the slowdown (e.g., scanning, shuffling, or joining). This visibility is crucial for diagnosing the root cause—whether it is an inefficient join, lack of partitioning, or data skew—and is the first step in any performance optimization process.

Why this answer

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.

Exam trap

Test-takers often focus on cluster CPU utilization alone, ignoring the query history execution time breakdown which exposes the exact stage causing bottlenecks.

Practice this question →
23
MCQmedium

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.VACUUM table_name
B.ANALYZE TABLE table_name COMPUTE STATISTICS
C.OPTIMIZE table_name
D.REORG TABLE table_name APPLY (PURGE)
AnswerC

OPTIMIZE triggers a compaction process that aggregates small files into larger, optimized Parquet files. This process significantly improves read performance by enabling better data skipping and reducing the overhead associated with listing and opening numerous tiny files, which is a common bottleneck in high-frequency Delta Lake write workloads.

Why this answer

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.

Exam trap

Candidates often confuse OPTIMIZE with VACUUM. VACUUM only removes old files that are no longer referenced; it does not reorganize data or compact active files to improve performance.

Practice this question →
24
MCQhard

A data engineer is configuring a Delta Live Tables (DLT) pipeline that processes streaming data from Apache Kafka. The pipeline performs a series of transformations and writes to a Delta table. The engineer notices that the pipeline is experiencing high latency and wants to optimize it for cost and performance. The pipeline is set to continuous mode. Which configuration change is most effective to reduce cost while maintaining acceptable latency?

A.Increase the number of Kafka partitions to improve parallelism.
B.Configure the pipeline to use Photon-optimized clusters.
C.Switch the pipeline to triggered mode and schedule it to run every 5 minutes.
D.Enable autoscaling on the DLT cluster and set a minimum number of workers.
AnswerC

Triggered mode runs the pipeline only when triggered, rather than continuously. Scheduling every 5 minutes can reduce cost because the cluster is not always running, while still providing acceptable latency for many use cases. This is a common cost-saving measure for streaming pipelines that do not require sub-second latency. It also allows the cluster to shut down between runs, saving DBUs.

Why this answer

Switching from continuous to triggered mode with a 5-minute schedule reduces cost by allowing the cluster to shut down between runs. Continuous mode keeps the cluster running 24/7, incurring constant DBU charges. Triggered mode with a schedule balances latency and cost, making it the most effective change for reducing cost while maintaining acceptable latency.

Exam trap

The trap here is assuming that performance optimizations like increasing partitions or using Photon will reduce cost, when they may actually increase it due to higher resource consumption or DBU rates.

Practice this question →
25
MCQmedium

A data engineer is reviewing a Databricks job that runs a notebook to process a large Delta table. The job takes 45 minutes, and the engineer notices that the cluster spends a significant amount of time in the 'Pending' state before execution begins. The cluster is a job cluster with autoscaling enabled and no cluster pool. The engineer wants to reduce the overall job duration and cost. Which action should the data engineer take?

A.Increase the maximum number of workers in the autoscaling configuration to 32 so the cluster scales up faster.
B.Switch the job to use a serverless job compute environment, which eliminates cluster startup time.
C.Enable a cluster pool and attach the job cluster to it so that instances are pre-provisioned and startup time is reduced.
D.Reduce the cluster's autoscaling minimum workers to 1 to lower cost, and accept the longer startup.
AnswerC

Cluster pools keep a set of idle instances ready, so when the job starts, the cluster can acquire instances quickly instead of waiting for cloud VM provisioning. This directly reduces the 'Pending' time and shortens the job duration. The pool does incur some idle cost, but for a job that runs frequently and has significant startup overhead, the reduction in runtime and the ability to use smaller clusters can offset that cost.

Why this answer

The 'Pending' state indicates the cluster is waiting for cloud instances to be provisioned. A cluster pool pre-provisions instances so they are ready when the job starts, which reduces the time spent waiting and shortens the overall job duration. While pools have some idle cost, for a frequently running job with significant startup overhead, the reduction in runtime and the ability to use right-sized clusters often results in net savings.

Increasing max workers or reducing min workers does not address provisioning delay.

Exam trap

The trap here is assuming that autoscaling settings affect initial cluster startup, when in fact they only control scaling after the cluster is running.

Practice this question →
26
MCQmedium

A data engineer is configuring a Databricks SQL warehouse to handle a workload that consists of many concurrent short queries during business hours and almost no queries at night. The engineer wants to minimize cost while ensuring low latency during peak hours. Which configuration should the engineer use?

A.Use a serverless SQL warehouse with auto-stop disabled to avoid cold starts.
B.Use a classic SQL warehouse with auto-scaling enabled and auto-stop set to 5 minutes.
C.Use a classic SQL warehouse with a fixed cluster size and auto-stop set to 60 minutes.
D.Use a serverless SQL warehouse with auto-stop set to 10 minutes and auto-scale enabled.
AnswerD

Serverless SQL warehouses automatically scale based on query load and can stop when idle, so they incur cost only when running queries. Setting auto-stop to 10 minutes ensures the warehouse shuts down quickly after the last query, minimizing idle cost. Auto-scaling handles concurrency during peak hours, providing low latency without over-provisioning.

Why this answer

A serverless SQL warehouse with auto-stop and auto-scaling provides the best balance: it scales up for concurrency during peak hours, scales down and stops when idle, and charges only for usage. The short auto-stop minimizes idle cost, while auto-scaling ensures low latency. Other options either incur idle cost or cannot handle variable concurrency efficiently.

Exam trap

The trap here is assuming that disabling auto-stop is necessary to avoid cold starts, but serverless warehouses start quickly and idle cost outweighs the benefit.

Practice this question →
27
MCQmedium

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?

A.Write latency will increase, but read performance will remain unaffected.
B.Read performance will improve over time as files are automatically compacted and optimized for downstream queries.
C.Storage costs will increase significantly due to the creation of temporary duplicate data files.
D.The cluster will fail to start because these configurations are deprecated in recent Databricks runtimes.
AnswerB

These settings ensure that files are written at optimal sizes and that small files are consolidated after writes. This results in a cleaner data layout that facilitates more efficient data skipping and reduced I/O, directly leading to better read performance for end-users and BI tools querying the Delta table.

Why this answer

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.

Exam trap

Candidates often mistakenly believe these settings increase write latency significantly or require manual intervention to trigger, ignoring that they are automated, asynchronous background processes designed for long-term read efficiency.

Practice this question →
28
MCQeasy

A data engineer is reviewing the cost of a Databricks job that runs on a daily basis. The job uses an all-purpose cluster that is manually started and stopped by the engineer. The job typically runs for 30 minutes, but the engineer often forgets to stop the cluster, leading to hours of idle time. Which action should the engineer take to reduce cost?

A.Increase the cluster's auto-stop threshold to 60 minutes to avoid premature termination.
B.Use a job cluster instead of an all-purpose cluster for the job.
C.Schedule the cluster to start and stop at specific times using a cron expression.
D.Configure the cluster to auto-stop after 30 minutes of inactivity.
AnswerB

Job clusters are created when a job starts and terminated when the job completes, eliminating idle time. They are billed at a lower rate than all-purpose clusters and are designed for automated workloads. This directly addresses the issue of forgotten clusters and reduces cost significantly. It also simplifies management, as the cluster lifecycle is tied to the job run.

Why this answer

Job clusters are purpose-built for automated jobs and terminate automatically when the job completes, eliminating idle time. They are also cheaper than all-purpose clusters. This directly solves the problem of forgotten clusters and reduces cost, unlike auto-stop thresholds or scheduling, which are workarounds.

Exam trap

The trap here is relying on auto-stop or manual scheduling to manage cluster lifecycle, when using a job cluster is the designed solution for automated workloads.

Practice this question →
29
MCQmedium

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.Enable auto-termination on every individual query executed.
B.Use the Databricks Billing System to set a hard limit that automatically shuts down the account.
C.Implement Budget Policies in the Databricks account console to set alerts on usage.
D.Manually check the Spark UI after every query to calculate the cost.
AnswerC

Budget policies in the Databricks account console allow administrators to define budgets and receive proactive alerts. This is the official and most effective way to track consumption and manage costs across different workspaces, providing the visibility needed to prevent excessive spending before it impacts the monthly cloud bill.

Why this answer

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.

Exam trap

Candidates often confuse cluster auto-termination policies with Unity Catalog permissions, forgetting that account-level budget policies are specifically designed for spend monitoring and alerts.

Practice this question →
30
MCQmedium

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?

A.Provision a larger all-purpose cluster with double the worker nodes to brute-force execution speed.
B.Convert the workload to use a Databricks Job cluster configured with spot instances and automatic fallback to on-demand.
C.Upgrade the cloud provider virtual machine family to the latest generation without changing cluster types.
D.Increase the Apache Spark executor memory fraction and decrease shuffle partition counts.
AnswerB

Job clusters are cheaper than all-purpose clusters and terminate after the run. Configuring spot instances with automatic fallback to on-demand preserves cost savings while surviving pre-emptions, directly addressing the reliability failure and the four-hour runtime.

Why this answer

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.

Exam trap

Candidates often select 'All-Purpose Clusters' for production jobs because they are easier to manage, failing to recognize that Job clusters are cheaper and more reliable for automated tasks.

Practice this question →
31
MCQeasy

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.Load all data into a high-performance in-memory database.
B.Store the data in Delta format on cloud object storage.
C.Replicate the data into multiple cloud regions for higher availability.
D.Convert the table to a legacy Hive format for better compatibility.
AnswerB

Object storage is highly durable and inexpensive. Delta Lake allows you to query this data with high performance using Databricks SQL while keeping the storage costs at the lowest possible tier. This is the standard, most cost-effective architecture for large, rarely updated datasets in a modern data lakehouse.

Why this answer

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.

Exam trap

Candidates mistakenly choose proprietary data warehouse storage tiers or caching mechanisms for cold data, driving up unnecessary infrastructure costs.

Practice this question →
32
Multi-Selecthard

A data engineer is optimizing a Spark job that reads from a large Delta table and performs a join with a smaller dimension table. The job is running slowly, and the engineer suspects data skew and shuffle overhead are the main issues. Which two techniques should the engineer apply to improve performance and reduce cost? (Choose two.)

Select 2 answers
A.Increase the number of shuffle partitions to 2000.
B.Cache the larger table in memory before the join.
C.Repartition the larger table on the join key before the join.
D.Enable adaptive query execution (AQE) and set spark.sql.adaptive.skewJoin.enabled to true.
E.Use broadcast join for the smaller dimension table.
AnswersD, E

Adaptive Query Execution (AQE) dynamically optimizes query plans at runtime, including handling skew by splitting skewed partitions. Enabling skew join optimization allows Spark to automatically detect and mitigate skew during joins, improving performance without manual intervention. This reduces shuffle overhead and prevents straggler tasks, leading to faster completion and lower cost.

Why this answer

Broadcast join eliminates shuffle for the smaller table, and enabling AQE with skew join optimization dynamically handles skew in the larger table. Together, they reduce shuffle overhead and mitigate stragglers, improving performance and lowering cost. Other options either introduce unnecessary shuffles or do not directly address skew.

Exam trap

The trap here is thinking that repartitioning or increasing shuffle partitions will solve skew, when in fact they can add overhead without addressing the root cause.

Practice this question →
33
MCQmedium

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?

A.Increase the executor memory size for all workers in the cluster.
B.Use the 'salting' technique by adding a random prefix to the skewed key.
C.Remove the group-by clause and process the data using a UDF.
D.Reduce the number of executors to force the job to run sequentially.
AnswerB

Salting distributes the skewed data across multiple tasks by adding a random prefix to the grouping key. This ensures that the heavy skewed key is spread out, preventing any single task from hitting memory limits. It is a robust, standard solution to handle data skew in Spark.

Why this answer

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.

Exam trap

Candidates often suggest increasing cluster size (vertical scaling) or memory settings. These are inefficient 'band-aid' fixes that do not address the root cause of uneven data distribution.

Practice this question →

Ready to test yourself?

Try a timed practice session using only Cost and Performance Optimization questions.

Start 20-question session