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 )

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