Denormalizing, pivoting, and unpivoting data

Denormalizing, pivoting, and unpivoting are core Spark SQL transformation patterns used in Databricks to reshape data for analytics: denormalization joins normalized tables into wider tables for read performance, PIVOT rotates row values into columns, and UNPIVOT (or stack()) converts columns back into rows. These transformations are commonly applied in the Silver-to-Gold layer to prepare data for BI tools and downstream consumption.

1 · Learn the must-know

• 1Denormalization is typically performed by joining multiple normalized (e.g., star-schema) tables into a single wide table, trading storage and duplication for query simplicity and read performance.
• 2The PIVOT clause in Spark SQL rotates unique values from one column into multiple new columns, requiring an aggregate function (e.g., SUM, COUNT, AVG) to combine the resulting grouped values.
• 3PIVOT needs to resolve the output schema, so the pivot column's distinct values are typically enumerated explicitly (or via a subquery) rather than being fully dynamic at runtime; excessive cardinality can lead to very wide, inefficient tables.
• 4UNPIVOT (or the stack() function) converts columns into rows, transforming wide tables into a long/tall format, which is useful for normalizing sensor, telemetry, or survey-style data.
• 5explode() and posexplode() are used to flatten array or map-typed columns into multiple rows, a common denormalization/unpivoting technique when working with semi-structured (JSON) data.
• 6These reshaping operations are most often applied when transforming data from the Silver layer to the Gold layer in the medallion architecture, optimizing tables for specific reporting or ML consumption patterns.

2 · Check your understanding