Prune BigQuery partitions when merging data

This example loads orders with three levels of nested tables: lines, their parts and the serial numbers of the parts. Each load replaces the days that its data contains, in the root table and in the nested tables. On BigQuery, the delete drops only the partitions of these days, which is fast and free.

In this example, you learn how to:

• replace records with a delete-insert destination scope
• fill a placeholder with the days that the extract saw
• copy event_day from each order into its nested records with the propagate_to_nested map step
• set the partition column and the destination scope of nested tables with nested_hints
• load nested tables without the _dlt_root_id column

Full source code

from datetime import date
from typing import Any, Iterator

import dlt
from dlt.common.schema.typing import TColumnSchema, TDeleteInsertStrategyDict
from dlt.extract import DltResource
from dlt.sources.helpers.transform import propagate_to_nested

# `event_day` is the daily partition column of the root table and of the nested tables
EVENT_DAY: TColumnSchema = {
    "name": "event_day",
    "data_type": "date",
    "partition": True,
    "nullable": False,
}
# each table deletes only the days of the load
REPLACE_DAYS: TDeleteInsertStrategyDict = {
    "disposition": "merge",
    "strategy": "delete-insert",
    "destination_scope": "event_day IN {days}",
}
NESTED_TABLES = ("lines", ("lines", "parts"), ("lines", "parts", "serials"))


# every nested table deletes by its own destination scope, so it needs no `_dlt_root_id`
@dlt.source(root_key=False)
def shop(data: list[dict[str, Any]]) -> Any:
    # the days of this load, a new set for each source instance
    days: set[date] = set()

    def track_day(order: dict[str, Any]) -> dict[str, Any]:
        days.add(order["event_day"])
        return order

    def days_of(resource: DltResource, table_name: str) -> list[date]:
        # dlt calls this function after the extract step
        return sorted(days)

    @dlt.resource(
        write_disposition={**REPLACE_DAYS, "extra_placeholders": {"days": days_of}},
        columns=[EVENT_DAY],
        nested_hints={
            path: dlt.mark.make_nested_hints(columns=[EVENT_DAY], write_disposition=REPLACE_DAYS)
            for path in NESTED_TABLES
        },
    )
    def orders() -> Iterator[list[dict[str, Any]]]:
        yield data

    # copy `event_day` into the lines, parts and serials
    return orders.add_map(track_day).add_map(propagate_to_nested("event_day", max_nesting=3))


def make_orders(days: list[date], size: int) -> list[dict[str, Any]]:
    # three orders per day, each with `size` lines, parts per line and serials per part
    return [
        {
            "order_id": f"{day}-{n}",
            "event_day": day,
            "lines": [
                {
                    "line": line,
                    "parts": [
                        {"part": part, "serials": [{"serial": f"{part}-{s}"} for s in range(size)]}
                        for part in range(size)
                    ],
                }
                for line in range(size)
            ],
        }
        for day in days
        for n in range(3)
    ]


# use the "bigquery" destination to prune the partitions
pipeline = dlt.pipeline(
    pipeline_name="replace_daily_partitions",
    destination="duckdb",
    dataset_name="shop_data",
)

# load five days, each order has two lines with two parts with two serials
days = [date(2026, 10, day) for day in range(1, 6)]
pipeline.run(shop(make_orders(days, size=2)))

# replace two days, their orders now have one line with one part with one serial
replaced = [days[1], days[3]]
load_info = pipeline.run(shop(make_orders(replaced, size=1)))

# the load package keeps the days that the merge replaced
package_state = pipeline.get_load_package_state(load_info.loads_ids[0])
assert package_state["placeholders"]["orders"]["days"] == replaced

# only the replaced days changed, in the root table and in the nested tables
dataset = pipeline.dataset()
query = "SELECT event_day, COUNT(*) FROM {table} GROUP BY event_day"
for table, rows_per_day, replaced_rows_per_day in (
    ("orders", 3, 3),
    ("orders__lines", 6, 3),
    ("orders__lines__parts", 12, 3),
    ("orders__lines__parts__serials", 24, 3),
):
    counts = dict(dataset(query.format(table=table)).fetchall())
    assert counts == {day: replaced_rows_per_day if day in replaced else rows_per_day for day in days}
    # nested tables link to their parent with `_dlt_parent_id` only
    assert "_dlt_root_id" not in pipeline.default_schema.get_table_columns(table)

Partition pruning on BigQuery

Before it inserts the load, dlt deletes the replaced days from each table:

DELETE FROM `shop_data`.`orders__lines` WHERE event_day IN ('2026-10-02', '2026-10-04');
DELETE FROM `shop_data`.`orders__lines__parts` WHERE event_day IN ('2026-10-02', '2026-10-04');
DELETE FROM `shop_data`.`orders__lines__parts__serials` WHERE event_day IN ('2026-10-02', '2026-10-04');
DELETE FROM `shop_data`.`orders` WHERE event_day IN ('2026-10-02', '2026-10-04');

On BigQuery, the partition hint creates tables partitioned by day. Each delete compares only the partition column to literals, so BigQuery drops the whole partitions of these days and reads no data. Such a delete changes only metadata: it is fast and processes 0 bytes, so it is free. The load works even on tables with require_partition_filter.

The destination_scope of the nested tables is required for this. Without it, dlt deletes the nested rows of the replaced orders with a subquery on the root table: _dlt_root_id IN (SELECT _dlt_id FROM orders WHERE ...). This delete reads all partitions of the nested table. A nested table can use its own destination scope only because propagate_to_nested copies event_day from the order into all its nested records.

Nested tables without _dlt_root_id

By default, delete-insert adds the _dlt_root_id column to nested tables to delete nested rows with their root rows. Here, every nested table deletes by its own destination scope, so dlt never reads _dlt_root_id. The source sets root_key=False, and the nested tables link to their parent rows with _dlt_parent_id only. This works for any nesting level, here for three levels.

If you add a nested table without a destination scope, dlt needs _dlt_root_id to delete its rows. Then the load fails with No `root_key` column (e.g. `_dlt_root_id`) in table ....

warning

If an order moves to another day, dlt does not delete its copy in the old day. See the warning in Replacing a partition with delete-insert.