dlt-hub / dlt-hub/dlt

`scd2` merge strategy missing from `requires_root_key()` strategy whitelist — child tables silently lose `_dlt_root_id`

Open Beginner friendly
#4,047 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.