- Categories:
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>' )
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'
));
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',
));