Counting Non‑Null Columns Per Row the Smart Way in Snowflake

In complex datasets, columns often contain missing or incomplete values. Identifying how many fields within each record actually contain data is a common need in data quality analysis. In Snowflake, this can be done elegantly in one line without manually naming each column.

In this article, we’ll explore a real-world example, compare classic versus modern approaches, and show how Snowflake’s array functions make your SQL cleaner, faster, and far more scalable.

Real-World Scenario: Measuring Data Completeness in Customer Records

Picture a company building its customer data warehouse in Snowflake - aggregating information from multiple lead sources, CRM systems, and marketing platforms. The customer_master table now includes dozens of attributes such as name, email, phone, address, loyalty_id, country, and more.

As expected, not all systems provide consistent or complete data. Some records arrive without phone numbers, others lack addresses or emails - and some leads are only partially enriched during ingestion. Before analytics teams roll out customer segmentation, marketing teams initiate campaigns or ML processing, Data engineers and BI developers need a way to measure how complete each record really is - fast, scalable, dynamic, and with as little room for error as possible.

Sample Data

Here’s a sample dataset with 10 columns and 15 rows representing customer information:

customer_master table

Now, let’s count how many columns actually contain non‑NULL values in each row. This will help us measure the completeness of each record.

The Traditional Way

This method works - but it’s neither elegant nor scalable for dynamic schemas or modern analytics environments.

SELECT customer_id,
       (CASE WHEN first_name IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN last_name IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN city IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN country IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN zip IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN loyalty_id IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN notes IS NOT NULL THEN 1 ELSE 0 END) AS number_of_columns_with_value
FROM customer_master;

Tedious and Not Scalable:

• Manual updates: Each new column requires updating this query.
• Hard to read: Dozens of CASE statements make code unwieldy.
• Error-prone: Easy to miss a column or introduce typos.
• Performance impact: The Snowflake optimizer handles it efficiently but running CASE per column increases maintenance overhead for wide tables.

The Modern Snowflake Way

Snowflake’s arrays allow you to group multiple columns into a single collection - perfect for row-level operations like counting fields, transforming structures, or creating semi-structured outputs.

An array in Snowflake is an ordered list of elements (numbers, strings, or mixed types). You can manipulate them like lists: get their size, access elements, and even nest arrays inside JSON-like structures.

Example: Employee with Array of Previous Workplaces

employees table

Let’s introduce two essential Snowflake array functions, both functions take a list of elements separated by commas and return them as a SQL array.

• ARRAY_CONSTRUCT() builds an array including all elements, even if some are NULL.
• ARRAY_CONSTRUCT_COMPACT() builds an array but omits NULL values, making it ideal for counting non-null fields or building dense lists.

SELECT 
  ARRAY_CONSTRUCT('Google', NULL, 'Snowflake')         AS full_array,
  ARRAY_CONSTRUCT_COMPACT('Google', NULL, 'Snowflake') AS compact_array;

Implementing the Solution -

With the ARRAY_CONSTRUCT_COMPACT()function, the same logic becomes a single, compact line:

SELECT *
  , ARRAY_SIZE(ARRAY_CONSTRUCT_COMPACT(*)) AS number_of_columns_with_a_value
FROM customer_master;

• ARRAY_CONSTRUCT_COMPACT(*) turns an entire row into an array, skipping nulls.
• ARRAY_SIZE(...) instantly returns the count of values that are not NULL.

Why It’s Better?

• Dynamic: Automatically adapts to schema changes - no column list needed.
• Readable: One line instead of dozens of repetitive CASE statements.
• Scalable: Works efficiently even on very wide, multi-column tables.
• Performance: Snowflake’s internal array processing is highly optimized, often outperforming manual CASE-based logic, especially on large datasets.

Sometimes, you don’t need to count all columns - just a specific set, like contact‑related or demographic fields.

You can handle this in two ways:

Manually list specific columns:

SELECT *
  , ARRAY_SIZE(ARRAY_CONSTRUCT_COMPACT(first_name, last_name, email, phone)) AS filled_contact_fields
FROM customer_master;

Use the EXCLUDE syntax to automatically include all columns except a few:

SELECT *
  , ARRAY_SIZE(ARRAY_CONSTRUCT_COMPACT(SELECT * EXCLUDE (customer_id, notes))) AS number_of_filled_columns
FROM customer_master;

The EXCLUDE clause is particularly helpful when your table has many columns, and you want to maintain flexibility without rewriting your query each time the schema changes.

Conclusion

What used to require a full page of conditional SQL now fits comfortably in two lines. This is one of those simple but game-changing tricks that perfectly captures the elegance of Snowflake SQL.

Common Scenarios for Counting Non‑NULL Columns

• Choosing the Most Complete Record for a Key When merging multiple data sources (CRM, ERP, lead platform), pick the row with the highest number of non‑NULL values per ID. Example: Deduplicate leads and retain the record that holds the richest set of attributes.
• ETL Validation After Data Loads Compare pre‑ and post‑load completeness metrics to ensure no field loss occurred during ETL or ingestion. Example: Detect schema mapping issues when loading data from JSON or Parquet files.
• Source Data Comparison and Prioritization When integrating multiple feeds of the same dataset (like sales data from POS and eCommerce), compare which source provides more complete information per entity. Example: Retain the “golden record” for Master Data Management (MDM).
• Detecting Schema Drift in Semi‑Structured Data When ingesting dynamic JSON or event data, counting non‑NULL columns reveals missing fields or changes in key names between data versions.
• Assessment Before Feature Engineering (ML) Evaluate whether records have enough usable data points for model input. Example: Exclude rows with fewer than 70% populated columns before training machine learning models.
• Error or Anomaly Reviews Identify “suspiciously sparse” records - often early signs of upstream ingestion, scraping, or API extraction failures.
• Data Enrichment Measurement Track how many attributes were successfully added during enrichment steps. Example: Compare completeness before and after appending demographic or location enrichment data.

CREATE TABLE customer_master (
    customer_id INT,
    first_name VARCHAR,
    last_name VARCHAR,
    email VARCHAR,
    phone VARCHAR,
    city VARCHAR,
    state VARCHAR,
    zip VARCHAR,
    loyalty_id VARCHAR,
    notes VARCHAR
);

INSERT INTO customer_master VALUES
(1, 'John', 'Doe', 'jdoe@email.com', '555-8842', 'New York', 'NY', '10001', 'L123', NULL),
(2, 'Mary', 'Smith', 'marysmith@email.com', NULL, 'Boston', 'MA', '02118', 'L124', 'Referred'),
(3, 'Michael', 'Johnson', 'mjohnson@email.com', '555-3559', 'Chicago', 'IL', '60611', 'L125', 'Active'),
(4, 'David', 'Cohen', 'davidcohen@email.com', '555-7124', NULL, NULL, NULL, 'L126', 'Loyal'),
(5, 'Priya', 'Nair', 'priya.nair@email.com', NULL, 'Dallas', 'TX', NULL, NULL, NULL),
(6, 'James', 'Brown', NULL, '555-7321', 'Houston', 'TX', '77002', 'L127', NULL),
(7, 'Emma', 'Davis', 'emmadavis@email.com', '555-4478', 'Chicago', NULL, NULL, 'L128', NULL),
(8, 'Chris', 'Wilson', NULL, NULL, 'Denver', 'CO', '80205', NULL, NULL),
(9, 'Daniel', 'White', 'danielwhite@email.com', '555-9854', 'Orlando', 'FL', '32801', 'L129', NULL),
(10, 'Laura', 'Miller', NULL, NULL, 'San Diego', 'CA', '92101', 'L130', NULL),
(11, 'Wei', 'Zhang', 'wei.zhang@email.com', '555-8421', 'San Francisco', 'CA', '94102', NULL, 'Imported'),
(12, 'Olivia', 'Thomas', NULL, '555-4217', NULL, 'NY', NULL, 'L131', NULL),
(13, 'Jacob', 'Taylor', 'jacob.taylor@email.com', NULL, 'Austin', 'TX', '78701', 'L132', NULL),
(14, 'Sophia', 'Anderson', 'sophia.anderson@email.com', '555-2154', 'Los Angeles', 'CA', '90001', 'L133', NULL),
(15, 'Henry', 'Moore', NULL, NULL, NULL, NULL, NULL, NULL, NULL);