dbt model cannot be refered in the same model

Help
1

The problem I’m having

Hi, I’m new to dbt and
I’m having a problem referencing the same table creating the model

I'm trying to multiple inserts into this table say tableA.
So this is a sample of the code i have

select distinct 1 as x1,
a.col1 as x2
from tableA a

  union

select 
distinct 1 as x1, 
cr.col5 as x2
from tableA cr
and not exists (
  select 1
  from {{ this }} m
  where m.x1 = 1
  AND m.x2 = cr.x2
  )

Error:
19:56:47 Opening a new connection, currently in state closed
19:56:48 Snowflake adapter: Snowflake query id: 01abc28c-0402-d621-005b-8a830196377e
19:56:48 Snowflake adapter: Snowflake error: 002003 (42S02): SQL compilation error:
Object ‘DEV.DW.tableB’ does not exist or not authorized.

Any advice?

2

You say that your sample code is for tableA, but the error message is

Are you refing that model but haven’t built it yet, or aren’t granting permissions to it? I don’t see where in your code tableB is being used, maybe if you’re doing a dbt run without any selection logic this is actually an error in a different model.

3

Hey Joelabes, tableA is created in another model and already exists. And tableB is the filename of this the model I’m trying to run now and get the error. The error is complaning about tableB, because i’m refering it with {{ this }}} in the where clause in the second select stmt

4

Ahh OK! On your first run, the table doesn’t exist.

The standard solution for this is to use the is_incremental() macro. It will return false unless the table alrady exists (and you’re not running a --full-refresh- check the doc for the full rundown).

You need to write your model in such a way that it works whether or not the table already exists, to enable dbt to run in an idempotent way.

5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.