Data manipulation
Window functions and joins, VARIANT, AI functions and expectations.
This section tests everyday transformation skill at scale: window functions, joins and aggregations, semi-structured data in VARIANT, model inference inside SQL, and data quality rules in pipelines. The biggest way people lose marks is reaching for the familiar tool (a self join, a JSON string, a Python loop, a try block) when a built-in feature does the job correctly and in parallel.
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 line | In plain words | A question looks like | The answer | The trap |
|---|---|---|---|---|
| 3.1 Apply advanced data transformations, including window functions, joins, and aggregations, using Spark SQL and PySpark to process large datasets. | Transform large tables with window functions (calculations over a group of related rows without collapsing them), joins and aggregations, in Spark SQL or PySpark. | A retailer wants each order alongside the customer's previous order date and a running total, on billions of rows. The question asks which construct fits, or why a result has too few or too many rows. | A window function with PARTITION BY and ORDER BY (for example lag, or sum over an ordered frame) keeps every row and adds the calculation, in one pass. A GROUP BY aggregation collapses rows to one per group, so use it only when the result should be smaller. For top-N per group, rank rows with row_number in a window and filter on it. A join on a non-unique key multiplies rows, so check key uniqueness when counts look wrong. Try it: On a small orders table, compute lag(order_date) and a running sum with a window, then reproduce it with a self join and compare the plans. | A self join on customer and a date condition returns the right idea for small data, but it is expensive at this scale and double counts when dates tie. Aggregating then joining back also works but adds a shuffle that a window avoids. Runner-up wins when: GROUP BY wins when the output should be one row per group, such as monthly totals for a dashboard. |
| 3.2 Develop a model and query semi-structured data using the VARIANT data type and related functions (e.g., parse_json, variant_get). | Store irregular JSON-like records in a VARIANT column, a Databricks type that holds semi-structured data in a binary form, and pull fields out with functions. parse_json builds a variant from a JSON string and variant_get reads a field by path. | Event records from many apps carry different fields and the shape changes often. The question asks how to store them and read one field with the right type. | Store them as VARIANT, created with parse_json, which Databricks recommends over JSON strings (it needs Databricks Runtime 15.4 or above). Read fields with variant_get using a path such as $.owner and a type, or the colon shorthand, for example raw:owner::string. try_variant_get returns NULL instead of an error when the cast fails. Field names are case sensitive. schema_of_variant shows the shape and variant_explode flattens arrays into rows. A variant can hold a variant null that differs from SQL NULL; is_variant_null tells them apart. Try it: Create a table with a VARIANT column from parse_json on two differently shaped JSON strings and query a field with the colon syntax. | Keeping JSON as a string and using get_json_object works, but the text is reparsed on every query and gives no help with types. Forcing a fixed struct schema breaks when a new field appears. VARIANT columns cannot be used as clustering keys, partitions, or in comparisons or grouping, so extract and cast first. Runner-up wins when: A typed struct wins when the shape is stable and known, because every field gets a real type and statistics up front. |
| 3.3 Apply AI functions, including ai_query, to perform model inference within data pipelines for enrichment and classification tasks. | Call a model from inside SQL or a pipeline to enrich or classify rows, using AI functions. ai_query sends each row to a Model Serving endpoint and returns the answer as a column. | A support team needs a sentiment label on two million existing ticket rows, produced in a scheduled pipeline. The question asks which call fits and how to keep one bad row from stopping the run. | Use ai_query(endpoint, request) in a SQL or PySpark step over the table, so inference runs set-based across the data. Setting failOnError to false makes the function return a struct holding the response and an error message instead of failing the whole query. The definer needs CAN QUERY on the endpoint. modelParameters sets constant parameters and responseFormat asks for structured output; both need newer runtimes, so check the docs for versions. The function is not available on Databricks SQL Classic warehouses. | Looping over rows in Python and calling the endpoint per row works but throws away the parallelism and adds your own retry code. Calling a model by hand per ticket suits interactive use, not two million rows. Runner-up wins when: Calling the endpoint from application code wins for one interactive request at a time, where a table scan makes no sense. |
| 3.4 Implement data quality expectations in Lakeflow Pipelines to quarantine, drop, or fail on bad records. | Declare rules on a pipeline table (expectations) that say what a good row looks like, and choose what happens to rows that break them: keep and count them, drop them, or stop the update. | A pipeline loads payments, and rows with a missing account id appear. The business wants bad rows kept for review without blocking good data. The question asks which action or pattern fits. | The default expectation (EXPECT, or expect in Python) keeps violating rows and only records metrics. ON VIOLATION DROP ROW (expect_or_drop) removes bad rows before the write and logs a count. ON VIOLATION FAIL UPDATE (expect_or_fail) fails the update and rolls back that table update atomically. Dropped rows are not kept anywhere, so to keep them for review you build a quarantine pattern: a second table fed by the inverse condition. Metrics show in the pipeline's data quality tab and the event log; fail actions record none, because the update stops first. Try it: Add one expect_or_drop and one inverse-condition quarantine table to a Free Edition pipeline and compare row counts. | Choosing drop looks like it handles bad data but silently loses the rows the business wants to review. Choosing fail protects the target but blocks all good data for a few bad rows. Runner-up wins when: Fail wins when even one bad row makes downstream output wrong, for example a regulatory total. |
Mind map: Pick the data quality action
People assume dropping bad rows is the safe option; it is only safe if nobody needs those rows again.
| What you observe | What it points to | What to do (and when the runner-up wins) |
|---|---|---|
| Bad rows are rare and only need counting | Default EXPECT (warn) | Rows stay in the target; watch the metrics. |
| Bad rows must not reach the target but nobody reviews them | DROP ROW | Only a count is kept. |
| Bad rows must be reviewed or repaired later | Quarantine table from the inverse condition | Drop wins if a count is enough. |
| One bad row makes the output wrong | FAIL UPDATE | Atomic rollback of that update; fix then rerun. |
Mind map: Semi-structured and model steps
People assume a string column or a loop is flexible enough; the built-in type and function are usually both faster and safer.
| What you observe | What it points to | What to do (and when the runner-up wins) |
|---|---|---|
| Shape changes from record to record | VARIANT with parse_json | A struct wins if the shape is fixed. |
| Need one field as a typed value | variant_get or colon path with a cast | Use try_variant_get when casts may fail. |
| Need a model answer on every row of a table | ai_query in the pipeline step | Direct endpoint calls win for a single interactive request. |
| A few model calls may fail | failOnError set to false | Leave it true when any failure must stop the run. |