Joins, Aggregation, NULLs, Duplicates and Sampling in Snowflake SQL

1.Joins: which rows survive, and where NULLs come from

Data for a model is often split across tables, and the join type you pick decides which rows reach the training set. In Snowflake SQL, a plain JOIN is an INNER JOIN. Each left row is paired with every right row that matches the ON condition, and rows without a match disappear. Outer joins keep the unmatched rows and fill the missing side with NULL. This is a common source of the missing values you will clean later.

For most joins the ON clause is optional, which makes it easy to get wrong. Without it, the result is a Cartesian product. The documentation warns that this uses a lot of resources and is often a user error. USING(key_column) is a shorthand for an equality join on columns that share a name. With SELECT *, the shared key appears only once in the output.

A comma between tables in the FROM clause is another way to write an inner join, with the join condition in the WHERE clause. The aggregation example in the next section uses this form. A comma without a WHERE clause behaves like CROSS JOIN, so it is a Cartesian product too.

In Snowpark Python you join DataFrames by calling the join method. Pass a column expression, using each DataFrame's col method to say which side a column comes from. When both DataFrames share the join column, you can pass its name in a list instead.

df_lhs.join(df_rhs, df_lhs.col("key") == df_rhs.col("key")).select(df_lhs["key"].as_("key"), "value1", "value2").show()

The sources show the Snowpark join with a column-equality condition and the shorthand df_lhs.join(df_rhs, ["key"]). They do not show how to choose outer join types from Snowpark. The outer-join semantics above are given in SQL, which you can also run from Snowpark through session.sql().

Sources12

2.Aggregating with GROUP BY

Aggregation turns many rows into one row per entity, such as one row per product or per customer. GROUP BY puts rows with the same group-by values together and computes aggregate functions for each group. A group-by item can be a column name, a position in the SELECT list, or a general expression. The example below does a join and an aggregation in one statement: it joins sales to products and sums profit per product.

SELECT p.product_ID, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit
  FROM products AS p, sales AS s
  WHERE s.product_ID = p.product_ID
  GROUP BY p.product_ID;

GROUP BY ALL groups by every SELECT item that does not use an aggregate function, so you don't have to repeat those columns. GROUP BY ROLLUP adds subtotal rows for hierarchical data, GROUP BY CUBE adds subtotals for every combination of dimensions, and GROUPING SETS computes several GROUP BY clauses in one statement.

Watch for names that clash. If a SELECT alias has the same name as a real column and you GROUP BY that name, Snowflake groups by the column, not the alias. In the documented example, grouping by state when state is also an alias for ANY_VALUE(employment_state) groups the salaries by the table's state column (California, Oregon), not by employment status.

On the Python side, the Snowpark pandas API (pandas on Snowflake) aggregates with the familiar pandas groupby. The documented example computes the average daily snowfall per location after loading a table with pd.read_snowflake:

df.groupby("LOCATION").mean()["SNOWFALL"]

Sources34

3.Filling NULLs with COALESCE and removing duplicates with DISTINCT

NULLs reach your data from the source system, from outer joins, and from TRY_CAST on values it could not convert. In SQL, COALESCE replaces them: it returns the first non-NULL argument, or NULL if every argument is NULL. COALESCE(discount, 0) gives a default, and COALESCE(col_a, col_b, col_c) takes the first column that has a value. NVL(expr1, expr2) is the two-argument form: it returns expr2 when expr1 is NULL.

COALESCE has one sharp edge. Snowflake implicitly converts the arguments to a compatible type, and that conversion can fail. COALESCE('17', 1) converts '17' to the number 17, but COALESCE('foo', 1) raises an error. Snowflake recommends passing arguments of the same type, or converting them explicitly, for example with the TRY_CAST you met earlier.

In Python, the Snowpark pandas API drops rows that contain NULLs with df.dropna(). The sources for this lesson do not document the Snowpark Python DataFrame methods for filling NULLs or for removing duplicates, so check the Snowpark API reference for those.

Duplicates are handled in the SELECT list. SELECT ALL, the default, keeps every row of the result. SELECT DISTINCT removes duplicates, so two rows that are identical across all selected columns become one. Because DISTINCT compares only the columns you select, drop irrelevant columns such as load timestamps before deduplicating. Otherwise rows that differ only in those columns will survive as separate rows.

DISTINCT cannot tell you which duplicate survives. When you need the entire row for each key, use QUALIFY with ROW_NUMBER: QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1 keeps the latest row per customer. The documentation calls this version explicit about which duplicate to keep.

To measure data quality, Snowflake provides system data metric functions. NULL_COUNT returns the total number of NULL values in a column, and DUPLICATE_COUNT returns the count of column values that have duplicates, including NULL values.

Sources564789

4.Sampling with SAMPLE / TABLESAMPLE

When a table is too big to explore or to prototype on, take a sample of it. SAMPLE and TABLESAMPLE mean the same thing in Snowflake. You can sample a fraction of the table, where each row or block is included with a given probability, or a fixed number of rows.

SELECT * FROM testtable SAMPLE  ?  (3) SEED (82);

Where you put SAMPLE matters in a join. A SAMPLE clause applies only to the table right before it. table1 JOIN table2 SAMPLE (50) joins all of table1 to half of table2. To sample the join result, run the join as a subquery and put SAMPLE after it. That works only with row-based (Bernoulli) sampling and no seed, and it does not save any join work. To make the join itself smaller, sample each table before joining it.

On speed: SYSTEM is often faster than BERNOULLI, and sampling without a seed is often faster than sampling with one. Fixed-size sampling can be slower than the equivalent fraction, because it prevents some query optimization. These sources do not cover a Snowpark Python sampling method, but you can run any of these SAMPLE queries through session.sql().

Sources10

Exam traps

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

1. 1.If a SELECT alias and a table column share a name, GROUP BY uses the alias you just defined.Why is that wrong?

   Snowflake resolves the name to the table column first, so the query can group by a completely different field from the one it displays.

   Covered in Aggregating with GROUP BY

2. 2.Putting SAMPLE after a join, or sampling a join result in a subquery, cuts the cost of the join.Why is that wrong?

   A SAMPLE clause applies to one table only, and sampling a join result happens after the full join has run. To make the join smaller, sample the input tables.

   Covered in Sampling with SAMPLE / TABLESAMPLE

3. 3.Leaving out the ON clause just returns the matching rows, like a natural join.Why is that wrong?

   Except for NATURAL JOIN, which infers its join columns, a join with no ON clause is a Cartesian product: every left row paired with every right row.

   Covered in Joins: which rows survive, and where NULLs come from