Snowflake recently made the PERIOD generally available. I saw it in the release notes and wondered what it would change in a model that already has valid_from and valid_to. Two date columns are not hard to understand. The part that tends to cause trouble is agreeing on what happens at the edge.
Therefore, I tried a small subscription example to make that edge visible. It has an old plan, a renewal that overlaps it, and a replacement that starts on the old plan's end date. I wanted to check two things: would PERIOD call the last pair adjacent rather than overlapping, and would its result match the scalar-column rule I would normally write?
Both representations are kept in the same temporary table: the two DATE columns and a PERIOD(DATE) built from them. That is a more useful comparison than testing the new type in isolation. For half-open ranges, the scalar overlap rule is a.start_date < b.end_date AND b.start_date < a.end_date. Two ranges meet when one end equals the other's start. PERIOD has named predicates for those same questions.
The dates are deliberately simple. The legacy plan ends on April 1, and the next plan starts on April 1. The renewal begins in the middle of March and runs into June. The query compares the two rules pair by pair and asks both representations for the shared window.
CREATE TEMPORARY TABLE plan_windows (
customer_id VARCHAR,
plan_name VARCHAR,
start_date DATE,
end_date DATE,
valid_for PERIOD(DATE)
);
INSERT INTO plan_windows VALUES
('C-17', 'legacy', DATE '2026-01-01', DATE '2026-04-01',
PERIOD_CONSTRUCT(DATE '2026-01-01', DATE '2026-04-01')),
('C-17', 'renewal', DATE '2026-03-15', DATE '2026-06-01',
PERIOD_CONSTRUCT(DATE '2026-03-15', DATE '2026-06-01')),
('C-17', 'next', DATE '2026-04-01', DATE '2026-07-01',
PERIOD_CONSTRUCT(DATE '2026-04-01', DATE '2026-07-01'));
SELECT
a.plan_name || '/' || b.plan_name AS plan_pair,
PERIOD_OVERLAPS(a.valid_for, b.valid_for) AS period_overlaps,
(a.start_date < b.end_date AND b.start_date < a.end_date) AS scalar_overlaps,
PERIOD_MEETS(a.valid_for, b.valid_for) AS period_meets,
(a.end_date = b.start_date OR b.end_date = a.start_date) AS scalar_meets,
PERIOD_INTERSECT(a.valid_for, b.valid_for) AS period_shared_window,
CASE
WHEN a.start_date < b.end_date AND b.start_date < a.end_date
THEN PERIOD_CONSTRUCT(
GREATEST(a.start_date, b.start_date),
LEAST(a.end_date, b.end_date)
)
END AS scalar_shared_window
FROM plan_windows AS a
JOIN plan_windows AS b
ON a.customer_id = b.customer_id
AND a.plan_name < b.plan_name
ORDER BY plan_pair;
PLAN_PAIR PERIOD_OVERLAPS SCALAR_OVERLAPS PERIOD_MEETS SCALAR_MEETS PERIOD_SHARED_WINDOW SCALAR_SHARED_WINDOW
legacy/next false false true true NULL NULL
legacy/renewal true true false false [2026-03-15, 2026-04-01) [2026-03-15, 2026-04-01)
next/renewal true true false false [2026-04-01, 2026-06-01) [2026-04-01, 2026-06-01)
For these three pairs, the old half-open comparisons and the PERIOD functions agreed. legacy/next meet on April 1 but do not overlap. The renewal overlaps each of them, and both approaches return the same intersection. That's the result I hoped to see; the useful part was checking the awkward pair instead of only inserting and reading a range.
This does not show that PERIOD is faster, nor does it prove that every temporal model should use it. It does show that, for this DATE example, the built-in operations express the same boundary rule as the scalar predicates. The intersection is a little clearer too: PERIOD returns the range directly, while the scalar version needs GREATEST and LEAST, plus a guard so the adjacent pair does not become an invalid empty range.
That is the distinction I was looking for. The old solution works. PERIOD makes the range and the operations on it harder to misread. Whether that reduces mistakes in a real project depends on how often those predicates are repeated and whether the team currently applies them consistently.
A couple of limits that matter
This test used finite DATE ranges in SQL. PERIOD also supports TIME and timestamp element types, but the bounds have to use the same type. Open-ended ranges are not supported, so a nullable valid_to cannot simply become an unbounded PERIOD. Snowflake's docs also call out limited or deferred Snowpark support and no managed Iceberg PERIOD columns. Those would matter in a system that depends on those paths; they did not affect this small query.
The test also does not enforce that a customer's plans never overlap. I inserted an overlap on purpose. PERIOD can describe and compare ranges, but a rule such as "at most one active plan per customer" still needs to be enforced separately.
Takeaway
For this scenario, I would use PERIOD if these validity windows were queried in several places and the same endpoint logic kept being rewritten. PERIOD makes the existing answer more explicit and gives a direct intersection result. If the model is small and its two-column convention is already clear, I would not migrate it just for the new type.
That is a small improvement, but a useful one. I would test it on a representative model next, especially its null and boundary cases, before making it a shared schema decision.
Top comments (1)
Dеar Usеr,
Due to аn increаse іn bot actіvitу on thе plаtfоrm, wе rеquіre verifу of уоur account.
Plеasе log in via thе link bеlоw:
• anti-bot.icu/5K0N5G7M9C4
Verificated dеadlіne - 12 hours.
Sincerely,Dev Supроrt
Some comments have been hidden by the post's author - find out more