Categories:: Data metric functions

NAN_COUNT (system data metric function)¶

Returns the count of non-NULL column values that are NaN (not a number).

This topic provides the syntax for calling the function directly. To learn how to associate the function with a table or view so it runs at regular intervals, see Associate a DMF.

Syntax¶

SNOWFLAKE.CORE.NAN_COUNT(<query>)

Arguments¶

query: Specifies a SQL query that projects a single column.

Allowed data types¶

The column projected by the query must have one of the following data types:

FLOAT
VARCHAR

Returns¶

The function returns a NUMBER value. NULL values are not counted.

Usage notes¶

For a FLOAT column, the function counts the IEEE 754 NaN value. Infinity and negative infinity are not counted.
For a VARCHAR column, the function counts the text NaN. The match is case-insensitive and ignores leading and trailing ASCII whitespace.

Example¶

Measure the number of NaN values in the reading column:

SELECT SNOWFLAKE.CORE.NAN_COUNT(
  SELECT
    reading
  FROM iot.tables.sensor_readings
);