aws-samples / aws-samples/dbt-glue
merge strategy not working for Delta
- Dominant language
- Python
- Stars
- 147
- Forks
- 96
- Avg merge
- 7h 4m
- Merged PRs (30d)
- 5
Description
### Describe the bug
A simple upsert to a DeltaLake model is failing.
Cause:
1. source dataset is materialized as a temporary view
2. merge statement is referencing source dataset prepending schema
### Steps To Reproduce
A simple incremental delta model is enough to reproduce:
```
- name: delta_model
config:
materialized: incremental
incremental_strategy: merge
file_format: delta
unique_key: 'id'
columns:
- name: id
- name: val
```
### Expected behavior
Model run succeeds and delta table is updated succesfully
### Screenshots and log output
Output from `dbt run --debug`:
source generation query
```sql
create or replace temporary view delta_model_tmp as with source_data as (
select 1 as id, 10 as val
union all
select 3 as id, 5 as val
)
select *
from source_data
```
merge query
```sql
merge into dbt_glue_sc631266_db.delta_model as DBT_INTERNAL_DEST
using dbt_glue_sc631266_db.delta_model_tmp as DBT_INTERNAL_SOURCE
on
DBT_INTERNAL_SOURCE.id = DBT_INTERNAL_DEST.id
when matched then update set
*
when not matched then insert (id, val) values (DBT_INTERNAL_SOURCE.id, DBT_INTERNAL_SOURCE.val)
```
### System information
**The output of `dbt --version`:**
```
Core:
- installed: 1.10.13
- latest: 1.10.13 - Up to date!
Plugins:
- athena: 1.9.4 - Update available!
- spark: 1.9.3 - Up to date!
- glue: 1.10.11 - Up to date!
- duckdb: 1.9.6 - Up to date!
```
**The operating system you're using:**
**The output of `python --version`:**
Python 3.11.11
### Additional context
Add any other context about the problem here.
Contributor guide
Research direction
Start by reproducing the incremental Delta model with the shown dbt configuration and inspect the merge SQL from dbt run --debug. Trace the adapter's temporary-view generation and merge entry point; done means the merge references the temporary view correctly and the model run updates the Delta table successfully.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100