Skip to main content
Last updated on

Schema enforcement

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​

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.
note

Databricks attempts to safely cast column data types to match the target table.

INSERT examples​

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

SQL
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. Databricks returns an UNRESOLVED_COLUMN.WITH_SUGGESTION error (SQLSTATE 42703) that suggests the valid column names:

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

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

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

Schema enforcement for MERGE operations​

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:

SQL
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. Databricks returns a DELTA_MERGE_UNRESOLVED_EXPRESSION error that names the columns it can resolve:

SQL
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:

SQL
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:

SQL
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;

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 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.

On this page