I agree that CTEs can help compose complex query from several ingredient queries.
But once several different queries start sharing common CTE ingredients - this is an indicator that you are relying on implicit(informal) schema, which should be formalized and formalized.
Just take your CTE ingredient queries and declare them as views, isolated to your namespace. And let everyone reuse your views, instead of copy-pasting CTE ingredients, or using code-generation to achieve the same.
The benefit is single source of truth - there will be only one definition of CTE
subquery, and it can evolve/extend independently while letting everyone reuse your parts.
I assure you - if you take your CTE with 4 subqueries, and instead create 4 views, the query plan will be the same regardless of using CTEs with code-generation or using views.
The benefit is you dont have to use codegeneration, each view will be testable/verifiable
But once several different queries start sharing common CTE ingredients - this is an indicator that you are relying on implicit(informal) schema, which should be formalized and formalized.
Just take your CTE ingredient queries and declare them as views, isolated to your namespace. And let everyone reuse your views, instead of copy-pasting CTE ingredients, or using code-generation to achieve the same.
The benefit is single source of truth - there will be only one definition of CTE subquery, and it can evolve/extend independently while letting everyone reuse your parts.
I assure you - if you take your CTE with 4 subqueries, and instead create 4 views, the query plan will be the same regardless of using CTEs with code-generation or using views.
The benefit is you dont have to use codegeneration, each view will be testable/verifiable