Incremental Python models fail

[SC-fmeyer]

Target

MotherDuck database

Version

dbt-duckdb 1.9.6

General

Running incremental Python models for a MotherDuck connection fail when using the dbt-duckdb extension in version 1.9.6. Going back to version 1.9.2 works.

There was a recent change in 1.9.5 mentioning the problem: #591

Reproduction steps

• Create simple Python dbt model sample_model.py like

import pandas as pd

def model(dbt, session):
    dbt.config(materialized="incremental")

    df = pd.DataFrame({
        'id': [1, 2, 5],
        'value': ['A', 'B', 'C']
    })

    if dbt.is_incremental:
        existing_query = f"SELECT id FROM {dbt.this}"
        existing_ids = session.sql(existing_query).df()
        
        if not existing_ids.empty:
            df = df[~df['id'].isin(existing_ids['id'])]

    return df

• Modify dbt_project.yml accordingly to your model folder
• Run the model at least 2 times via dbt run --select sample_model

Result

Following error occurs:

08:10:33  Finished running 1 incremental model in 0 hours 0 minutes and 5.45 seconds (5.45s).
08:10:34  
08:10:34  Completed with 1 error, 0 partial successes, and 0 warnings:
08:10:34  
08:10:34  Failure in model sample_model (models/marts/dev/sample_model.py)
08:10:34  Runtime Error in model sample_model (models/marts/dev/sample_model.py)
  Catalog Error: Table with name sample_model__559a98c6be48 does not exist!
  Did you mean "sample_model__eb759410ca9c"?

[jwills]

Hey @SC-fmeyer -- is this happening on a MotherDuck database, or for a local DuckDB instance?

[goobill]

I also found similar issues with the plugin and incremental models.

A quick fix was remove quotes from identifiers by updating dbt_project.yml

https://docs.getdbt.com/reference/project-configs/quoting

quoting:
  database: false
  schema: false
  identifier: false

It resolved that issue but I then got an issue with DBT wrapping column names with quotes. Annoyingly DBT doesn't have a config to disable this.

I came across these issues when trying to add SQLGlot into this plugin. #544

[SC-fmeyer]

Hey @SC-fmeyer -- is this happening on a MotherDuck database, or for a local DuckDB instance?

@jwills Ah sorry for not mentioning, it is on MotherDuck.

[jwills]

@SC-fmeyer no worries-- I figured it had to be MD (it's the only DuckDB that uses these temp tables) and while I couold repro it, for the life of me I could not figure out how it was happening or how to fix it. Threw it over to @hrl20 and @matsonj to take a look at.

[jwills]

huh maybe @goobill's comment about the quoting is a clue?

[hrl20]

Hi! i've looked into the issue and have a likely fix: #638
For now, i believe this problem can be worked around with disable_transactions: true (the link pr contains the explanation as to why that is the case)