dbt-labs / dbt-labs/dbt-adapters

[Feature] Add automatic target partition filtering in batch_iceberg_merge to enable partition pruning

Open
#1,801 1 comment 0 reactions 0 assignees View on GitHub
feature:iceberg pkg:dbt-athena triage:product type:enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.