Snowflake Joins, Set Operators, ASOF JOIN and QUALIFY for Data Discovery

1.Joins: which rows survive

Once discovery shows that a required element lives in another table, you need a join. The main choice is what happens to rows that have no match. Snowflake's recommended form is JOIN with an ON subclause. A bare JOIN, with no INNER or OUTER keyword, is an inner join.

USING (key_column) is shorthand for an equality on a column that has the same name in both tables. With SELECT *, that column appears only once. The most expensive mistake is to leave out the join condition. An INNER JOIN with no ON, or a comma join with no WHERE, produces a Cartesian product. Almost all of that output pairs rows that are not related.

Sources1

2.Set operators: stacking and comparing result sets

Joins add columns side by side. Set operators stack rows from separate queries. During discovery they answer questions such as "which keys appear in both sources?" or "which ones are missing from the target?" The plain forms match columns by position, so each query must select the same number of columns, with compatible types and the same meaning. The output column names come from the first query. If you write SELECT * on two tables whose columns are in a different order, you get wrong results without any error. UNION BY NAME and UNION ALL BY NAME match columns by name instead, and fill a column that is missing from one input with NULL.

SELECT postal_code FROM sales_office_postal_example
INTERSECT
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;

+-------------+
| POSTAL_CODE |
|-------------|
| 94061       |
| 98005       |
+-------------+

With MINUS, the order of the queries matters. Swapping them answers the opposite question. Precedence follows ANSI: INTERSECT binds tighter than UNION [ALL] and MINUS, which have equal precedence and are evaluated from left to right. Snowflake recommends adding parentheses whenever you mix operators.

SELECT postal_code FROM sales_office_postal_example
 ? 
SELECT postal_code FROM customer_postal_example
ORDER BY postal_code;

+-------------+
| POSTAL_CODE |
|-------------|
| 94070       |
| 98116       |
+-------------+

Sources2

3.ASOF JOIN: the closest match in time

Time-series sources rarely line up exactly. A trade at 09:00:05 has no quote stamped at exactly 09:00:05, so an equality join finds nothing. ASOF JOIN solves this. For each left-hand row it returns one right-hand row: the closest one in time, in the direction set by the comparison operator. The required MATCH_CONDITION names the left table's time column first, and its parentheses are mandatory. The supported types are DATE, TIME and the timestamp types, as well as NUMBER (for example UNIX epoch seconds). An optional ON or USING clause, which allows only equality conditions joined with AND, partitions the match. Use it so that a stock is matched only with quotes for the same stock.

SELECT *
  FROM left_table l ASOF JOIN right_table r
    MATCH_CONDITION(l.c3>=r.c3)
    ON(l.c1=r.c1 and l.c2=r.c2)
  ORDER BY l.c1, l.c2;

+----+----+----------+------+------+------+----------+------+
| C1 | C2 | C3       |   C4 | C1   | C2   | C3       |   C4 |
|----+----+----------+------+------+------+----------+------|
| A  |  1 | 09:15:00 | 3.21 | A    |  1   | 09:14:00 | 3.19 |
| A  |  2 | 09:16:00 | 3.22 | NULL | NULL | NULL     | NULL |
| B  |  1 | 09:17:00 | 3.23 | B    |  1   | 09:16:00 | 3.04 |
| B  |  2 | 09:18:00 | 4.23 | NULL | NULL | NULL     | NULL |
+----+----+----------+------+------+------+----------+------+

Remove the ON clause and only time decides the match, so A-rows can pair with B-rows. Changing >= to <= reverses the direction and finds the next quote instead of the previous one. If two right-table rows tie on the timestamp, Snowflake still returns only one of them, and which one can differ between runs. A few further rules: you can use several ASOF joins in one query, but each needs its own MATCH_CONDITION. ASOF does not work with LATERAL table functions or LATERAL inline views. You can add a regular INNER JOIN, for example to look up company names, after the ASOF join.

Sources3

4.Filtering at the right stage: WHERE, HAVING, QUALIFY

After joining and combining, you filter down to what the business goal needs. Snowflake has a different filter for each stage of evaluation. WHERE filters source rows before grouping. HAVING filters the groups that GROUP BY produces, so its predicate can refer only to constants, GROUP BY expressions and aggregates. QUALIFY filters on window-function results, much as HAVING filters on aggregates. A common use is keeping the latest row per key. A query that uses QUALIFY must include at least one window function, in either the SELECT list or the QUALIFY predicate.

Sources45

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.UNION keeps every row from both queries, so it is the right choice for appending two extracts.Why is that wrong?

   Plain UNION means UNION DISTINCT and removes duplicates. Use UNION ALL to keep them.

   Covered in Set operators: stacking and comparing result sets

2. 2.An ASOF JOIN always returns the same row when several right-table rows share the closest timestamp.Why is that wrong?

   ASOF returns exactly one matching row. When rows tie, which one is returned can change from run to run.

   Covered in ASOF JOIN: the closest match in time

3. 3.An ASOF JOIN drops left-table rows that have no earlier (or later) match, like an inner join.Why is that wrong?

   Unmatched left rows are kept, and the right-table columns are filled with NULLs.

   Covered in ASOF JOIN: the closest match in time