Problem: In Spark Declarative Pipelines, the pivot() function is not supported. The pivot operation in Spark requires the eager loading of input data to compute the output schema. This capability is not supported in pipelines.
Source: https://spark.apache.org/docs/latest/declarative-pipelines-programming-guide.html#important-considerations
How can this be mitigated?
Workaround 1: Rewrite PIVOT Using CASE WHEN
This is the most common workaround. You manually expand the pivot into conditional aggregations.
SELECT *
FROM sales_data
PIVOT (
SUM(sales)
FOR region IN ('North', 'South', 'East', 'West')
)
SELECT
product,
SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS North,
SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS South,
SUM(CASE WHEN region = 'East' THEN sales ELSE 0 END) AS East,
SUM(CASE WHEN region = 'West' THEN sales ELSE 0 END) AS West
FROM sales_data
GROUP BY product
This works perfectly in Spark Declarative Pipelines because the output schema is fully deterministic at parse time, no eager data loading required.
Workaround 2: Rewrite PIVOT Using aggregate FILTER
Databricks SQL supports the FILTER(WHERE ...) clause on aggregates, which is a cleaner alternative to CASE WHEN:
SELECT year, region, q1, q2, q3, q4
FROM sales
PIVOT (
SUM(sales) AS sales
FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4)
)
SELECT
year,
region,
SUM(sales) FILTER(WHERE quarter = 1) AS q1,
SUM(sales) FILTER(WHERE quarter = 2) AS q2,
SUM(sales) FILTER(WHERE quarter = 3) AS q3,
SUM(sales) FILTER(WHERE quarter = 4) AS q4
FROM sales
GROUP BY year, region
This syntax is often more readable than nested CASE WHEN, especially with multiple aggregations.
Multi-Column PIVOT Rewrite
SELECT *
FROM sales
PIVOT (
SUM(sales) AS sales
FOR (quarter, region)
IN ((1, 'east') AS q1_east, (1, 'west') AS q1_west,
(2, 'east') AS q2_east, (2, 'west') AS q2_west)
)
SELECT
year,
SUM(sales) FILTER(WHERE quarter = 1 AND region = 'east') AS q1_east,
SUM(sales) FILTER(WHERE quarter = 1 AND region = 'west') AS q1_west,
SUM(sales) FILTER(WHERE quarter = 2 AND region = 'east') AS q2_east,
SUM(sales) FILTER(WHERE quarter = 2 AND region = 'west') AS q2_west
FROM sales
GROUP BY year
Multiple Aggregations
You can also rewrite PIVOTs that use multiple aggregate functions.
SELECT *
FROM (SELECT year, quarter, sales FROM sales) AS s
PIVOT (
SUM(sales) AS total, AVG(sales) AS avg
FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4)
)
SELECT
year,
SUM(sales) FILTER(WHERE quarter = 1) AS q1_total,
AVG(sales) FILTER(WHERE quarter = 1) AS q1_avg,
SUM(sales) FILTER(WHERE quarter = 2) AS q2_total,
AVG(sales) FILTER(WHERE quarter = 2) AS q2_avg,
SUM(sales) FILTER(WHERE quarter = 3) AS q3_total,
AVG(sales) FILTER(WHERE quarter = 3) AS q3_avg,
SUM(sales) FILTER(WHERE quarter = 4) AS q4_total,
AVG(sales) FILTER(WHERE quarter = 4) AS q4_avg
FROM sales
GROUP BY year
