Support for Partition-level "replace" strategy (BigQuery OVERWRITE PARTITIONS)

Feature description

Currently, the write_disposition="replace" option in dlt performs a full truncation of the destination table before loading new data.

However, a widespread pattern in data engineering (especially with BigQuery) is idempotent partition loading.

Are you a dlt user?

Yes, I run dlt in production.

Use case

I receive a file daily containing events data for a specific date (e.g., 2023-01-01).

My goal is to load this data into a partitioned BigQuery table. If I re-run the pipeline for that date, I want to overwrite only the events$20230101 partition, preserving the history in the rest of the table.

Current dlt behavior: If I use "replace", I lose my history (entire table gets dropped and populated with this one given date). If I use "append", I get duplicates if I re-run the job.

I've tried to achieve that using $ in a resource name, like this:

@dlt.source
def source():
for day in gen_recent_days(3):
   yield resource(day=day).with_name(
        f"events${day:%Y%m%d}"
    )

As a result, I'm getting sharding tables instead. The dollar sign gets replaced with _ and for each resource gets loaded into a separate table: events_20230101, events_20230102, events_20230103 etc.
I'd like each resource to get loaded to the same table events to another partition.

Proposed solution

I would like a mechanism to instruct dlt to replace only the data corresponding to specific partition values, rather than the whole table.

Currently, the only way to achieve this in dlt is a manual workaround:

1. Load data into a staging table with write_disposition="replace".
2. Use pipeline.sql_client() to manually execute a BigQuery transaction that deletes the specific partition from the destination table and inserts from the staging table.

Example:

import dlt
from dlt.destinations import bigquery
from google.cloud import bigquery

# 1. Define your data and the target partition date
data = [{"id": 1, "name": "Alice", "date": "2023-01-01"}, 
        {"id": 2, "name": "Bob", "date": "2023-01-01"}]
partition_date = "2023-01-01"

# 2. Configure the pipeline
pipeline = dlt.pipeline(
    pipeline_name="my_partition_pipeline",
    destination="bigquery",
    dataset_name="my_dataset"
)

# 3. Load data to a STAGING table first
# We force the table name to be 'my_table_staging'
info = pipeline.run(
    data,
    table_name="my_table_staging", 
    write_disposition="replace" 
)

client = bigquery.Client()

# Define the query that selects your new data
sql = "SELECT * FROM `project.dataset.my_table_staging`"

# Configure destination to be the SPECIFIC partition
job_config = bigquery.QueryJobConfig(
    destination="project.dataset.target_table$20230101",
    write_disposition="WRITE_TRUNCATE" # This overwrites ONLY the partition defined above
)

query_job = client.query(sql, job_config=job_config)
query_job.result()
print("Partition overwritten.")

This works but requires writing custom, dialect-specific SQL for every pipeline, which reduces the benefit of using dlt as an abstraction layer.

This feature is critical for building idempotent incremental pipelines.

Having this abstractly available in dlt would be a massive improvement for incremental loading workflows.

Related issues

No response

Start by tracing how pipeline.run handles write_disposition="replace" and how the BigQuery destination interprets table names and partitions; resource.with_name and pipeline.sql_client illustrate the current behavior and workaround. Done means an abstract partition-level replace can overwrite only the requested partition, preserve other partitions, and avoid custom dialect-specific SQL.