Cost and Performance Optimization practice questions

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

Why it fails: Running VACUUM with zero retention removes active files needed by concurrent long-running transactions and time travel queries, leading to severe job failures and data corruption risks. It does not compact existing active small files to improve scan throughput.

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

Why it fails: Upgrading driver memory only treats the symptom of excessive file metadata by caching pointers, driving up compute costs unnecessarily. It fails to address the fundamental underlying storage inefficiency causing high cloud storage list-operation latency.

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

Why it fails: Migrating to plain Parquet completely removes ACID transaction guarantees, time travel, and schema enforcement capabilities inherent to Lakehouse architecture. It also fails to solve file fragmentation issues, as Parquet files generated by streaming apps remain small.

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

Why it fails: Partitioning by a high-cardinality column, like a unique ID, creates too many small partitions (the 'small file problem'). This leads to massive metadata overhead and degrades performance significantly, making it one of the worst practices for data layout design in Delta Lake, as it prevents effective data skipping.

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

Why it fails: Partitioning by date allows the query engine to ignore irrelevant folders entirely during a query, which is known as partition pruning. This drastically reduces the total data scanned and improves query execution speed. It is the standard approach for time-series data to ensure performant access patterns.

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

Why it fails: Disabling partitioning for date-based queries forces the engine to scan the entire table, making it highly inefficient. Even with Z-Ordering, the absence of partition pruning means the system must process much more metadata and data than necessary, which is suboptimal for large datasets frequently filtered by date.

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

Why it fails: Adding high-cardinality columns like customer_id to partitioning leads to the small file problem. This creates millions of tiny files, which increases metadata overhead, overwhelms the file system, and severely degrades query performance. Partitioning should only be used for low-cardinality columns that are frequently used in equality filters.

Convert the table to a Parquet format without Delta features.

Why it fails: Removing Delta Lake features would eliminate transaction logging, time travel, and schema enforcement, which are critical for reliability. Furthermore, standard Parquet does not provide built-in data skipping metadata like column-level statistics that Z-Ordering leverages. This change would likely make data management harder and decrease overall query performance during scans.

Enable liquid clustering on the existing table immediately.

Why it fails: Liquid clustering is a feature that replaces traditional partitioning and Z-Ordering. While it is a powerful optimization, simply enabling it on an existing table does not automatically re-cluster the entire historical dataset. You must perform an explicit rewrite of the data to see performance benefits, making this a more involved process.

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

Why it fails: Enabling 'spark.databricks.io.cache.enabled' reserves a portion of the executor memory for the Delta cache. If the cluster is not sized appropriately, this caching takes away from the memory available for shuffle operations. During a join, if the shuffle space is too small, Spark will spill data to disk.

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

Why it fails: The configuration allows scaling up to 8 workers, which is the current limit. The issue is not that it cannot scale, but that the existing resources are failing to hold the shuffle data in memory. Even with 8 workers, if the memory per worker is small, spilling remains inevitable.

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

Why it fails: While very high shuffle partition counts can lead to small files and metadata overhead, they do not typically cause shuffle spills. Spilling occurs when the memory footprint of a specific task exceeds the allocated heap space. Reducing partitions would actually increase the memory pressure per task, potentially worsening the spilling.

Always use All-Purpose clusters to ensure high availability.

Why it fails: All-Purpose clusters are billed at a higher DBU rate than Jobs clusters. For automated production workflows, they are inefficient and costly. Jobs clusters are specifically optimized for this use case and provide lower pricing, making them the preferred choice for cost-effective data engineering pipelines in Databricks.

Set the maximum DBU limit on the workspace to zero.

Why it fails: Setting a DBU limit to zero will prevent any jobs from running, which does not achieve the goal of processing data. While cost governance is important, this approach is impractical and will result in total system failure. Proper resource management should focus on optimization rather than complete service denial.

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

Why it fails: Broadcasting a 200 GB dimension table would require collecting it entirely into memory on each executor, which is not feasible and would likely cause out-of-memory errors. The broadcast hint is appropriate only for tables small enough to fit in memory (typically under 10 GB).

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

Why it fails: Increasing partitions may improve parallelism but does not reduce shuffle volume for a join. The shuffle still exchanges all matching keys across the network. Repartitioning by join key could help, but only if it aligns with the join keys and avoids a subsequent shuffle.

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

Why it fails: Bucketing can avoid shuffle if both tables are bucketed by the join key and the number of buckets is compatible. However, the scenario does not mention that tables are persisted or that the join key is known ahead of time. Applying bucketBy to DataFrames before a join does not directly eliminate shuffle unless the data is written and read as bucketed tables.

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

Why it fails: Increasing the number of shuffle partitions typically creates more, smaller partitions, which can increase task scheduling overhead and produce many tiny files. Without adaptive coalescing, the shuffle read/write volume remains roughly the same, and the job may slow down due to more tasks. It does not reduce the total shuffle data size.

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

Why it fails: Disabling broadcast joins does not reduce shuffle for large joins; it forces sort-merge joins, which still require shuffling both sides. The scenario involves a MERGE operation, not a join that would benefit from broadcast. This setting would likely increase shuffle and degrade performance.

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

Why it fails: Adding more memory or workers can help with out-of-memory issues but does not directly reduce shuffle data volume. The shuffle read and write sizes are determined by the query plan and data distribution. More resources may mask the problem but will increase cost without addressing the root cause of excessive shuffle.

Enable auto compaction on the table.

Why it fails: Auto compaction automatically compacts small files after a write, but it is triggered only during writes, not for existing small files from past appends. If the table already has many small files, auto compaction will not retroactively fix them. The engineer needs to run OPTIMIZE to compact the existing files first.

Repartition the table by a high-cardinality column.

Why it fails: Repartitioning changes the physical layout but does not necessarily reduce the number of small files; it could even increase them if not done carefully. Repartitioning is a heavy operation and may not be suitable for a frequently updated table. It also does not directly address the small file problem unless combined with compaction.

Run VACUUM on the table to remove old files.

Why it fails: VACUUM removes old, unreferenced files that are no longer part of the table's current version. While it can reduce storage costs, it does not address the problem of many small files from frequent appends. In fact, VACUUM might remove files that are still needed for time travel if the retention period is too short, and it does not improve query performance for current data.

Increase the warehouse size to accommodate peak concurrency.

Why it fails: Increasing the warehouse size adds more compute resources but does not solve the startup latency for short queries. It also raises cost for all queries, including short ones, and may not efficiently handle the mix of workloads because a larger cluster still requires startup time and may be over-provisioned during idle periods. This is a blunt approach that does not address the root causes.

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

Why it fails: Photon can accelerate query execution, but increasing the auto-stop timeout keeps the warehouse running longer during idle periods, increasing cost. It does not reduce startup time for short queries; it merely avoids some startups by keeping the cluster alive, which is wasteful if queries are sporadic. This combination does not optimally balance performance and cost for the described mixed workload.

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

Why it fails: Separating workloads can isolate resources, but it increases management overhead and may not reduce cost because both warehouses incur startup and idle costs. It also does not inherently improve startup time for short queries unless paired with a serverless or always-on configuration. This approach can be valid in some cases, but it is not the most effective first step for the stated goals.

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

Why it fails: Increasing the auto-stop threshold keeps the warehouse running longer during idle periods, which increases cost. While it reduces cold starts, the trade-off is higher DBU consumption during inactivity. For sporadic access, this is not cost-effective because the warehouse would remain active even when not needed. The goal is to reduce cost, so this change would likely increase it.

Configure the warehouse to use a smaller cluster size.

Why it fails: Using a smaller cluster size reduces cost per DBU but may increase query latency, especially for complex dashboard queries. If queries take longer, user experience suffers. Also, it does not address the idle time cost; the warehouse still runs during inactivity. For sporadic access, the main cost driver is idle time, so reducing cluster size may not yield significant savings and could degrade performance.

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

Why it fails: Decreasing the auto-stop threshold saves cost during idle time but may cause more frequent cold starts, leading to longer query times and a negative user experience. Users may experience delays when the warehouse starts up. Since the requirement is to not impact user experience, this change could be detrimental. It might save cost but at the expense of performance.

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

Why it fails: Increasing maxPartitionBytes allows each Spark task to read more data, which can reduce the number of tasks but does not reduce the total amount of data read. It might even increase memory pressure. This setting is about tuning parallelism, not reducing data volume through skipping.

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

Why it fails: Optimize write reduces the number of small files written during writes, which can improve write performance and reduce file count. However, it does not directly reduce the amount of data read during queries. It is a write-side optimization and does not provide data skipping benefits for existing data.

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

Why it fails: While OPTIMIZE with Z-ORDER is a valid technique, it is essentially the same as option B. In a multi-select question, only two options are correct, and option B already covers Z-ORDER. Option E is redundant and not a separate action. Additionally, OPTIMIZE alone without Z-ORDER does not provide data skipping benefits.

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

Why it fails: Increasing shuffle partitions can help with skew or large shuffles, but setting it to an extremely high value creates many tiny tasks, adding scheduling overhead and small output files. It does not address the driver memory issue caused by broadcasting the large dimension, and it may worsen performance. This is a tuning knob for shuffle size, not for broadcast join memory pressure.

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

Why it fails: AQE can convert sort-merge joins to broadcast joins at runtime when statistics show a small side, but setting the auto-broadcast threshold above 50 GB would force the dimension to be broadcast, which is exactly the problem. This increases driver memory pressure and can cause OOM. AQE is useful, but not with an oversized broadcast threshold in this scenario.

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.

Why it fails: Caching the dimension on executors does not change the fact that a broadcast join collects the dimension on the driver and distributes it as a broadcast variable. The driver memory issue remains, and the cache consumes executor memory that could be used for the join. Caching can help repeated scans, but it does not fix the broadcast join memory problem.

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

Why it fails: All-purpose clusters are designed for interactive use and are typically more expensive than job clusters. A long auto-stop timeout keeps the cluster running even when idle, increasing cost. For a nightly batch job, this configuration wastes resources and money, as the cluster remains active outside the job's runtime, and autoscaling may not be necessary for a single-pass job.

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

Why it fails: SQL warehouses are optimized for SQL analytics and BI workloads, not for general ETL jobs that may use DataFrame or RDD APIs. While they can run SQL, they are not the right tool for complex ETL transformations. Additionally, SQL warehouses are typically more expensive for batch processing and do not support the full range of Spark APIs needed for ETL.

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

Why it fails: Autoscaling can add overhead and may not be beneficial for a single-pass job with predictable workload. A short auto-stop timeout is irrelevant for job clusters because they terminate when the job finishes. This configuration may lead to unnecessary scaling fluctuations and does not guarantee cost savings compared to a fixed-size job cluster tailored to the workload.

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

Why it fails: Single-node clusters completely lack distributed processing capabilities, restricting query execution to a single driver and causing immediate out-of-memory failures on large datasets. They cannot scale out workloads across multiple worker nodes to handle concurrency effectively.

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

Why it fails: Pre-warmed idle driver nodes do not address concurrency spikes, since workers execute queries, and idle drivers waste cost. It is tempting because eliminating cold starts sounds attractive, and would suit predictable latency-sensitive workloads, not sporadic unpredictable analytics needing elastic worker scaling.

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

Why it fails: Disabling auto-termination causes clusters to run continuously, racking up massive idle charges during off-peak hours and weekends. This is an anti-pattern for cost optimization in cloud environments where resources should scale down when not in use.

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

Why it fails: Adjusting the data retention period only affects how many historical versions are preserved for time travel and vacuum operations. It has no direct impact on file sizing issues, small file accumulation, or read query performance degradation caused by streaming micro-batches.

Enable predictive optimization to automatically manage compaction and vacuum operations.

Why it fails: Predictive optimization intelligently identifies and runs maintenance operations like compaction and vacuum, but it operates asynchronously. For streaming pipelines with high ingestion rates, synchronous controls like optimized writes and Auto Compact are required immediately upon file creation.

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

Why it fails: Replacing Delta Lake with standard Parquet eliminates transactional guarantees, time travel, and native optimization features like Z-Ordering and OPTIMIZE. Standard Parquet does not solve the small file problem inherent to continuous streaming micro-batch data ingestion pipelines.

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

Why it fails: Doubling worker nodes on an all-purpose cluster increases hourly DBU and cloud costs while spot pre-emptions still terminate workers, so failures persist and spend rises. It is tempting because extra parallelism shortens runtime, and would be correct when the job is compute-bound and running on reliable on-demand capacity.

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

Why it fails: Newer VM generations change price-performance and hardware capabilities, but pre-emptions still terminate spot instances and the all-purpose cluster still lacks automatic retry, so reliability is unchanged. It is tempting because newer families often cost less per compute unit, and would be correct when the workload is stable and the constraint is raw instance price.

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

Why it fails: Tuning executor memory fraction and shuffle partitions alters in-memory execution and disk I/O, not instance lifecycle; spot pre-emptions still kill executors and the job still fails. It is tempting because shuffle tuning often resolves spills and skew, and would be correct when the job is slow or out-of-memory rather than interrupted.

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

Why it fails: A max_capacity of 10 limits the maximum number of instances the pool can hold. While restrictive for large teams, it caps rather than increases infrastructure spending and does not explain unexpected continuous baseline charges during idle times.

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

Why it fails: Databricks instance pools fully support spot instances when configured correctly. The unexpected cost is not caused by an inability to use spot pricing, but rather by the resource allocation strategy maintaining active idle virtual machines.

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

Why it fails: Instance types like i3.xlarge are storage-optimized instances commonly used for caching and shuffle-heavy workloads. While they have specific pricing structures, storage optimization does not inherently trigger unexpected persistent idle billing compared to minimum idle node counts.

VACUUM table_name

Why it fails: VACUUM removes expired data files that are no longer referenced by the Delta log. While it is essential for storage cost management and data privacy compliance, it does not physically rewrite existing data to optimize read layout or address the issue of small file fragmentation within the active table state.

ANALYZE TABLE table_name COMPUTE STATISTICS

Why it fails: ANALYZE TABLE computes column-level statistics used by the query optimizer to choose efficient join strategies and filter pushdowns. It does not reorganize the underlying physical data files to resolve small file issues, though it is a recommended step to perform after running OPTIMIZE to update the table metadata.

REORG TABLE table_name APPLY (PURGE)

Why it fails: REORG TABLE with the APPLY (PURGE) option is typically used for rewriting data to remove deleted records or for specific table maintenance tasks related to liquid clustering. It is not the standard or most efficient command for general compaction of small files compared to the standard OPTIMIZE command.

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

Why it fails: Multi-cluster load balancing is designed to increase concurrency by spinning up additional clusters, not to force execution on the largest instance type. Using the largest instance type unnecessarily increases costs and does not guarantee improved performance for smaller queries that might be better served by smaller, cost-effective clusters.

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

Why it fails: Setting a warehouse to the maximum size at all times forces the highest billing rate regardless of current workload demands. This is a poor cost-optimization strategy, as it ignores the elasticity benefits of Databricks SQL, leading to significant over-provisioning and wasted budget during periods of low or moderate activity.

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

Why it fails: Disabling the query cache forces the warehouse to recompute results for every query, which significantly increases compute usage and costs. The query cache is a performance feature designed to return results instantly for identical queries, saving both time and money by avoiding unnecessary re-execution of complex operations.

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

Why it fails: While there is a slight performance overhead during the write process to reorganize data, the primary goal is improving read performance. Read performance will definitely be improved by these settings, as they prevent the creation of small files that would otherwise slow down subsequent read operations across the table.

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

Why it fails: These settings do not create permanent duplicate files. While compaction involves rewriting data, Delta Lake's vacuuming process eventually removes the old files. The minor temporary storage increase is negligible compared to the massive performance benefits gained by having an efficiently partitioned and compacted table layout for analytical read tasks.

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

Why it fails: These settings remain fully supported and are considered best practices for managing small files in Delta Lake. They are not deprecated, and the cluster will start successfully. The use of these flags is a standard recommendation for ensuring long-term table performance without relying solely on manual maintenance tasks.