> Hygiene, readability, consistency, and correctness of SQL code are some of the many barriers to moving
> forward with self-serve data. Whether it be convoluted tables with a growing number of columns, or a 1,000+
> line unformatted chain of SQL CTE’s conspicuously named “dim_person”, [...]
I really wonder why people prefer CTEs over creating a view which is what I always do. Those single-purpose
view typically get a name derived from the name of the table or view they are there for, with an underscore
prepended to mark them as private (not part of the official API). This setup almost looks like a CTE in the
SQL source but has the distinct advantage of creating a relation that I can query against, re-use, and run
tests against. What's not to like? When is a CTE genuinely better than an ad-hoc view?
The same goes BTW for those query monsters one can find as earnest solution proposals on postgresql.org
(meaning they are imbued with some authority and probably seen by some as an affirmation what good SQL looks
like). Turns out many of these are really lousily written. When I find something useful I just copy & paste
to see whether they work for me, then at some point I tend to break them apart into more manageable and
potentially re-usable chunks.
I really wonder why people prefer CTEs over creating a view which is what I always do. Those single-purpose view typically get a name derived from the name of the table or view they are there for, with an underscore prepended to mark them as private (not part of the official API). This setup almost looks like a CTE in the SQL source but has the distinct advantage of creating a relation that I can query against, re-use, and run tests against. What's not to like? When is a CTE genuinely better than an ad-hoc view?
The same goes BTW for those query monsters one can find as earnest solution proposals on postgresql.org (meaning they are imbued with some authority and probably seen by some as an affirmation what good SQL looks like). Turns out many of these are really lousily written. When I find something useful I just copy & paste to see whether they work for me, then at some point I tend to break them apart into more manageable and potentially re-usable chunks.