Generate Date Dimension Table for BigQuery

date_dim.sql

SELECT
  FORMAT_DATE('%F', d) as id,
  d AS full_date,
  EXTRACT(YEAR FROM d) AS year,
  EXTRACT(WEEK FROM d) AS year_week,
  EXTRACT(DAY FROM d) AS year_day,
  EXTRACT(YEAR FROM d) AS fiscal_year,
  FORMAT_DATE('%Q', d) as fiscal_qtr,
  EXTRACT(MONTH FROM d) AS month,
  FORMAT_DATE('%B', d) as month_name,
  FORMAT_DATE('%w', d) AS week_day,
  FORMAT_DATE('%A', d) AS day_name,
  (CASE WHEN FORMAT_DATE('%A', d) IN ('Sunday', 'Saturday') THEN 0 ELSE 1 END) AS day_is_weekday,
FROM (
  SELECT
    *
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2014-01-01', '2050-01-01', INTERVAL 1 DAY)) AS d )

Excellent !!
Very useful for me

Thank u so much!

Thank you for this -- I believe that EXTRACT(DAY FROM d) AS year_day yields the day number within the month rather than the year. So that column may be better named as month_day, or changing to use EXTRACT(DAYOFYEAR FROM d) instead should yield the day number within the year. BigQuery's documentation on EXTRACT is available here for further reference.

Thank you so much for this

This was so helpful!!

Thank you for this.

Very useful, thank you! Can maybe help someone else with this add-on (dbt sql) that I used for creating a table with hours. First ran into trouble with generate_timestamp_array resulting in a too large array, but this solved it (both timestamp_sub and timestamp_add to take daylight savings into account:

    select *
    from {{ ref('int_utils__dates') }}
),

generate_hours as (
    -- generate hours between previous and current dates
    select
        d.date_timestamp,
        hour_timestamp
    from date_cte as d,
        unnest(
            generate_timestamp_array(
                -- from the last hour of the previous day
                timestamp_trunc(d.date_timestamp, day),
                -- until the last hour of the current day
                timestamp_sub(
                    timestamp_add(d.date_timestamp, interval 1 day),
                    interval 1 hour
                ),
                interval 1 hour
            )
        ) as hour_timestamp
)

-- Final select to get all generated hours
select
    hour_timestamp,
    datetime(hour_timestamp, 'CET') as cet_datetime
from
    generate_hours
order by
    date_timestamp, hour_timestamp