page119
101
Remediating CTEs
Here are some tips:
•
Replace CTEs with views or direct embedding within the core SQL
statement.
•
Simplify CTEs by removing complexity, nesting, layering code, and
using recursive calls.
•
Replace SELECT * with explicit attribute names.
•
Denormalize data structures using dynamic tables to remove
dependency on CTEs. Note that dynamic tables at the time of writing
are still in in public preview.
•
Refactor data pipelines to deliver denormalized tables as part of the
process and not as an afterthought. Tune the design!
Summary
You began this chapter by creating a database, schema, and role to begin your
investigations into query profiles with the express intent of reusing your new
environment throughout the remainder of this book. You also declared warehouses sized
between X-Small and X-Large and very briefly investigated the effect of using different
sized warehouses on query performance.
Having identified where to access query profiles, you used the new environment to
create an example query to fulfil an imaginary business requirement. Using the example
query as a starting point, you began investigating query profiles.
Optimizing query profiles is dependent upon understanding what both “good” and
“bad” query profiles look like. I introduced new terminology, explained how to interpret
query profiles, and showcased how to identify and remediate problems.
With a firm understanding of query profiles, in the next chapter you will investigate
micro-partitions.
ChapTer 3
The Query profiler