What you will be able to do
- Use cross joins, UNION variants and subqueries to combine table-like results
- Write a recursive CTE and explain how its working table drives each iteration
- Choose between fixed joins, a recursive CTE and CONNECT BY for hierarchical data
- Use SAMPLE and APPROX_COUNT_DISTINCT (HLL) to get answers from a subset or an estimate instead of an exact full scan
1.Cross joins, UNION and subqueries
To enrich data, you combine one result with another. Snowflake can join any table-like object: a table, a view, a table function's output, or the result set of a subquery that returns a table. A cross join pairs every row on one side with every row on the other side, producing a Cartesian product. This is occasionally what you want, for example to build every combination of two dimensions. More often, it happens by accident when a join condition is left out, and the result can be very large and expensive.
UNION stacks two result sets instead of putting their columns side by side. The variants differ in two ways: whether duplicate rows are removed, and whether columns are matched by position or by name.
| Operator | Matches columns by | Duplicates |
|---|---|---|
| UNION [ DISTINCT ] (the default) | Column position | Removed |
| UNION ALL | Column position | Kept |
| UNION [ DISTINCT ] BY NAME | Column name | Removed |
| UNION ALL BY NAME | Column name | Kept |
Use the BY NAME forms when the inputs list their columns in different orders, when their schemas change as columns are added or reordered, or when the columns you want to combine sit at different positions.
Checkpoint 1 of 6· Check yourself
An analyst writes SELECT ... UNION SELECT ... to append this month's events to last month's, and expects every row to be kept. What actually happens?
Plain UNION removes duplicate rows. Use UNION ALL to keep every row.
“The default is UNION DISTINCT (that is, combine rows by column position with duplicate elimination).”Source: docs.snowflake.com
2.CTEs and how recursion works
A CTE is a named subquery declared in a WITH clause. It works like a temporary view that exists only for the statement that defines it, and it makes long queries modular. Be careful when naming one: a CTE takes precedence over a table or view with the same name. A recursive CTE refers to itself. It has an anchor clause, which selects the top of the hierarchy, and a recursive clause, which selects the next level down from the previous level. The two are joined with UNION ALL.
WITH [ RECURSIVE ] <cte_name> AS
(
<anchor_clause> UNION ALL <recursive_clause>
)
SELECT ... FROM ...;The recursive clause can only project, join and filter. It can't use aggregate or window functions, GROUP BY, ORDER BY, LIMIT or DISTINCT. Snowflake evaluates the recursion with a working table that holds only the latest iteration's output. A query that is constructed incorrectly can loop forever. It stops only when it times out, for example under STATEMENT_TIMEOUT_IN_SECONDS, or when you cancel it.
Checkpoint 2 of 6· Put it in order
Put the logical evaluation of a recursive CTE in order.
- 1.Overwrite the working table with the contents of the temp table, and repeat while it is not empty
- 2.Evaluate the recursive clause, reading the working table wherever cte_name is referenced
- 3.Write the recursive clause's result to the final result set and to a temp table
- 4.Make the accumulated results available to the main SELECT through cte_name
- 5.Evaluate the anchor clause and write its result to the final result set and to the working table
The anchor clause runs once. Each iteration then reads only the previous iteration's rows, and the loop ends when an iteration returns no rows.
“The working table contains only the result of the most recent iteration.”Source: docs.snowflake.com
Sources3
3.Hierarchical data: joins, recursive CTEs or CONNECT BY
A common way to store a hierarchy is one table in which manager_ID points to another row's employee_ID. If you know how many levels there are, a self-join per level is enough: an employees-to-managers LEFT OUTER JOIN lists every employee with their manager. If the number of levels changes, though, the query has to change too. For hierarchies of unknown depth, Snowflake offers two tools.
| Aspect | CONNECT BY | Recursive CTE |
|---|---|---|
| Tables it can join | Self-joins only | A table can be joined to one or more other tables |
| Referring to the previous level | The PRIOR keyword | The table name and the CTE name |
| Columns | All columns of each row are available without listing them | Columns must be declared, and the anchor and recursive projections must match them |
| Derived columns (for example a sort_key) | START WITH can't add them; SYS_CONNECT_BY_PATH gives a similar effect | Supported |
| Built-in pseudo-columns | LEVEL, CONNECT_BY_ROOT, CONNECT_BY_PATH | None; build them yourself |
Both tools assume the tree has no gaps. If a vice president's record is deleted, the employees below them are cut off from the rest of the hierarchy. A table can hold several trees, but each query processes one contiguous tree.
Checkpoint 3 of 6· Match them up
Match each hierarchy feature to what it does.
Tap a term, then the definition that fits it.
CONNECT BY gives you PRIOR and pseudo-columns but allows only self-joins. A recursive CTE begins with an anchor clause and can join to other tables.
“in CONNECT BY you use the keyword PRIOR to indicate which column values should be taken from the previous iteration”Source: docs.snowflake.com
Sources4
4.Sampling and approximate distinct counts
On very large tables, a representative subset or an estimate is often good enough. SAMPLE (or its synonym TABLESAMPLE) returns randomly chosen rows. It differs from LIMIT, which returns rows in whatever way is fastest. You can sample a percentage of the table, or a fixed number of rows up to 1,000,000.
| Method | Unit sampled | Fixed-size (num ROWS) | SEED / REPEATABLE | Notes |
|---|---|---|---|---|
| BERNOULLI / ROW (the default) | Each row, with probability p/100 | Supported | Not supported | Expected row count is (p/100)*n |
| SYSTEM / BLOCK | Each block of rows, with probability p/100 | Not supported | Supported | Often faster; can be biased on small tables |
Checkpoint 4 of 6· Fill the gap
Which sampling method lets this query return the same sample every time it runs against an unchanged table?
SELECT * FROM testtable SAMPLE ? (3) SEED (82);A seed applies only to SYSTEM or BLOCK sampling. With a seed and an unchanged table, the query returns the same sample.
Source: docs.snowflake.comWhere you put SAMPLE matters. It applies to the one table it follows, not to the whole join. To sample the result of a join, run the join in a subquery and sample that subquery. The join still runs in full, so sampling after it doesn't reduce the join's cost.
SELECT *
FROM (
SELECT *
FROM t1 JOIN t2
ON t1.a = t2.c
) SAMPLE (1);For estimation, APPROX_COUNT_DISTINCT (alias HLL) uses the HyperLogLog algorithm to approximate COUNT(DISTINCT ...). It works as an aggregate function or as a window function with PARTITION BY. Related functions are HLL_ACCUMULATE, HLL_COMBINE and HLL_ESTIMATE; the sources for this lesson only name them.
Checkpoint 5 of 6· Check yourself
Which of these SAMPLE queries is valid?
A fixed-size sample is valid with the default row-based method and no seed. SYSTEM and SEED can't be used with a fixed-size sample, and SEED isn't supported on subqueries.
“SYSTEM, BLOCK, and SEED (seed) aren’t supported for fixed-size sampling.”Source: docs.snowflake.com
Checkpoint 6 of 6· Exam question
An analyst queries a `customer_events` table that holds many rows per `customer_id`, each with an `event_ts` column. They must return only the most recent row per customer in one SELECT, without a wrapping subquery or CTE. Which approach meets the requirement?
Correct answer: B — Append `QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_ts DESC) = 1` so the window result filters the rows.
- A. DISTINCT ON is PostgreSQL syntax and is not supported by Snowflake, so this statement fails to compile; QUALIFY with a ranking function is the Snowflake idiom.
- B. QUALIFY filters on window function results after they are computed, so ranking each customer's events newest-first and keeping row number 1 returns the latest event without nesting.
- C. HAVING filters grouped aggregate results, and a window function is not allowed there; grouping by customer_id would also collapse the detail rows being ranked.
- D. WHERE is evaluated before window functions are computed, so a window function in that clause raises an error instead of filtering the ranked rows.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.UNION keeps every row from both inputs.Why is that wrong?
Plain UNION means UNION DISTINCT and removes duplicate rows. UNION ALL keeps them.
Covered in Cross joins, UNION and subqueries
2.Putting SAMPLE after the second table samples the joined result, and so makes the join cheaper.Why is that wrong?
SAMPLE applies only to the table it follows. Even when you sample a join's result through a subquery, the join runs in full first.
Covered in Sampling and approximate distinct counts
3.CONNECT BY can do everything a recursive CTE can, including joining the hierarchy to other tables.Why is that wrong?
CONNECT BY allows only self-joins. Use a recursive CTE when the hierarchy has to be joined to other tables.
Covered in Hierarchical data: joins, recursive CTEs or CONNECT BY
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A cross join combines each row in the first table with each row in the second table, creating every possible combination of rows”
↩︎ Cross joins, UNION and subqueries“In fact, cross joins are usually the result of accidentally omitting the join condition.”
↩︎ Cross joins, UNION and subqueries“The result set returned by a subquery that returns a table.”
↩︎ Cross joins, UNION and subqueries - 2.
“UNION [ DISTINCT ] BY NAME combines rows by column name with duplicate elimination.”
↩︎ Cross joins, UNION and subqueries“UNION ALL combines rows by column position without duplicate elimination.”
↩︎ Exam trap 1“The default is UNION DISTINCT (that is, combine rows by column position with duplicate elimination).”
↩︎ Checkpoint - 3.https://docs.snowflake.com/en/user-guide/queries-cteOfficial docs
“A CTE (common table expression) is a named subquery defined in a WITH clause.”
↩︎ CTEs and how recursion works“the following are not allowed in the statement: Aggregate or window functions. GROUP BY, ORDER BY, LIMIT, or DISTINCT.”
↩︎ CTEs and how recursion works“Constructing a recursive CTE incorrectly can cause an infinite loop.”
↩︎ CTEs and how recursion works“The working table contains only the result of the most recent iteration.”
↩︎ Checkpoint - 4.
“This concept can be extended to as many levels as needed, as long as you know how many levels are needed.”
↩︎ Hierarchical data: joins, recursive CTEs or CONNECT BY“The CONNECT BY syntax supports convenient pseudo-columns such as LEVEL, CONNECT_BY_ROOT, and CONNECT_BY_PATH”
↩︎ Hierarchical data: joins, recursive CTEs or CONNECT BY“you can only query one tree at a time, and that tree must be contiguous.”
↩︎ Hierarchical data: joins, recursive CTEs or CONNECT BY“CONNECT BY allows only self-joins. Recursive CTEs are more flexible and allow a table to be joined to one or more other tables.”
↩︎ Exam trap 3“in CONNECT BY you use the keyword PRIOR to indicate which column values should be taken from the previous iteration”
↩︎ Checkpoint - 5.
“This parameter only applies to SYSTEM and BLOCK sampling.”
↩︎ Sampling and approximate distinct counts“sampling doesn’t reduce the number of rows joined and doesn’t reduce the cost of the join”
↩︎ Sampling and approximate distinct counts“The SAMPLE clause applies to only one table, not all preceding tables or the entire expression prior to the SAMPLE clause.”
↩︎ Exam trap 2“SYSTEM, BLOCK, and SEED (seed) aren’t supported for fixed-size sampling.”
↩︎ Checkpoint - 6.
“Uses HyperLogLog to return an approximation of the distinct cardinality of the input”
↩︎ Sampling and approximate distinct counts“HLL(col1, col2, ... ) returns an approximation of COUNT(DISTINCT col1, col2, ... )”
↩︎ Sampling and approximate distinct counts