Skip to content
Cheat sheets

Data Engineer Professional · Domain 3: Data Manipulation · 12% of the exam

Task 3.1: Apply advanced data transformations, including window functions, joins, and aggregations, using Spark SQL and PySpark to process large datasets.

Ranking and offset window functions, frames, QUALIFY, max_by and approximate aggregates, semi and anti joins, join hints and grouping sets.

Study it

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

The table daily_sales has one row per store and day, but stores that are closed have no row for that day. A report needs, for each row, the average sales over the 7 calendar days ending on that day, per store. Which window expression is correct?

  1. Aavg(sales) OVER (PARTITION BY store ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
  2. Bavg(sales) OVER (PARTITION BY store, day ORDER BY day RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)
  3. Cavg(sales) OVER (PARTITION BY store ORDER BY day RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)
  4. Davg(sales) OVER (PARTITION BY store ORDER BY day, store RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)
Show the answer and why

  • Aavg(sales) OVER (PARTITION BY store ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

    Incorrect

    ROWS counts rows, so after closed days the frame reaches back more than 7 calendar days.

  • Bavg(sales) OVER (PARTITION BY store, day ORDER BY day RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)

    Incorrect

    Partitioning by day as well leaves one row per partition, so each average covers a single day.

  • Cavg(sales) OVER (PARTITION BY store ORDER BY day RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)

    Correct

    A RANGE frame is defined by values of the single ORDER BY expression, so it covers 7 calendar days whether or not rows are missing.

  • Davg(sales) OVER (PARTITION BY store ORDER BY day, store RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW)

    Incorrect

    A RANGE frame needs an ORDER BY with a single expression; two expressions raise an error.

In ROWS mode the offsets count rows; in RANGE mode the window must have an ORDER BY with one expression, and the offset is a value subtracted from it, such as an interval for a date. Gaps in the data make the difference.

Databricks documentation

Task 3.1Report a problem with this question

Question 2 · choose 1

A query must list customers in orders who never appear in returns. The query SELECT DISTINCT customer_id FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM returns) returns no rows, although many such customers exist. Some rows in returns have a NULL customer_id. Which query returns the right customers?

  1. ASELECT DISTINCT o.customer_id FROM orders o LEFT JOIN returns r ON o.customer_id = r.customer_id
  2. BSELECT DISTINCT o.customer_id FROM orders o JOIN returns r ON o.customer_id <> r.customer_id
  3. CSELECT DISTINCT customer_id FROM orders WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM returns)
  4. DSELECT DISTINCT o.customer_id FROM orders o LEFT ANTI JOIN returns r ON o.customer_id = r.customer_id
Show the answer and why

  • ASELECT DISTINCT o.customer_id FROM orders o LEFT JOIN returns r ON o.customer_id = r.customer_id

    Incorrect

    A left join keeps every customer in orders, with or without returns, so it filters nothing out.

  • BSELECT DISTINCT o.customer_id FROM orders o JOIN returns r ON o.customer_id <> r.customer_id

    Incorrect

    This pairs each order with every non-matching return row, so customers with returns still appear.

  • CSELECT DISTINCT customer_id FROM orders WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM returns)

    Incorrect

    DISTINCT still keeps one NULL in the subquery result, so NOT IN is still UNKNOWN for every row and returns nothing.

  • DSELECT DISTINCT o.customer_id FROM orders o LEFT ANTI JOIN returns r ON o.customer_id = r.customer_id

    Correct

    An anti join returns left rows with no match on the right, and a NULL on the right never matches, so it does not hide the other customers.

A NOT IN predicate becomes UNKNOWN when the subquery returns a NULL, so no row qualifies. A LEFT ANTI JOIN (or NOT EXISTS) returns the left rows that have no match and is not affected by NULLs in the right table.

Databricks documentation

Task 3.1Report a problem with this question

Question 3 · choose 1

Products are ranked by revenue within each category. Two products tie for second place, and the next product must be ranked 3, not 4. Which function gives this numbering?

  1. Arank()
  2. Brow_number()
  3. Cpercent_rank()
  4. Ddense_rank()
Show the answer and why

  • Arank()

    Incorrect

    rank gives ties the same rank but skips the following ranks, so the next product is 4.

  • Brow_number()

    Incorrect

    row_number gives tied rows different numbers.

  • Cpercent_rank()

    Incorrect

    percent_rank returns a relative position between 0 and 1.

  • Ddense_rank()

    Correct

    dense_rank gives tied rows the same rank and leaves no gap after them.

rank and dense_rank both give tied rows the same value; rank leaves gaps after a tie and dense_rank does not. row_number numbers every row uniquely.

Databricks documentation

Task 3.1Report a problem with this question

Question 4 · choose 1

A gold query groups orders by customer_id. For each customer it must return one array of the distinct product IDs that the customer bought, even if a product was ordered many times. Which aggregate expression returns it?

  1. Acollect_list(product_id)
  2. Bcollect_set(product_id)
  3. Carray_distinct(product_id)
  4. Dcount(DISTINCT product_id)
Show the answer and why

  • Acollect_list(product_id)

    Incorrect

    collect_list returns all values in the group, so repeated products appear more than once.

  • Bcollect_set(product_id)

    Correct

    collect_set returns an array of all unique values of the expression within the group.

  • Carray_distinct(product_id)

    Incorrect

    array_distinct removes duplicates from an existing array; it is not an aggregate over the rows of a group.

  • Dcount(DISTINCT product_id)

    Incorrect

    This returns the number of distinct products, not an array of them.

collect_set is an aggregate function that returns an array of the unique values of an expression within each group, while collect_list keeps every value. array_distinct works on an array value, not on grouped rows.

Databricks documentation

Task 3.1Report a problem with this question

Question 5 · choose 1

A gold query selects nine descriptive columns and three aggregates. Its GROUP BY list keeps falling out of step with the SELECT list whenever a column is added. Which clause groups by every SELECT-list expression that contains no aggregate function?

  1. AGROUP BY ALL
  2. BGROUP BY *
  3. CGROUP BY ROLLUP (*)
  4. DGROUP BY GROUPING SETS (())
Show the answer and why

  • AGROUP BY ALL

    Correct

    ALL is a shorthand that adds every SELECT-list expression without an aggregate function as a grouping expression.

  • BGROUP BY *

    Incorrect

    A star is not a grouping expression; the documented shorthand for this is ALL.

  • CGROUP BY ROLLUP (*)

    Incorrect

    ROLLUP takes explicit grouping expressions and adds subtotal rows, which the report does not want.

  • DGROUP BY GROUPING SETS (())

    Incorrect

    An empty grouping set is a single global aggregate over all rows.

GROUP BY ALL adds all SELECT-list expressions that do not contain aggregate functions as group expressions. If there are none, it equals a global aggregation, and Databricks raises an error if the produced clause is not well formed.

Databricks documentation

Task 3.1Report a problem with this question

Question 6 · choose 1

A query must return each customer that has at least one order, once, with only the customer's columns. Customers can have hundreds of orders. Which join fits?

  1. Acustomers INNER JOIN orders ON customers.id = orders.customer_id
  2. Bcustomers LEFT ANTI JOIN orders ON customers.id = orders.customer_id
  3. Ccustomers LEFT SEMI JOIN orders ON customers.id = orders.customer_id
  4. Dcustomers LEFT OUTER JOIN orders ON customers.id = orders.customer_id
Show the answer and why

  • Acustomers INNER JOIN orders ON customers.id = orders.customer_id

    Incorrect

    An inner join repeats each customer once per order.

  • Bcustomers LEFT ANTI JOIN orders ON customers.id = orders.customer_id

    Incorrect

    This keeps the customers without a match, the opposite of what is needed.

  • Ccustomers LEFT SEMI JOIN orders ON customers.id = orders.customer_id

    Correct

    A semi join returns left rows that have a match, without columns or duplicates from the right.

  • Dcustomers LEFT OUTER JOIN orders ON customers.id = orders.customer_id

    Incorrect

    A left join keeps customers without orders and repeats the others.

A left semi join returns values from the left side that have a match on the right, without adding columns or rows from the right side.

Databricks documentation

Task 3.1Report a problem with this question

Practice domain 3 →Practice all domains →