Your table evolved without asking — the silent schema auto-merge

The default fails loudly — until one line changes the session

For a Delta INSERT, an unknown target column fails with UNRESOLVED_COLUMN.WITH_SUGGESTION (SQLSTATE 42703). For MERGE with INSERT * or UPDATE SET *, enforcement requires every target column in the source but silently ignores source columns that do not exist in the target.

SET spark.databricks.delta.schema.autoMerge.enabled = true;

Three sanctioned per-operation opt-ins

1. INSERT, batch, or streaming writes: set .option("mergeSchema", "true") on the individual bronze or landing write.
2. INSERT and MERGE: use SQL WITH SCHEMA EVOLUTION or the table API .withSchemaEvolution(). These forms require Databricks Runtime 15.4 LTS or above.
3. COPY INTO: use COPY_OPTIONS ('mergeSchema' = 'true') for that load.

What additive evolution actually does

New columns append at the end of the target schema, their case is preserved, and old rows read NULL for those columns. For MERGE, the new column must exist in the source schema and either be named in an assignment or be covered by UPDATE SET * / INSERT *. A computed expression can populate a source column, but assigning an expression to a new target name does not itself define that missing source-schema column.

Automatic evolution is for additive columns. Renames, type changes, and drops require explicit operations such as overwriteSchema or Delta column mapping, depending on the change. For silver and gold, keep evolution reviewed and explicit: ALTER TABLE … ADD COLUMN.

Setup — rerunnable and self-contained

SET spark.databricks.delta.schema.autoMerge.enabled = false;

CREATE OR REPLACE TABLE demo_evolve (
  id INT,
  amount BIGINT
);

INSERT INTO demo_evolve VALUES (1, 100);

CREATE OR REPLACE TEMP VIEW demo_source AS
SELECT 2 AS id, 200 AS amount, 'vip' AS tier;

Before

DESCRIBE demo_evolve;

The trap

SET spark.databricks.delta.schema.autoMerge.enabled = true;

INSERT INTO demo_evolve
SELECT * FROM demo_source;

After

DESCRIBE demo_evolve;

SELECT *
FROM demo_evolve
ORDER BY id;

Restore the enforcement default, then prove the failure

The first two statements intentionally reset the demo table to its original two-column state. Without that reset, tier would already exist and the final INSERT would succeed.

SET spark.databricks.delta.schema.autoMerge.enabled = false;

CREATE OR REPLACE TABLE demo_evolve (
  id INT,
  amount BIGINT
);

INSERT INTO demo_evolve VALUES (1, 100);

INSERT INTO demo_evolve (id, tier)
VALUES (3, 'x');

The deliberate silver / gold path

ALTER TABLE demo_evolve ADD COLUMN tier STRING;

INSERT INTO demo_evolve (id, tier)
VALUES (3, 'x');

The reviewed DDL changes the contract before the data arrives. The insert is then valid, and the schema change remains visible in table history.

Use this when

1. Bronze or landing only: additive source columns are expected and the individual write owns the schema contract.
2. Named-column consumers: downstream readers do not depend on ordinal positions.
3. Coordinated maintenance: you can restart streaming readers and avoid concurrent schema-changing writes.

Avoid this when

1. Silver or gold: evolve through reviewed ALTER TABLE only.
2. Positional readers: new columns append at the end and can change positional extracts.
3. Streaming readers or CDF consumers: coordinate any explicit schema change; schema updates terminate streams reading the table.
4. Session- or cluster-wide: never enable auto-merge as ambient configuration.

Snapshot before the write

CREATE OR REPLACE TEMP VIEW schema_before AS
SELECT column_name,
       full_data_type,
       ordinal_position
FROM my_catalog.information_schema.columns
WHERE table_schema = current_schema()
  AND table_name = 'demo_evolve';

Diff after the write

SELECT column_name,
       full_data_type,
       ordinal_position
FROM my_catalog.information_schema.columns
WHERE table_schema = current_schema()
  AND table_name = 'demo_evolve'
EXCEPT
SELECT column_name,
       full_data_type,
       ordinal_position
FROM schema_before;

Five-line gate checklist

1. Session auto-merge configuration is never set anywhere.
2. Evolution is enabled per write on bronze or landing only.
3. Silver and gold evolve only through reviewed ALTER TABLE.
4. DESCRIBE HISTORY is the audit trail for the table change.
5. Mechanics check: new columns append at the end, old rows read NULL, and a schema change terminates streams reading the table.