in this video we're going to take a look at a specific type of a materialization known as
ephemeral as a quick overview materializations are strategies for persisting dbt models
in a warehouse you can think of it as it's how your models are going to deploy we've
talked about how you can configure them in a separate video and as i mentioned in this
one we're going to look at ephemeral so let's start to unpack that a little bit
i think ephemeral is one of the more interesting types of materializations because they're not
directly built in the database they're effectively just a common table expression they're going to
take the code that you write within your model and whenever you reference it it's going to be used
as a cte in that other model before we actually get into reviewing these pros and cons let's go
through an example first and then we'll come back to this and it'll make a little more sense so here
we are back in our project and what we're going to do is within one of our models let's just say this
example team this is one we've used quite a bit in our examples we are going to add the config block
here it's set materialized equal to ephemeral and let's see what happens first off if we were to
just try to run this model what would happen dbt run now remember ephemeral is not going to
persist it's just a cte so let's see what happens if we try to run this guy it compiled it runs but
nothing was deployed and that's because it's just a cte and we didn't reference it anywhere
but what i do know is this example teams is used in another model example team details
here as the first cte remember this is going to compile as a cte so now where we see this ref
instead of this being the fully qualified name of a database schema and table it's going to
select all from the cte that we generate let's see what this looks like let's now try to run
this example team details model dbt run and we would expect this model to run but what will be
interesting is when we go into how it's compiled okay so this was run let's go into our target
run and example team details and take a look at what we got here so here we can see this now
looks a little different than what we're used to we can see dbt cte this is what's going to happen
when you use the ephemeral model it's replacing it with this we're looking at these side by side
and let's clean this part up a little bit so we can get a better view if we look at this here
let's actually bring this one up now too everything from here to here is pulled from this
example underscore teams this is our ephemeral model is being placed within this cte here so
again it's literally copying and pasting everything from right here right into here
and now what we can see if we go back to team details over here select all from that ref
teams is now select all from this it compiled it within a cte by itself and now we're selecting it
in another cte it's kind of like inception and it can get a little confusing sometimes but the
idea here is now this model is not deployed as its own materialized table or view in snowflake
it's only going to be visible when you run it so now let's go back to the documentation and
review some of those pros cons now that we've seen this in action a little bit
okay let's review again here so first off pros you can still write reusable logic that's true
we were able to put those in a separate model and then reference it and they can help you keep
your data warehouse clean by reducing clutter and that's because it's not going to actually
deploy as we saw it's just going to compile it as another cte within a cte depending on
how you use it and what they're saying here is alternatively you could just still deploy your
models but use custom schemas and put them in different places that's an option too
and if you're curious about custom schemas there is another video that touches on that you can
learn a little bit more about how that works what are some cons you cannot select directly
from this model again that's because it's built as a cte doesn't exist anywhere other than in the
dvt project when it compiles overuse of ephemeral can also make queries harder to debug and this is
true now that we saw what that compiled version looks like you can imagine if you have a lot of
those that can get a little tricky to figure out what's happening where and debug that so some
general advice use ephemeral for lightweight transformations that are early on in your dag
only used for one or two downstream models and for models that do not need to be queried directly
i think a lot of this makes more sense now that we've seen it that's why i wanted to go over it
first and show you what it looks like i think there's a lot of ways you could use this you can
be creative but now hopefully you have a baseline understanding of how this type of materialization
works and you can figure out in your project where you might want to use this thanks for watching