Categories:: System functions, Table functions

SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS¶

Returns the expectations for associations between data metric functions (DMFs) and a table, including whether an expectation is currently violated.

Syntax¶

SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS(
  REF_ENTITY_NAME  => '<object>'
  [ , SKIP_SUSPENDED_DMF => { TRUE | FALSE } ]
  [ , WITHIN_GROUP_VALUES => '<json_object>' ]
)

Arguments¶

REF_ENTITY_NAME => 'object'

Name of the table or view that has at least one DMF with one or more expectations. Must be fully qualified.

SKIP_SUSPENDED_DMF => { TRUE | FALSE }

If set to TRUE, the function doesn’t return expectations that are defined for associations between the object and suspended DMFs. A suspended DMF doesn’t run on the object’s specified schedule.

Default: TRUE

WITHIN_GROUP_VALUES => 'json_object'

For an association created with a WITHIN GROUP clause, a JSON object that maps grouping column names to the specific group values to evaluate. For example: '{"REGION": "US", "PRODUCT_CATEGORY": "Electronics"}'. If you omit this argument, expectations are evaluated across all groups.

Only string and numeric values are supported in the JSON object.

For more information, see Apply data quality checks by group.

Returns¶

Returns a table with the following columns:

Column	Data type	Description
`metric_database`	VARCHAR	Name of the database that contains the DMF.
`metric_schema`	VARCHAR	Name of the schema that contains the DMF.
`metric_name`	VARCHAR	Name of the DMF.
`expectation_name`	VARCHAR	Name that the user assigned the expectation when adding it to the association between the DMF and the table.
`expectation_id`	NUMBER	System-generated identifier.
`expectation_expression`	VARCHAR	Boolean expression of the expectation. See Defining what meets the expectation.
`arguments`	ARRAY	Columns with which the DMF is associated.
`value`	VARIANT	The result of the DMF evaluation.
`expectation_violated`	BOOLEAN	If TRUE, the expectation was violated. An expectation is violated when the `expectation_expression` evaluates to FALSE.
`group_by_values`	VARIANT	For an expectation defined on an association created with a WITHIN GROUP clause, a JSON object that identifies the group this row corresponds to. Each key is a grouping column name and each value is that column’s value for the group, for example `{"REGION": "US", "COUNTRY": "CA"}`. NULL for non-grouped associations. For more information, see Apply data quality checks by group.

Access control privileges¶

A role used to execute this operation must have the following privileges at a minimum:

Privilege	Object	Notes
SELECT	Table or view
USAGE	Data metric function (DMF)

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Example¶

Return the expectations for the associations between DMFs and table t1. The DMFs are executed to determine if the expectations are currently violated.

SELECT *
  FROM TABLE(SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS(
      REF_ENTITY_NAME => 'my_db.sch.t1'));