dbt-labs / dbt-labs/dbt-adapters
[Feature] Athena merge strategy: source-only columns cause false schema change errors
- 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
Assessment
This issue has not been assessed yet.