Using GAPS_FILL in BigQuery
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
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.
