Home / DE Professional practice test / Cost & Performance Optimisation

Free · 8 questions with explanations

Cost & Performance Optimisation: Databricks Data Engineer Professional Practice Questions

Exam-style questions on Cost & Performance Optimisation. Pick your answer, then open the explanation to see why it's right — and why the other options are wrong.

Updated November 16, 2026

1 Cost & Performance Optimisation Medium

A solutions architect is evaluating whether to register new production Delta Lake tables as Unity Catalog managed tables or external tables. The architect wants to take full advantage of platform-managed performance optimizations and operational simplifications. Which combination of features is EXCLUSIVELY available to Unity Catalog managed tables and NOT available for external tables?

  1. APredictive optimization, automatic liquid clustering, metadata caching, and automatic file deletion 8 days after DROP TABLE, which external tables lack.
  2. BACID transactions, time travel, schema enforcement, and Delta format support, which Unity Catalog provides for managed tables but not external ones.
  3. CDelta Sharing, Iceberg REST Catalog access, credential vending, and column-level lineage tracking, which require the table to be registered as managed.
  4. DPredictive optimization, metadata caching, and file deletion after DROP TABLE; automatic liquid clustering also covers external tables with a LOCATION.
  5. EUnity Catalog access controls, row filters, column masks, and centralized auditing, which do not apply to tables registered as external tables.
Show answer & explanation

Correct answer: A

WHY A: The Databricks documentation lists features unique to Unity Catalog managed tables: (1) Predictive optimization, which automatically runs OPTIMIZE, VACUUM, and ANALYZE; these must be manually scheduled for external tables. (2) Automatic liquid clustering, which selects clustering keys based on workload patterns and is enabled via predictive optimization; not available for external tables. (3) Metadata caching, in-memory caching of Delta transaction metadata to reduce cloud storage requests; always enabled for managed tables. (4) Automatic file deletion after DROP TABLE: managed tables delete the underlying cloud storage files after 8 days, while external tables require manual storage cleanup. WHY NOT B: ACID transactions, time travel, and schema enforcement are Delta Lake features available to both managed and external Delta tables; they are not exclusive to managed tables. WHY NOT C: Delta Sharing and credential vending work with both managed and external tables through Unity Catalog; they do not require managed registration. WHY NOT D: Automatic liquid clustering (with predictive optimization) is exclusive to managed tables; external tables cannot benefit from automatic clustering key selection. WHY NOT E: Unity Catalog access controls, row/column security, and auditing apply to every table registered in Unity Catalog, including external tables.

2 Cost & Performance Optimisation Hard

A data engineering team enables change data feed on a Silver Delta table (delta.enableChangeDataFeed = true) to power a downstream audit pipeline. Six months later, they attempt to read CDF changes starting from version 1 (the day CDF was enabled). The read fails with an error indicating the version is no longer available. Separately, after a DBA runs ALTER TABLE silver RENAME COLUMN amount TO total_amount, an ongoing CDF streaming read on the table also fails. Which explanation correctly identifies the root cause for BOTH failures?

  1. AFirst failure: change data feed records are transient and follow the table's retention policy; once version 1 has been removed by VACUUM, its change data is unreadable. Second failure: a column rename is a non-additive schema change under column mapping, and a CDF streaming read does not span a version range that contains one.
  2. BBoth failures come from change data feed needing to be re-enabled after a schema change. A column rename resets the CDF configuration, requiring ALTER TABLE ... SET TBLPROPERTIES (delta.enableChangeDataFeed = true) to be run again, and historical versions become inaccessible after re-enabling.
  3. CBoth failures come from insufficient permissions. Reading change data feed from specific historical versions, in batch or in a stream, requires the SELECT WITH HISTORY privilege, which is a separate grant from the standard SELECT on the current table version and is revoked by DDL changes.
  4. DFirst failure: CDF starts recording from the version after the table property is set, leaving no CDF records for version 1. Second failure: RENAME COLUMN creates a new table version, and CDF does not read across DDL-version boundaries, as Databricks excludes DDL transaction versions from CDF output.
  5. EBoth failures come from the table using Unity Catalog managed storage. CDF reads on managed tables go through the _change_data directory, which is controlled by the predictive optimization engine and is not accessible for historical queries beyond the last OPTIMIZE cycle.
Show answer & explanation

Correct answer: A

WHY A: Two separate root causes are identified. (1) Version availability: CDF records are documented as TRANSIENT. They follow the table's retention policy, and VACUUM removes old table versions' files at regular intervals; once version 1 is beyond the retention window, its change data can no longer be read. The documentation states: 'When a version is removed, you can no longer read the change data feed for that version.' CDF is not a permanent audit log and records are eventually purged. (2) Column rename: Delta column mapping permits renaming columns without rewriting data files, but CDF has documented limitations after non-additive schema changes (renaming, dropping, or changing types of columns). In DBR 12.2 LTS and below, CDF streaming reads on column-mapped tables that experienced non-additive changes fail outright. In DBR 13.x+, schema tracking helps ongoing streams, but a streaming read still does not span a version range that contains a non-additive schema change. WHY NOT B: Column renames do not reset CDF enablement. The delta.enableChangeDataFeed = true property persists across schema changes, so nothing needs to be re-enabled. WHY NOT C: Permissions are not the issue: the failures come from data retention and schema evolution constraints, not access control. SELECT WITH HISTORY is not a Delta Lake or Unity Catalog privilege. WHY NOT D: It is true that CDF records changes from the point it is enabled, but that is NOT why reading version 1 fails SIX MONTHS later: the version was readable at first and became unavailable after retention/VACUUM removed it. The rename failure comes from the non-additive schema change, not from DDL versions being excluded from CDF output. WHY NOT E: CDF reads are not managed or blocked by predictive optimization. There is no _change_data access restriction tied to the OPTIMIZE cycle.

3 Cost & Performance Optimisation Medium

A data engineer is optimizing a 10 TB Unity Catalog managed Delta table storing e-commerce events. Queries frequently filter on user_id (100 million distinct values) and event_type (12 distinct values). The table was created without any explicit file layout optimization. The Spark UI shows queries scanning all 2,000 data files even with highly selective WHERE user_id = '...' filter clauses. Which combination of actions correctly diagnoses the root cause and applies the most effective optimization?

  1. ARoot cause: with no layout optimization, user_id values are scattered across the files, and each file's min/max range includes the target value. Solution: run OPTIMIZE ... ZORDER BY (user_id, event_type) to co-locate similar values and tighten the per-file ranges for data skipping.
  2. BRoot cause: Delta Lake keeps no statistics for columns with more than 1,000 distinct values. Solution: partition the table on event_type (low cardinality) and set spark.sql.shuffle.partitions=200 to raise query-level parallelism for the user_id filters.
  3. CRoot cause: data skipping in Delta Lake works on partition columns, and neither user_id nor event_type is a partition key, leaving no files to skip. Solution: partition the table by user_id with range partitioning to enable partition pruning on the filter.
  4. DRoot cause: Delta Lake collects no file statistics until ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS user_id, event_type has been run. Solution: run ANALYZE once, after which queries use data skipping on user_id without changes to the data layout.
  5. ERoot cause: a scan of the 2,000 files in the Spark UI shows the query runs without predicate pushdown. Solution: switch from the PySpark DataFrame API to Spark SQL string queries, which support predicate pushdown where DataFrame operations do not.
Show answer & explanation

Correct answer: A

WHY A: Delta Lake's data skipping reads per-file column statistics (min, max, null count, row count) that are collected when data is written. For a high-cardinality column like user_id with random insertion order, values are spread across the files, so each file's min-max range overlaps any specific target user_id and no file can be skipped from statistics. OPTIMIZE ... ZORDER BY (user_id) co-locates records with similar user_id values into the same set of files, dramatically tightening per-file min-max ranges. After ZORDER, a query for a specific user_id finds that most files' ranges exclude the target value and skips them. Adding event_type to ZORDER further prunes on that filter. WHY NOT B: Delta Lake has no cardinality limit for statistics collection: statistics are collected on the first 32 columns by default regardless of cardinality. Partitioning on low-cardinality event_type (12 values) creates 12 partitions and does not help prune on user_id selectivity; shuffle partitions do not affect file skipping. WHY NOT C: Data skipping is NOT limited to partition columns. Delta Lake collects min/max statistics on data columns (up to 32 by default) and uses them for file pruning on any filtered column, partition or not; partitioning by a 100-million-value column is also impractical. WHY NOT D: File-level statistics ARE collected at write time, and for Unity Catalog managed tables Databricks also maintains statistics via predictive optimization. The root issue is data layout (scattered user_id values), not missing statistics. WHY NOT E: Predicate pushdown works identically for Spark SQL and the PySpark DataFrame API: both generate the same query plan with filters pushed to the scan node. The API choice does not affect predicate pushdown or data skipping.

4 Cost & Performance Optimisation Medium

An existing production Delta table is configured with Hive-style date partitioning (PARTITIONED BY date) and a nightly Z-order job (OPTIMIZE ... ZORDER BY (region, category)). The data engineering team wants to migrate this table to liquid clustering for better performance and reduced maintenance overhead. Which of the following statements is TRUE about this migration, and what is the recommended approach?

  1. ALiquid clustering is compatible with existing Hive-style partitioning. You can add CLUSTER BY (region, category) to the existing partitioned table via ALTER TABLE, and subsequent OPTIMIZE runs will apply both partitioning-pruning and clustering simultaneously.
  2. BALTER TABLE ... CLUSTER BY (date, region, category) can be run on the existing partitioned table and automatically drops the partition scheme, then rewrites all historical data into the clustered layout in a single atomic operation.
  3. CLiquid clustering is incompatible with partitioning and ZORDER. To migrate, the table must be recreated without partitioning (e.g., via CTAS without PARTITION BY), specifying CLUSTER BY (date, region, category) at creation. To apply clustering to all historical records, run OPTIMIZE FULL on the new table.
  4. DLiquid clustering replaces Z-order but still requires partitioning for high-cardinality timestamp columns. The correct approach is CLUSTER BY (region, category) while preserving the date partition column for range-based pruning.
  5. ELiquid clustering can be enabled on any existing unpartitioned Delta table via ALTER TABLE ... CLUSTER BY (...) without rewriting data. However, for a partitioned table, you must first run ALTER TABLE REMOVE PARTITIONING, which is not supported in Delta Lake — so recreation via CTAS is the only path.
Show answer & explanation

Correct answer: C

WHY C: Liquid clustering is explicitly incompatible with both Hive-style partitioning and ZORDER. You cannot add liquid clustering to an existing partitioned table — the table must be recreated without partitioning. The correct migration path is CTAS (CREATE TABLE ... AS SELECT) without a PARTITION BY clause, specifying CLUSTER BY on the migration-recommended columns. For a table using partition key (date) + ZORDER keys (region, category), the documentation recommends using all as clustering keys: CLUSTER BY (date, region, category). After creating the table with liquid clustering, OPTIMIZE FULL (Databricks Runtime 16.0+) forces reclustering of all existing records. WHY NOT A: Liquid clustering is NOT compatible with partitioning — attempting to add CLUSTER BY to a partitioned table will fail. WHY NOT B: ALTER TABLE ... CLUSTER BY only works on existing UNPARTITIONED tables. It does not drop partitioning or rewrite historical data — and it would fail on a partitioned table. WHY NOT D: Using liquid clustering alongside partitioning simultaneously is not supported. Liquid clustering is designed to REPLACE both partitioning and Z-order, not supplement them. WHY NOT E: An ALTER TABLE REMOVE PARTITIONING command is not supported in Delta Lake. Option E's conclusion (CTAS is required) is correct, but the framing misidentifies the blocking issue. Delta liquid clustering cannot be added to a partitioned table — the incompatibility is by design, not merely due to a missing ALTER TABLE command.

5 Cost & Performance Optimisation Hard

A Databricks production Delta table has deletion vectors enabled (delta.enableDeletionVectors = true). The operations team notices that after multiple DELETE and UPDATE operations, the underlying Parquet files have NOT been rewritten and old modified row data still physically exists in storage. They need to guarantee all rows marked by deletion vectors are physically purged from Parquet files for a compliance data retention requirement. Which statement correctly explains the behavior of deletion vectors and the correct command to physically purge all rows marked by deletion vectors?

  1. ADeletion vectors soft-delete rows by marking them without rewriting Parquet files. To purge the marked rows physically, run REORG TABLE <table_name> APPLY (PURGE) and then VACUUM to remove the old unreferenced files.
  2. BDeletion vectors rewrite the Parquet files during each DELETE or UPDATE, so leftover old files indicate a checkpointing failure. Run OPTIMIZE with a targeted WHERE clause to force the remaining files to be rewritten.
  3. CDeletion vectors mark rows as soft-deleted in the transaction log. Running VACUUM with the default retention rewrites the affected Parquet data files and removes the deletion vector markers, leaving a clean physical state.
  4. DDeletion vectors track row modifications in a sidecar file. Running ALTER TABLE <table_name> DROP FEATURE 'deletionVectors' is the purge command: it physically removes the marked rows and the old data files in one step.
  5. EDeletion vectors apply to DELETE operations, while UPDATE operations rewrite the affected Parquet files at write time. To purge the soft-deleted rows left by DELETE operations, run OPTIMIZE FULL on the table and then VACUUM.
Show answer & explanation

Correct answer: A

WHY A: Deletion vectors are a storage optimization that performs soft-deletes: DELETE, UPDATE, and MERGE operations mark rows as modified in a deletion vector sidecar file without rewriting the underlying Parquet data. Reads resolve current state by applying these modifications at query time. Physical changes to Parquet files occur when (1) OPTIMIZE runs (incremental compaction), (2) auto-compaction triggers, or (3) REORG TABLE ... APPLY (PURGE) is explicitly run. For a compliance purge, REORG TABLE ... APPLY (PURGE) is the command: it rewrites the data files containing records with deletion vector modifications. After REORG completes, VACUUM is run to remove the old unreferenced files. WHY NOT B: Deletion vectors purposely avoid rewriting Parquet files during DELETE/UPDATE; that is the performance benefit, so leftover files are expected and not a checkpointing failure. OPTIMIZE with a WHERE clause does incremental clustering/compaction but does not ensure that the deletion-vector-marked rows are physically purged. WHY NOT C: VACUUM removes unreferenced data files after the retention period; it does NOT rewrite files that still contain deletion-vector-marked rows. Files still referenced by the current table version are left untouched. WHY NOT D: DROP FEATURE removes the deletion vector capability from the table protocol; it is a protocol downgrade operation, not the data purge mechanism, and it does not remove the old data files. The compliance approach is REORG TABLE ... APPLY (PURGE) followed by VACUUM. WHY NOT E: Deletion vectors apply to DELETE, UPDATE, and MERGE operations, not just DELETE. UPDATE with deletion vectors enabled also uses soft-deletes rather than rewriting files at write time (especially with Photon for MERGE and UPDATE), and OPTIMIZE FULL is not the purge command.

6 Cost & Performance Optimisation

A data engineer runs a Silver Delta table that receives frequent small DELETE and UPDATE operations affecting a handful of rows scattered across many large data files. Without deletion vectors, each such operation is expensive and creates churn. The engineer enables deletion vectors on the table. Which statement correctly describes how deletion vectors improve performance and what maintenance implication follows?

  1. ADeletion vectors disable ACID guarantees for DELETE/UPDATE in exchange for speed, so a reader may briefly see rows that have been logically deleted; the maintenance implication is that you must run VACUUM immediately after every delete to restore consistency.
  2. BDeletion vectors rewrite every affected file eagerly but in parallel, so the total work is the same as copy-on-write; the only benefit is that the rewrites are compressed, and no follow-up maintenance is ever needed.
  3. CDeletion vectors move deleted rows into a separate _deleted Delta table so they can be recovered; queries must UNION the base table with the _deleted table and filter, which is why deletion vectors require rewriting all reader queries.
  4. DWith deletion vectors enabled, a DELETE/UPDATE marks affected rows as removed in a compact side file instead of immediately rewriting each large data file, so small row-level changes avoid rewriting entire files; a later OPTIMIZE (or predictive optimization) physically purges the soft-deleted rows and consolidates files.
  5. EDeletion vectors are a query-time cache only; they speed up repeated SELECTs by remembering previously filtered rows but have no effect on the cost of DELETE/UPDATE operations, which still rewrite files as before.
Show answer & explanation

Correct answer: D

WHY D: Deletion vectors implement merge-on-read for row-level changes: instead of rewriting whole data files when a few rows are deleted/updated, Delta records the affected row positions in a compact deletion-vector file, so the expensive file rewrites are avoided at write time. The soft-deleted rows are later physically removed and files consolidated by OPTIMIZE (or predictive optimization), which is the expected maintenance follow-up. WHY NOT B: Deletion vectors specifically avoid eager full-file rewrites (copy-on-write); the work is deferred, not merely compressed, and follow-up compaction is needed. WHY NOT C: Deletion vectors do not create a separate _deleted table or require readers to UNION anything; readers transparently skip marked rows. WHY NOT A: Deletion vectors preserve ACID and read consistency; readers never see logically deleted rows, and VACUUM is not a consistency fix. WHY NOT E: Deletion vectors change the cost of DELETE/UPDATE writes (that is their purpose); they are not merely a SELECT-side cache.

7 Cost & Performance Optimisation Hard

An analyst query against an external Parquet fact table with Hive-style partitions filters WHERE transaction_date = '2025-10-01' AND store_id = 'NYC-042'. The table is partitioned by transaction_date. The query consistently takes 45+ minutes and the Spark UI shows 800 of 1,000 total data files being scanned. The expected behavior is that partitioning on transaction_date should limit the scan to approximately 50 files for that date. Which root causes, diagnosable from the Spark UI and query plan, best explain this behavior — and what is the correct remediation?

  1. ADiagnosis: pruning on transaction_date works and the 800 files are partial skipping, since a date partition does not help store_id filters. Remediation: run OPTIMIZE ... ZORDER BY (transaction_date, store_id) to cut the file count.
  2. BDiagnosis: Adaptive Query Execution re-planned the shuffle partition count at runtime and pulled extra partition data into the file scan. Remediation: set spark.sql.adaptive.enabled=false so the static partition pruning plan is kept.
  3. CDiagnosis: an implicit CAST on the partition column (cast(transaction_date as string) in the scan node) blocks partition elimination, or partition directories are missing from the metastore. Remediation: use a DATE literal and run MSCK REPAIR TABLE.
  4. DDiagnosis: the plan builds a CartesianProduct between the transaction_date and store_id predicates, which defeats pruning. Remediation: replace the AND predicate with a semi-join against a one-row DataFrame holding the target values.
  5. EDiagnosis: the table's transaction log has grown too large for the driver to parse in time, so the planner falls back to a full scan. Remediation: run OPTIMIZE on the table to compact the transaction log files and shorten log parsing.
Show answer & explanation

Correct answer: C

WHY C: Two legitimate root causes, both diagnosable from the query plan, explain why partition pruning fails despite a partition filter. (1) Cast on the partition column: when type coercion resolves a mismatch by casting the partition COLUMN (rather than the literal), the predicate no longer references the raw partition value and the optimizer cannot prune. The scan node shows cast(transaction_date ...) and the filter is applied after reading all files. (When the literal side is coerced instead, constant folding preserves pruning, which is why this failure is subtle and easy to miss.) The fix is to make the literal type match the column type, e.g. DATE '2025-10-01' or a properly typed parameter. (2) For external tables with Hive-style partitioning, manually added partition directories that are not registered in the metastore leave the planner unaware of those partitions; running MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS) registers them. Delta tables keep their own partition metadata in the transaction log and do not need MSCK REPAIR, but external Hive-partitioned tables do. For store_id, without a layout optimization, data skipping relies only on per-file min/max statistics. WHY NOT A: This assumes pruning is working. If partition pruning were functioning, the scan would be about 50 files; 800 files confirms pruning is NOT happening. ZORDER would help store_id filtering but cannot fix the broken partition pruning. WHY NOT B: AQE tunes shuffle partition counts and join strategies at runtime, but it does not affect partition elimination. Partition elimination is a static optimization done during query planning, before execution, and is independent of AQE, so disabling AQE changes nothing here. WHY NOT D: CartesianProduct is a join operator between two unrelated inputs. A simple AND between two filter conditions on the SAME table never produces a CartesianProduct, so this misreads the query plan. WHY NOT E: This is a Parquet table, and in any case Delta's OPTIMIZE compacts data files, not the transaction log; log checkpointing is a separate automatic process. An oversized transaction log would slow query planning, not cause a fallback to a full table scan.

8 Cost & Performance Optimisation Medium

A PySpark job joins a 500 GB transactions fact table with a 2 GB product_catalog lookup table. The job is running slowly. A data engineer opens the Spark UI query profile and observes: (1) A SortMergeJoin node is shown — not a BroadcastHashJoin. (2) Shuffle read/write metrics show ~500 GB of data being shuffled across executors. (3) Join task durations are highly skewed — most tasks complete in 2 seconds but 3 tasks take over 8 minutes. Which diagnosis and remediation is MOST accurate and complete?

  1. ASortMergeJoin is the optimal strategy for two tables of this size, and the long tasks come from Spark's default shuffle partition count. Set spark.sql.shuffle.partitions=2000 to spread the 500 GB of shuffled rows more evenly across the join tasks.
  2. BThe long tasks come from null values in the join key. Add WHERE product_id IS NOT NULL to both sides before the join, removing the null-key tasks, resolving the skew, and leading the optimizer to select BroadcastHashJoin for product_catalog.
  3. CSortMergeJoin is expected here, as BroadcastHashJoin applies to tables under a fixed 10 MB limit. The 8-minute tasks show executors running out of memory, so raise spark.executor.memory to 16g and keep the sort-merge strategy for the lookup table.
  4. DThe 500 GB shuffle shows the transactions table is being collected to the driver. Set spark.driver.maxResultSize=0 to lift the driver result limit, after which the optimizer switches the join to BroadcastHashJoin and the skewed tasks disappear.
  5. ESortMergeJoin shows the optimizer lacks size statistics for product_catalog or it exceeds the broadcast threshold; the long tasks show hot-key skew. Run ANALYZE TABLE or add a broadcast() hint, and enable AQE skew join with spark.sql.adaptive.skewJoin.enabled=true.
Show answer & explanation

Correct answer: E

WHY E: This is the complete multi-factor diagnosis. (1) SortMergeJoin vs BroadcastHashJoin: for a 2 GB lookup table the optimizer should choose BroadcastHashJoin, which avoids shuffling the large table. Seeing SortMergeJoin means the optimizer either lacks up-to-date size statistics for product_catalog (stale or missing ANALYZE output, or a load path that bypasses statistics) or the size is above the broadcast threshold; without statistics the optimizer defaults to SortMergeJoin. The fix is ANALYZE TABLE product_catalog COMPUTE STATISTICS or an explicit broadcast(product_catalog) hint, which eliminates the 500 GB shuffle, the dominant cost. (2) Task skew: 3 tasks at 8 minutes against 2-second tasks is the classic data skew symptom, where certain join key values hold heavily concentrated rows (hot keys). AQE skew join optimization (spark.sql.adaptive.enabled=true and spark.sql.adaptive.skewJoin.enabled=true) detects skewed partitions at runtime and splits them into sub-partitions to balance task durations. WHY NOT A: SortMergeJoin is NOT optimal when one join side is small enough to broadcast. More shuffle partitions reduce per-partition size and help somewhat with skew, but they do not eliminate the fundamental 500 GB shuffle. WHY NOT C: The default spark.sql.autoBroadcastJoinThreshold is 10 MB but it is configurable, not fixed; with accurate statistics showing product_catalog is 2 GB, teams often raise the threshold or use hints. Also, OOM causes task failures and retries with error messages, not simply extra-long task durations. WHY NOT D: spark.driver.maxResultSize limits the size of data returned to the driver from collect() / take() operations. It is unrelated to executor-to-executor shuffle volume and does not influence join strategy selection. WHY NOT B: Null join keys CAN cause skew, but the scenario does not mention null values. More critically, filtering nulls would not change the SortMergeJoin selection or the 500 GB shuffle, which are driven by statistics and threshold settings, not null handling.

Take the full DE Professional practice test →