What you will be able to do
- Add columns to a Delta table and predict what existing rows contain
- Rename and drop stored columns, knowing when column mapping, REORG TABLE and VACUUM are needed
- Rewrite a table's schema with overwriteSchema when a metadata change is not an option
- Anticipate the effect of a schema change on concurrent writers and streaming readers
1.Changing the stored schema, not just a DataFrame
A withColumnRenamed or drop in a notebook changes one DataFrame. The table it was read from keeps its schema. To change the stored structure of a Delta table, use ALTER TABLE. It changes the schema explicitly without writing new data, and supports adding columns at any position, reordering, renaming and dropping them.
Two side effects belong in the plan before you run one. A schema update conflicts with every concurrent write, so a nightly job that alters a table while an ingestion job appends to it can fail with a write conflict. A schema update also terminates every stream reading from the table, and those streams have to be restarted. Schedule structural changes for a quiet window and tell the owners of downstream streams.
Permissions differ by operation in Unity Catalog. ADD COLUMN, ALTER COLUMN and DROP COLUMN need MODIFY on the table. Every other operation, RENAME COLUMN included, needs ownership of the table.
Checkpoint 1 of 3· Check yourself
A nightly job runs ALTER TABLE silver.orders RENAME COLUMN cust_id TO customer_id on a Delta table. At the same time, an ingestion job is appending to the table and a Structured Streaming query is reading from it. What should the team expect?
A schema update conflicts with every concurrent write, even though ALTER TABLE writes no new data, so the append can fail. It also terminates every stream reading from the table, and those streams have to be restarted. That is why structural changes belong in a quiet window.
“Schema updates conflict with all concurrent write operations. Databricks recommends coordinating schema changes to avoid write conflicts.”Source: docs.databricks.com
2.Adding columns: existing rows get NULL
ALTER TABLE ... ADD COLUMNS adds one or more columns, each with a type, an optional comment and an optional position. Without FIRST or AFTER, a new column goes at the end. New columns are nullable by default.
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name], ...)You can't attach a DEFAULT while adding a column to an existing Delta table. Every existing row reads NULL for that column. Run ALTER COLUMN ... SET DEFAULT afterwards and the default applies only to rows inserted after that point. Existing rows still need an explicit update if they must hold a value.
Nested structures can grow too. Use a dotted path to add a field inside a struct. For a struct nested in an array or a map, insert element, key or value in the path. For example, ALTER TABLE my_table ADD COLUMNS (points.element.z DOUBLE) adds z to every struct in an ARRAY<STRUCT<x, y>> column. This is the stored counterpart of the arrays of structs you explode in a DataFrame.
Checkpoint 2 of 3· Fill the gap
The points column holds a map from string labels to structs. Which path component adds field z to each of those structs?
-- points is MAP<STRING, STRUCT<x: DOUBLE, y: DOUBLE>>
ALTER TABLE my_table ADD COLUMNS (points. ? .z DOUBLE);Here the struct is the map's value, so the path goes through value. Use element for a struct inside an ARRAY and key when the struct is the map's key.
3.Renaming and dropping stored columns
ALTER TABLE t RENAME COLUMN old TO new and ALTER TABLE t DROP COLUMN c (or DROP COLUMNS (a, b)) work on Delta tables. To make them metadata-only operations, so that no existing data is rewritten, the table needs column mapping enabled.
A metadata-only drop hides the column but doesn't erase it. The values stay in the data files. That matters when a column is dropped because it held something that shouldn't be kept. To remove the data physically, run REORG TABLE to rewrite the files, then VACUUM to delete the files that still contain the dropped column.
| Change | Statement | Existing data | Unity Catalog permission |
|---|---|---|---|
| Add a column | ALTER TABLE ... ADD COLUMNS | Existing rows read NULL | MODIFY |
| Set a default | ALTER COLUMN ... SET DEFAULT | Applies to new rows only | MODIFY |
| Rename a column | RENAME COLUMN old TO new | No rewrite when column mapping is enabled | Table ownership |
| Drop a column | DROP COLUMN / DROP COLUMNS | Stays in files until REORG TABLE then VACUUM | MODIFY |
Two heavier options exist. ALTER TABLE ... REPLACE COLUMNS redefines the whole column list in one statement, so a single command can add, remove, reorder and rename. The other option is to rewrite the table: read it, reshape it with DataFrame methods such as withColumnRenamed, and overwrite it with overwriteSchema set to true. This rewrites all the data, but it needs no column mapping and works for renames, drops and type changes.
(spark.read.table(...)
.withColumnRenamed("dateOfBirth", "birthDate")
.write
.mode("overwrite")
.option("overwriteSchema", "true")
.saveAsTable(...)
)Schema changes that come from the data being written, such as new columns appearing in source files, are a different mechanism. That is schema evolution with Auto Loader.
Checkpoint 3 of 3· Check yourself
A Delta table has column mapping enabled. A team runs ALTER TABLE customers DROP COLUMN ssn because the column held data that must not be kept. Compliance now requires the ssn values to be physically removed from storage. What still has to happen?
A metadata-only drop hides the column, but its values stay in the data files. REORG TABLE first rewrites the files without the column. VACUUM then deletes the old files that still hold the dropped data.
“Dropping a column from metadata does not delete the underlying data for the column in files.”Source: docs.databricks.com
Sources
(3)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Use ALTER TABLE statements to explicitly change a table's schema without writing new data.”
↩︎ Changing the stored schema, not just a DataFrame“Schema updates conflict with all concurrent write operations.”
↩︎ Changing the stored schema, not just a DataFrame“Updating a table schema terminates any streams reading from that table.”
↩︎ Changing the stored schema, not just a DataFrame“By default, nullability is true.”
↩︎ Adding columns: existing rows get NULL“To add a field to a struct nested inside an ARRAY or MAP, use one of these special properties in the column path:”
↩︎ Adding columns: existing rows get NULL“To rename columns without rewriting any of the columns' existing data, you must enable column mapping for the table.”
↩︎ Renaming and dropping stored columns“Dropping a column from metadata does not delete the underlying data for the column in files.”
↩︎ Renaming and dropping stored columns“Then use VACUUM to physically delete the files that contain the dropped column data.”
↩︎ Renaming and dropping stored columns“to redefine the full column list of a table, including adding, removing, reordering, or renaming columns in a single operation”
↩︎ Renaming and dropping stored columns“You can change a column's type or name or drop a column by rewriting the table.”
↩︎ Renaming and dropping stored columns“Dropping a column from metadata does not delete the underlying data for the column in files.”
↩︎ Exam trap 2“Schema updates conflict with all concurrent write operations. Databricks recommends coordinating schema changes to avoid write conflicts.”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-alter-table-manage-columnOfficial docs
“If you use Unity Catalog you must have MODIFY permission to:”
↩︎ Changing the stored schema, not just a DataFrame“All other operations require ownership of the table.”
↩︎ Changing the stored schema, not just a DataFrame“Unless FIRST or AFTER name are specified the column or field will be appended at the end.”
↩︎ Adding columns: existing rows get NULL“When you add a column to an existing Delta table, you cannot define a DEFAULT value.”
↩︎ Adding columns: existing rows get NULL“you can optionally define a default value for the column, but this is only applied for new rows inserted into the table.”
↩︎ Exam trap 1“All columns added to Delta tables are treated as NULL for existing rows.”
↩︎ Prediction - 3.
“RENAME COLUMN Renames a column or field in a Delta Lake table.”
↩︎ Renaming and dropping stored columns