Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: Pivoting (rowsâcolumns) is a reshape operation with cardinality and performance implications. **SQL**: Aggregate + CASE: `SELECT product_id, MAX(CASE WHEN month='Jan' THEN sales END) AS jan_sales, ... FROM sales GROUP BY product_id`. Or PIVOT (SQL...
Architectural Logic: Pivoting (rowsâcolumns) is a reshape operation with cardinality and performance implications. SQL: Aggregate + CASE: SELECT product_id, MAX(CASE WHEN month='Jan' THEN sales END) AS jan_sales, ... FROM sales GROUP BY product_id. Or PIVOT (SQL Server). Spark: df.groupBy("product_id").pivot("month").agg(sum("sales")). Why Cardinality Matters: High cardinality in pivot column (e.g., pivot by user_id) causes column explosionâhundreds of columns, schema bloat. Scalability: Dynamic pivots require dynamic SQL or Spark; static pivots are efficient. Cost: Wide tables increase storage and scan cost; consider whether long format is preferable for downstream analytics. Trade-off: Pivot for human-readable reports; long format for ML and flexible querying.
Red Flag: Pivoting on unbounded columns (e.g., all product names)âschema explosion. Pro-Move: Limit pivot to fixed dimensions (e.g., 12 months); use long format + BI tool pivot for dynamic cases.
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.