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.

1 Cost & Performance Optimisation

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. AACID transactions, time travel, schema enforcement, and support for Delta format — these are unique to managed tables under Unity Catalog governance.
  2. BUnity Catalog access controls, row-level security, column masks, and centralized auditing — these security features are managed-table-exclusive because external tables cannot be fully governed by Unity Catalog.
  3. CDelta Sharing, Iceberg REST Catalog access, credential vending, and column-level lineage tracking — these capabilities require managed table registration and are not supported on external tables.
  4. DAutomatic file deletion after DROP TABLE, predictive optimization, and metadata caching — but automatic liquid clustering also applies to external tables registered with a LOCATION clause.
  5. EPredictive optimization, automatic liquid clustering (with predictive optimization), metadata caching, and automatic file deletion 8 days after DROP TABLE — these are exclusive to Unity Catalog managed tables and not available for external tables.
Show answer & explanation

Correct answer: E

WHY E: The Databricks documentation explicitly lists features unique to Unity Catalog managed tables: (1) Predictive optimization — automatically runs OPTIMIZE, VACUUM, and ANALYZE; must be manually scheduled for external tables. (2) Automatic liquid clustering — intelligently selects clustering keys based on workload patterns, 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 underlying cloud storage files after 8 days; external tables require manual storage cleanup. WHY NOT A: ACID transactions, time travel, and schema enforcement are Delta Lake features available to both managed and external Delta tables — not exclusive to managed tables. WHY NOT C: Delta Sharing and credential vending work with both managed and external tables through Unity Catalog; they are not managed-table-exclusive. 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 B: Unity Catalog access controls, row/column security, and auditing apply to all tables registered in Unity Catalog, including external tables — they are not exclusive to managed tables.

2 Cost & Performance Optimisation

An analyst query against a partitioned Delta fact table 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. AThe 800-file scan indicates data skipping is working but only achieves partial pruning. This is expected behavior — partitioning on a date column is not sufficient for column-level skipping. Solution: run OPTIMIZE ... ZORDER BY (transaction_date, store_id) to further prune files.
  2. BThe 800-file scan despite a partition predicate on transaction_date indicates one of two root causes: (1) A data type mismatch between the filter literal and the partition column type (e.g., passing a STRING '2025-10-01' to a DATE-typed partition) prevents the optimizer from safely performing partition elimination. (2) For external tables, manually added partition directories not registered in the metastore would cause all files to be scanned; running MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS) registers missing partitions. For store_id, without layout optimization, data skipping is limited by per-file min-max statistics only.
  3. CThe 800-file scan is caused by the query plan generating a CartesianProduct between the transaction_date filter and the store_id filter. In the query profile, look for a CartesianProduct node and replace the AND predicate with a semi-join against a single-row DataFrame containing the target values.
  4. DThe 800-file scan indicates Adaptive Query Execution (AQE) has dynamically changed the number of shuffle partitions, causing additional partition data to be read. Disable AQE with spark.sql.adaptive.enabled=false to restore expected partition pruning behavior.
  5. EThe 800-file scan indicates the table's Delta transaction log has grown too large for the driver to parse within its timeout, causing the query planner to fall back to a full scan. Run OPTIMIZE on the table to compact transaction log files and reduce Delta log parse time.
Show answer & explanation

Correct answer: B

WHY B: Two legitimate, diagnosable root causes explain why partition pruning fails despite a partition filter, visible in the query plan. (1) Data type mismatch: If transaction_date is a DATE column but the filter literal '2025-10-01' is interpreted as a STRING, Spark's type coercion may prevent the optimizer from safely doing partition elimination — the filter appears as a post-scan predicate over all files rather than a partition pruning step. This is a common and subtle production issue; the query plan in the Spark UI would show the full scan with the filter applied after reading, not before. The fix is to ensure the literal type matches the column type (e.g., using 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 cause the planner to be unaware of those partitions and scan all files. Running MSCK REPAIR TABLE (Hive) or ALTER TABLE RECOVER PARTITIONS registers the missing partitions. Delta Lake managed tables maintain their own partition metadata via the transaction log and do not need MSCK REPAIR, but external Hive-partitioned tables do. WHY NOT A: If partition pruning were functioning correctly, the scan would be ~50 files. Scanning 800 files confirms partition pruning is NOT occurring — ZORDER would help with store_id filtering but cannot fix the broken partition pruning root cause. WHY NOT C: CartesianProduct is a JVM-level join operation between two unrelated DataFrames or subqueries. A simple AND predicate between two filter conditions on the SAME table never produces a CartesianProduct — this reveals a fundamental misunderstanding of query plans. WHY NOT D: AQE optimizes shuffle partition count and join strategies at runtime, but it does NOT affect partition elimination on Delta tables. Partition elimination is a static optimization done during query planning (before execution), completely independent of AQE. WHY NOT E: Delta's OPTIMIZE command compacts data files, not the transaction log. Delta log checkpointing is a separate automatic process. An oversized transaction log would slow query planning latency, not cause a fallback to full table scans.

3 Cost & Performance Optimisation

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. AThe SortMergeJoin instead of BroadcastHashJoin indicates the optimizer either lacks accurate table size statistics for product_catalog or its size exceeds the broadcast threshold — causing a 500 GB full shuffle for sort-merge. Skewed task durations indicate hot-key data skew. Remediation: (1) Run ANALYZE TABLE product_catalog COMPUTE STATISTICS or apply a broadcast(product_catalog) hint to eliminate the shuffe. (2) Enable AQE skew join optimization with spark.sql.adaptive.enabled=true and spark.sql.adaptive.skewJoin.enabled=true.
  2. BThe SortMergeJoin is the optimal strategy for large tables. The high task skew is caused by Spark's default partition count — set spark.sql.shuffle.partitions=2000 to distribute data more evenly across tasks.
  3. CThe SortMergeJoin is expected because BroadcastHashJoin only applies to tables under 10 MB. For larger lookup tables, SortMergeJoin is always the correct strategy. The 8-minute skewed tasks indicate executor OOM — increase executor memory via spark.executor.memory=16g.
  4. DThe 500 GB shuffle indicates the transactions table is being collected to the driver. Disable driver result collection with spark.driver.maxResultSize=0, which will automatically allow the optimizer to switch to BroadcastHashJoin.
  5. EThe skewed tasks are caused by null values in the join key. Add WHERE product_id IS NOT NULL to both sides of the join before the join operation, which eliminates null-key tasks, resolves the skew, and allows the optimizer to select BroadcastHashJoin.
Show answer & explanation

Correct answer: A

WHY A: This is the complete, technically correct multi-factor diagnosis. (1) SortMergeJoin vs BroadcastHashJoin: For a 2 GB small table, the optimizer should choose BroadcastHashJoin, which avoids shuffling the large table entirely. Seeing SortMergeJoin means the optimizer either lacks up-to-date size statistics for product_catalog (stale or missing ANALYZE output) or the table was loaded in a way that bypasses statistics. Without statistics, the optimizer defaults to SortMergeJoin. The fix is to run ANALYZE to update statistics, or explicitly use a broadcast() hint. This eliminates the 500 GB shuffle, which is the dominant cost. (2) Task skew: The 3 tasks with 8-minute durations vs. 2-second tasks are classic data skew symptoms — certain join key values have heavily concentrated rows (hot keys). AQE's skew join optimization (spark.sql.adaptive.skewJoin.enabled=true) detects skewed partitions at query runtime and automatically splits them into sub-partitions to balance task durations. WHY NOT B: SortMergeJoin is NOT optimal when one join side is small enough to broadcast. Adding more shuffle partitions reduces per-partition size and helps skew, but does not eliminate the fundamental 500 GB shuffle overhead. WHY NOT C: The default spark.sql.autoBroadcastJoinThreshold is 10 MB but is configurable. With accurate statistics showing product_catalog is 2 GB, DBAs often raise this threshold or use hints. Additionally, 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 E: Null join keys CAN cause skew, but the scenario does not mention null values. More critically, filtering nulls alone would not resolve the SortMergeJoin selection or the underlying 500 GB shuffle — which are driven by statistics and threshold settings, not null handling.

4 Cost & Performance Optimisation

A Silver layer Delta table is partitioned by transaction_date and a nightly maintenance job runs OPTIMIZE ... ZORDER BY (customer_id) on the latest partition. The team is evaluating a migration to liquid clustering. Which of the following accurately describes the KEY behavioral difference between Z-ordering and liquid clustering's OPTIMIZE that is most relevant to this decision?

  1. AZ-ordering and liquid clustering both use the same underlying Hilbert space-filling curve algorithm for data co-location. The only practical difference is that liquid clustering runs automatically via predictive optimization, while ZORDER requires explicit scheduling.
  2. BZ-ordering is idempotent: running it multiple times on the same data converges and subsequent runs become no-ops with no rewriting. Liquid clustering OPTIMIZE is also idempotent but additionally supports concurrent writes via row-level concurrency enabled by deletion vectors.
  3. CZ-ordering is NOT incremental: each OPTIMIZE ... ZORDER BY run potentially rewrites previously Z-ordered files in the partition to incorporate new data. Liquid clustering's OPTIMIZE IS incremental — it only rewrites data files that have not yet been clustered or need reclustering, skipping files already in a good state and dramatically reducing write amplification for frequently-written tables.
  4. DThe primary difference is capacity: liquid clustering supports up to 32 clustering keys while Z-order only supports a maximum of 4 columns. For multi-dimensional filtering workloads, liquid clustering is significantly superior.
  5. EZ-ordering can be applied on top of an existing partitioned table without any schema changes. Liquid clustering requires dropping existing partitions using ALTER TABLE REMOVE PARTITIONING before enabling, which triggers a full table rewrite.
Show answer & explanation

Correct answer: C

WHY C: This is the technically accurate and documentation-backed key distinction. ZORDER is explicitly documented as NOT idempotent: 'Z-ordering is not idempotent but aims to be an incremental operation.' For a table with daily appends, each new batch of data is scattered across files and must be re-incorporated into the Z-order layout on the next OPTIMIZE run, potentially causing wide rewrites of already-Z-ordered files in the same partition. Liquid clustering's OPTIMIZE is specifically designed to be INCREMENTAL — it only rewrites data files that need clustering (haven't been processed or have enough unclustered data to justify rewriting), skipping files already in a good clustering state. This dramatically reduces write amplification for tables with frequent incremental writes, making nightly maintenance jobs shorter and less resource-intensive. WHY NOT A: Z-ordering in Delta Lake does not use Hilbert curves in the same way. More importantly, the incremental vs. non-incremental nature of OPTIMIZE is a more fundamental and exam-relevant difference than scheduling method. WHY NOT B: Z-ordering is explicitly documented as NOT idempotent. It aims to be incremental but is not guaranteed to converge or be a no-op on subsequent runs. WHY NOT D: Liquid clustering supports a maximum of FOUR clustering keys — not 32. The 32-column reference describes the default number of columns for which statistics are collected, not clustering key capacity. WHY NOT E: ALTER TABLE REMOVE PARTITIONING is NOT a supported Delta Lake command. Migration from partitioned + ZORDER to liquid clustering requires a CTAS approach without PARTITION BY, not an ALTER TABLE modification.

5 Cost & Performance Optimisation

An engineer is building a Gold layer aggregation table that must propagate updates and deletes from a Silver layer Delta table. Two approaches are being evaluated: (A) Stream directly from the Silver table using spark.readStream.table('silver'), and (D) Stream from the Silver table's change data feed using .option('readChangeFeed', 'true'). The Silver table receives frequent UPDATE and DELETE operations. Which choice is correct and why?

  1. AOption A is preferred: streaming directly from a Delta table is the simplest approach and Databricks transparently handles updates and deletes in streaming mode by tracking changed files automatically.
  2. BBoth options are equivalent for UPDATE and DELETE handling. The choice between direct streaming and CDF streaming should be based solely on whether the target table needs row-level _change_type metadata for compliance auditing.
  3. COption A is preferred but only with .option('skipChangeCommits', 'true') added — this ensures updates and deletes are silently buffered and then applied as batch updates to the Gold table at the end of each micro-batch.
  4. DOption B is preferred: Change data feed (CDF) exposes per-row change events with _change_type values (insert, update_preimage, update_postimage, delete), enabling downstream logic to correctly propagate UPDATE and DELETE operations. Direct streaming (Option A) from a table with non-append operations throws an exception because Structured Streaming expects append-only sources by default.
  5. EOption B is not needed because setting delta.enableChangeDataFeed = true on the Silver table automatically converts all downstream streams into CDF streams — no .option('readChangeFeed', 'true') is required on the consuming query.
Show answer & explanation

Correct answer: D

WHY D: Structured Streaming from a Delta table (Option A) is designed for append-only sources. When the source table has non-append operations (UPDATE, DELETE, MERGE), the streaming engine throws an AnalysisException because modified files cannot be re-emitted without breaking exactly-once semantics. The workaround skipChangeCommits IGNORES updates/deletes rather than propagating them — causing data drift in the Gold table. CDF is the correct solution: with readChangeFeed: true, each micro-batch streams per-row change events including update_preimage (row before change), update_postimage (row after change), and delete events. This enables precise downstream propagation using merge/upsert foreachBatch logic. WHY NOT A: Direct streaming without skipChangeCommits will throw an exception when the Silver table has UPDATE/DELETE operations. It does NOT transparently handle change events. WHY NOT C: skipChangeCommits IGNORES (skips) file-changing operations entirely — it does not buffer and later apply them. Data changed via UPDATE/DELETE with skipChangeCommits enabled is simply dropped and never propagates downstream, causing data quality issues in the Gold layer. WHY NOT B: The options are NOT equivalent for UPDATE/DELETE handling. Direct streaming will fail or silently lose changes on a non-append-only source. CDF correctly surfaces all row-level change events. This is not merely a metadata preference. WHY NOT E: delta.enableChangeDataFeed = true is a table-level property that enables the RECORDING of CDF events to the table's change log. It does NOT automatically configure consuming streams to read from the CDF feed. The reader must explicitly opt in with .option('readChangeFeed', 'true') to receive change events rather than the regular append-only stream.

6 Cost & Performance Optimisation

A data platform team runs dozens of external Delta tables and manually schedules OPTIMIZE and VACUUM jobs to control small files and stale data, which is costly to maintain and often mistimed. They are told that converting to Unity Catalog managed tables and enabling predictive optimization would reduce this operational overhead. Which statement correctly explains the benefit and the key difference from their current setup?

  1. AConverting to managed tables disables OPTIMIZE and VACUUM entirely because managed tables never accumulate small files, so the team can simply delete their maintenance jobs and rely on the write path to always produce optimally sized files.
  2. BManaged and external tables are identical for maintenance purposes; the only benefit of converting is a shorter table name, and Predictive Optimization must still be triggered manually per table on a cron schedule, so the operational overhead is unchanged.
  3. CUnity Catalog managed tables let Databricks own the table's storage lifecycle, and Predictive Optimization automatically runs maintenance operations like OPTIMIZE and VACUUM when beneficial — removing the need to hand-schedule and tune those jobs. External tables, whose storage the customer manages, do not get this automatic managed maintenance in the same way.
  4. DPredictive Optimization only applies to external tables and requires the team to keep their manual VACUUM jobs, since it optimizes file sizing but never reclaims storage; managed tables are not supported by Predictive Optimization.
  5. EThe benefit comes entirely from Photon, not table type: enabling Photon on the warehouse auto-compacts all Delta tables regardless of managed vs external, so the table conversion is unnecessary and Predictive Optimization is just a billing feature.
Show answer & explanation

Correct answer: C

WHY C: Unity Catalog managed tables put the storage lifecycle under Databricks' control, and Predictive Optimization automatically performs maintenance such as OPTIMIZE (compaction/clustering) and VACUUM when it determines they are worthwhile — eliminating the need to hand-schedule and tune those jobs. This managed automatic maintenance is a benefit of managed tables that externally-managed tables do not receive the same way. WHY NOT B: Managed vs external is not merely a naming difference, and Predictive Optimization is automatic, not a manual per-table cron — it directly reduces overhead. WHY NOT A: Managed tables still accumulate small files from streaming/frequent writes; OPTIMIZE/VACUUM are not disabled, they are automated. WHY NOT D: Predictive Optimization targets managed tables and does perform maintenance including VACUUM-style storage reclamation; the claim it excludes managed tables is backwards. WHY NOT E: Photon accelerates query execution; it does not auto-run OPTIMIZE/VACUUM maintenance, and the maintenance automation comes from managed tables + Predictive Optimization, not Photon.

7 Cost & Performance Optimisation

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 automatically rewrite Parquet files during each DELETE or UPDATE operation. If old files are still present, it indicates a checkpointing failure. Run OPTIMIZE with a targeted WHERE clause to force file rewriting.
  2. BDeletion vectors only apply to DELETE operations. UPDATE operations always rewrite the affected Parquet files immediately. To purge soft-deleted rows from DELETE operations, run OPTIMIZE FULL on the table.
  3. CDeletion vectors mark rows as soft-deleted in the transaction log. Running VACUUM immediately rewrites all affected Parquet data files and removes deletion vector markers, ensuring a clean physical state.
  4. DDeletion vectors track row modifications in a sidecar file. Running ALTER TABLE <table_name> DROP FEATURE 'deletionVectors' physically purges all marked rows and removes the feature from the table protocol.
  5. EDeletion vectors perform soft-deletes by marking rows as modified without rewriting Parquet files. Physical changes are applied during OPTIMIZE or auto-compaction. To guarantee all deletion-vector-marked rows are physically purged, run REORG TABLE <table_name> APPLY (PURGE) followed by VACUUM to remove the old unreferenced files.
Show answer & explanation

Correct answer: E

WHY E: 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 requiring guaranteed physical removal of all deletion-vector-marked rows, REORG TABLE ... APPLY (PURGE) is the correct command — it rewrites all data files containing records with deletion vector modifications. After REORG completes, VACUUM should be run to remove the old unreferenced files. WHY NOT A: Deletion vectors purposely avoid rewriting Parquet files during DELETE/UPDATE — that is the performance benefit. OPTIMIZE with a WHERE clause does incremental clustering/compaction but does not guarantee all deletion vector sidecar records 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. VACUUM deletes old, unreferenced files — not files that are still referenced by the current table version. WHY NOT D: DROP FEATURE removes the deletion vector capability from the table protocol but is a protocol downgrade operation, not a data purge mechanism. The correct approach for compliance purges is REORG TABLE ... APPLY (PURGE). WHY NOT B: Deletion vectors apply to DELETE, UPDATE, and MERGE operations, not just DELETE. UPDATE operations with deletion vectors enabled also use soft-deletes rather than immediate file rewrites (especially when Photon is available for MERGE and UPDATE operations).

8 Cost & Performance Optimisation

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 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.
  2. BLiquid 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.
  3. CALTER 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.
  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: A

WHY A: 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 B: Liquid clustering is NOT compatible with partitioning — attempting to add CLUSTER BY to a partitioned table will fail. WHY NOT C: 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.

Take the full DE Professional practice test →