CrateDB Incremental model materialization not working with dbt-core

CrateDB
1

I am trying to use CrateDB with dbt Core 1.9.3 for incremental model materialization. The initial data loads successfully, but the incremental model is not working as expected.

Here is the model I used:

{{ config(materialized='incremental',
    unique_key='id',
    incremental_strategy='delete+insert'
  )
}}

select
id,
device_id,
temperature,
humidity,
pressure
from doc.timeseries
where device_id = 1
{% if is_incremental() %}
and id > (select MAX(id) from {{ this }})  
{% endif %}

During incremental runs, I can see that new records are being generated in the temporary tables, but they are not getting moved to the model table. However, when I run dbt run --full-refresh, the new data loads successfully.

What could be causing this issue, and how can I fix it?"

2

Thank you Ayas for reporting this, we have reproduced this and tracked it down to a missing REFRESH TABLE command, we will get it fixed ASAP.

3

Hi again Ayas, this is fixed in the latest version of dbt-cratedb2, please run:

pip install dbt-cratedb2 --upgrade

(or equivalent for your virtual env)
and give it another try.
Please also do not hesitate to let us know if you encounter any other issues.
Thank you.

4

Hi Hernan ,
I see that the issue has been resolved, and the model is now updating with new data. However, I have a question—after running the incremental model, the temporary table with the new data is still present. Is this expected behavior? Should we manually handle its removal, or is it supposed to be managed by the dbt run command?

5

Thank you Ayas for your time in our call earlier, I am just giving an update here in case anyone else comes across this post.
The cleanup of the temporary tables is not currently implemented, but it is in our backlog and we can prioritise it if needed. The tables can easily be identified by their name, so setting up a schedule task to clean this up outside of dbt should also be relatively simple.