What are "intermediate" models in dbt?
The idea of "intermediate" models in dbt can be a bit confusing.
I know it personally took me way too long to wrap my head around the proper way to use them.
But when used right, they can be extremely helpful for your project to keep it easy to read and maintainable long term.
So in today's video I'll break down what "intermediate" models are, show an example use case and leave you with some tips on things to avoid.
Thank you for watching!
►► The Starter Guide for dbt (Free PDF)
Get clarity on key dbt concepts so you can build better projects & avoid common mistakes → bit.ly/starter-dbt
Timestamps:
0:00 - Intro
0:56 - What is the Intermediate layer?
4:39 - Sample use case
6:26 - Things to Avoid
Title & Tags:
What are "intermediate" models in dbt?
#kahandatasolutions #dataengineering #databuildtool
►► The Starter Guide for dbt (Free PDF) → bit.ly/starter-dbt Get clarity on key dbt concepts so you can build better projects & avoid common mistakes
Great video ! Funny thing that we ran at the exact same problem you explained and we did the same exact solution by creating intermediate models folder, i did't now it was a thing in dbt, but to be honest i came expecting a special feature that dbt offer, for example in our case we had to use intermediate model for optomazation and materlaized as table, i wish dbt have a special intermediate model that drops the table when the run is done, more like a temp tables
Great video. A couple of comments Where you have very complex transformation logic combined with many many millions of rows, I have found that materializing intermediate models to tables (instead of views, and incrementally) to be much more performant. Also, I have found that you can indeed benefit from sourcing more than one mart table, from the same (high performing) intermediate table (or intermediate view over that intermediate table). To give you an example, NetSuite data ingested to snowflake. We build several netsuite fact tables, which all source from the same highly transformed "core" intermediate table. If we did that from an intermediate view, its just way too slow.
What would be the right way if I have to use an intermediate model in two different marts? Great tutorial btw.
There is an antipattern on your graph where you are using staging model twice inside intermediate model and inside its mart.
How is it different from ephemeral models? I do not see that much difference.
Intermediate = A naming convention for a directory of models Ephemeral = A dbt Materialization (aka a config) Therefore, you can create models in the Intermediate directory AS Ephemeral models. But in theory these could alternatively be View or Table materializations. Hope that helps clear it up!