dbt-labs / dbt-labs/dbt-adapters
[Bug] insert_overwrite materialization does not respect column ordering for existing tables - dbt-snowflake
- Dominant language
- Python
- Stars
- 233
- Forks
- 362
- Avg merge
- 3d 22h
- Merged PRs (30d)
- 9
Description
### Is this a new bug?
- [x] I believe this is a new bug
- [x] I have searched the existing issues, and I could not find an existing issue for this bug
### Which packages are affected?
- [ ] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [ ] dbt-postgres
- [ ] dbt-redshift
- [x] dbt-snowflake
- [ ] dbt-spark
### Current Behavior
The insert_overwrite macro in dbt-snowflake uses overwrite_columns from the model config to build the INSERT OVERWRITE statement's column list. If overwrite_columns is not specified, it falls back to SELECT *, which relies on the source column ordering. This means that when columns in an existing table have a different order than the columns in the dbt model (e.g., after a column reorder), the data gets inserted into the wrong columns, causing data corruption or errors.
### Expected Behavior
The insert_overwrite macro should use dest_columns (the columns from the target/destination table) to build the column list for the INSERT OVERWRITE statement. This ensures the insert always respects the correct column ordering of the existing table, regardless of whether columns have been reordered in the dbt model. The target column list should always be explicitly specified rather than relying on user-provided config or implicit SELECT * ordering.
### Steps To Reproduce
> 1. Create a dbt model with an incremental materialization using insert_overwrite strategy in Snowflake:
>
> {{ config(> materialized='incremental',> incremental_strategy='insert_overwrite' ) }}> SELECT col_a, col_b, col_c FROM source_table>
> 2. Run dbt run to create and populate the table initially.
> 3. Reorder the columns in the dbt model:
>
> {{ config(> materialized='incremental',> incremental_strategy='insert_overwrite', ) }}> SELECT col_c, col_a, col_b FROM source_table>
> 4. Run dbt run again (incremental run).
> 5. Observe that the INSERT OVERWRITE statement does not respect the target table's column ordering, causing data to be inserted into the wrong columns.
### Relevant log output
```shell
```
### Environment
```markdown
- OS: macOS 26.2 (arm64)
- Python: 3.11.10
- dbt-snowflake: 1.11.0
```
### Additional Context
_No response_
Contributor guide
Assessment
This issue has not been assessed yet.