Categories:

Data metric functions

SCHEMA_CHANGE_DROP_COUNT (system data metric function)

Returns the number of columns dropped from 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 dropped column 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 a column is dropped:

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