Cost & Performance Optimisation: Databricks Data Engineer Professional Practice Questions

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.

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.

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.

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.

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.

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.

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.

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.