We have a situation where we need multiple (4-10) inserts to a target table & target table is also serving as a source. The sequence in which queries are executed is critical for final results. This is incremental model
Each query is independent unit of work and you have dependencies between them to run sequentially within BR table (ABC) model.
Query 1 - Source 1 → BR table (ABC)
Query 2 - BR table (ABC) & source 2 → BR table (ABC)
Query 3 - BR table (ABC) & Source 3 → BR table (ABC)
Query 4 - BR table (ABC) & Source 4 → BR table (ABC)
We have tried the UNION all approach suggested in Inserts | dbt Developer Hub. However the results are not as expected, not sure if the order of queries is followed in above scenario.
code as below
{{config(
materialized = ‘incremental’,
full_refresh = false,
schema = ‘schema name’,
incremental_strategy = ‘insert’,
)
}}
With base_data AS(
select * from {{ref(‘Query 1’)}}
UNION
select * from {{ref(‘Query 2’)}}
UNION
select * from {{ref(‘Query 3’)}}
UNION
select * from {{ref(‘Query 4’)}}
)
select
*
FROM base_data