`scd2` merge strategy missing from `requires_root_key()` strategy whitelist — child tables silently lose `_dlt_root_id`
- Dominant language
- Python
- Stars
- 5.9k
- Forks
- 605
- Avg merge
- 1d 14h
- Merged PRs (30d)
- 38
Description
### dlt version
1.27.2
### Describe the problem
Switching a resource's `write_disposition` from `"merge"` (default `delete-insert` strategy) to `{"disposition": "merge", "strategy": "scd2"}` silently disables `_dlt_root_id` propagation to nested child tables.
On a fresh dataset the load **succeeds**, but the child tables are created **without** `_dlt_root_id` at all — so nested rows cannot be linked back to a specific parent version, which defeats the point of SCD2 for nested data. On a dataset that previously loaded with plain `merge` (where the child schema already has `_dlt_root_id REQUIRED`), the scd2 load **fails**, because the new child JSONL no longer contains the column.
### Root cause
`requires_root_key()` in `dlt/common/normalizers/json/helpers.py` auto-enables root-key propagation only for these merge strategies:
```python
merge_requires = (
merge_strategy in ["delete-insert", "upsert", "insert-only"]
if root_key_propagation is None
else root_key_propagation
)
```
`"scd2"` is missing from the list, so the auto-detection that fires for plain `merge` does not fire for scd2 — even though scd2 semantically needs the same lineage column on child tables.
### Expected behavior
`"scd2"` should be included in the strategy list (or the check should key off the `merge` disposition rather than the strategy), so that switching a nested resource to scd2 keeps `_dlt_root_id` on child tables without the user having to know to also set `root_key=True` on the source.
### Steps to reproduce
Verified on dlt 1.27.2 against BigQuery:
```python
import dlt
@dlt.source(name="repro") # note: no root_key=True
def repro():
@dlt.resource(
name="parent",
primary_key="id",
write_disposition={"disposition": "merge", "strategy": "scd2"},
)
def parent():
yield {"id": 1, "name": "foo", "children": [{"k": "v1"}]}
yield {"id": 2, "name": "bar", "children": [{"k": "v2"}]}
return parent
p = dlt.pipeline(pipeline_name="repro_scd2", destination="bigquery", dataset_name="dlt_repro")
p.run(repro())
```
Observed: load succeeds; `parent__children` schema is `{k, _dlt_parent_id, _dlt_list_idx, _dlt_id}` — **no** `_dlt_root_id`.
Expected: `parent__children` includes `_dlt_root_id`, as it does for `delete-insert`/`upsert` merges.
### Workaround (and why it is hazardous)
`@dlt.source(root_key=True)` enables propagation, but:
1. It is source-wide, so **every** resource in the source — including `replace`/`append` ones — gets `_dlt_root_id` required on its child tables. There is no per-resource control (`root_key_propagation` in the internal signatures is plumbing for the same source-wide setting, not a per-resource override).
2. If any scd2 data was already loaded while this bug was active, applying the workaround afterwards fails hard on BigQuery: dlt emits `ALTER TABLE ... ADD COLUMN _dlt_root_id ... NOT NULL` against the now-populated child table, and BigQuery rejects it (`Cannot add required fields to an existing schema`). Filed separately as dlt-hub/dlt#4048 (same DDL failure class as dlt-hub/dlt#4041).
### Suggested fix
Add `"scd2"` to the strategy list in `requires_root_key()`.
### Operating system
macOS
### Python version
3.14
### Destination
Google BigQuery
### Additional information
Related: dlt-hub/dlt#3811 (its reproducer used `@dlt.source(root_key=True)` without explaining why — that is exactly the workaround for this bug).
Contributor guide
Research direction
Start in dlt/common/normalizers/json/helpers.py at requires_root_key() and inspect how merge strategies control root-key propagation. Reproduce the nested-resource case described against the existing strategy behavior, then verify that scd2 retains _dlt_root_id on child tables without requiring source-wide root_key=True.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- data-engineering
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 74/100