Using GAPS_FILL in BigQuery

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.

SELECT
  dates.transaction_date

FROM GAP_FILL (
  TABLE learning.dates_with_gaps,
  'transaction_date',
  INTERVAL 1 DAY
) dates

WITH bounds AS (
  SELECT
    MIN(transaction_date) AS start_date,
    MAX(transaction_date) AS end_date
  FROM `learning.dates_with_gaps`
)

SELECT filled_date AS transaction_date
FROM bounds
JOIN UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS filled_date
LEFT JOIN `learning.dates_with_gaps` dates ON filled_date = dates.transaction_date