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>' )
Copy

Arguments¶

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.

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 employeeTable table that had a blank value in the name column.

SELECT *
  FROM TABLE(SYSTEM$DATA_METRIC_SCAN(
    REF_ENTITY_NAME  => 'governance.sch.employeesTable',
    METRIC_NAME  => 'snowflake.core.blank_count',
    ARGUMENT_NAME => 'name',
  ));
Copy
Language: English