Reshaping columns, rows, and arrays in a table

Spark SQL and DataFrame APIs provide DDL-style and functional operations to reshape tables: adding/dropping columns, renaming, splitting strings into multiple columns, filtering rows, and exploding array columns into multiple rows. These operations are used constantly when cleaning raw or semi-structured data during ETL/ELT transformations in Databricks notebooks.

1 · Learn the must-know

• 1ALTER TABLE ... ADD COLUMNS adds new columns; ALTER TABLE ... DROP COLUMN drops one (requires Delta column mapping enabled for DROP COLUMN); withColumn() adds/replaces a column in a DataFrame, while drop() removes one.
• 2Renaming uses ALTER TABLE ... RENAME COLUMN in SQL or withColumnRenamed() in DataFrames; renaming does not change underlying data, only metadata.
• 3split(col, pattern) splits a string column into an array, and array elements can be pulled into separate columns via indexing (e.g., split(col,',')[0]) or getItem().
• 4Filtering uses WHERE in SQL or .filter()/.where() on DataFrames; both accept boolean column expressions and can be chained for multiple conditions.
• 5explode(array_col) turns each element of an array (or map) into a separate row, duplicating the other column values across rows; rows with NULL or empty arrays are dropped by explode but kept by explode_outer.
• 6Column and row operations can be combined in a single SELECT/DataFrame chain, but exploding before filtering (or vice versa) changes row counts and results, so operation order matters.

2 · Check your understanding