It’s totally a matter of preference! I thought it was overkill when I started at Fishtown, but now I like it!
As rules of thumb:
- We always like to have
reforsourcefunctions at the top of a file, as we feel that it makes it easy for us to understand the dependencies when first looking at a model. “Importing” them as CTEs helps us with this. - We pretty much always finish off our queries with a
select * from my_final_cte. I find that this pattern helps me when writing SQL for a model, as it means I can keep chaining together CTEs easily, without having to go back and add a CTE name, parens, and indents to the part of the query I was just working on! I also find it easier for debugging during development
Overall these conventions help us ensure every analyst that works on our project writes SQL that looks consistent, which improves readability in the long run!
We’ve also done some quick investigation and found that on modern data warehouses it doesn’t impact the performance of your queries, more on that over here 
CTEs are Passthroughs--Some research!