Overview
Data Manipulation is a renamed and broadened version of the earlier Data Transformation, Cleansing, and Quality section, and it has four objectives. The first is advanced transformation with window functions, joins and aggregations on large datasets in Spark SQL and PySpark, where the exam cares about correctness and cost, such as ranking within groups or choosing a join that avoids a huge shuffle.
The second is modelling and querying semi-structured data with the VARIANT type, using parse_json, variant_get and path syntax. The third is calling models from SQL or pipelines with AI functions, especially ai_query, to enrich or classify records. The fourth is data quality: expectations in Lakeflow Pipelines that keep, drop or fail on bad records, and the quarantine pattern that routes failures to a separate table for review. Together they describe what happens between raw ingestion and trustworthy silver and gold tables.
How it's tested
Questions often show a short query or transformation and a desired output, then ask which change produces it. Others describe a data quality or enrichment requirement and ask which feature, function or expectation policy fits. Read the output requirement carefully, because ranking ties and null handling are common hinges.
-
Match the window
A ranking, running total or latest-row-per-key requirement is given, and you pick the window function and frame.
-
Query the semi-structured payload
JSON events must be stored and queried flexibly, and you choose VARIANT functions or path syntax.
-
Choose the expectation policy
A rule fails for some rows, and you decide whether to warn, drop, fail or quarantine them.
A pipeline must keep invalid orders for review while loading only valid ones into silver. expect_or_drop removes bad rows but leaves no copy. expect_or_fail stops the whole update. The quarantine pattern writes valid rows to silver with a drop expectation and routes rows that fail the inverted rule into a separate quarantine table, so it meets both needs.
Objectives 4
Official objectives from the Databricks outline, in outline order.
3.1 Apply advanced data transformations, including window functions, joins and aggregations, using Spark SQL and PySpark on large datasetsCorrect and efficient set-based transformations.
- What it means
- This covers analytical SQL and DataFrame operations at scale: ranking and offset window functions such as row_number, rank, dense_rank, lag and lead; running totals with frame clauses; grouping with rollup, cube and grouping sets; and inner, outer, semi, anti and cross joins.
- What to understand
- row_number gives unique positions, rank leaves gaps after ties and dense_rank does not. A latest-row-per-key pattern uses row_number over a partition ordered by timestamp descending, then filters to 1. Left anti joins find unmatched rows. On large data, broadcast the small side of a join, avoid exploding joins on non-unique keys, and filter early so less data is shuffled.
- How it shows up
- Expect a desired result table and a choice of query or a question about why duplicates appeared after a join.
- Common confusion
- Using rank when exactly one row per key is needed; ties give several rows ranked 1.
3.2 Develop a model and query semi-structured data using the VARIANT data type, including functions such as parse_json and variant_getStoring and querying flexible JSON with VARIANT.
- What it means
- VARIANT is a column type that stores semi-structured values such as JSON in an efficient binary encoding. parse_json converts a JSON string into VARIANT, variant_get extracts a path and casts it to a type, and the colon path syntax reads nested fields directly in SQL.
- What to understand
- VARIANT avoids fixing a struct schema up front while querying faster than raw JSON strings. try_variant_get returns null rather than an error when a cast fails. schema_of_variant helps discover structure, and variant_explode turns arrays or objects into rows. Frequently used fields can be extracted into typed columns in silver for performance and clarity.
- How it shows up
- Questions show a JSON payload and ask how to store it or extract a nested value safely.
- Common confusion
- Storing JSON as STRING and parsing with from_json on every query when VARIANT fits better.
3.3 Apply AI functions, including ai_query, to perform model inference within data pipelines for enrichment and classificationCalling models from SQL and pipelines.
- What it means
- AI functions are SQL functions that call models. ai_query sends each row's input to a Model Serving endpoint, such as a foundation model or a custom model, and returns the response. Task-specific functions such as ai_classify, ai_extract and ai_analyze_sentiment cover common jobs without prompt writing.
- What to understand
- ai_query can run in batch over a table inside a pipeline or job, writing enrichment results to a column. Options let you set a return type and handle failures per row rather than failing the whole query. Cost and throughput depend on the endpoint, so filter and deduplicate inputs first and persist results instead of calling the model on every read.
- How it shows up
- A scenario wants to classify or extract fields from text at scale, and you choose the AI function and where to run it.
- Common confusion
- Calling ai_query in a view queried repeatedly, paying for inference on every read.
3.4 Implement data quality expectations in Lakeflow Pipelines to quarantine, drop or fail on bad recordsDeclaring quality rules and their actions.
- What it means
- Expectations are named boolean constraints on pipeline datasets. The default action keeps failing rows and records metrics; the drop action removes failing rows; the fail action stops the update. Quarantine is a pattern that sends failing rows to a separate table.
- What to understand
- In Python, expect, expect_or_drop and expect_or_fail decorate a dataset, and expect_all variants take a dictionary of rules. SQL uses CONSTRAINT ... EXPECT with ON VIOLATION DROP ROW or FAIL UPDATE. Metrics for each expectation appear in the pipeline UI and event log. Quarantine uses the inverted rule on a second dataset so no row is lost.
- How it shows up
- Expect a requirement about what should happen to bad rows and to the pipeline, and you choose the expectation action.
- Common confusion
- Thinking the default expectation removes bad rows; it only records them while keeping the row.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
row_number vs. rank vs. dense_rank
Unique position, gapped ties, and gapless ties.
- Remember
- Use row_number for exactly one row per key.
- Tested as
- Ranking and deduplication questions.
- Trap
- Using rank and getting duplicates on ties.
-
Window frame
ROWS or RANGE bounds that define which rows a window function sees.
- Remember
- Running totals use UNBOUNDED PRECEDING to CURRENT ROW.
- Tested as
- Running total questions.
- Trap
- Default RANGE frames grouping tied order values.
-
Left anti join
Returns left rows with no match on the right.
- Remember
- Clean way to find new or orphaned keys.
- Tested as
- Reconciliation questions.
- Trap
- Using a left join and forgetting the null filter.
-
Broadcast join
Ships a small table to all executors to avoid a shuffle.
- Remember
- Only for a genuinely small side.
- Tested as
- Performance-aware join questions.
- Trap
- Broadcasting a large table and running out of memory.
-
VARIANT
Binary-encoded semi-structured column type.
- Remember
- Flexible like JSON, faster to query.
- Tested as
- Semi-structured modelling.
- Trap
- Defaulting to STRING columns of JSON.
-
parse_json and variant_get
Convert JSON to VARIANT and extract typed paths.
- Remember
- try_variant_get returns null on bad casts.
- Tested as
- Extraction questions.
- Trap
- Using variant_get where malformed values should not fail the query.
-
ai_query
SQL function that calls a Model Serving endpoint per row.
- Remember
- Persist results; do not recompute on every read.
- Tested as
- Enrichment scenarios.
- Trap
- Putting it in a frequently queried view.
-
Task-specific AI functions
ai_classify, ai_extract, ai_analyze_sentiment and similar.
- Remember
- No prompt engineering needed for common tasks.
- Tested as
- Classification questions.
- Trap
- Writing a custom prompt for a built-in task.
-
Expectation actions
Retain and record, drop row, or fail update.
- Remember
- Default action keeps the failing row.
- Tested as
- Data quality questions.
- Trap
- Assuming the default drops rows.
-
Quarantine pattern
Route rows failing a rule into a separate table.
- Remember
- Uses the inverted condition so nothing is lost.
- Tested as
- Review and audit requirements.
- Trap
- Using expect_or_drop when bad rows must be kept.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Expectation actions
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Warn (default) | Row kept, violation counted | Pipeline continues with bad data present | Assumed to filter rows |
| Drop | Failing rows removed from the target | No copy of bad rows kept | Used when bad rows must be reviewed |
| Fail | Update stops on a violation | Protects critical tables from any bad row | Used for minor issues, stopping pipelines often |
| Quarantine pattern | Bad rows written to a separate table | Keeps valid and invalid rows apart | Thought to be a built-in fourth action |
Storing semi-structured data
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| STRING of JSON | Raw text parsed at query time | Flexible but slow to query | Kept in silver for heavy querying |
| STRUCT | Fixed schema of typed fields | Fast, but schema changes need evolution | Breaks when payload shape varies |
| VARIANT | Binary semi-structured value | Flexible and faster than strings | Forgetting to extract hot fields into columns |
Ranking functions on ties
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| row_number | 1, 2, 3 even on ties | Always unique | Order is arbitrary among ties without a tiebreaker |
| rank | 1, 1, 3 on a tie | Leaves gaps | Used for one row per key |
| dense_rank | 1, 1, 2 on a tie | No gaps | Confused with rank in top-N questions |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Default expectation drops
- What candidates pick
- Expect a plain expect decorator to remove bad rows.
- Why it looks right
- The word expectation sounds like enforcement.
- Why it's wrong
- The default action keeps the row and only records the violation in metrics.
Clue to watch for The code uses expect without _or_drop or _or_fail.
-
Rank for dedup
- What candidates pick
- Filter rank = 1 to keep the latest event per key.
- Why it looks right
- rank sounds like the natural ordering function.
- Why it's wrong
- Ties on the timestamp give several rows with rank 1; row_number guarantees one.
Clue to watch for The requirement says exactly one row per key.
-
Broadcast the big table
- What candidates pick
- Add a broadcast hint to the larger join side.
- Why it looks right
- Broadcast joins are known to be fast.
- Why it's wrong
- Broadcasting a large table can exhaust executor memory; only the small side should be broadcast.
Clue to watch for Table sizes differ greatly in the question.
-
variant_get on dirty data
- What candidates pick
- Use variant_get with a cast on a field that is sometimes malformed.
- Why it looks right
- It is the main extraction function.
- Why it's wrong
- A failed cast raises an error; try_variant_get returns null and keeps the query running.
Clue to watch for The scenario says some payloads have bad values.
-
Prompt for sentiment
- What candidates pick
- Write a custom ai_query prompt to label sentiment.
- Why it looks right
- ai_query is the general-purpose function.
- Why it's wrong
- ai_analyze_sentiment does this directly with less setup and no prompt maintenance.
Clue to watch for The task matches a built-in AI function.
-
Fail for minor rules
- What candidates pick
- Use expect_or_fail for a non-critical formatting rule.
- Why it looks right
- Strict quality seems safer.
- Why it's wrong
- Failing the update on minor issues halts all downstream data; drop or quarantine suits non-critical rules.
Clue to watch for The rule is described as low impact.
Answer framework
Work through these steps when a Section 3 item asks how to transform or check data.
- Restate the required output shape: one row per key, a running value, a rollup, unmatched rows, or an enriched or validated record.
- For relational logic, pick the window function and frame or join type that matches, and check how ties and nulls behave.
- For semi-structured payloads, store them as VARIANT and extract with path syntax, variant_get or try_variant_get depending on data cleanliness.
- For enrichment, prefer a task-specific AI function, fall back to ai_query with a return type, and persist results to avoid repeated inference.
- For quality, decide the fate of the bad row and of the pipeline, then choose warn, drop, fail or the quarantine pattern.
Practice question
Want more like this? The Databricks DE Pro practice test has questions for every domain.
Checklist
0 of 10
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Split your time between the two familiar objectives and the two new ones. For window functions and joins, write queries on a sample table with deliberate ties, nulls and duplicate keys. Compare row_number, rank and dense_rank outputs side by side, and change the frame clause on a running total to see how RANGE treats tied order values. Run a join on a non-unique key and watch the row count grow.
For VARIANT, load a file of nested JSON events into a VARIANT column, then practise path syntax, variant_get with casts and try_variant_get on malformed values. Extract two frequently used fields into typed columns and note the query difference.
For AI functions, read the function reference and try ai_classify and ai_query on a few rows using a pay-per-token endpoint if your workspace allows it. Note where inference should run and how failures are handled.
Finally, add each expectation action to a small pipeline, run it with bad rows, and read the metrics in the pipeline UI. Build one quarantine table so the pattern is familiar.
FAQ
How much of the exam is Data Manipulation?
Data Manipulation is 12% of the scored content, about seven questions out of 60.
Is VARIANT new to this exam?
Yes. VARIANT and AI functions were added in the October 2026 revision; the previous guide did not list them.
Do I need to know prompt engineering for ai_query?
Not in depth. Know what ai_query does, when a task-specific function is simpler, and how to run inference efficiently in a pipeline.
What is the default expectation action?
The default keeps the row and records the violation. Drop removes failing rows, and fail stops the update.
Is quarantine a built-in expectation action?
No. It is a pattern: valid rows go to the main table and rows failing the inverted rule go to a separate table.
Are PySpark and SQL both tested?
Yes. Questions may show either Spark SQL or PySpark DataFrame code for the same transformation.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 3 knowledge
You have covered window functions and joins, VARIANT, AI functions and pipeline expectations. Test yourself on short query and data quality scenarios.
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