The new PERIOD data type for temporal ranges in Snowflake

The PERIOD data type stores an anchored temporal range as a single value containing an inclusive beginning bound and an exclusive regulated end bound in the format [begin, end). Additionally, this update introduces the ability to modify or clear filters on data metric function (DMF) associations using the FILTER clause.

Use cases

Imagine you need to manage employee employment windows, contract validities, or version validity intervals without manually dealing with separate start and end date columns.

• Create columns that store date, time, or timestamp ranges.
• Compare periods using native overlap, intersection, and boundary functions.
• Apply data quality checks to specific subsets of rows using filters on data metric functions.

How to use

You can define a column with the PERIOD type by specifying its element type and constructing values using constructors or typed literals.

CREATE OR REPLACE TABLE employment_windows (
  employee_id INTEGER,
  active_period PERIOD(DATE)
);

INSERT INTO employment_windows VALUES
  (1, PERIOD(DATE) '[2020-01-15, 2023-06-30)');

ALTER TABLE customer_data
  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
    ON (email)
    FILTER (status = 'ACTIVE');

What happens in this code:

1. Creates a table containing a column of type PERIOD(DATE).
2. Inserts a record into the table using a typed literal with half-open bounds.
3. Modifies a data metric function association on an existing table to evaluate only active rows.

Limitations and requirements

The PERIOD data type is Generally Available (GA). Empty periods are not allowed (begin must be strictly less than end). Unbounded periods and infinity literals are not supported, requiring conventions like far-future dates. PERIOD columns are not supported on managed Iceberg tables.