Databricks Data Engineer Professional
Section 5 · 15%Cost & Performance Optimization

Cost and performance

Managed tables, Predictive Optimization, Liquid Clustering, deletion vectors, caching, Change Data Feed and the query profile.

This section tests whether you can pick the least-effort way to keep tables fast and cheap, and read the evidence (a query profile, a change feed) that tells you what to change. The biggest way people lose marks is reaching for a manual technique such as partitioning, ZORDER or a scheduled OPTIMIZE job when a managed feature already does the work.

PracticeS5 practice bank →

9 items, practice or timed exam mode.

Walk the guide, line by line

Each row is one line of the official guide, in its own words on the left. Read across: plain words, what a question looks like, the answer, and the trap.

Guide lineIn plain wordsA question looks likeThe answerThe trap
5.1 Explain how Unity Catalog managed tables, Predictive Optimization, and Liquid Clustering reduce operational and maintenance overhead for a given workload.A Unity Catalog managed table is a table whose files Unity Catalog owns and lays out for you. Predictive Optimization is a service that runs maintenance (compaction, clustering, VACUUM, statistics) on those tables by itself. Liquid Clustering is a flexible data layout that replaces partitioning and ZORDER. Together they remove the maintenance jobs a team would otherwise write and watch.A team spends every week tuning OPTIMIZE and VACUUM schedules for dozens of tables and wants that work gone. The question asks which combination removes the most operational effort.Use Unity Catalog managed tables with Predictive Optimization turned on, and cluster the tables with Liquid Clustering. Predictive Optimization only works on managed tables, runs OPTIMIZE (compaction and incremental clustering), VACUUM and ANALYZE, and is enabled by default for accounts, with catalogs and schemas able to override it. It runs on serverless jobs compute that Databricks bills for, so there is nothing for you to size.
Try it: In a Free Edition notebook, run DESCRIBE DETAIL on a managed table and look at the clustering and file-count columns.
Putting the tables in external storage and scheduling your own maintenance jobs. Predictive Optimization does not run on external tables, so every table you keep external stays a manual job.
Runner-up wins when: External tables win when another system outside Databricks must own and write the files, because a managed table hands file ownership to Unity Catalog.
5.2 Choose the appropriate Delta optimization technique (deletion vectors, Liquid Clustering, CLUSTER BY AUTO) for a given table access pattern.Deletion vectors let DELETE, UPDATE and MERGE mark rows as removed in a small side file instead of rewriting the whole Parquet file. Liquid Clustering with CLUSTER BY lets you choose columns to organise data by. CLUSTER BY AUTO lets Predictive Optimization choose and change those columns for you from how the table is queried.A table is queried with filters that change from quarter to quarter and nobody knows which columns will matter next. Another table takes frequent single-row updates that rewrite large files. The question asks which feature fits each pattern.For unpredictable or shifting filter columns, use CLUSTER BY AUTO on a managed table, which needs Predictive Optimization and Databricks Runtime 15.4 LTS or above. It changes keys only when the expected data-skipping savings outweigh the clustering cost. For frequent row-level changes that rewrite big files, enable deletion vectors, which speed up DELETE, UPDATE and MERGE; run REORG TABLE with APPLY (PURGE) when you must physically remove the soft-deleted rows.
Try it: Create a small managed table, run ALTER TABLE t CLUSTER BY AUTO, and read the table properties afterwards.
Thinking deletion vectors improve read filtering. They only make writes cheaper; they do nothing for data skipping, and deleted rows stay in the files until a rewrite or purge.
Runner-up wins when: Explicit CLUSTER BY on named columns wins over AUTO when the filter columns are known and stable and you want to control them yourself.
5.3 Understand how caching (Delta cache) improves query performance for repeated reads of the same data.The disk cache (the docs' current name for the Delta cache) keeps a local copy of Parquet and Delta files on each worker's SSD the first time they are read from cloud storage. Later reads of the same files come from local disk and are faster.A dashboard reruns similar queries over the same large table all day on the same cluster and the reads from cloud storage dominate the time. The question asks what speeds up the repeated reads.Use workers with local SSD storage so the disk cache is on. It fills on first read, is automatic with no code changes, and evicts stale entries by itself when files are created, deleted or rewritten, so results stay correct. It is controlled by spark.databricks.io.cache.enabled.Calling df.cache() everywhere. That is the Spark cache, which holds memory blocks, needs code changes and is not the right tool for plain repeated Parquet or Delta reads; the docs recommend the automatic disk cache.
Runner-up wins when: The Spark cache wins when you reuse an expensive computed DataFrame, not raw files, several times inside one job.
5.4 Apply Change Data Feed (CDF) to expose row-level changes (updates/deletes) for efficient incremental downstream processing.Change Data Feed (CDF) records which rows were inserted, updated or deleted in each table version, so a downstream job can read only the changes. Each change row carries a _change_type of insert, update_preimage, update_postimage or delete, plus the commit version.A downstream table must apply only the rows that changed in a source table, including deletes, instead of rescanning the source every run. The question asks how to expose those changes.Enable CDF on the source (delta.enableChangeDataFeed = true on a Delta table; newer runtimes also offer automatic CDF on managed tables with row tracking) and read it with table_changes or the readChangeFeed option, batch or streaming. Only changes made after it was enabled are recorded. Apply them downstream with a MERGE or AUTO CDC.
Try it: Create a Delta table with CDF on, update one row, then run SELECT * FROM table_changes('t', 0) and look at the _change_type column.
Diffing two snapshots or using time travel to compare versions. That reads whole tables each run and is the expensive full scan that CDF avoids.
Runner-up wins when: Reading the source as a plain stream wins when the source is append-only, because an append-only stream already gives only the new rows and CDF adds nothing.
5.5 Use the query profile to identify performance bottlenecks, such as data-skipping inefficiencies, join strategies, and shuffle operations.The query profile is a visual breakdown of how a query ran: each operator with its time, memory and rows, the share of files pruned by data skipping, the joins and the shuffles (data moved between machines). You read it to find the slowest operator before you change anything.A slow query is shown as a query profile. One scan reads almost every file and another operator shuffles a huge amount of data. The question asks what to look at and fix.Open the profile, check the top operators and the DAG, and look at the files-pruned figure on each scan: low pruning means the layout does not match the filters, so change clustering. A very heavy shuffle or an exploding join points to the join or the data, not to the cluster size.
Try it: Run any join in a Free Edition SQL editor and open its query profile from the history to find the join and scan operators.
Adding more compute straight away. That raises cost but leaves a bad file layout or an exploding join untouched.
Runner-up wins when: More compute wins when the profile shows healthy pruning and sensible joins and the time is simply spread across well-sized tasks.
5.6 Compare Liquid Clustering vs partitioning/ZORDER for a given table size and query pattern.Partitioning splits a table into folders by a column value, and ZORDER sorts data inside files as a one-time or repeated step. Liquid Clustering replaces both with keys you can change without rewriting existing data. It is not compatible with partitioning or ZORDER on the same table.A fast-growing table is filtered on a high-cardinality column and the access pattern is expected to change. The question asks whether to partition, ZORDER or cluster.Use Liquid Clustering. The docs recommend it for all new tables and say it helps with high-cardinality filters, skew, fast growth, concurrent writes and changing access patterns. You can change keys with ALTER TABLE CLUSTER BY, and existing data is reclustered by OPTIMIZE FULL; up to four keys are allowed.Partitioning by the high-cardinality column. It creates huge numbers of tiny folders and files, and changing it later means rewriting the table.
Runner-up wins when: Partitioning wins when an existing very large table is always filtered by one low-cardinality column such as a date and downstream tools or lifecycle rules depend on those folders.

Mind map: Choosing the layout or maintenance feature

People assume layout and maintenance are manual jobs that must be tuned per table, when the managed features choose and run them for you.

What you observeWhat it points toWhat to do (and when the runner-up wins)
Many tables, a team spending time on OPTIMIZE and VACUUM schedulesManaged tables plus Predictive OptimizationTurn Predictive Optimization on. If the tables must stay external, you schedule the jobs yourself, so the answer flips.
Filter columns are unknown or keep changingCLUSTER BY AUTOLet Predictive Optimization pick the keys. If the columns are known and stable, explicit CLUSTER BY wins.
Single-row updates rewrite large filesDeletion vectorsEnable them on the table. They do not help reads, and soft-deleted rows need REORG with PURGE to be physically removed.
Hot table, same files read all dayDisk cacheUse SSD worker types. Spark cache wins only for reused computed results inside one job.

Mind map: Reading a slow query

People assume a slow query needs a bigger cluster, when the profile usually shows where the work is wasted.

What you observeWhat it points toWhat to do (and when the runner-up wins)
Scan reads nearly all files, low pruningLayout does not match filtersCluster on the filter columns.
One huge shuffle or exploding joinJoin or data shapeFix the join keys or filters first. Raising shuffle partitions does not remove the volume.
Pruning good, joins sensible, time spread across tasksGenuinely compute-boundAdd compute; here the usual answer flips.
Downstream job rescans the whole source each runMissing change trackingUse Change Data Feed. If the source is append-only, a plain stream wins.