CREATE DATA METRIC FUNCTION¶

Creates a new data metric function (DMF) in the current or specified schema, or replaces an existing data metric function.

After creating a DMF, apply it to a table column using an ALTER TABLE … ALTER COLUMN command or a view column using the ALTER VIEW command.

This command supports the following variants:

See also:

DMF command reference, CREATE OR ALTER <object>

Syntax¶

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

Variant syntax¶

CREATE OR ALTER DATA METRIC FUNCTION¶

Creates a new data metric function if it doesn’t already exist, or transforms an existing data metric function into the function defined in the statement. A CREATE OR ALTER DATA METRIC FUNCTION statement follows the syntax rules of a CREATE DATA METRIC FUNCTION statement and has the same limitations as an ALTER FUNCTION (DMF) statement.

Unlike a CREATE OR REPLACE DATA METRIC FUNCTION command, a CREATE OR ALTER command updates the object without deleting and recreating it.

Supported function alterations include changes to the COMMENT property.

For more information, see CREATE OR ALTER DATA METRIC FUNCTION usage notes.

CREATE [ OR ALTER ] DATA METRIC FUNCTION ...
Copy

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.

( table_arg TABLE( col_arg data_type [ , ... ] ) [ , table_arg TABLE( col_arg data_type [ , ... ] ) ] )

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

You must specify:

  • An argument name for each table (table_arg).

  • For each table, an argument name for at least one column, along with its data type (col_arg data_type).

    You can optionally specify arguments for 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:

Privilege

Object

Notes

CREATE DATA METRIC FUNCTION

Schema

The privilege only enables the creation of data metric functions in the schema.

If you want to enable the creation of user-defined functions, such as SQL or Java UDFs, the role must have the CREATE FUNCTION privilege.

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.

General 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.

CREATE OR ALTER DATA METRIC FUNCTION usage notes¶

  • The following alterations are not supported:

    • Changing the body of the data metric function.

    • Changing the data metric and the input table definition.

Example: Single table argument¶

Create a DMF that calls the COUNT function to return the total number of rows that have positive numbers in the first three columns of 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
$$;
Copy

Example: Multiple table arguments¶

Returns the number of records where the value of a column in one table does not have a corresponding value in the column of another table:

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.referential_check(
  arg_t1 TABLE (arg_c1 INT), arg_t2 TABLE (arg_c2 INT))
RETURNS NUMBER
AS
$$
  SELECT
    COUNT(*)
    FROM arg_t1
  WHERE
    arg_c1 NOT IN (SELECT arg_c2 FROM arg_t2)
$$;
Copy

For an example that uses this DMF to validate referential integrity, see Example: Using multiple table arguments to perform referential checks.

Example: Alter a data metric function using the CREATE OR ALTER DATA METRIC FUNCTION command¶

Alters the single-table data metric function created in the example above to set security and comment.

CREATE OR ALTER SECURE DATA METRIC FUNCTION governance.dmfs.count_positive_numbers(
  arg_t TABLE(
    arg_c1 NUMBER,
    arg_c2 NUMBER,
    arg_c3 NUMBER
  )
)
RETURNS NUMBER
COMMENT = "count positive numbers"
AS
$$
  SELECT
    COUNT(*)
  FROM arg_t
  WHERE
    arg_c1>0
    AND arg_c2>0
    AND arg_c3>0
$$;
Copy
Language: English