Compares two schemas (from DDL strings or existing tables) and returns a detailed diff with generated ALTER TABLE statements for compatible changes, supporting column additions, type widening, property changes, and clustering changes.
COMPARE SCHEMA <source> WITH <target>
-- Source/target can be a DDL string or table name## Overview COMPARE SCHEMA analyzes the structural differences between two table schemas and generates ALTER TABLE statements to bring the target schema into alignment with the source. It is designed for ETL pipelines, schema drift detection, and automated schema evolution workflows. ## Behavior The command resolves both source and target schemas. For table name references, the engine opens the Delta table and reads its current schema from the snapshot. For DDL strings, the engine parses the CREATE DELTA TABLE statement to extract the schema definition. The comparator analyzes the schemas and produces a change set. Each change is classified by type and compatibility: ### Supported Changes (Compatible) - **ADD_COLUMN**: Columns present in the source but absent from the target. Generates ALTER TABLE ADD COLUMN statements. - **WIDEN_TYPE**: Type promotions that are safe and lossless (e.g., INT to BIGINT, FLOAT to DOUBLE). Generates ALTER TABLE ALTER COLUMN TYPE statements. - **SET_PROPERTY**: Table properties in the source that differ from the target. Generates ALTER TABLE SET TBLPROPERTIES statements. - **CLUSTER_BY**: Clustering column changes. Generates ALTER TABLE CLUSTER BY statements. ### Incompatible Changes (Report Only) - **NARROW_TYPE**: Type narrowing (e.g., BIGINT to INT) that may lose data. - **ADD_NOT_NULL**: Adding a NOT NULL constraint to an existing nullable column. - **PARTITION_CHANGE**: Changes to partition columns, which cannot be altered in place. The result set contains seven columns: | Column | Type | Description | |--------|------|-------------| | change_type | UTF8 | The type of change (ADD_COLUMN, WIDEN_TYPE, etc.). | | column_name | UTF8, nullable | The affected column name (NULL for table-level changes). | | old_value | UTF8, nullable | The current value or type in the target schema. | | new_value | UTF8, nullable | The desired value or type from the source schema. | | alter_statement | UTF8, nullable | The generated ALTER TABLE SQL (NULL for incompatible changes). | | is_compatible | BOOLEAN | true if the change can be applied automatically. | | incompatibility_reason | UTF8, nullable | Explanation for incompatible changes. | ## Compatibility COMPARE SCHEMA is a DeltaForge extension. There is no equivalent in standard SQL. The generated ALTER statements use DeltaForge DDL syntax and can be executed directly.
| Name | Type | Description |
|---|---|---|
source | The source schema to compare from. Accepts either a fully qualified table name (the table's live schema is read from the Delta snapshot) or a DDL string enclosed in single quotes containing a CREATE DELTA TABLE statement. The source represents the desired or incoming schema. | |
target | The target schema to compare against. Accepts either a fully qualified table name or a DDL string. The target represents the existing schema that needs to be synchronized. ALTER statements are generated to modify the target to match the source. |
-- Compare two existing tables
COMPARE SCHEMA staging.raw.events WITH warehouse.analytics.events;-- Compare a DDL string with an existing table
COMPARE SCHEMA 'CREATE DELTA TABLE t (id BIGINT, name STRING, email STRING)'
WITH warehouse.sales.customers;-- Compare two DDL strings to check type compatibility
COMPARE SCHEMA
'CREATE DELTA TABLE src (id BIGINT, price DOUBLE, created_at TIMESTAMP)'
WITH
'CREATE DELTA TABLE tgt (id INT, price FLOAT)';-- Schema drift detection in an ETL pipeline
COMPARE SCHEMA staging.ingest.raw_orders WITH gold.analytics.orders;