CertSafari
    Databricks Certified Data Engineer Associate· Lessons

    Domain 3 · Lesson 12/33

    Changing Delta table columns with ALTER TABLE

    Manipulate columns, rows, and table structures by adding, dropping, splitting, renaming column names, applying filters, and exploding arrays

    6 min read
    3.14% of exam
    3 sources
    Published 22 Oct 2026
    Docs as of 30 Sep 2026

    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?

    Sources12

    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.

    Syntax for adding columns with an optional comment and positionsql
    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);

    Sources21

    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.

    Stored-column changes and what happens to existing data
    ChangeStatementExisting dataUnity Catalog permission
    Add a columnALTER TABLE ... ADD COLUMNSExisting rows read NULLMODIFY
    Set a defaultALTER COLUMN ... SET DEFAULTApplies to new rows onlyMODIFY
    Rename a columnRENAME COLUMN old TO newNo rewrite when column mapping is enabledTable ownership
    Drop a columnDROP COLUMN / DROP COLUMNSStays in files until REORG TABLE then VACUUMMODIFY

    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.

    Renaming a column by rewriting the table with overwriteSchemapython
    (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?

    Sources13

    Sources

    (3)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 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. 2.
      “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

    Spotted a mistake, or was something unclear? Tell us.