dbt-labs / dbt-labs/dbt-adapters

[Feature] Athena merge strategy: source-only columns cause false schema change errors

Open
#1,831 0 comments 0 reactions 0 assignees View on GitHub
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

When using the Athena iceberg merge strategy with `delete_condition`, a common pattern is to include a control column (e.g., `_is_deleted`) in the incremental query that only exists in the source, not the target:

```sql
{{ config(
materialized='incremental',
incremental_strategy='merge',
table_type='iceberg',
unique_key='id',
on_schema_change='fail',
delete_condition='src._is_deleted = true'
) }}

{% if not is_incremental() %}
select id, msg from source_table
{% else %}
select id, msg, _is_deleted from source_cdc_table
{% endif %}
```

This column is only needed in MERGE conditions — it should never be inserted into or exist in the target table. However, `process_schema_changes` compares all source columns against the target, which causes:

- `on_schema_change='fail'`: false schema change error because `_is_deleted` is not in the target
- `on_schema_change='append_new_columns'`: `_is_deleted` is unexpectedly added to the target

The existing `merge_exclude_columns` config does not help here — it only excludes columns from the UPDATE SET clause, not from schema change detection or INSERT.

There should be a way to exclude source-only control columns from schema comparison, INSERT, and UPDATE, while still allowing them in MERGE conditions via `src.*` references.

### Describe alternatives you've considered

- Using `on_schema_change='ignore'`: works but loses protection against real unintended schema changes
- Adding `_is_deleted` to the target table: pollutes the target schema with operational metadata
- Using a CTE to drop the column before the incremental logic: not possible since `process_schema_changes` operates on the tmp table's schema directly

### Who will this benefit?

Athena users implementing CDC (Change Data Capture) or soft-delete patterns with the iceberg merge strategy, where source-only control columns drive MERGE conditions but should not affect the target schema.

### Are you interested in contributing this feature?

Yes, I have an implementation ready.

### 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.