dbt-labs / dbt-labs/dbt-adapters
[Feature] Add automatic target partition filtering in batch_iceberg_merge to enable partition pruning
- Dominant language
- Python
- Stars
- 233
- Forks
- 362
- Avg merge
- 3d 22h
- Merged PRs (30d)
- 9
Description
### Is this your first time submitting a feature request?
- [x] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [x] I have searched the existing issues, and I could not find an existing issue for this feature
- [x] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion
### Describe the feature
In `batch_iceberg_merge`, each batch filters the **source** by partition conditions (`WHERE batch`), but the **target** table has no corresponding filter in the MERGE ON clause. This means Athena cannot leverage Iceberg partition pruning on the target side, resulting in a full target table scan for every batch.
For example, with `partitioned_by=['DAY(date_column)']` and 365 daily partitions, `get_partition_batches` groups them into batches of up to `partitions_limit` (default 100). Each batch still performs a full target scan, even though only a subset of partitions is relevant.
**Proposed config:**
```yaml
{{ config(
materialized='incremental',
incremental_strategy='merge',
table_type='iceberg',
partitioned_by=["DAY(date_column)"],
unique_key='id',
merge_with_target_partition_filter=true,
) }}
```
**Behavior:**
- Default: `false` (backward compatible, no change to existing behavior)
- When `true`: automatically derive target partition filters from `partitioned_by` config and inject them into the MERGE ON clause during batch merge
- When `incremental_predicates` is also set: skip target partition filter injection (user's explicit predicates take precedence)
- Bucket partitions: skip due to substring replacement complexity
**Generated SQL example:**
```sql
-- Current (merge_with_target_partition_filter=false or unset): no target partition filter → full target scan per batch
MERGE INTO target AS target
USING (
SELECT ... FROM tmp
WHERE date_trunc('day', date_column) = DATE '2024-01-01'
or date_trunc('day', date_column) = DATE '2024-01-02'
) AS src
ON (target.id = src.id)
-- Proposed (merge_with_target_partition_filter=true): target partition filter enables Iceberg pruning
MERGE INTO target AS target
USING (
SELECT ... FROM tmp
WHERE date_trunc('day', date_column) = DATE '2024-01-01'
or date_trunc('day', date_column) = DATE '2024-01-02'
) AS src
ON (
target.id = src.id
AND (date_trunc('day', target.date_column) = DATE '2024-01-01'
or date_trunc('day', target.date_column) = DATE '2024-01-02')
)
```
**Implementation approach:**
Rather than post-hoc string replacement on batch conditions, generate target conditions at the source — in `get_partition_batches`, produce `(source_condition, target_condition)` pairs by applying `target.` prefix to column names during condition construction. This avoids fragile regex/string substitution on expressions like `date_trunc('day', date_column)`.
**Scope:**
- Batch merge only — single merge is unaffected
- Opt-in via `merge_with_target_partition_filter=true`
- Disabled when `incremental_predicates` is set (user's explicit config takes precedence)
- Skipped when bucket partitions are present
### Describe alternatives you've considered
Users can manually set incremental_predicates to add target-side filters, but this requires per-model configuration and duplicates information already available in partitioned_by. Also, `incremental_predicates` is static and cannot dynamically adapt to batch-specific partition values.
### Who will this benefit?
Any dbt-athena user running Iceberg incremental models with merge strategy and batch processing. The performance improvement scales with the number of partitions — larger tables benefit most.
### Are you interested in contributing this feature?
yes
### Anything else?
_No response_
Contributor guide
Assessment
This issue has not been assessed yet.