DBT models don't replace tables or views

[canthonissen]

I was checking out the duckdb connector for dbt and tried to replicate a dbt-snowflake tutorial.
After making the necessary change to the SQL code, I found that dbt run fails after the first run, even with --full-refresh,
Upon further investigation, I found that the SQL that gets executed is of the form:
create or replace ... with the snowflake adapter, bur
create ... with the duckdb adapter.
This causes the model to fail, when it tries to create objects that already exits.
Is this intended behaviour, or am I missing a config somewhere?
I'm using:
dbt-core 1.7.11
dbt-duckdb 1.7.3

[jwills]

That's surprising-- is it something you could post somewhere so I could try to reproduce it?

[canthonissen]

Ok. You can have a look here: https://github.com/canthonissen/dbt_duckdb_puzzle
a) Run create_and_load_db.py to create and populate the database with raw data
b) Run dbt deps to load the dbt_utils package
c) dbt build should work the first time round
Thereafter any reruns fail.

[jwills]

yeah I just need a version of your profiles.yml for learn_duckdb so I can run it locally-- thanks!

[canthonissen]

Here you go:

learn_duckdb:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: airbnb.db

[jwills]

yeah I seem to be able to run dbt build over and over again w/o issue; how does it fail for you? What error do you get?

[canthonissen]

1. First run, no problem

(base) (venv) PS C:\Users\Use\OneDrive\Development\dbt-course\learn_duckdb> dbt build
18:52:28 Running with dbt=1.7.11
18:52:29 Registered adapter: duckdb=1.7.3
18:52:29 Unable to do partial parsing because saved manifest not found. Starting full parse.
18:52:31 Found 8 models, 1 snapshot, 9 tests, 1 seed, 3 sources, 0 exposures, 0 metrics, 507 macros, 0 groups, 0 semantic models
18:52:31
18:52:31 Concurrency: 1 threads (target='dev')
18:52:31
18:52:31 1 of 19 START sql view model main.src_hosts .................................... [RUN]
18:52:32 1 of 19 OK created sql view model main.src_hosts ............................... [OK in 0.17s]
18:52:32 2 of 19 START sql view model main.src_listings ................................. [RUN]
18:52:32 2 of 19 OK created sql view model main.src_listings ............................ [OK in 0.10s]
18:52:32 3 of 19 START sql view model main.src_reviews .................................. [RUN]
18:52:32 3 of 19 OK created sql view model main.src_reviews ............................. [OK in 0.10s]
18:52:32 4 of 19 START seed file main.seed_full_moon_dates .............................. [RUN]
18:52:32 4 of 19 OK loaded seed file main.seed_full_moon_dates .......................... [INSERT 272 in 0.20s]
18:52:32 5 of 19 START snapshot dev.scd_raw_listings .................................... [RUN]
18:52:32 5 of 19 OK snapshotted dev.scd_raw_listings .................................... [OK in 0.24s]
18:52:32 6 of 19 START sql view model main.dim_hosts_cleansed ........................... [RUN]
18:52:32 6 of 19 OK created sql view model main.dim_hosts_cleansed ...................... [OK in 0.10s]
18:52:32 7 of 19 START sql view model main.dim_listings_cleansed ........................ [RUN]
18:52:32 7 of 19 OK created sql view model main.dim_listings_cleansed ................... [OK in 0.09s]
18:52:33 8 of 19 START sql incremental model main.fct_reviews ........................... [RUN]
18:52:34 8 of 19 OK created sql incremental model main.fct_reviews ...................... [OK in 1.37s]
18:52:34 9 of 19 START test accepted_values_dim_listings_cleansed_room_type__Entire_home_apt__Private_room__Shared_room__Hotel_room [RUN]
18:52:34 9 of 19 PASS accepted_values_dim_listings_cleansed_room_type__Entire_home_apt__Private_room__Shared_room__Hotel_room [PASS in 0.08s]
18:52:34 10 of 19 START test dim_listings_minimum_nights ................................ [RUN]
18:52:34 10 of 19 PASS dim_listings_minimum_nights ...................................... [PASS in 0.05s]
18:52:34 11 of 19 START test no_nulls_in_dim_listings ................................... [RUN]
18:52:34 11 of 19 PASS no_nulls_in_dim_listings ......................................... [PASS in 0.11s]
18:52:34 12 of 19 START test not_null_dim_listings_cleansed_host_id ..................... [RUN]
18:52:34 12 of 19 PASS not_null_dim_listings_cleansed_host_id ........................... [PASS in 0.05s]
18:52:34 13 of 19 START test not_null_dim_listings_cleansed_listing_id .................. [RUN]
18:52:34 13 of 19 PASS not_null_dim_listings_cleansed_listing_id ........................ [PASS in 0.05s]
18:52:34 14 of 19 START test positive_value_dim_listings_cleansed_minimum_nights ........ [RUN]
18:52:34 14 of 19 PASS positive_value_dim_listings_cleansed_minimum_nights .............. [PASS in 0.05s]
18:52:34 15 of 19 START test relationships_dim_listings_cleansed_host_id__host_id__ref_dim_hosts_cleansed_ [RUN]
18:52:34 15 of 19 PASS relationships_dim_listings_cleansed_host_id__host_id__ref_dim_hosts_cleansed_ [PASS in 0.06s]
18:52:34 16 of 19 START test unique_dim_listings_cleansed_listing_id .................... [RUN]
18:52:34 16 of 19 PASS unique_dim_listings_cleansed_listing_id .......................... [PASS in 0.06s]
18:52:34 17 of 19 START sql table model main.mart_fullmoon_reviews ...................... [RUN]
18:52:36 17 of 19 OK created sql table model main.mart_fullmoon_reviews ................. [OK in 1.12s]
18:52:36 18 of 19 START test consistent_created_at ...................................... [RUN]
18:52:36 18 of 19 PASS consistent_created_at ............................................ [PASS in 0.06s]
18:52:36 19 of 19 START sql view model main.dim_listings_w_hosts ........................ [RUN]
18:52:36 19 of 19 OK created sql view model main.dim_listings_w_hosts ................... [OK in 0.09s]
18:52:36
18:52:36 Finished running 6 view models, 1 seed, 1 snapshot, 1 incremental model, 9 tests, 1 table model in 0 hours 0 minutes and 4.60 seconds (4.60s).
18:52:36
18:52:36 Completed successfully
18:52:36
18:52:36 Done. PASS=19 WARN=0 ERROR=0 SKIP=0 TOTAL=19

Second run

(base) (venv) PS C:\Users\Use\OneDrive\Development\dbt-course\learn_duckdb> dbt build
18:52:44 Running with dbt=1.7.11
18:52:45 Registered adapter: duckdb=1.7.3
18:52:45 Found 8 models, 1 snapshot, 9 tests, 1 seed, 3 sources, 0 exposures, 0 metrics, 507 macros, 0 groups, 0 semantic models
18:52:45
18:52:45 Concurrency: 1 threads (target='dev')
18:52:45
18:52:45 1 of 19 START sql view model main.src_hosts .................................... [RUN]
18:52:45 1 of 19 ERROR creating sql view model main.src_hosts ........................... [ERROR in 0.12s]
18:52:45 2 of 19 START sql view model main.src_listings ................................. [RUN]
18:52:45 2 of 19 ERROR creating sql view model main.src_listings ........................ [ERROR in 0.09s]
18:52:46 3 of 19 START sql view model main.src_reviews .................................. [RUN]
18:52:46 3 of 19 ERROR creating sql view model main.src_reviews ......................... [ERROR in 0.07s]
18:52:46 4 of 19 START seed file main.seed_full_moon_dates .............................. [RUN]
18:52:46 4 of 19 ERROR loading seed file main.seed_full_moon_dates ...................... [ERROR in 0.09s]
18:52:46 5 of 19 START snapshot dev.scd_raw_listings .................................... [RUN]
18:52:46 5 of 19 ERROR snapshotting dev.scd_raw_listings ................................ [ERROR in 0.12s]
18:52:46 6 of 19 SKIP relation main.dim_hosts_cleansed .................................. [SKIP]
18:52:46 7 of 19 SKIP relation main.dim_listings_cleansed ............................... [SKIP]
18:52:46 8 of 19 SKIP relation main.fct_reviews ......................................... [SKIP]
18:52:46 9 of 19 SKIP test accepted_values_dim_listings_cleansed_room_type__Entire_home_apt__Private_room__Shared_room__Hotel_room [SKIP]
18:52:46 10 of 19 SKIP test dim_listings_minimum_nights ................................. [SKIP]
18:52:46 11 of 19 SKIP test no_nulls_in_dim_listings .................................... [SKIP]
18:52:46 12 of 19 SKIP test not_null_dim_listings_cleansed_host_id ...................... [SKIP]
18:52:46 13 of 19 SKIP test not_null_dim_listings_cleansed_listing_id ................... [SKIP]
18:52:46 14 of 19 SKIP test positive_value_dim_listings_cleansed_minimum_nights ......... [SKIP]
18:52:46 15 of 19 SKIP test relationships_dim_listings_cleansed_host_id__host_id__ref_dim_hosts_cleansed_ [SKIP]
18:52:46 16 of 19 SKIP test unique_dim_listings_cleansed_listing_id ..................... [SKIP]
18:52:46 17 of 19 SKIP relation main.mart_fullmoon_reviews .............................. [SKIP]
18:52:46 18 of 19 SKIP test consistent_created_at ....................................... [SKIP]
18:52:46 19 of 19 SKIP relation main.dim_listings_w_hosts ............................... [SKIP]
18:52:46
18:52:46 Finished running 6 view models, 1 seed, 1 snapshot, 1 incremental model, 9 tests, 1 table model in 0 hours 0 minutes and 0.97 seconds (0.97s).
18:52:46
18:52:46 Completed with 5 errors and 0 warnings:
18:52:46
18:52:46 Runtime Error in model src_hosts (models\src\src_hosts.sql)
Catalog Error: Could not rename "src_hosts__dbt_tmp" to "src_hosts": another entry with this name already exists!
18:52:46
18:52:46 Runtime Error in model src_listings (models\src\src_listings.sql)
Catalog Error: Could not rename "src_listings__dbt_tmp" to "src_listings": another entry with this name already exists!
18:52:46
18:52:46 Runtime Error in model src_reviews (models\src\src_reviews.sql)
Catalog Error: Could not rename "src_reviews__dbt_tmp" to "src_reviews": another entry with this name already exists!
18:52:46
18:52:46 Runtime Error in seed seed_full_moon_dates (seeds\seed_full_moon_dates.csv)
Catalog Error: Table with name "seed_full_moon_dates" already exists!
18:52:46
18:52:46 Runtime Error in snapshot scd_raw_listings (snapshots\scd_raw_listings.sql)
Catalog Error: Table with name "scd_raw_listings" already exists!
18:52:46
18:52:46 Done. PASS=0 WARN=0 ERROR=5 SKIP=14 TOTAL=19

Run with full refresh

(base) (venv) PS C:\Users\Use\OneDrive\Development\dbt-course\learn_duckdb> dbt build --full-refresh
18:52:59 Running with dbt=1.7.11
18:52:59 Registered adapter: duckdb=1.7.3
18:53:00 Found 8 models, 1 snapshot, 9 tests, 1 seed, 3 sources, 0 exposures, 0 metrics, 507 macros, 0 groups, 0 semantic models
18:53:00
18:53:00 Concurrency: 1 threads (target='dev')
18:53:00
18:53:00 1 of 19 START sql view model main.src_hosts .................................... [RUN]
18:53:00 1 of 19 ERROR creating sql view model main.src_hosts ........................... [ERROR in 0.11s]
18:53:00 2 of 19 START sql view model main.src_listings ................................. [RUN]
18:53:00 2 of 19 ERROR creating sql view model main.src_listings ........................ [ERROR in 0.07s]
18:53:00 3 of 19 START sql view model main.src_reviews .................................. [RUN]
18:53:00 3 of 19 ERROR creating sql view model main.src_reviews ......................... [ERROR in 0.09s]
18:53:00 4 of 19 START seed file main.seed_full_moon_dates .............................. [RUN]
18:53:00 4 of 19 ERROR loading seed file main.seed_full_moon_dates ...................... [ERROR in 0.09s]
18:53:00 5 of 19 START snapshot dev.scd_raw_listings .................................... [RUN]
18:53:01 5 of 19 ERROR snapshotting dev.scd_raw_listings ................................ [ERROR in 0.12s]
18:53:01 6 of 19 SKIP relation main.dim_hosts_cleansed .................................. [SKIP]
18:53:01 7 of 19 SKIP relation main.dim_listings_cleansed ............................... [SKIP]
18:53:01 8 of 19 SKIP relation main.fct_reviews ......................................... [SKIP]
18:53:01 9 of 19 SKIP test accepted_values_dim_listings_cleansed_room_type__Entire_home_apt__Private_room__Shared_room__Hotel_room [SKIP]
18:53:01 10 of 19 SKIP test dim_listings_minimum_nights ................................. [SKIP]
18:53:01 11 of 19 SKIP test no_nulls_in_dim_listings .................................... [SKIP]
18:53:01 12 of 19 SKIP test not_null_dim_listings_cleansed_host_id ...................... [SKIP]
18:53:01 13 of 19 SKIP test not_null_dim_listings_cleansed_listing_id ................... [SKIP]
18:53:01 14 of 19 SKIP test positive_value_dim_listings_cleansed_minimum_nights ......... [SKIP]
18:53:01 15 of 19 SKIP test relationships_dim_listings_cleansed_host_id__host_id__ref_dim_hosts_cleansed_ [SKIP]
18:53:01 16 of 19 SKIP test unique_dim_listings_cleansed_listing_id ..................... [SKIP]
18:53:01 17 of 19 SKIP relation main.mart_fullmoon_reviews .............................. [SKIP]
18:53:01 18 of 19 SKIP test consistent_created_at ....................................... [SKIP]
18:53:01 19 of 19 SKIP relation main.dim_listings_w_hosts ............................... [SKIP]
18:53:01
18:53:01 Finished running 6 view models, 1 seed, 1 snapshot, 1 incremental model, 9 tests, 1 table model in 0 hours 0 minutes and 0.91 seconds (0.91s).
18:53:01
18:53:01 Completed with 5 errors and 0 warnings:
18:53:01
18:53:01 Runtime Error in model src_hosts (models\src\src_hosts.sql)
Catalog Error: Could not rename "src_hosts__dbt_tmp" to "src_hosts": another entry with this name already exists!
18:53:01
18:53:01 Runtime Error in model src_listings (models\src\src_listings.sql)
Catalog Error: Could not rename "src_listings__dbt_tmp" to "src_listings": another entry with this name already exists!
18:53:01
18:53:01 Runtime Error in model src_reviews (models\src\src_reviews.sql)
Catalog Error: Could not rename "src_reviews__dbt_tmp" to "src_reviews": another entry with this name already exists!
18:53:01
18:53:01 Runtime Error in seed seed_full_moon_dates (seeds\seed_full_moon_dates.csv)
Catalog Error: Table with name "seed_full_moon_dates" already exists!
18:53:01
18:53:01 Runtime Error in snapshot scd_raw_listings (snapshots\scd_raw_listings.sql)
Catalog Error: Table with name "scd_raw_listings" already exists!
18:53:01
18:53:01 Done. PASS=0 WARN=0 ERROR=5 SKIP=14 TOTAL=19

[canthonissen]

I added target/ and logs/ to the repo (https://github.com/canthonissen/dbt_duckdb_puzzle).
You can have a look to see if it generates the same sql code on your side.
The DML in target/run does not use replace or drop statements for tables and views, so it makes sense that the build fails.
This was not the case when I tested the snowflake connector, which generated create or replace statements.

[jwills]

Huh- maybe an OS problem, somehow? I run on OS X, it looks like you're doing this on Windows?

[canthonissen]

I am.
Is the same sql code being generated on OS X?
If so, it's likely a duckdb issue, otherwise it's probably dbt.

[jwills]

Have you tried running it under WSL? Most of the Windows users of dbt I know use that for running dbt pipelines, I'm curious if that would make a difference

[matsonj]

i tried to repro on my windows machine, but was able to run dbt run & dbt build w/o issue. I used this repo: dbt_duckdb_puzzle

versions:

windows 11 pro
python 3.10.11
dbt 1.7.7
duckdb 1.7.3

I suppose there could be a regression between dbt 1.7.7 and the current version, but that seems unlikely.

[canthonissen]

I set up a new venv with python 3.10, dbt-core 1.7.7, dbt-duckdb 1.7.3 and duckdb 0.10.1.
Still getting the same issue. It does not seem to be related to the SQL code generation.
The only difference I can see is that I am using Windows 10 Home
It work fine under WSL with the latest releases.
Neither environment seems to replace or drop old tables/views. It just seems to work under WSL.
Very strange.

[jarichb]

I'm now getting a similar issue but on mac OSX also on DuckDB
dbt-duckdb==1.8.3
dbt-core==1.8.6
Python 3.8.11

Exactly the same issue basically, also first run going perfect and second run getting these errors

[jwills]

This is a Motherduck database, I assume-- since otherwise the file for the database in question would have to exist and be attached already.

[jwills]

And are we attaching to MD later? Like, is MD in the path of the profile?

[williamkaper]

And are we attaching to MD later? Like, is MD in the path of the profile?

Profile connects to the main target md db and the project overrides the database for reports. I shared w Jacob on the MD Slack and I can share w you as well when Im back home

[williamkaper]

@jwills just sent you the same docs I sent over to @matsonj

[matsonj]

based on my current understanding, i made a minimal repro: https://github.com/matsonj/repro

@williamkaper can you take a look at the dbt_project.yml and the macros to make sure this replicates what you are seeing?

I am able to run dbt build multiple times in a run without issue, so I must be missing something in the config that forces this to occur.

[williamkaper]

I pushed up a change to the dummy project @matsonj shared that shows the issue (at least when I run it)

[williamkaper]

Just to update @jarichb - I was able to work with the DuckDB/DBT guys to reproduce the issue we were both experiencing, and they are looking into what's causing it.

[guenp]

Hey @williamkaper, thanks so much for working with @matsonj and creating a repro!
I was able to repro the Catalog error if I run:

dbt build --vars '{"build_env":"PROD","org_prefix":"a_fake"}'

but if I run:

dbt build --vars '{"build_env":"prod","org_prefix":"a_fake"}'

(Note lower case "prod"), it does not reproduce.
Could one of you verify that this is also the case on your end?

[matsonj]

Confirmed on my end. Lower case works.

[williamkaper]

Confirmed as well. I added logic to my generate_database_names macro to lower the generated database name string and that solved it. Thank you @guenp !!! 🎉 🎉 🥇 . I'm assuming there will an update to the adapter to eventually protect against this?

[guenp]

Awesome, thanks for confirming! 🎉
Yeah for sure I'll take a look with @jwills and see if we can figure out a fix.

[jarichb]

Thanks for the hard work all, looking forward to see the solution 😀

…

On Thu, 3 Oct 2024, 07:47 Jacob Matson, ***@***.***> wrote: Confirmed on my end. Lower case works. — Reply to this email directly, view it on GitHub <#376 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AP4NCDTZUYEXJC6ERS5LZ5DZZTK53AVCNFSM6AAAAABGERTAL2VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTAOBSG44DSMQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[williamkaper]

@guenp figured out the issue. 🎉 Basically it is a string casing issue on the database name.

@jarichb if you change your dbt_project to +database: "{{ (env_var('ENV', 'DEV') + '_project') | lower}}" it should resolve your issue until the casing issue in the library is resolved

[jarichb]

Thanks @guenp and @williamkaper, works now! :)

[guenp]

Thanks all for reporting/creating a repro! This was fixed in #453