Categories:

System functions, Table functions

SYSTEM$DATA_METRIC_SCAN¶

Returns the rows identified by a data quality metric as containing data that failed a data quality check. For example, if you use the NULL_COUNT data metric function as an argument, the function returns the rows in the table that contain a NULL value in a specific column.

Syntax¶

SYSTEM$DATA_METRIC_SCAN(
  REF_ENTITY_NAME  => '<object>'
  , METRIC_NAME  => '<data_metric_function>'
  , ARGUMENT_NAME => '<column>'
   [ , AT_TIMESTAMP => '<timestamp>' ] )
Copy

Arguments¶

Required:

REF_ENTITY_NAME => 'object'

Name of the table or view on which the specified data metric function will run. The function returns rows from this object.

METRIC_NAME => 'data_metric_function'

Name of the system data metric that you want to run to evaluate the specified table or view. Only the following system functions are supported:

  • SNOWFLAKE.CORE.NULL_COUNT

  • SNOWFLAKE.CORE.NULL_PERCENT

  • SNOWFLAKE.CORE.BLANK_COUNT

  • SNOWFLAKE.CORE.BLANK_PERCENT

  • SNOWFLAKE.CORE.DUPLICATE_COUNT

ARGUMENT_NAME => 'column'

Name of the column in the specified table or view that is being passed as an argument to the specified data metric function.

Optional:

AT_TIMESTAMP => 'timestamp'

Timestamp that is being passed as an argument to check the results of a DMF evaluation on the table or view in the past.

Returns¶

Rows from the specified table or view.

Access control privileges¶

Executing this function requires the following privileges:

  • SELECT on the specified table.

  • USAGE on the specified data metric function.

Usage notes¶

  • This function does not support user-defined metrics.

  • If the specified table is protected by a policy, such as a masking policy or row access policy, the function might return unexpected or incomplete data because results depend on the user’s role when executing the function.

Examples¶

Given that the SNOWFLAKE.CORE.NULL_COUNT system metric returns the total number of NULL values in a particular column, the following returns the rows of the employeesTable table that have NULL values in the SSN column.

SELECT *
  FROM TABLE(SYSTEM$DATA_METRIC_SCAN(
    REF_ENTITY_NAME  => 'governance.sch.employeesTable',
    METRIC_NAME  => 'snowflake.core.null_count',
    ARGUMENT_NAME => 'SSN'
  ));
Copy

Given that the SNOWFLAKE.CORE.BLANK_COUNT system metric returns the count of how many values in a column are blank, the following returns the rows of the employeesTable table that had a blank value in the name column in the version of the table that existed at a specific time in the past.

SELECT *
  FROM TABLE(SYSTEM$DATA_METRIC_SCAN(
    REF_ENTITY_NAME  => 'governance.sch.employeesTable',
    METRIC_NAME  => 'snowflake.core.blank_count',
    ARGUMENT_NAME => 'name',
    AT_TIMESTAMP => '2024-08-28 02:00:00 -0700'
  ));
Copy
Language: English