Another new time series function in BigQuery in addition to the ones previously presented is the GAPS_FILL table-valued function.

It allows us to fill in a time series (DATE, DATETIME, TIMESTAMP) with missing rows to a desired time grain.

Previously, one could have solved this by joining with a date dimension or by using GENERATE_DATE_ARRAY for example.

It's simpler now, you just need to provide:
- the table you'd like to fill in
- the column you'd like to fill in (for example a DATE column)
- the interval you'd like the filling in to happen (time grain of the table)

In the example below, it allows us to fill in the time series with 2 missing days.

Since it's a table-valued function, it acts like a table so you select FROM it.

Obligatory remark that this is in 'Preview' for now.

Found it useful? Subscribe to my Analytics newsletter atnotjustsql.com.