How Snowflake’s NULL quirks stealthily mess with your counts, joins, and filters (and how to fix it)

Have you ever been surprised by how Snowflake handles NULLs when you thought you understood SQL pretty well? It’s one of those subtle quirks that trips up even experienced engineers and architects, and it can quietly sabotage reports, analytics, or application logic before you even notice. You might assume NULL means the same across databases or that COUNT(column) always behaves predictably – but that’s not quite how Snowflake plays the game.

I remember early on, on a project with Lisa’s team, we struggled for hours trying to explain why our row counts didn’t add up and why comparisons with NULL seemed to defy logic. The culprit? Snowflake’s unique take on NULL handling. It was a lesson in how something as fundamental as NULL can have surprises underneath. If you want fewer headaches and more confidence when working with Snowflake, understanding these differences isn’t optional. It’s essential.

How does Snowflake treat NULLs differently from the SQL you might know? Let’s walk through some of the trickiest behaviors and then unpack a concrete mental model to keep them manageable.

Counting NULLs: Why COUNT(*) and COUNT(column) Tell Different Stories

Almost universally, COUNT(*) counts every row in a result set, while COUNT(column_name) counts only the rows where that column is not NULL. Snowflake follows this convention but with an important nuance when NULLs are explicitly present.

Here’s a quick example illustrating the difference:

-- Demonstrates how COUNT(*) counts all rows, but COUNT(column_name) excludes NULLs
WITH sample_data AS (
 SELECT 1 AS id, 'A' AS val UNION ALL
 SELECT 2, NULL UNION ALL
 SELECT 3, 'B'
)
SELECT
 COUNT(*) AS total_rows,
 COUNT(val) AS count_non_null_vals
FROM sample_data;

The result? total_rows is 3, but count_non_null_vals is 2. That’s expected. But what trips people is assuming COUNT(column) counts rows with NULLs when they don’t. In Snowflake, NULLs are simply not counted in COUNT(column) unless you explicitly check for them.

This behavior impacts reporting and ETL pipelines. If you want to count NULLs explicitly, you’d need to do something like:

SELECT COUNT(*) - COUNT(val) AS null_count FROM sample_data;

or use a CASE expression to flag NULLs.

Nulls and String Comparisons: Not Just Empty Strings in Disguise

People often assume NULL and empty string (”) are interchangeable or comparable in SQL string operations. In Snowflake, these are completely distinct.

NULL represents missing or unknown data. An empty string is a valid string with zero length. When comparing strings, Snowflake treats NULL as not equal to anything – not even another NULL. So conditions like column = NULL will always evaluate to UNKNOWN, and the row will be excluded from the result set unless you explicitly use IS NULL.

This distinction matters, for example, when cleansing data or joining on string keys. You might think these two rows match:

Row 1: name = NULL
Row 2: name = ''

But Snowflake treats them as different. To handle NULLs safely in string comparisons, you often need to use COALESCE to substitute NULLs with a default value:

SELECT *
FROM users
WHERE COALESCE(name, '') = '';

This catches NULL and empty strings in one go.

How Snowflake’s IS DISTINCT FROM Changes the NULL Equality Game

One of the surprises I found was Snowflake’s IS DISTINCT FROM and IS NOT DISTINCT FROM operators. In standard SQL, NULL comparisons are tricky because NULL = NULL returns UNKNOWN (effectively false in Boolean logic). But Snowflake flips this on its head.

Here’s a snippet that shows the behavior:

-- Shows that NULL IS DISTINCT FROM NULL returns FALSE in Snowflake
SELECT
 NULL IS DISTINCT FROM NULL AS null_distinct,
 NULL = NULL AS null_equals,
 NULL IS NOT DISTINCT FROM NULL AS null_not_distinct;

The output is:

| null_distinct | null_equals | null_not_distinct |

|—————|————-|——————-|

| FALSE | NULL | TRUE |

In plain language: Snowflake treats NULLs as equal when using IS NOT DISTINCT FROM, meaning NULL IS NOT DISTINCT FROM NULL returns TRUE. That’s different from the usual SQL logic where NULL = NULL is undefined.

This is hugely helpful in deduplication processes and when you want to consider rows with NULLs in the same columns as duplicates. It removes some of the guesswork and awkwardness of null-safe equality checks. But it can catch you off guard if you assume standard SQL behavior.

Null Replacement: Using COALESCE and NVL to Your Advantage

Since NULLs can muck up joins, filters, and aggregates, Snowflake offers both COALESCE and NVL functions to replace NULLs with specified values. They’re functionally equivalent but good to know both exist.

Here’s how you can replace NULL scores with zero for calculations or reporting:

-- Demonstrates replacing NULL values with a default using COALESCE and NVL
WITH sample_data AS (
 SELECT 1 AS id, NULL AS score UNION ALL
 SELECT 2, 85 UNION ALL
 SELECT 3, NULL
)
SELECT
 id,
 COALESCE(score, 0) AS score_coalesce,
 NVL(score, 0) AS score_nvl
FROM sample_data;

This way, you avoid surprises where arithmetic or aggregates silently drop NULLs or propagate them unexpectedly.

Remember, Snowflake’s NULL propagation means any arithmetic involving a NULL operand results in NULL:

SELECT 1 + NULL; -- returns NULL

Using COALESCE or NVL upfront lets you control that.

What’s the best way to handle NULLs safely and predictably in Snowflake? Here’s the mechanism I rely on.

• **Always know which NULL you mean**
  Distinguish between NULL and empty string explicitly. Use COALESCE(column, '') or NULLIF(column, '') where you want to unify them. Don’t assume they’re equal or interchangeable.
• **Use COUNT(*) for total rows, COUNT(column) for non-NULL counts**
  When counting NULLs, subtract COUNT(column) from COUNT(*) or use a CASE expression to flag NULLs explicitly.
• **Replace NULLs early with COALESCE or NVL when doing arithmetic or comparisons**
  This avoids NULL propagation bugs and ensures predictable results in calculations and joins.
• **Adopt IS DISTINCT FROM and IS NOT DISTINCT FROM for null-safe equality checks**
  These operators treat NULLs as equal, which cleans up logic around deduplication and joins involving nullable columns.
• **Guard Boolean logic involving NULLs with explicit checks**
  Since NULL conditions evaluate to UNKNOWN, use IS NULL, IS NOT NULL, or COALESCE-wrapped expressions in WHERE clauses to avoid accidentally filtering out rows.
• **Define NOT NULL constraints where possible**
  If a column logically should not contain NULLs, enforce it at the schema level. Snowflake will error on NULL inserts to NOT NULL columns, avoiding silent data quality issues.

What Goes Wrong When You Don’t Understand Snowflake’s NULL Behavior

You’ll end up with inaccurate counts, failed joins, or worse, corrupted reports that pass unnoticed. Here are the common pitfalls:

• Counting NULLs unintentionally leads to underreporting or skewed metrics.
• Joins fail or exclude rows because NULLs do not match anything, including other NULLs.
• String comparisons mistakenly treat NULL and empty string as equal, causing logic errors.
• Boolean filters exclude rows with NULL conditions unless checked explicitly.
• Arithmetic expressions yield NULL results silently, causing downstream failures or confusion.

That project with Lisa’s team? Once we introduced COALESCE and started using IS NOT DISTINCT FROM, things settled. We could count, join, and compare with confidence. It took some patience, but understanding the quirks helped us build more reliable pipelines and dashboards.

“The beginning of knowledge is the discovery of something we do not understand.” – Frank Herbert

NULLs aren’t just placeholders for missing data. They’re active participants in your queries, behaving in ways you need to respect and control. Snowflake’s NULL handling might feel quirky, but it also gives you powerful ways to write clearer, more robust SQL once you know the rules.

So next time NULLs trip you up, remember: they’re not just “nothing” – they’re a distinct state with their own logic. Handle them explicitly, and your Snowflake work will run smoother than you’d expect. 🌨️💡🛠️

References

• Related reading: https://brontowise.com/category/distributed-computing/snowflake/