CREATE DATA METRIC FUNCTION¶

Syntax¶

CREATE [ OR REPLACE ] DATA METRIC FUNCTION [ IF NOT EXISTS ] <name>
  ( <arg_name> TABLE( <c1> <data_type> [ , ... ] ) )
  RETURNS NUMBER [ [ NOT ] NULL ]
  [ LANGUAGE SQL ]
  [ COMMENT = '<string_literal>' ]
  AS
  '<expression>'

Required parameters¶

name

Identifier for the DMF; must be unique for your schema.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

( arg_name TABLE( c1 data_type [ , ... ] ) )

The signature for the DMF, which is used as input for the expression.

You must specify:

• Only one argument name for the table, arg_name.

• At least one column name and its data type, c1 data_type.

  You can optionally specify additional columns and their data types. The columns must be in the same table and cannot reference a different table.

RETURNS NUMBER

The data type of the output of the function.

The data type can only be NUMBER.

AS expression

SQL expression that determines the output of the function. The expression must be deterministic and return a scalar value. The expression can reference other table objects, such as by using a WITH clause or a WHERE clause.

The delimiters around the expression can be either single quotes or a pair of dollar signs. Using $$ as the delimiter makes it easier to write expressions that contain single quotes.

If the delimiter for the expression is the single quote character, then any single quotes within expression (for example, string literals) must be escaped by single quotes.

The expression does not support the following:

• Using the JOIN construct.

• Using nondeterministic functions (for example, CURRENT_TIME).

• Referencing an object that depends on a UDF or UDTF.

• Returning a nonscalar output.

Optional parameters¶

LANGUAGE SQL

Specifies the language used to write the expression.

SQL is the only supported language.

COMMENT = 'string_literal'

A comment for the DMF.

Access control requirements¶

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

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.

Usage notes¶

• If you want to update an existing data metric function and need to see the current definition of the function, run the DESCRIBE FUNCTION (DMF) command or call the GET_DDL function.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Example: Count non-NULL values¶

Create a DMF that calls the COUNT function to return the total number of rows that don’t contain NULL values when evaluating the first three columns in the table:

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.count_positive_numbers(
  arg_t TABLE(
    arg_c1 NUMBER,
    arg_c2 NUMBER,
    arg_c3 NUMBER
  )
)
RETURNS NUMBER
AS
$$
  SELECT
    COUNT(*)
  FROM arg_t
  WHERE
    arg_c1>0
    AND arg_c2>0
    AND arg_c3>0
$$;

Example: Foreign key reference¶

You can create a data metric function that references a different table than the table to which you assign the DMF. For example, you might want to create a DMF to measure the number of rows that are due to a foreign key constraint. As a representative example, consider two tables, salespeople and salesorders, which have a common column called sp_id that is designated as a foreign key constraint.

Create a DMF to identify the number of rows that include a foreign key reference. The WHERE clause specifies the sp_id column, which contains the identifiers for the sales persons, in the salespeople table:

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.foreign_key_sales(
  arg_t TABLE (arg_c INT))
RETURNS NUMBER AS
'SELECT COUNT(*) FROM arg_t
   WHERE arg_c IN (SELECT sp_id FROM salespeople)';

Copy

Call the DMF manually and specify the sp_id column in the salesorders table:

SELECT governance.dmfs.foreign_key_sales(SELECT sp_id FROM salesorders);

Copy

The output returns the number of rows in the salesorder table where the value in the sp_id column is the result of a value in the sp_id column in the salespeople table.