Version >= 1.9.5 failing to clean up `__dbt_backup` tables on incremental models during full refresh

[patrikdevlin]

Problem

Upgrading to anything after (v1.9.4) *__dbt__backup tables are failing to get cleaned up during --full-refresh. This creates lingering tables and bloated developer ux (especially when using duckdb -ui).

Must be an incremental model for the issue to occur.

Screenshots / notes

incremental model config

{{ config(
    materialized="incremental", 
    unique_key=['id'], 
    materialization_strategy='delete+insert',
) }}

➜ dbt build --select stg_klaviyo__flows --full-refresh
14:03:07  Running with dbt=1.9.8
14:03:07  Registered adapter: duckdb=1.10.0
14:03:09
14:03:09  Concurrency: 4 threads (target='dev')
14:03:09
14:03:13  1 of 1 START sql incremental model main_stg.stg_klaviyo__flows ................. [RUN]
14:03:19  1 of 1 OK created sql incremental model main_stg.stg_klaviyo__flows ............ [OK in 5.36s]
14:03:19
14:03:19  Finished running 1 incremental model in 0 hours 0 minutes and 9.93 seconds (9.93s).
14:03:19
14:03:19  Completed successfully
14:03:19
14:03:19  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Relevant output with --debug flag

      drop table if exists "dev"."main_stg"."stg_klaviyo__flows__dbt_backup" cascade

14:07:19  SQL status: OK in 0.000 seconds
14:07:19  On model.dbt.stg_klaviyo__flows: ROLLBACK
14:07:19  Failed to rollback 'model.dbt.stg_klaviyo__flows'
14:07:19  On model.dbt.stg_klaviyo__flows: Close
14:07:19  1 of 1 OK created sql incremental model main_stg.stg_klaviyo__flows ............ [OK in 5.32s]

resulting tables in duckdb

➜ duckdb /tmp/dev.duckdb
DuckDB v1.4.1 (Andium) b390a7c376
Enter ".help" for usage hints.
D select name from (show all tables);
┌────────────────────────────────┐
│              name              │
│            varchar             │
├────────────────────────────────┤
│ stg_klaviyo__flows             │
│ stg_klaviyo__flows__dbt_backup │
└────────────────────────────────┘

Solution

Not entirely sure what's causing this, but for what it's worth it seems like the issue was introduced in v1.9.5

[jwills]

@hrl20 if you have time for this one