Schema enforcement

Azure Databricks validates data quality by enforcing schema on write for Delta Lake tables. Schema enforcement doesn't apply to tables using non-Delta formats, such as CSV or JSON files in cloud storage.

Schema enforcement for INSERT operations

Azure Databricks enforces the following rules when inserting data into a table:

• All inserted columns must exist in the target table.
• All column data types must match the column data types in the target table.

INSERT examples

The following examples write to a managed Delta Lake table named enforce_demo. To create it, run the following:

CREATE OR REPLACE TABLE main.default.enforce_demo (id INT, name STRING, amount BIGINT);

The following INSERT fails because unknown_column doesn't exist in enforce_demo. Azure Databricks returns an UNRESOLVED_COLUMN.WITH_SUGGESTION error (SQLSTATE 42703) that suggests the valid column names:

INSERT INTO main.default.enforce_demo (id, unknown_column) VALUES (1, 'value');

The following INSERT succeeds. Azure Databricks safely casts the integer 42 to the BIGINT type of the amount column:

INSERT INTO main.default.enforce_demo (id, amount) VALUES (1, 42);

Schema enforcement for MERGE operations

Azure Databricks enforces the following rules when inserting or updating data as part of a MERGE operation:

• If the data type in the source statement does not match the target column, MERGE tries to safely cast column data types to match the target table.
• The target columns of an UPDATE or INSERT action must exist in the target table.
• When using INSERT * or UPDATE SET *:
  • The source dataset must have all the columns present in the target table.
  • Enforcement ignores columns in the source dataset that aren't present in the target table.

MERGE examples

The following examples reuse the enforce_demo table from the previous section, along with a source table named enforce_source that has an extra column. To create the source table, run the following:

CREATE OR REPLACE TABLE main.default.enforce_source (id INT, name STRING, amount BIGINT, extra_col STRING);

INSERT INTO main.default.enforce_source VALUES (1, 'Alice', 100, 'x'), (2, 'Bob', 200, 'y');

The following MERGE fails because it assigns to unknown_column, which doesn't exist in enforce_demo. Azure Databricks returns a DELTA_MERGE_UNRESOLVED_EXPRESSION error that names the columns it can resolve:

MERGE INTO main.default.enforce_demo AS t
USING main.default.enforce_source AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.unknown_column = s.name
WHEN NOT MATCHED THEN INSERT (id, unknown_column) VALUES (s.id, s.name);

The enforce_source table includes an extra_col column that enforce_demo doesn't have. The following MERGE with INSERT * succeeds because the source contains every target column. Enforcement ignores extra_col:

MERGE INTO main.default.enforce_demo AS t
USING main.default.enforce_source AS s
ON t.id = s.id
WHEN NOT MATCHED THEN INSERT *;

Modify a table schema

You can update the schema of a table using explicit ALTER TABLE statements or automatic schema evolution. See Update table schemas with schema evolution.

For example, to add a column explicitly:

ALTER TABLE catalog.schema.table_name ADD COLUMN new_column STRING;

To enable automatic schema evolution for a write operation, set the mergeSchema option:

SQL

SET spark.databricks.delta.schema.autoMerge.enabled = true;
INSERT INTO catalog.schema.table_name SELECT * FROM source_table;

Python

df.write.option("mergeSchema", "true").mode("append").saveAsTable("catalog.schema.table_name")

Schema evolution has special semantics for INSERT and MERGE operations. See Enable schema evolution.

External tables

If you modify an external table's metadata directly with external clients outside Azure Databricks or by using path-based access, Unity Catalog doesn't automatically sync updates to the schema. This might prevent schema enforcement from applying correctly.

Run MSCK REPAIR TABLE <table-name> SYNC METADATA to sync the schema with Unity Catalog. See REPAIR TABLE.