Error: dbt tmp table doesn't exists on incremental model

Help
,
1

The problem I’m having

During our data ingestion process, we temporarily store data from upstream sources in staging schemas. We then use dbt to transform this data into our production tables through incremental models. Recently, we encountered errors indicating that temporary tables doesn’t exist, such as social_posts__dbt_tmp072749812934, do not exist.

The context of why I’m trying to do this

dbt command

dbt run --models tag:social_posts

Data transform models sql query

{{ config(
    materialized="incremental",
    unique_key='external_id',
    pre_hook=[....],
    post_hook=[....]
) }}

select
  .....
  .....
where threads.ts = threads.thread_ts
AND threads.text IS NOT NULL
{% if execute and is_incremental() %}
  and ((select max(loaded_on) from {{ this }}) is null
    or extract(epoch from threads._sdc_batched_at)::bigint > (select max(loaded_on) from {{ this }}))
{% endif %}

What I’ve already tried

I attempted to reproduce the error, but it does not occur consistently, making it difficult to pinpoint the issue. Could this be a bug in dbt-core, or is it possibly related to a configuration issue on our end? I’m happy to provide additional details about our configuration if that would help in diagnosing the problem.

Some example code or error messages

[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50  Finished running 2 incremental models in 1.63s.
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50  Completed with 1 error and 0 warnings:
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50  Database Error in model social_posts (models/social_posts.sql)
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50    relation "social_posts__dbt_tmp072749812934" does not exist
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50    LINE 7:         from "social_posts__dbt_tmp072749812934"
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50                         ^
[2024-08-23, 07:27:50 UTC] {subprocess.py:93} INFO - 07:27:50    compiled SQL at target/run/integration_transformations/models/social_posts.sql