Overview
Section 5 has six objectives about making Delta tables and queries cheaper and faster. The first explains how Unity Catalog managed tables with Predictive Optimization and Liquid Clustering remove manual maintenance such as scheduled OPTIMIZE and VACUUM jobs. The second asks you to choose a Delta technique, such as deletion vectors, Liquid Clustering with chosen keys, or CLUSTER BY AUTO, for an access pattern.
The third covers the disk cache, earlier called the Delta cache, which keeps copies of remote files on local SSDs for repeated reads. The fourth is Change Data Feed, which exposes row-level inserts, updates and deletes so downstream jobs process only changes. The fifth uses the query profile to spot data skipping problems, poor join strategies and heavy shuffles. The sixth compares Liquid Clustering with partitioning and ZORDER for a given table size and query pattern.
How it's tested
Items describe a table's size, write pattern and query filters, or show a query profile excerpt, and ask which optimisation helps most. Several options are usually valid techniques; the right one matches the specific bottleneck, such as poor file pruning, slow deletes or a skewed shuffle.
-
Pick the layout technique
Table size, filter columns and change frequency are given, and you choose Liquid Clustering, partitioning, ZORDER or automatic clustering.
-
Speed up changes
Frequent updates, deletes or merges are slow, and you identify deletion vectors or Change Data Feed as the fix.
-
Read the profile
A query profile shows files read, spill or join type, and you diagnose the bottleneck.
A 300 GB managed table is filtered by customer_id one month and by region the next as access patterns change. Partitioning by either column would create small files and lock in one pattern. ZORDER needs full rewrites. Liquid Clustering lets you change clustering keys without rewriting existing data, and CLUSTER BY AUTO can pick keys from query history, so Liquid Clustering is the answer.
Objectives 6
Official objectives from the Databricks outline, in outline order.
5.1 Explain how Unity Catalog managed tables, Predictive Optimization and Liquid Clustering reduce operational overhead for a workloadLetting the platform run table maintenance.
- What it means
- Unity Catalog managed tables let Databricks control storage location and lifecycle. Predictive Optimization then decides when to run OPTIMIZE, VACUUM and statistics collection on those tables, based on usage. Liquid Clustering organises data by clustering keys incrementally, so no separate partition or ZORDER maintenance jobs are needed.
- What to understand
- Predictive Optimization works only on managed tables and is enabled at account, catalog or schema level. It removes hand-scheduled maintenance jobs and the risk of forgetting VACUUM. Liquid Clustering works with it so new data is clustered as maintenance runs. External tables do not get these automatic operations, which is often the hinge in a question.
- How it shows up
- Questions ask how to cut maintenance effort or why automatic optimisation is not happening on a table.
- Common confusion
- Expecting Predictive Optimization to run on external tables; it applies to Unity Catalog managed tables.
5.2 Choose the appropriate Delta optimization technique (deletion vectors, Liquid Clustering, CLUSTER BY AUTO) for a given table access patternMatching Delta features to read and write patterns.
- What it means
- Deletion vectors mark removed or changed rows without rewriting whole Parquet files, which speeds up DELETE, UPDATE and MERGE. Liquid Clustering co-locates data by chosen keys to improve file skipping. CLUSTER BY AUTO lets Databricks pick and change clustering keys based on observed queries.
- What to understand
- Use deletion vectors for tables with frequent small changes; the trade-off is extra work at read time until files are rewritten. Choose explicit clustering keys when filter columns are known and stable, and automatic clustering when they vary or are unknown. Up to four clustering keys can be chosen, and high-cardinality filter columns benefit most.
- How it shows up
- Expect a description of writes and filters and a choice among these three features.
- Common confusion
- Using deletion vectors to speed up reads; they speed writes and add a little read work.
5.3 Understand how caching (Delta cache) improves query performance for repeated reads of the same dataLocal SSD caching of remote data files.
- What it means
- The disk cache, earlier called the Delta cache, stores copies of remote Parquet and Delta data files on the local SSDs of compute nodes after the first read. Later reads of the same files come from local disk, which is much faster than cloud object storage.
- What to understand
- It works automatically on supported instance types, SQL warehouses and serverless compute, and stays consistent because changed files get new names. It differs from Spark's cache() and persist(), which hold DataFrame results in memory and must be managed manually. The disk cache helps repeated reads of the same data, not first-time scans.
- How it shows up
- Questions compare the disk cache with Spark caching or ask why a repeated dashboard query became fast.
- Common confusion
- Calling df.cache() to speed repeated table reads when the disk cache already handles it.
5.4 Apply Change Data Feed (CDF) to expose row-level changes (updates and deletes) for efficient incremental downstream processingReading only what changed in a Delta table.
- What it means
- Change Data Feed records row-level changes for a Delta table once delta.enableChangeDataFeed is set to true. Readers get each changed row with _change_type, _commit_version and _commit_timestamp columns, either in batch with table_changes or as a stream with the readChangeFeed option.
- What to understand
- Change types are insert, delete, update_preimage and update_postimage. Downstream jobs process only changes since a version, which is much cheaper than comparing full snapshots. CDF only covers changes after it is enabled and is subject to the table's retention settings. It also lets OpenSharing recipients consume changes when history is shared.
- How it shows up
- A downstream table must stay in sync with updates and deletes, and you choose CDF and how to read it.
- Common confusion
- Expecting CDF to return changes made before it was enabled on the table.
5.5 Use the query profile to identify performance bottlenecks, such as data-skipping inefficiencies, join strategies and shuffle operationsDiagnosing slow queries from operator metrics.
- What it means
- The query profile is a visual plan of an executed query, showing each operator with time spent, rows processed, bytes read, files pruned and memory spilled. It is available for SQL warehouses and serverless queries and complements the Spark UI on classic compute.
- What to understand
- Poor data skipping shows many files read compared to files pruned, pointing to missing clustering or non-selective filters. Join nodes reveal broadcast hash, shuffle hash or sort merge strategies. Large shuffle exchanges and spill to disk show memory pressure or skew. Find the operator with the highest time share first, then match it to a fix.
- How it shows up
- Expect a profile summary and a question about the most likely cause or best fix.
- Common confusion
- Adding compute when the profile shows full scans that clustering would prune.
5.6 Compare Liquid Clustering vs partitioning and ZORDER for a given table size and query patternChoosing a data layout strategy.
- What it means
- Partitioning splits a table into directories by column values, ZORDER co-locates related values within files through a rewrite, and Liquid Clustering organises data by keys incrementally with keys that can change. Databricks recommends Liquid Clustering for new tables.
- What to understand
- Partitioning only helps large tables, typically over a terabyte, on low-cardinality columns, and over-partitioning creates small files. ZORDER is not incremental and must be rerun on new data. Liquid Clustering replaces both, cannot be combined with them on the same table, and handles high-cardinality keys and changing query patterns.
- How it shows up
- A table size, write rate and filter set are given, and you pick the layout.
- Common confusion
- Partitioning a small table by a high-cardinality column and creating thousands of tiny files.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Predictive Optimization
Automatic OPTIMIZE, VACUUM and statistics on managed tables.
- Remember
- Managed tables only.
- Tested as
- Maintenance overhead questions.
- Trap
- Expecting it on external tables.
-
Liquid Clustering
Incremental clustering by up to four changeable keys.
- Remember
- Recommended default for new tables.
- Tested as
- Layout questions.
- Trap
- Combining it with partitioning.
-
CLUSTER BY AUTO
Databricks chooses clustering keys from query history.
- Remember
- Needs Predictive Optimization.
- Tested as
- Unknown or shifting filters.
- Trap
- Using it when keys are known and stable.
-
Deletion vectors
Mark changed rows without rewriting files.
- Remember
- Speeds DELETE, UPDATE and MERGE.
- Tested as
- Write-heavy tables.
- Trap
- Thinking they speed reads.
-
Disk cache
Local SSD copies of remote data files.
- Remember
- Automatic and consistent.
- Tested as
- Repeated read scenarios.
- Trap
- Confusing it with Spark cache().
-
Change Data Feed
Row-level change records for a Delta table.
- Remember
- Enable with delta.enableChangeDataFeed.
- Tested as
- Incremental downstream processing.
- Trap
- Expecting history before enablement.
-
_change_type values
insert, delete, update_preimage, update_postimage.
- Remember
- Filter out preimages for current values.
- Tested as
- CDF consumption questions.
- Trap
- Treating preimages as new rows.
-
Query profile
Operator-level metrics for executed queries.
- Remember
- Look at files pruned, spill and join type.
- Tested as
- Bottleneck diagnosis.
- Trap
- Scaling compute before reading it.
-
Data skipping
Using file statistics to avoid reading irrelevant files.
- Remember
- Improved by clustering on filter columns.
- Tested as
- Scan-heavy queries.
- Trap
- Filtering on columns without stats.
-
Partitioning threshold
Only worth it for very large tables on low-cardinality columns.
- Remember
- Small files are the main risk.
- Tested as
- Layout trade-offs.
- Trap
- Partitioning by date and hour on a small table.
-
ZORDER
Co-locates values within files during OPTIMIZE.
- Remember
- Not incremental; rerun after new data.
- Tested as
- Legacy layout comparison.
- Trap
- Assuming new data is ordered automatically.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Liquid Clustering vs. partitioning vs. ZORDER
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Liquid Clustering | Incremental clustering by changeable keys | No rewrite to change keys | Combined with partitions on one table |
| Partitioning | Directory per column value | Only for very large tables and low cardinality | Small file explosion |
| ZORDER | Multi-column co-location by rewrite | Must be rerun for new data | Assumed to be incremental |
Disk cache vs. Spark cache
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Disk cache | Remote files copied to local SSD | Automatic, survives across queries | Manually enabling it on serverless |
| Spark cache()/persist() | DataFrame held in executor memory or disk | Manual, per session | Forgetting to unpersist and losing memory |
Getting changes downstream
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Full table re-read | Scan everything and compare | Simple but expensive | Used for large, mostly static tables |
| Change Data Feed | Read only changed rows by version | Includes updates and deletes | Not enabled before changes occurred |
| Streaming read of Delta | Reads appended data | Fails or skips on updates without options | Used where updates must flow downstream |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
ZORDER keeps up
- What candidates pick
- Assume ZORDER automatically orders newly written data.
- Why it looks right
- OPTIMIZE ZORDER was run once.
- Why it's wrong
- ZORDER is not incremental; new files stay unordered until it runs again.
Clue to watch for The table receives regular appends.
-
Deletion vectors for reads
- What candidates pick
- Enable deletion vectors to make scans faster.
- Why it looks right
- Optimisation features usually help reads.
- Why it's wrong
- They speed writes by avoiding file rewrites; readers must apply the vectors until files are compacted.
Clue to watch for The bottleneck is slow MERGE or DELETE.
-
Partition everything
- What candidates pick
- Partition a 50 GB table by customer_id.
- Why it looks right
- Partitioning is a familiar optimisation.
- Why it's wrong
- High cardinality on a modest table creates many small files and slows queries; clustering fits better.
Clue to watch for Table size is well under a terabyte.
-
Predictive Optimization on external
- What candidates pick
- Enable Predictive Optimization to maintain an external table.
- Why it looks right
- It sounds account-wide.
- Why it's wrong
- It applies only to Unity Catalog managed tables; external tables need manual maintenance or conversion.
Clue to watch for The table is registered as EXTERNAL.
-
Bigger warehouse first
- What candidates pick
- Increase warehouse size for a slow filtered query.
- Why it looks right
- More compute usually means faster.
- Why it's wrong
- If the profile shows few files pruned, the fix is layout, not size.
Clue to watch for Files read are close to total files.
-
CDF retroactive
- What candidates pick
- Enable Change Data Feed and read last month's changes.
- Why it looks right
- CDF reads table history.
- Why it's wrong
- Change records exist only for commits after enablement, within retention.
Clue to watch for The feature was just turned on.
Answer framework
Follow these steps when a Section 5 question asks how to cut cost or latency.
- Check whether the table is a Unity Catalog managed table, since Predictive Optimization and automatic clustering depend on it.
- Identify the bottleneck from the scenario or query profile: scan volume, slow writes, repeated reads, shuffle, skew or spill.
- For scan volume, choose Liquid Clustering on filter columns or CLUSTER BY AUTO, and avoid partitioning unless the table is very large.
- For slow updates and merges, enable deletion vectors so small changes avoid full file rewrites; for downstream sync, enable Change Data Feed and read by version.
- For repeated reads, rely on the disk cache before manual caching, and confirm improvements by comparing query profiles before and after each change.
Practice question
Want more like this? The Databricks DE Pro practice test has questions for every domain.
Checklist
0 of 11
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Treat this section as a set of trade-offs you can measure. Create a managed table of a few million rows and query it with a selective filter, noting files read in the query profile. Add Liquid Clustering on the filter column, run OPTIMIZE, and compare files pruned. Then try CLUSTER BY AUTO and read how Databricks chooses keys.
Next, run repeated MERGE statements with and without deletion vectors and compare the files written in the table history. Enable Change Data Feed, make some updates and deletes, and read the changes with table_changes so the four change types are familiar.
Run the same query twice on a SQL warehouse to see the disk cache effect, and contrast it with calling cache() on a DataFrame in a notebook.
Spend the remaining time reading query profiles: look for scans with little pruning, sort merge joins that could be broadcast, and spill. Finish by writing a one-line rule for when partitioning still makes sense, because many distractors rely on old partitioning habits.
FAQ
How much of the exam is Cost & Performance Optimization?
Cost & Performance Optimization is 15% of the scored content, about nine questions, making it the second-largest section.
Is ZORDER still tested?
Yes, as a comparison point. You must know when Liquid Clustering replaces partitioning and ZORDER and why ZORDER is not incremental.
Is the Delta cache the same as the disk cache?
Yes. The guide uses Delta cache, the earlier name; current documentation calls it the disk cache. It stores remote file copies on local SSDs.
What does Predictive Optimization run?
It runs OPTIMIZE, VACUUM and statistics collection on Unity Catalog managed tables when Databricks predicts a benefit.
When should I enable Change Data Feed?
When downstream tables or consumers need row-level updates and deletes from a Delta table without re-reading it entirely.
Where do I find the query profile?
Open a query in query history for a SQL warehouse or serverless compute and view its profile, which lists operators with time, rows and files pruned.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 5 knowledge
You have covered managed table automation, clustering choices, deletion vectors, caching, Change Data Feed and query profiles. Try timed questions that mix layout and diagnosis.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the Databricks DE Pro AI tutor Get any concept explained your way