Hi!
I’ve run into a bit of confusion about how best to structure a couple of models that use a few different sources, and how best I can make them incremental.
For context - we have 2 sources, one (web_old) is no longer getting new information and the other (web_new) is.
We have a order_completed table in both of them.
What I’ve done is set up a basic transformation model for both of them. Due to some difficulties with the raw data the transformation isn’t exactly the same, but the output is the same.
base_web_old__order_completed / base_web_new__order_completed
I’ve then union_relations() them both in base_web_combined__order_completed and done some window functions in final() so I can count occurrences etc - this needs to happen here as there will be common user_ids across both tables.
Now the question is - do I need to make base_web_new__order_completed incremental, or do I just need to make base_web_combined__order_completed incremental? My guess is that I’d want to make base_web_new__order_completed so it’s not rebuilding every time the model runs, and I’d also want base_web_combined__order_completed to be incremental.
For the combined model, do I want to add an if_incremental() block to both the union_relations() CTE as well as the final select query?
Alternatively, am I doing this completely wrong and I don’t need the individual transformation models - so I should just add them all into one combined model - but I don’t think I can use union_relations() on CTEs?
Thanks!