BigQuery GAPS_FILL: Fill Missing Rows in Time Series

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.

Using GAPS_FILL in BigQuery

Comments

Practical SQL

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

More from this blog