Categories:: LOCAL schema , Table functions

DATA_QUALITY_MONITORING_EXPECTATION_STATUS¶

For a specified object, returns a row for every time a data metric function (DMF) with an expectation was run. You can obtain the status of the expectation in each row.

See also:: DATA_QUALITY_MONITORING_EXPECTATION_STATUS view (LOCAL view)

Syntax¶

DATA_QUALITY_MONITORING_EXPECTATION_STATUS(
  REF_ENTITY_NAME => '<string>' ,
  REF_ENTITY_DOMAIN => '<string>'
  )

Copy

Arguments¶

REF_ENTITY_NAME => 'string'

The name of the table object on which the data metric function with an expectation is set. The name must be fully qualified.

The entire object name must be enclosed in single quotes.
If the object name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/characters. The double quotes must be enclosed within the single quotes, such as '"table_name"'.

REF_ENTITY_DOMAIN => 'string'

The object type on which the data metric function with an expectation is set.

If the object is a kind of table, use 'TABLE' as the argument value.

If the object is a view or materialized view, use 'VIEW' as the argument value.

For a list of supported object types on which a data metric function can be set, see Supported table kinds.

Output¶

The function returns rows with the following columns:

Column name	Data type	Description
`scheduled_time`	TIMESTAMP_LTZ	The time the DMF is scheduled to run based on the schedule that you set for the table or view.
`change_commit_time`	TIMESTAMP_LTZ	The time the DMF trigger operation occurred, or `None` if the DMF is not scheduled to run by a trigger operation. For information about the trigger operation, see Schedule the DMF to run.
`measurement_time`	TIMESTAMP_LTZ	The time at which the metric was evaluated.
`table_id`	NUMBER	Internal/system-generated identifier of the table that is associated with the DMF.
`table_name`	VARCHAR	Name of the table that is associated with the DMF.
`table_schema`	VARCHAR	Name of the schema name that contains the table that is associated with the DMF.
`table_database`	VARCHAR	Name of the database that contains the table that is associated with the DMF.
`metric_id`	NUMBER	Internal/system-generated identifier of the DMF.
`metric_name`	VARCHAR	Name of the DMF.
`metric_schema`	VARCHAR	Name of the schema that contains the DMF.
`metric_database`	VARCHAR	Name of the database that contains the DMF.
`metric_return_type`	VARCHAR	Return type of the DMF.
`arguments_ids`	ARRAY	Array of the identifiers of the DMF arguments. Array elements are in the same order as the arguments.
`arguments_types`	ARRAY	Array of the domain/type of each argument. Array elements are in the same order as the arguments. Currently only supports COLUMN type arguments.
`arguments_names`	ARRAY	Array of the names of the DMF arguments. For column arguments, each element is the name of a column. Array elements are in the same order as the arguments.
`reference_id`	VARCHAR	The ID to uniquely identify the metric entity reference, known as the association ID.
`value`	VARIANT	The result of the DMF evaluation.
`expectation_name`	VARCHAR	Name that was given to the expectation when it was added to the association between the DMF and the object.
`expectation_id`	VARCHAR	System-generated identifier.
`expectation_expression`	VARCHAR	Boolean expression of the expectation. See Defining what meets the expectation.
`expectation_violated`	BOOLEAN	If TRUE, the expectation was violated. An expectation is violated when the `expectation_expression` evaluates to FALSE. A NULL value indicates the evaluation of the expectation failed.

Access control requirements¶

To access this function, the role in use must have the SNOWFLAKE.DATA_QUALITY_MONITORING_LOOKUP application role, at a minimum. For other application role options, see Managing access to the DMF results. Use the GRANT APPLICATION ROLE command to grant the application role to a role.

To view results, the role in use must also have the following privileges:

The SELECT or OWNERSHIP privileges on the object (table or view) to which the data metric function is assigned.
The USAGE or OWNERSHIP privileges on the data metric function.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

Usage notes¶

Errors occur if the specified object name does not exist or if the query operator is not authorized to view any data metric function on the object. Unsupported object types specified in the REF_ENTITY_DOMAIN argument, such as 'STREAM', also return errors.

Examples¶

Return a row for each data metric function with an expectation that is assigned to the table named my_table:

SELECT *
  FROM TABLE(SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_EXPECTATION_STATUS(
    REF_ENTITY_NAME => 'my_db.sch1.my_table',
    REF_ENTITY_DOMAIN => 'TABLE'));

Copy