I was wondering if it was possible to have an ephemeral model but to be able to use incremental logic in there?
The reason is that recently we were trying to move some of our CTE into ephemeral tables but we don’t want to pull all the records every time our ETL runs.
A workaround to this is to create a CTE and include the incremental logic there (assuming the model is materialized as incremental). The long term issue with that is if the CTE is useful for other new models, it would be nice to not have to repeat the CTE in that new models. If we go the route of materialized it as its own model, then we start to clutter our schema with a lot of intermediate tables.
As a test, I created a child model that is materialized as “ephemeral” but contained incremental logic.
I then created a parent model that is materialized as “incremental”. I ref the child model.
To test it, I ran the parent model once to populate the table. I ran it a 2nd time in the hopes that it would interpolate the child SQL as a CTE within the parent. It did not and pulled all the records, essentially ignoring the incremental logic in the child.
Anyhow, I thought I’d share this particular experience and hoping that we’ll have the ability to create incremental ephemeral tables.