Refer child model in parent model in DBT

Question

Below is my data flow in DBT

publisher_DB_view ->(incremental pull)-> Raw_tbl ->(appends)-> stg_tbl

The requirement is that Raw table should pull only the records incrementally from publisher_DB_view where publisher_DB_view .date_id > stg_tbl.date_id. To resolve this i am refering to stg_tbl.date_id inside where clause of raw_tbl.date_id like below .

raw_tbl.sql

{{config(materialized='table')}}
select * 
from {{ref('external_db_view')}}
where date_id > ( select max(date_id) from {{ref('stg_tbl)}})
--------------------------------------------------------------

stg_tbl.sql

{{config(materialized='incremental',unique_key='date_id')}}
select * 
from {{ref('raw_tbl')}}

My model fails with uncaught exception while parsing. I assume this is because of DAG nature so a child model can not be referenced back in parent model . I tried implementing this by creating a macro / pre_hook to get max(date_id) from stg_tbl and then use in raw_tbl however it also fails with same parsing error . i checked by referring to another model (independent) and then model succeeds)

Is this a real limitation in DBT and if so , is there any work around to get this done ? Thanks in advance for answering !

Answer 1

If Raw_tbl is not needed, then stg_tbl can be changed to look like dbt's documentation suggestion:

select * 
from {{ref('external_db_view')}}

{% if is_incremental() %}

-- this filter will only be applied on an incremental run
-- (uses > to include records whose timestamp occurred since the last run of this model)
where date_id > ( select max(date_id) from {{ this }}))

{% endif %}

Answer 2

In the example you use,

• in model raw_tbl , raw_tbl depends on stg_tbl
• in model stg_tbl, it depends on raw_tbl again

This leads to a circular dependency, which breaks so called DAG directed acyclic graph. And DAG is a mandatory requirement, otherwise it will cause an infinite loop.

In your example, I can recommend use two separate stages of incremental models. Each model is independent from each other, but only depends on itself {{ this }} .

raw_tbl.sql

{{config(materialized='incremental')}}
select * 
from {{ref('external_db_view')}}
where date_id > ( select max(date_id) from {{ this }})

stg_tbl.sql

{{config(materialized='incremental',unique_key='date_id')}}
select * 
from {{ref('raw_tbl')}}  
where date_id > ( select max(date_id) from {{ this }})