Snowflake count_if returns 0 if no records satisfy the condition, while the documentation states otherwise

by 
# Postmortem: Snowflake `COUNT_IF` Behavior vs. Documentation Discrepancy

## Summary
- Snowflake's `COUNT_IF` function returns `0` when no records meet the condition.
- Official documentation claims it should return `NULL` in such cases.
- Discrepancy confirmed via live testing of `COUNT_IF(1 != 1)` returning `0`.

## Root Cause
- Implementation aligns with SQL's `COUNT` aggregation behavior (always returns `0` for empty sets).
- Documentation incorrectly describes edge-case logic for `COUNT_IF`.
- Internal validation processes failed to catch documentation inaccuracy.

## Why This Happens in Real Systems
- Engine optimization prioritizes consistent `COUNT` semantics for easier SQL migrations.
- Documentation/implementation misalignment often occurs due to:
  - Overlooked edge cases in technical specs.
  - Copy-paste errors from similar functions (e.g., `SUM(NULL)` behavior).
  - Lack of automated tests validating documentation examples.
- Vendor lock-in pressure to match ANSI SQL standards for common aggregations.

## Real-World Impact
- Unexpected `0` instead of `NULL` causes:
  - Incorrect downstream calculations (e.g., `SUM / COUNT_IF` yields division by zero).
  - Misleading analytics when distinguishing between "no data" and "zero matches".
  - Silent failures in data pipelines expecting `NULL`-handling logic.
- Breaks compatibility promises when migrating from databases that return `NULL`.

## Example or Code
```sql
-- Expected per documentation: (not-null, null, null)
-- Actual Snowflake behavior: (1, 0, 0)
SELECT
  COUNT_IF(1 = 1),   -- Returns 1 ✅
  COUNT_IF(1 != 1),  -- Returns 0 ❌ (doc says NULL)
  COUNT_IF(NULL);    -- Returns 0 ❌ (doc says NULL)

How Senior Engineers Fix It

  • Explicit NULL coercion: 
    SELECT NULLIF(COUNT_IF(condition), 0)
  • Standardize with SUM(CASE) for predictable NULL handling: 
    SELECT SUM(CASE WHEN condition THEN 1 END)
  • Mitigation rollout steps:
    1. Audit existing COUNT_IF usage via query history/logs.
    2. Update documentation references and team knowledge bases.
    3. Implement data validation checks for critical aggregates.
  • Escalate documentation fixes via Snowflake support tickets.

Why Juniors Miss It

  • Assuming documentation is always authoritative without edge-case testing.
  • Overlooking aggregation nuances:
    • COUNT(*) vs. COUNT(expr) vs. COUNT_IF behavioral differences.
  • SQL abstraction layers (ORMs/dashboards) obfuscating actual function output.
  • Lack of defensive checks for NULL/zero equivalence in SQL.