Skip to main content
Last updated on

Update a streaming table schema with ALTER TABLE

Beta

Updating a streaming table schema with ALTER TABLE is in Beta. To request access, sign up for the Beta.

ALTER TABLE supports column-level schema changes: adding, dropping, and renaming columns, and widening a column's type. These are metadata-only operations. They do not require a full refresh, do not reset the streaming checkpoint, and do not re-ingest data.

note

Auto Loader tracks its own source schema, so DROP COLUMN and RENAME COLUMN might not fully take effect on tables backed by Auto Loader. See Auto Loader source schemas are not updated.

Supported operations​

All operations require a pipeline that publishes to Unity Catalog. They also require external metadata, except on managed tables created with CREATE TABLE ... FLOW. Some operations have additional requirements:

Operation

Additional requirements

ADD COLUMN

No additional requirements.

DROP COLUMN

Column mapping mode name

RENAME COLUMN

Column mapping mode name

ALTER COLUMN ... TYPE

Type widening

Operation

Additional requirements

ADD COLUMN

No additional requirements.

DROP COLUMN

Column mapping mode name

RENAME COLUMN

Column mapping mode name

ALTER COLUMN ... TYPE

Type widening

Enable external metadata​

Column operations require external metadata on the pipeline that owns the streaming table. If it is not enabled, the command fails with an error. See Enable external data access to streaming tables and materialized views.

note

External metadata is not required for managed tables in pipelines created with the CREATE TABLE ... FLOW syntax.

Set pipelines.externalMetadata.enabled to true in the pipeline configuration:

JSON
{
  "configuration": {
    "pipelines.externalMetadata.enabled": "true"
  }
}

To set this in the Lakeflow Pipelines Editor, select the pipeline settings and add pipelines.externalMetadata.enabled with the value true.

Run one pipeline update after enabling it. ALTER TABLE column operations are available from then on.

Enable column mapping​

DROP COLUMN and RENAME COLUMN require column mapping mode name. Set it in the pipeline definition, which applies it on the next update:

SQL
CREATE OR REFRESH STREAMING TABLE orders
  TBLPROPERTIES ('delta.columnMapping.mode' = 'name')
  AS SELECT * FROM STREAM read_files('/Volumes/main/sales/raw');
warning

Enabling column mapping is irreversible and raises the table's reader and writer protocol versions. Older Databricks Runtime versions and external readers might no longer be able to read the table. See Schema evolution in Databricks.

Enable type widening​

ALTER COLUMN ... TYPE requires type widening. Without it, the statement fails instead of widening the column. Enable it for every table in the pipeline with the pipelines.enableTypeWidening configuration:

JSON
{
  "configuration": {
    "pipelines.enableTypeWidening": "true"
  }
}

Or enable it for a single table with the delta.enableTypeWidening table property:

SQL
CREATE OR REFRESH STREAMING TABLE orders
  TBLPROPERTIES ('delta.enableTypeWidening' = 'true')
  AS SELECT * FROM STREAM read_files('/Volumes/main/sales/raw');

Tables with type widening enabled require Databricks Runtime 15.4 LTS or above to read. See type widening in Lakeflow pipelines.

Update your pipeline source code after the ALTER​

ALTER TABLE changes the table. It does not change your pipeline source code. If your streaming table declares an explicit schema, you must make the same change in the pipeline source code, or the next pipeline update reconciles the table back toward the declared schema. This is the general behavior described in Limitation: Pipeline updates and changes made with ALTER.

For a streaming table with an implicit schema, such as CREATE OR REFRESH STREAMING TABLE st AS SELECT * FROM ..., no source code change is needed.

If the pipeline runs on a schedule, a triggered update could run between your ALTER TABLE and your source code change and reconcile the table back to the declared schema. To avoid this, pause the pipeline while you make the matching change:

  1. Pause the pipeline schedule.
  2. Run the ALTER TABLE statement.
  3. Update the pipeline source code to match the new schema.
  4. Unpause the pipeline schedule.

Handle an incompatible source type change​

If a source changes a column type incompatibly, for example user_id from STRING to INT, the pipeline update fails because Delta cannot convert the existing column. Migrate the column in place instead of running a full refresh:

SQL
-- 1. Add a column with the new type.
ALTER TABLE main.bronze.users ADD COLUMN user_id_new INT;

-- 2. Backfill it from the old column.
UPDATE main.bronze.users SET user_id_new = CAST(user_id AS INT);

-- 3. Drop the old column.
ALTER TABLE main.bronze.users DROP COLUMN user_id;

-- 4. Rename the new column into place.
ALTER TABLE main.bronze.users RENAME COLUMN user_id_new TO user_id;

Then update the pipeline source code to declare user_id as INT and run an update. The update succeeds without a checkpoint reset or a full refresh.

Step 2 is a DML statement against a streaming table, which has its own requirements. See Add, change, or delete data in a target streaming table. If a downstream query streams from this table, you may need to set skipChangeCommits when reading it so the backfill does not fail that stream.

Handle non-additive source schema changes​

When a source read by your streaming table drops or renames a column, the stream stops and reports the change instead of guessing your intent. Acknowledge the change to continue:

JSON
{
  "configuration": {
    "spark.databricks.delta.streaming.allowSourceColumnDrop": "always",
    "spark.databricks.delta.streaming.allowSourceColumnRename": "always"
  }
}

Set each configuration to always or to a specific Delta table version:

  • always acknowledges all current and future changes of that kind for the pipeline.
  • A version number acknowledges all schema changes up to and including that version of the source table. Find the version in the source table's history with DESCRIBE HISTORY, or from the error reported when the stream stops.
note

Acknowledging a source change does not propagate it to the streaming table. Use ALTER TABLE to make the matching change on the target.

Limitations​

The following limitations apply when you update a streaming table schema with ALTER TABLE.

  • A full refresh regenerates the streaming table from the pipeline source code, so column changes made with ALTER TABLE are not preserved. Make the equivalent change in the pipeline source code if you need it to survive a full refresh.
  • Auto Loader tracks its source schema independently of the streaming table. To make a DROP COLUMN or RENAME COLUMN take effect on a table backed by Auto Loader, see Auto Loader source schemas are not updated.
  • Streaming views are not supported. Schema evolution does not work when a streaming view sits on the path to the streaming table, covering both flows that read from a streaming view and flows defined from one. Use ALTER TABLE column operations only on streaming tables whose flows read directly from their sources.
  • DROP COLUMN and RENAME COLUMN are blocked on streaming tables with an AUTO CDC flow, which tracks change data state keyed on column identifiers. The command fails with an error. ADD COLUMN and ALTER COLUMN ... TYPE are supported.
  • Only top-level columns are supported. Changes to fields nested inside structs, arrays, or maps are not.

Auto Loader source schemas are not updated​

If a flow reads with Auto Loader, Auto Loader tracks the schema of its source files separately from the streaming table schema, and ALTER TABLE does not change it. For a source that keeps producing the column:

  • DROP COLUMN: Auto Loader continues to infer the column from source files, and the next update writes it back to the table.
  • RENAME COLUMN: Auto Loader keeps producing the old column name. For an implicit-schema table, the old column is added back alongside the new one.

To make a drop or rename take effect on a streaming table backed by Auto Loader, also constrain what Auto Loader reads. Declare an explicit reader schema that omits the column, and set the rescuedDataColumn option so the omitted field lands in the rescued data column rather than being dropped. When you supply a schema, Auto Loader does not add a rescued data column for you, so without that option the field is discarded. See What is the rescued data column?.

Additional resources​

On this page