Enabling root-key propagation on an existing populated nested table emits `ALTER ... ADD _dlt_root_id NOT NULL` — BigQuery: "Cannot add required fields to an existing schema"

dlt version

1.27.2

Describe the problem

When root-key propagation is enabled after a nested table already exists with data, dlt's schema migration emits ALTER TABLE ... ADD COLUMN _dlt_root_id ... NOT NULL against the populated child table. BigQuery never allows adding REQUIRED fields to an existing schema, so the load fails:

400 ... Cannot add required fields to an existing schema. (field: _dlt_root_id)

preceded by the warning:

Column(s) ['_dlt_root_id'] with NOT NULL are being added to existing table parent__children.
If there's data in the table the operation will fail.

There is no recovery path inside dlt: every subsequent run retries the same invalid DDL. The user has to hand-fix the destination schema (add the column as NULLABLE and backfill) or drop the child tables.

This is the same failure class as dlt-hub/dlt#4041 (MSSQL, plain merge, root cause there listed as unproven). The reproduction below confirms the suspected mechanism deterministically: any path where root-key propagation turns on after a child table exists non-empty produces this invalid DDL. The easiest way to get into that state today is the scd2 whitelist bug filed as dlt-hub/dlt#4047: scd2 child tables are silently created without _dlt_root_id, and applying the documented root_key=True workaround afterwards hits this wall.

Expected behavior

For existing non-empty tables, destinations that cannot add NOT NULL columns (BigQuery can never; MSSQL needs a default) should add new lineage columns as NULLABLE — _dlt_root_id is only consumed by merge/scd2 follow-up SQL, which tolerates NULLs for pre-existing rows — or fail with a clear migration error instead of emitting DDL the destination is guaranteed to reject.

Steps to reproduce

Verified on dlt 1.27.2 against BigQuery. Two runs into the same dataset:

Run 1 — scd2 without root_key (companion bug: child table created without _dlt_root_id):

import dlt

def make_source(root_key, rows):
    @dlt.source(name="repro", root_key=root_key)
    def src():
        @dlt.resource(
            name="parent",
            primary_key="id",
            write_disposition={"disposition": "merge", "strategy": "scd2"},
        )
        def parent():
            yield from rows
        return parent
    return src()

p = dlt.pipeline(pipeline_name="repro_scd2_migration", destination="bigquery", dataset_name="dlt_repro")

# Run 1: loads fine; parent__children has NO _dlt_root_id
p.run(make_source(False, [
    {"id": 1, "name": "foo", "children": [{"k": "v1"}]},
    {"id": 2, "name": "bar", "children": [{"k": "v2"}]},
]))

# Run 2: enable root_key (the documented workaround) -> load step fails
p.run(make_source(True, [
    {"id": 1, "name": "foo-updated", "children": [{"k": "v1b"}]},
    {"id": 3, "name": "baz", "children": [{"k": "v3"}]},
]))

Run 2 fails at step=load with:

<class 'dlt.destinations.exceptions.DatabaseTransientException'>
400 POST https://bigquery.googleapis.com/bigquery/v2/projects/<project>/queries?prettyPrint=false:
Cannot add required fields to an existing schema. (field: _dlt_root_id)

Note the exception is classified as transient, but the failure is permanent — retries can never succeed.

For completeness: starting a fresh dataset with root_key=True from the very first run works correctly even with sparse nested arrays (rows with children: []) — _dlt_root_id is part of the initial CREATE and the load succeeds. The bug is purely in evolving an existing populated table.

Operating system

macOS

Python version

3.14

Destination

Google BigQuery (same DDL class reported on MSSQL in dlt-hub/dlt#4041)

Additional information

Companion issue: dlt-hub/dlt#4047 — scd2 missing from the requires_root_key() strategy whitelist — the most likely way users end up with populated child tables lacking _dlt_root_id in the first place.

Run the supplied two-run Python reproduction against BigQuery and inspect the destination schema-migration path that generates the ADD _dlt_root_id DDL. Done means an existing populated child table receives a nullable lineage column, or a clear migration error, while fresh root_key=True loading continues to work.