Expected behavior

This is a follow-up to #487. When the dbt snapshot fails on merge statement, it should fail in the same way when running the command again.

Or it should success when something in snapshot configuration and/or source data is fixed.

I will prepare a PR for that.

Actual behavior

Actually, when it fails on merge, the next attempt stops with

Database Error in snapshot users_snapshot (snapshots/_snapshots.yml)
  TrinoUserError(type=USER_ERROR, name=TABLE_ALREADY_EXISTS, message="line 7:5: Destination table '<my_schema>.users_snapshot__dbt_tmp' already exists", query_id=20250610_100816_29040_jq48g)

Steps To Reproduce

1. Configure a snapshot

snapshots:
  - name: users_snapshot
    relation: source('playground', 'users')

    config:
      schema: playground
      strategy: check
      unique_key: user_id
      check_cols: ['email']
      hard_deletes: invalidate

2. Prepare a playground

create table users (
    user_id int,
    name varchar(255),
    email varchar(255),
    created_at timestamp(6)
);

3. First version of entity

insert into users (user_id, name, email, created_at)
values (2, 'Bob Smith', '[email protected]', timestamp '2022-02-14 07:00:00');

4. Initialize snapshot as

dbt snapshot -s users_snapshot

5. Another row for the same user

insert into users(user_id, name, email, created_at)
values (2, 'Bob Smith', '[email protected]', timestamp '2022-02-14 07:00:00');

6. Catch the change with snapshot

dbt snapshot -s users_snapshot

7. Another change

insert into users(user_id, name, email, created_at)
values (2, 'Bob Smith', '[email protected]', timestamp '2022-02-14 07:00:00');

8. There is should fail like in [snapshot] 'check' fails on insert-only sources #487

dbt snapshot -s users_snapshot

9. Run again to observe different error

dbt snapshot -s users_snapshot

Log output/Screenshots

Temporary table is not being dropped, so

create table "<my_schema>"."users_snapshot__dbt_tmp"
as
...

simply fails

Operating System

"Debian GNU/Linux 12 (bookworm)" (it's in devcontainers)

dbt version

1.9.4

Trino Server version

475-galaxy-1-u122-ga37c7e679a7-jdk24

Python version

3.11.12

Are you willing to submit PR?

• Yes I am willing to submit a PR!