BACKGROUND:
I have a Stored Procedure in Snowflake where a snippet of INSERT logic is called in a javascript loop, iterating over a period of time (one day’s worth of data is inserted per iteration).
ISSUE:
I am attempting to translate the above scenario into dbt.
The farthest I have come is to make the INSERT logic into an incremental model that uses a Jinja for-loop to produce SELECT statements which will then be combined together using UNION ALL.
The snag is, there are WITH (cte’s) clauses in the INSERT logic. This makes UNION ALL impossible to work because you end up with the same WITH definition (but with different dates) repeated for each iteration.
QUESTION:
Can you guys suggest for me another way to translate the above scenario into dbt?
CONSIDERATION:
I’ve thought about rewriting the INSERT logic into accepting a range of dates instead of just one day, but this particular logic is inherited from another system that, as much as possible, I want to keep in-sync with.