Databricks Certified Data Engineer - Associate

Explanation

The correct answer is B. PIVOT.

Why PIVOT is correct:

• PIVOT is a SQL operation specifically designed to transform data from a long format (where each row represents a single observation) to a wide format (where multiple observations are represented as columns).
• In Spark SQL, the PIVOT clause rotates rows into columns, aggregating data as needed.
• This operation is commonly used for data reshaping and creating cross-tabulation reports.

Why other options are incorrect:

• A. TRANSFORM: This is not a standard SQL keyword for data reshaping. TRANSFORM is sometimes used in other contexts (like Hive UDFs) but not for converting between long and wide formats.
• C. SUM: This is an aggregation function used for mathematical operations, not for data reshaping.
• D. CONVERT: This is typically used for data type conversion, not for changing the structure of a table from long to wide format.

Example of PIVOT usage in Spark SQL:

SELECT *
FROM sales_data
PIVOT (
  SUM(amount)
  FOR product IN ('A', 'B', 'C')
)

SELECT *
FROM sales_data
PIVOT (
  SUM(amount)
  FOR product IN ('A', 'B', 'C')
)

This would convert long-format sales data (with rows for each product) into wide format (with columns for each product).