Why Do COUNT(*) and COUNT(column) Return Different Results in Snowflake?
Clarify row counts, missing values and completeness percentages with a small Snowflake query.
The doubt: did COUNT lose some rows?
COUNT(*) counts rows, while COUNT(column) counts rows where the chosen expression is not NULL. Different results can be expected rather than evidence of lost data. Name each measure according to its meaning instead of labelling both as total records.
For a completeness report, the difference is useful. If an input has three records and two known scores, the report should communicate three received records and two populated scores. Calling the second number received records hides the missing value.
A small Snowflake exercise
The following uses invented scores and inline rows. It was reviewed against the Snowflake COUNT reference; no live Snowflake account was used, so cloud execution is not claimed.
WITH scores AS (
SELECT column1::NUMBER AS score
FROM VALUES (10),(NULL),(30)
)
SELECT COUNT(*) AS received_rows,
COUNT(score) AS populated_scores,
COUNT(*) - COUNT(score) AS missing_scores
FROM scores;Expected values are received_rows 3, populated_scores 2 and missing_scores 1. A permitted Snowflake session and appropriate compute may be needed to try it. Do not run teaching examples against sensitive production data.
Is a zero score counted?
Yes: zero is a value, not NULL. Replacing the missing score with zero changes populated_scores to 3 and missing_scores to 0, but it also changes the meaning of the data. If the score is unknown, inserting zero can falsely describe a measured outcome. Decide the replacement rule with the data owner.
Does an empty string mean missing?
Not automatically. For a text field, an empty string and NULL are distinct cases unless your ingestion or transformation rules convert them. A useful quality check may count blank, whitespace-only and NULL values separately. Do not assume that a numeric completeness example covers every text-field rule.
How do I calculate a completeness percentage?
Define the denominator as eligible received records, then divide populated values by that denominator using an appropriate decimal calculation. Here the rate is 2/3, approximately 66.67%. Decide how an empty input is represented; division by zero should not be silently turned into a claim of complete data.
Practice and expected result
Add a fourth score of 0. Predict received_rows 4, populated_scores 3 and missing_scores 1. Completeness becomes 75%. Then imagine a rejected record never entered the table: these counts cannot reveal it by themselves. Reconcile file receipt and rejection logs as well as table counts. This distinguishes completeness within the table from completeness of the entire ingestion process.
Snowflake course.
Official Snowflake COUNT reference.
Prepared by AI Leaf Global Learn for classroom learners in Mudichur/Tambaram, Chennai, and online learners across India. Confirm current course and batch details. Area tags describe audiences, not additional offices.