I work quite a lot with temporal/SCD2 type table so the new (still in preview) RANGE data type in BigQuery (and its supporting methods) are a welcome addition.

What does it do?

So instead of storing valid_from & valid_to in separate columns, we now have a datatype to store the time segment in an [valid_from, valid_to) interval, of the form:

SELECT RANGE(DATE '2021-01-01', DATE '2023-01-01').

Note that the interval is left closed, right open (so left bound is included while the right one not).

This new semantic comes with a set of compatible functions :

- constructors for RANGE and arrays of RANGEs

- RANGE_START and RANGE_END to determine start and end of a segment

- RANGE_OVERLAPS, RANGE_INTERSECT and RANGE_CONTAINS to test the existence of an overlap, obtain the segment that overlaps and test the inclusion of a RANGE in another RANGE , respectively

While perhaps not a game changer, I still find the value in this upcoming feature.

Again, since this is still in preview it is not yet ready to use used in production.

See below an illustration of how it is used.

Found it useful? Check out to my Analytics newsletter at notjustsql.com.