why do we make the intermediate .sql
For me the intermediate tables make sense for a couple of cases.
One is purely for organizing your queries and tables. One of the strengths of dbt is its ability to make your data assets (queries and tables) easy for the analytic engineering team to manage. A well organized dbt project makes it easier for new hires to onboard, for other team members to understand queries quickly, and for you to remember how queries that you previously built work. Intermediate tables can help you break out queries that have many CTEs into more logical blocks. Instead of a query with several CTEs it may be easier to break out initial parts of the query into a materialized table or view, using the intermediate layer. This also let’s you more easily debug upstream parts of your query if necessary.
The other reason I use intermediate tables is to reuse tables in downstream fact/dimension/marts tables. Often you may find yourself rewriting the same CTE in several queries, for example if you need a “spine” of all your users, or a date range. In this case you can materialize an intermediate table and “ref” that intermediate table in several downstream queries. This let’s you reuse that query and if you need to make a logical change to the intermediate table, the logic will be updated in all of your downstream queries.
Hope that helps!