page113
95
While automatic clustering and search optimization can help, automatic clustering
can invalidate cached results. There is no substitute for well-formed and optimally
performing SQL statements.
Common Table Expressions
This chapter was inspired by the post at
https://select.dev/posts/should-you-use-
ctes-in-snowflake
by Niall Woodward (@NiallWoodward).
CTEs are individually named temporary result sets built within the SQL statement. A
SQL statement may have none, one, or many CTEs. They are identified by the presence
of a
WITH
clause before the
SELECT
statement. CTEs may also be referred to as subquery
refactoring and are supported by many RDBMSs in addition to Snowflake.
The following is the general form of a CTE:
WITH <subquery>
SELECT <attributes>
FROM <table>
WHERE <predicates>
ORDER BY <ordering>;
Simple CTE Use Case
CTEs are often used to create subsets of data that may be repeatedly used within the
main body of the
FROM
/
WHERE
clause to simplify code. A common use case is to use
UNION
ALL
to join two tables into a single CTE thus simplifying the main body of code, as this
next example shows:
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
Create an example SQL statement with a CTE:
WITH regiongroup AS
(
SELECT r_regionkey,
r_name,
'EMEA' AS r_regiongroup
FROM region_baseline
ChapTer 3
The Query profiler