# Is it possible to have incremental logic in ephemeral tables?

**URL:** <https://discourse.getdbt.com/t/is-it-possible-to-have-incremental-logic-in-ephemeral-tables/682>\
**Category:** Help\
**Tags:** incremental, ephemeral-models\
**Created:** [November 6, 2019, 3:26pm UTC](https://discourse.getdbt.com/t/is-it-possible-to-have-incremental-logic-in-ephemeral-tables/682 "2019-11-06T15:26:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![chanwd](https://avatars.discourse-cdn.com/v4/letter/c/b782af/32.png) [@chanwd](https://discourse.getdbt.com/u/chanwd)\
**Post date:** [November 6, 2019, 3:26pm UTC](https://discourse.getdbt.com/t/is-it-possible-to-have-incremental-logic-in-ephemeral-tables/682/1 "2019-11-06T15:26:40Z")

</div>

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](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/62c07cbcd3495e515f1a2a7701aaa251e2d9a51e.png)

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

![41%20AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/22ff094728b7e87014a746e6f2155dc555c453b3.png)

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.

---

<div class="post-metadata">

**Author:** ![fetanchaud](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/fetanchaud/32/189_2.png) [@fetanchaud](https://discourse.getdbt.com/u/fetanchaud)\
**Post date:** [November 6, 2019, 4:13pm UTC](https://discourse.getdbt.com/t/is-it-possible-to-have-incremental-logic-in-ephemeral-tables/682/2 "2019-11-06T16:13:33Z")

</div>

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 :

> <https://github.com/fishtown-analytics/dbt/blob/dev/louisa-may-alcott/core/dbt/include/global_project/macros/etc/is_incremental.sql>

It will always return false because your model is ephemeral.

Hoping it helps,  
Best  
@fabrice.etanchaud

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [September 23, 2022, 4:02am UTC](https://discourse.getdbt.com/t/is-it-possible-to-have-incremental-logic-in-ephemeral-tables/682/3 "2022-09-23T04:02:08Z")

</div>

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