Schema evolution: new NOT NULL column fails on existing Silver data (Lakeflow Connect source)

[faruk]

Hi everyone,

We ingest data from our source database with Lakeflow Connect, through a foreign catalog, into a Bronze layer. In the Silver layer, we apply the correct data types and constraints based on our data dictionary.

Recently, some tables in the source database were updated and received new columns. Databricks automatically synced these schema changes. However, one of the new columns, in the table my_taable, is defined as NOT NULL, and the Silver write now fails.

The issue is that some data was already ingested into Silver before this column existed. When the column is added, all the existing rows get NULL for it, so the NOT NULL constraint is violated as soon as Databricks tries to write.

The error we get is:
NOT NULL constraint violated for column: my_column.

Our questions:

1. What is the recommended way to handle a new NOT NULL column when the target table already contains data?
2. Should we add the column as nullable first, backfill the existing rows (with a default value or from the source), and only then add the NOT NULL constraint?
3. Would a column default value (DEFAULT) on the Delta table be a good option here?

[ThomazNeto · Accepted solution]

Hi Faruk,

Short answer: yes to question 2, and DEFAULT won't fix the rows you already have.

1. Add as nullable, backfill, then set NOT NULL

That's the order the docs support. When you add a column to an existing Delta table, all existing rows are treated as NULL, and before adding a NOT NULL constraint Databricks checks that every existing row satisfies it. So the constraint can only go on after the backfill:

ALTER TABLE silver.my_table ADD COLUMN my_column STRING;

MERGE INTO silver.my_table t
USING bronze.my_table b
ON t.id = b.id
WHEN MATCHED AND t.my_column IS NULL THEN UPDATE SET t.my_column = b.my_column;

ALTER TABLE silver.my_table ALTER COLUMN my_column SET NOT NULL;

If the source can't provide the value for old rows, use an UPDATE with a business default instead of the MERGE.

2. DEFAULT doesn't cover existing rows

The ALTER TABLE docs say that when you add a column to an existing Delta table you can't define a DEFAULT in the same statement, and a default you set afterwards only applies to new rows. Setting it with ALTER COLUMN doesn't change existing rows either. Also, the docs describe the default as being used on INSERT and MERGE ... INSERT when the column isn't specified. If your Silver job selects the column explicitly and writes NULL, don't count on the default to fill it.

3. Check Bronze before backfilling from it

Adding a NOT NULL column to a source table that already has rows usually requires a default value on the source side (SQL Server, for example, rejects it otherwise). The Lakeflow Connect full refresh docs list "column additions with default values" among the DDL changes that need a full refresh, which you have to trigger manually unless auto full refresh is enabled. Until that happens, the older Bronze rows may not have the source value, so a backfill from Bronze can still leave NULLs. A full refresh reprocesses all records from the source, but keep in mind it first clears the target table's data and state.

Docs:
ALTER TABLE ADD COLUMN / ALTER COLUMN
Constraints
Lakeflow Connect full refresh (auto full refresh policy)

[faruk]

Hi @ThomazNeto , 

Thank you for you detailed answer. 

[data_pulse · Accepted solution]

Good points already shared on the nullable → backfill → NOT NULL approach. I'd add a few Lakeflow Connect and Silver recovery considerations, particularly to avoid unnecessary full refreshes.

1. Lakeflow Connect: Verify historical data before backfilling

Schema synchronization doesn't guarantee that historical values for newly added columns are populated in Bronze. Depending on the source connector and DDL change, a full refresh may be needed.

SELECT
    COUNT(*) AS total_rows,
    COUNT(*) - COUNT(my_column) AS null_rows
FROM bronze.my_taable;

If Bronze still contains historical NULL values, a Silver backfill from Bronze won't resolve them. Check the connector's full-refresh requirements and whether the source can supply those values.

2. Silver table type determines the recovery strategy

If the source genuinely has no historical values and the business approves a fallback, a conditional UPDATE is simpler than another MERGE :

UPDATE silver.my_taable
SET my_column = 'UNKNOWN'
WHERE my_column IS NULL;

This is only appropriate when UNKNOWN is a valid business value. If historical source values exist, a keyed backfill remains preferable.

For a Lakeflow-managed streaming table, don't assume the same ad hoc DML approach is supported. Consider pipeline-supported mechanisms such as REPLACE WHERE flows for bounded historical reprocessing, or a carefully planned refresh.

%sql
CREATE OR REFRESH STREAMING TABLE silver.my_taable
FLOW REPLACE WHERE ingest_date >= date_add(current_date(), -7) BY NAME
SELECT ... FROM STREAM(bronze.my_taable)

For a one-time historical backfill you'd widen the predicate to the affected range once, the window itself never repairs history outside it.

For AUTO CDC-managed Silver,

If Silver is maintained using Lakeflow AUTO CDC, consider protecting existing non-null values from partial CDC updates:

CREATE FLOW silver_cdc AS AUTO CDC INTO silver_my_taable
FROM stream(bronze_cdc_events)
KEYS (id)
IGNORE NULL UPDATES ON (my_column)
SEQUENCE BY change_sequence
STORED AS SCD TYPE 1

Historical repair for a CDC-managed streaming table needs a supported backfill or refresh strategy, not an assumption that ordinary UPDATE/MERGE DML can be applied.

3. Distinguish Bronze and Silver refreshes

A Lakeflow Connect full refresh reloads source data into Bronze, while a Silver recomputation rebuilds derived data from Bronze.

If Bronze is already complete and replayable, a Silver-only recovery may be sufficient. But refreshing Silver cannot reconstruct historical values that are missing from Bronze.

4. Validate the incoming Silver write, not just existing rows

The original issue is a write failure, so even after historical backfilling, the next run may fail if the transformation still produces explicit NULL values.

from pyspark.sql import functions as F

invalid = silver_transformed_df.filter(
    F.col("my_column").isNull()
)

if invalid.limit(1).count():
    raise ValueError(
        "Silver write blocked: my_column contains NULL"
    )

Also verify whether NOT NULL is enforced by the Silver Delta table, the pipeline definition, or the write schema. Fixing the table data alone may not fix the pipeline logic.

5. DEFAULT is useful, but not a historical repair mechanism

A Delta DEFAULT can help future inserts that omit the column, subject to table and runtime support. It won't repair existing nulls or explicitly supplied null values.

My preferred sequence would be:

Verify source/Bronze completeness → identify Silver table type → targeted backfill or supported refresh → validate incoming writes → enforce constraints → resume processing.

This addresses both the historical data gap and the underlying write-path issue without automatically resorting to a full pipeline refresh.