Categories:

Data metric functions

SCHEMA_CHANGE_TYPE_COUNT (system data metric function)

Returns the number of columns whose data type changed in the associated table or view since the previous evaluation of the function. On the first evaluation, the function records a baseline of the object’s columns and returns 0.

Syntax

Not applicable.

Returns

The function returns a scalar value with a NUMBER data type.

Usage notes

  • You can’t call this function directly. To learn how to associate the function with a table or view so it runs at regular intervals, see Associate a DMF.
  • Each column whose data type changed counts as one change. A change of length, precision, or scale counts as a type change. For example, changing a column from NUMBER(10,0) to NUMBER(15,0) counts as one change.
  • Renamed columns aren’t counted. To count renames along with every other kind of schema change, use SCHEMA_CHANGE_COUNT.
  • Unlike SCHEMA_CHANGE_COUNT, this function doesn’t write a row for each change to the DATA_QUALITY_MONITORING_LOGS view.
  • You can’t use FILTER or WITHIN GROUP with this function.

Example

Associate the function with the customers table, and define an expectation that is violated when the data type of a column changes:

ALTER TABLE crm.public.customers
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.SCHEMA_CHANGE_TYPE_COUNT
    ON ()
    EXPECTATION no_type_changes (VALUE = 0);