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 } ] )

Copy

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

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.

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)

The USAGE privilege on the parent database and schema are required to perform operations on any object in a 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'));

Copy