How a Null Date Column Silently Dropped Half Our Client's Rows

The Incident: Half Our Client's Rows Vanished After a Routine Deploy

Last Tuesday at 3:17 AM IST, our nightly customer sync pipeline ran green. The dbt job completed, the Snowflake COPY INTO succeeded, and the Slack notification said 'All tests passed.' Then at 9:03 AM, our financial services client in Mumbai noticed their morning dashboard was missing nearly half of their customer records.

The client constraint that set us up was clear from the start. We were migrating them from a legacy CRM to Snowflake on Azure, with a strict SLA of 99.9% row retention. The data source was an on-prem SQL Server with a nullable last_contact_date column. Our pipeline was straightforward: Python extract to Parquet, then COPY INTO Snowflake, followed by dbt transformations. The deploy that broke everything added a new filter in dbt to exclude customers not contacted in over 180 days.

What We Tried and What Actually Failed

Our First Attempt: A Naive Date Filter

The dbt model stg_customers.sql had this line:

WHERE last_contact_date >= DATEADD(day, -180, CURRENT_DATE())

We assumed Snowflake would treat NULL dates as 'not matching' and exclude them. That assumption was wrong in the worst way.

The result was immediate and silent. 48% of rows vanished from the final dim_customers table. No errors in dbt logs. No failed tests. The pipeline reported success. The client noticed the discrepancy during their morning dashboard review.

Why It Failed: Three-Valued Logic in Snowflake

The root cause was three-valued logic in Snowflake. When last_contact_date is NULL, the expression last_contact_date >= DATEADD(day, -180, CURRENT_DATE()) evaluates to UNKNOWN, not TRUE or FALSE.

Snowflake's WHERE clause keeps only TRUE rows. UNKNOWN rows are dropped silently, just like FALSE rows. NULL is not a date value. It is the absence of information.

As the Analyst Prep Kit explains: 'A comparison can answer yes, no, or unknown. WHERE keeps only the yes rows, so a row whose comparison met a NULL is dropped exactly as if it had answered no.' NULL in SQL: Why = NULL Finds Nothing

There was no warning. No error. Just missing data.

The Working Approach: Explicit NULL Handling

The Fix We Applied

We rewrote the filter in stg_customers.sql:

WHERE last_contact_date >= DATEADD(day, -180, CURRENT_DATE())
   OR last_contact_date IS NULL

Then we added a dbt test to catch future regressions:

tests:
  - dbt_utils.expression_is_true:
      expression: "last_contact_date IS NOT NULL OR last_contact_date IS NULL"

Finally, we created a monitoring query in Snowflake to alert on row count drops:

SELECT COUNT(*) AS total_rows,
      COUNT(last_contact_date) AS non_null_dates
FROM {{ ref('stg_customers') }}
WHERE _etl_date = CURRENT_DATE()

Real Commands and File Paths

Here is what we actually touched:

• Pipeline repo: /opt/pipelines/customer_sync/
• dbt project: /opt/pipelines/customer_sync/dbt_project.yml
• Staging model: /opt/pipelines/customer_sync/models/staging/stg_customers.sql
• Snowflake table: PROD.CUSTOMERS.DIM_CUSTOMERS
• Deployment command: cd /opt/pipelines/customer_sync && dbt run --models stg_customers

We deployed the fix at 10:42 AM IST. By 11:15 AM, the dashboard showed all 48% of the missing rows restored.

Pitfalls We Would Warn an Intern About

1. Never Trust a Green Pipeline Status

A successful dbt run does not mean correct data. Always validate row counts before and after transformations. Set up row count anomaly alerts, not just job success alerts.

As Rishabh Saxena learned the hard way: 'A successful pipeline run does not equal correct data. Monitoring job status is not the same as monitoring data quality.' LinkedIn Post

2. NULL Is Not a Value, It Is Absence

WHERE col >= '2024-01-01' drops NULL rows silently. WHERE col <> '2024-01-01' also drops NULL rows. Always ask: should NULL rows be included or excluded?

The explainanalyze guide puts it bluntly: 'The only way to test for NULL is with IS NULL or IS NOT NULL. WHERE col = NULL always returns zero rows, because col = NULL evaluates to NULL, which is not TRUE, so the row is filtered out.' NULL in SQL: Three-Valued Logic

3. Date Columns Are Especially Dangerous

Date comparisons with NULL produce UNKNOWN, not TRUE or FALSE. Timezone conversions can introduce unexpected NULLs. Always check for NULLs in date columns before filtering.

4. Test for the Negative Case

Write tests that verify NULL rows are handled as intended. Use IS NULL and IS NOT NULL explicitly in WHERE clauses. Log rejected or NULL records to a quarantine table for inspection.

What We Would Do Differently Next Time

Schema Validation at Ingestion

We would add explicit schema validation in the Python extract layer. Reject or quarantine records with unexpected NULLs in critical columns. Use Great Expectations or dbt source freshness tests.

Defensive SQL Patterns

We would adopt a team rule: every WHERE clause with a date comparison must include explicit NULL handling. We created a dbt macro for safe date filtering:

{% macro safe_date_filter(column, days_ago) %}
  ({{ column }} >= DATEADD(day, -{{ days_ago }}, CURRENT_DATE()) OR {{ column }} IS NULL)
{% endmacro %}

Monitoring and Alerting

We would implement row count delta alerts in Snowflake using QUERY_HISTORY. Set up a daily data quality report sent to the client. Never trust a green checkmark again.

The Datameer documentation warned about exactly this class of problem: 'A workbook with a simple filter that works with dates fails or drops records with ComputationException: Filter: (AFTER ... failed with NullPointerException: First argument of function AFTER is null!' Datameer Support

Their recommended fix: 'Check if the filtered date column has empty values and replace them with a chosen date.' We chose to preserve the NULLs instead, which was the right call for our business logic.

The biggest lesson from this incident is simple. NULL is not a value. It is the absence of information. If you do not handle it explicitly, SQL will make decisions on your behalf. Silent bugs are the hardest ones to catch.

We now run a pre-deploy checklist that includes: verify NULL handling in every WHERE clause, validate row counts against the previous day, and confirm that the monitoring query returns expected non-null date counts. It takes five minutes. It saves days of debugging.

The client's dashboard has been stable since the fix. We have not had another incident.

But we check the row counts every morning anyway.