I’ve been wondering this for a while and appreciate the writeup!! I love CTE’s but it does seem to be a bit excessive in the DBT examples and I do wonder if they takeaway from some readability.
A couple of additional questions here:
-
“Second, it gives you an easy mechanism to consistently alias all input tables” - would you not be better served by using that consistent alias in your table names? Does orders really need “fct_” in-front of it and does customers really need “stg_jaffle_shop__”? If they didn’t then you wouldn’t have to remember and maintain the loosely held common alias mechanism over all your files.
-
Is the last example of switching which table you’re returning a really a very common one in your workflows? If not - it’s also not too hard to comment out a section (easy with most code editors and a shortcut) and type “select * from customers_orders” at the end. That’s not really too many additional key presses, but I may be missing something.