aws-samples / aws-samples/dbt-glue

merge strategy not working for Delta

Open
#627 0 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.