Trying Out Snowflake's PERIOD Data Type for Contract and Pricing Plan History Management

This is an English translation of the Japanese article : https://dev.classmethod.jp/articles/snowflake-try-period/

This is Sagara.

With the Snowflake 10.32 release, the PERIOD data type became Generally Available.

By using the PERIOD data type, you can handle a time range such as "valid from when to when" as a single value, instead of splitting it into two columns for the start and end timestamps.

https://docs.snowflake.com/en/release-notes/2026/10_32

https://docs.snowflake.com/en/sql-reference/data-types-period

https://docs.snowflake.com/en/sql-reference/functions-period

In this post, assuming history management of customer contracts and pricing plans for an EC site, I'll try managing when each customer's pricing plan was valid using PERIOD(DATE).

While comparing it with the traditional approach of using separate start date / end date columns, I'll walk through practical use cases such as detecting overlapping periods and checking the contract status as of a specific point in time.

Feature Overview

The PERIOD data type holds a specific range on the timeline as a single value, consisting of a begin value and an end value of the same element type.

You can specify the following five element types (for TIME and TIMESTAMP types, you can also specify a scale of 0 to 9).

• PERIOD(DATE)
• PERIOD(TIME)
• PERIOD(TIMESTAMP_NTZ)
• PERIOD(TIMESTAMP_LTZ)
• PERIOD(TIMESTAMP_TZ)

Unlike INTERVAL, which merely represents a length of elapsed time, PERIOD has a concrete position: "from where to where on the timeline."

For example, it's well suited for managing information such as:

• Customer contract validity periods
• Product price application periods
• Employee tenure periods
• Campaign execution periods
• Validity periods of master data versions

There are mainly three ways to create a PERIOD value.

-- 1. PERIOD_CONSTRUCT function
SELECT PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-04-01');

-- 2. Typed literal
SELECT PERIOD(DATE) '[2026-01-01, 2026-04-01)';

-- 3. Cast from a string
SELECT '[2026-01-01, 2026-04-01)'::PERIOD(DATE);

The main functions are as follows (see the official docs for details).

Category	Function	Description
Construction / Access	PERIOD_CONSTRUCT	Constructs a PERIOD from a begin value and an end value
Construction / Access	PERIOD_BEGIN / PERIOD_END	Gets the begin value (inclusive) / end value (exclusive)
Comparison	PERIOD_CONTAINS	Determines whether another PERIOD or a datetime is contained
Comparison	PERIOD_OVERLAPS	Determines whether two PERIODs overlap
Comparison	PERIOD_MEETS	Determines whether two PERIODs are adjacent
Comparison	PERIOD_EQUALS	Determines whether the boundaries of two PERIODs match
Comparison	PERIOD_PRECEDES / PERIOD_SUCCEEDS	Determines whether one is entirely before / after the other
Comparison	PERIOD_IMMEDIATELY_PRECEDES / PERIOD_IMMEDIATELY_SUCCEEDS	Determines whether one precedes / follows the other with no gap
Set operations	PERIOD_INTERSECT	Gets the overlapping portion (NULL if there is no overlap)
Set operations	PERIOD_LDIFF / PERIOD_RDIFF	Gets the portion remaining before the other begins / after the other ends

In this verification, I'll try out several representative functions from this list that relate to real-world use cases.

Treated as a half-open interval [begin, end)

A PERIOD is a half-open interval that includes the begin value and excludes the end value.

[begin value, end value)

For example, consider the following two periods.

[2026-01-01, 2026-04-01)
[2026-04-01, 2026-07-01)

These two periods touch at April 1, 2026, but they do not overlap. This is because the first period does not include April 1, 2026, while the second period does.

With this format, it becomes easier to handle data with consecutive periods, such as pricing plan switches and contract renewals.

Comparison with other DWH / DB products

Products that handle periods as a dedicated data type or feature exist outside of Snowflake as well. Since we're here, I looked into them briefly as far as I could easily tell.

BigQuery

BigQuery has the RANGE data type.

RANGE<DATE> '[2026-01-01, 2026-04-01)'

BigQuery's RANGE is also a half-open interval that includes the lower bound and excludes the upper bound. You can specify DATE, DATETIME, and TIMESTAMP as element types.

It's similar to Snowflake's PERIOD, but in BigQuery you can directly express a range that is unbounded on one side by specifying UNBOUNDED or NULL. On the other hand, Snowflake's PERIOD does not support infinite values or NULL boundaries, so to express "still valid today," you need to work around it by using a far-future date such as 9999-12-31 as the end value.

https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types

PostgreSQL

PostgreSQL has Range Types.

The following are provided as representative built-in types.

• daterange
• tsrange
• tstzrange
• int4range
• int8range
• numrange

PostgreSQL's Range Types are characterized by being able to handle not only date and timestamp ranges but also integer and numeric ranges, and additionally by automatically providing a Multirange Type (a set of non-contiguous ranges) such as datemultirange corresponding to each Range Type.

SELECT daterange('2026-01-01', '2026-04-01', '[)');

Also, you can freely combine inclusive/exclusive boundaries using [ / ] (inclusive) and ( / ) (exclusive). Snowflake's PERIOD is fixed to the half-open interval [begin, end), so PostgreSQL is more flexible in this respect.

https://www.postgresql.org/docs/current/rangetypes.html

Preparation

Create a schema for verification

First, create a database and schema for verification. It's also fine to use an existing database and schema to suit your environment.

CREATE OR REPLACE DATABASE PERIOD_SAMPLE_DB;

CREATE OR REPLACE SCHEMA PERIOD_SAMPLE_DB.PUBLIC;

USE DATABASE PERIOD_SAMPLE_DB;
USE SCHEMA PUBLIC;

Create a contract / pricing plan history table

Create a table to manage the pricing plan history for each customer. Traditionally, you would often define two columns, VALID_FROM and VALID_TO.

This time, we'll store the begin value and end value together in the VALID_PERIOD column.

CREATE OR REPLACE TABLE CUSTOMER_PLAN_HISTORY (
    CUSTOMER_ID    NUMBER,
    PLAN_VERSION   NUMBER,
    PLAN_NAME      VARCHAR,
    MONTHLY_FEE    NUMBER(10, 2),
    VALID_PERIOD   PERIOD(DATE)
);

DESC TABLE CUSTOMER_PLAN_HISTORY;

If the type of VALID_PERIOD is displayed as PERIOD(DATE), the table creation is complete.

Trying it out

1. Insert data for verification

Assuming cases where the pricing plan switches for each customer, let's insert some dummy data.

-- Verification data for customer pricing plans
-- Register PERIOD as a half-open interval [begin date, end date)
-- Only CUSTOMER_ID=8 intentionally includes invalid data with overlapping periods

INSERT INTO CUSTOMER_PLAN_HISTORY
    (CUSTOMER_ID, PLAN_VERSION, PLAN_NAME, MONTHLY_FEE, VALID_PERIOD)
VALUES
    -- Customer 1: history switching FREE -> STANDARD -> PREMIUM
    (1, 1, 'FREE',     0.00,    PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-03-01')),
    (1, 2, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-03-01', DATE '2026-06-01')),
    (1, 3, 'PREMIUM',  2980.00, PERIOD_CONSTRUCT(DATE '2026-06-01', DATE '9999-12-31')),

    -- Customer 2: continues with STANDARD, with a price increase along the way
    (2, 1, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-01-15', DATE '2026-07-01')),
    (2, 2, 'STANDARD', 1080.00, PERIOD_CONSTRUCT(DATE '2026-07-01', DATE '9999-12-31')),

    -- Customer 3: downgrade from PREMIUM to STANDARD
    (3, 1, 'PREMIUM',  2980.00, PERIOD_CONSTRUCT(DATE '2026-02-01', DATE '2026-05-01')),
    (3, 2, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-05-01', DATE '2026-10-01')),
    (3, 3, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-10-01', DATE '9999-12-31')),

    -- Customer 4: uses TRIAL only for a short period
    (4, 1, 'TRIAL',    0.00,    PERIOD_CONSTRUCT(DATE '2026-03-01', DATE '2026-03-15')),
    (4, 2, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-03-15', DATE '2026-09-01')),
    (4, 3, 'PREMIUM',  2980.00, PERIOD_CONSTRUCT(DATE '2026-09-01', DATE '9999-12-31')),

    -- Customer 5: uses PREMIUM long-term, with a price increase at year end
    (5, 1, 'PREMIUM',  2980.00, PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-12-31')),
    (5, 2, 'PREMIUM',  3480.00, PERIOD_CONSTRUCT(DATE '2026-12-31', DATE '9999-12-31')),

    -- Customer 6: multiple price revisions
    (6, 1, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-04-01')),
    (6, 2, 'STANDARD', 1180.00, PERIOD_CONSTRUCT(DATE '2026-04-01', DATE '2026-08-01')),
    (6, 3, 'STANDARD', 1280.00, PERIOD_CONSTRUCT(DATE '2026-08-01', DATE '9999-12-31')),

    -- Customer 7: a special plan applied only during the campaign period
    (7, 1, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-04-01')),
    (7, 2, 'CAMPAIGN', 500.00,  PERIOD_CONSTRUCT(DATE '2026-04-01', DATE '2026-05-01')),
    (7, 3, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-05-01', DATE '9999-12-31')),

    -- Customer 8: invalid data with overlapping periods due to a registration mistake
    (8, 1, 'STANDARD', 980.00,  PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-06-01')),
    (8, 2, 'PREMIUM',  2980.00, PERIOD_CONSTRUCT(DATE '2026-05-01', DATE '2026-08-01'));

2. Check the display format of PERIOD values and the functions for extracting boundary values

Let's look at the registered PERIOD values as they are.

SELECT
    CUSTOMER_ID,
    PLAN_VERSION,
    PLAN_NAME,
    VALID_PERIOD,
    PERIOD_BEGIN(VALID_PERIOD) AS VALID_FROM,
    PERIOD_END(VALID_PERIOD) AS VALID_TO
FROM CUSTOMER_PLAN_HISTORY
WHERE CUSTOMER_ID = 1
ORDER BY PLAN_VERSION;

PERIOD values are returned in the display format [begin value, end value). By using PERIOD_BEGIN and PERIOD_END, you can extract the begin date and end date individually, in a form close to the traditional VALID_FROM / VALID_TO columns.

3. Get the pricing plan valid as of a specified date

As a practical use case, let's check the pricing plans that were valid as of May 15, 2026.

SELECT
    CUSTOMER_ID,
    PLAN_NAME,
    MONTHLY_FEE,
    VALID_PERIOD
FROM CUSTOMER_PLAN_HISTORY
WHERE PERIOD_CONTAINS(VALID_PERIOD, DATE '2026-05-15')
ORDER BY CUSTOMER_ID;

PERIOD_CONTAINS is a function that determines whether a PERIOD contains the specified datetime. With this query, only pricing plans that include May 15, 2026 are retrieved.

For example, in the case of customer 1, [2026-03-01, 2026-06-01) is retrieved, but [2026-01-01, 2026-03-01), which ended before May 15, 2026, is not.

4. Check the behavior of boundary values in a half-open interval

Let's verify the behavior of the half-open interval, which is a key point of PERIOD. Customer 1's pricing plans include the following two adjacent periods.

[2026-01-01, 2026-03-01)
[2026-03-01, 2026-06-01)

Let's search for the plan valid as of March 1, 2026.

SELECT
    PLAN_VERSION,
    PLAN_NAME,
    VALID_PERIOD,
    PERIOD_CONTAINS(VALID_PERIOD, DATE '2026-03-01') AS CONTAINS_2026_03_01
FROM CUSTOMER_PLAN_HISTORY
WHERE CUSTOMER_ID = 1
ORDER BY PLAN_VERSION;

March 1, 2026, which is the end value, is not included in the previous period; it is included only as the begin value of the next period. Thanks to this behavior, you can manage pricing plan switches with gap-free adjacent periods without duplicating boundary values.

5. Determine overlaps between a campaign period and pricing plans

Assuming a campaign running from April 1, 2026 to June 1, 2026, let's find customers whose pricing plan validity periods overlap with this period.

WITH campaign AS (
    SELECT PERIOD_CONSTRUCT(DATE '2026-04-01', DATE '2026-06-01') AS CAMPAIGN_PERIOD
)
SELECT
    h.CUSTOMER_ID,
    h.PLAN_NAME,
    h.VALID_PERIOD,
    PERIOD_OVERLAPS(h.VALID_PERIOD, c.CAMPAIGN_PERIOD) AS IS_OVERLAPPED,
    PERIOD_INTERSECT(h.VALID_PERIOD, c.CAMPAIGN_PERIOD) AS OVERLAPPED_PERIOD
FROM CUSTOMER_PLAN_HISTORY h
CROSS JOIN campaign c
ORDER BY h.CUSTOMER_ID, h.PLAN_VERSION;

PERIOD_OVERLAPS determines whether two PERIODs overlap even at a single point, and PERIOD_INTERSECT returns the actually overlapping portion.

For example, customer 3's [2026-02-01, 2026-05-01) partially overlaps with the campaign period [2026-04-01, 2026-06-01), and [2026-04-01, 2026-05-01) is returned in OVERLAPPED_PERIOD.

6. Distinguish adjacent periods from overlapping periods

In pricing plan history management, there are cases where you want to distinguish between "periods that are merely adjacent with no gap" and "periods that are actually overlapping."

WITH history_with_prev AS (
    SELECT
        CUSTOMER_ID,
        PLAN_VERSION,
        PLAN_NAME,
        VALID_PERIOD,
        LAG(VALID_PERIOD) OVER (
            PARTITION BY CUSTOMER_ID ORDER BY PLAN_VERSION
        ) AS PREVIOUS_PERIOD
    FROM CUSTOMER_PLAN_HISTORY
)
SELECT
    CUSTOMER_ID,
    PLAN_VERSION,
    PLAN_NAME,
    PREVIOUS_PERIOD,
    VALID_PERIOD,
    PERIOD_MEETS(PREVIOUS_PERIOD, VALID_PERIOD) AS IS_ADJACENT,
    PERIOD_OVERLAPS(PREVIOUS_PERIOD, VALID_PERIOD) AS IS_OVERLAPPED
FROM history_with_prev
WHERE PREVIOUS_PERIOD IS NOT NULL
ORDER BY CUSTOMER_ID, PLAN_VERSION;

PERIOD_MEETS is a function that determines whether two PERIODs are adjacent with no gap. For customers 1 through 7, who switch plans normally, the result is IS_ADJACENT = TRUE and IS_OVERLAPPED = FALSE.

On the other hand, for customer 8, where invalid data was intentionally inserted, the result is IS_ADJACENT = FALSE and IS_OVERLAPPED = TRUE as shown below, so the period overlap can be detected.

In this way, by using PERIOD comparison functions, you can implement overlap checks on history data such as SCD Type 2 without writing your own comparisons of begin and end dates.

7. Manage operator working hours with PERIOD using TIMESTAMP_NTZ as the element type

Finally, let's also try a case where PERIOD is used at the time level rather than the date level. Assuming call center operator working hours, we'll create a table with PERIOD(TIMESTAMP_NTZ).

CREATE OR REPLACE TABLE OPERATOR_SHIFT (
    OPERATOR_ID  NUMBER,
    SHIFT_PERIOD PERIOD(TIMESTAMP_NTZ)
);

INSERT INTO OPERATOR_SHIFT
    (OPERATOR_ID, SHIFT_PERIOD)
VALUES
    (101, PERIOD_CONSTRUCT('2026-09-15 09:00:00'::TIMESTAMP_NTZ, '2026-09-15 17:00:00'::TIMESTAMP_NTZ)),
    (102, PERIOD_CONSTRUCT('2026-09-15 13:00:00'::TIMESTAMP_NTZ, '2026-09-15 21:00:00'::TIMESTAMP_NTZ));

Let's check which operators were on duty as of 15:00 on September 15, 2026, and the overlapping portion of the two operators' working hours.

SELECT
    a.OPERATOR_ID AS OPERATOR_A,
    b.OPERATOR_ID AS OPERATOR_B,
    PERIOD_OVERLAPS(a.SHIFT_PERIOD, b.SHIFT_PERIOD) AS IS_OVERLAPPED,
    PERIOD_INTERSECT(a.SHIFT_PERIOD, b.SHIFT_PERIOD) AS OVERLAPPED_SHIFT
FROM OPERATOR_SHIFT a
JOIN OPERATOR_SHIFT b
    ON a.OPERATOR_ID < b.OPERATOR_ID;

As shown, I was able to confirm that PERIOD can be used not only for contract periods but also for time-level range management such as working hours and equipment operating hours.

Conclusion

I tried managing customer pricing plan history using Snowflake's PERIOD data type.

History management of pricing plans like this can also be implemented with the traditional approach of managing begin and end dates in separate columns. On the other hand, with PERIOD, overlap and containment relationships between periods can be expressed with dedicated functions, so you don't have to write your own logic combining BETWEEN and inequality operators, and I felt that query readability improves.

In particular, it seems effective for use cases such as SCD Type 2 history management, managing validity periods of contracts and pricing plans, matching campaign periods against contract periods, and managing working hours and equipment operating hours.

On the other hand, as of September 16, 2026, there are limitations such as usage inside VARIANT and usage in Iceberg tables. Since you need to use an end value such as 9999-12-31 to represent a "still valid" period, when actually adopting it, it seems necessary to decide the rules for this end value in advance on the application and data integration side.

I think it's a convenient data type if used well, so please do consider it.