Is it possible to have incremental logic in ephemeral tables?

Help
,
1

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.

33%20AM
33%20AM697×272 31.7 KB

I then created a parent model that is materialized as “incremental”. I ref the child model.

41%20AM

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.

2

Hi @chanwd, sql code in your incremental block will never be evaluated, returning all rows every time.

Have a look at the is_incremental() macro definition :

It will always return false because your model is ephemeral.

Hoping it helps,
Best
@fabrice.etanchaud

1 Like
3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.