Basic Date Dimension

Basic Date Dimension

View Code to generate a date dimension from a range of dates

Data dimensions are common in data warehouses and provide lots of useful precalculated details about a date that help with reporting and analysis. It’s common to store these as a physical table, but in BiqQuery we can use a view to generate on the fly the dimension which will auto-extend daily and have a zero cost.

A more advanced version with a fiscal calendar can be found here Advanced Date Dimension with Fiscal Calendar

CREATE OR REPLACE VIEW `projectid`.dataset.dim_date_basic
AS
WITH
 all_days AS
(
SELECT day AS date,
    EXTRACT(YEAR FROM day) AS year,
    EXTRACT(WEEK FROM day) AS week,
    EXTRACT(MONTH FROM day) AS month_no,
    FORMAT_DATE("%m", day) AS month_no_fmt,
    EXTRACT(DAY FROM day) AS day_no,
    FORMAT_DATE("%d", day) AS day_no_fmt,
    EXTRACT(YEAR FROM day) || '-' || FORMAT_DATE("%m", day) AS year_month,
    EXTRACT(YEAR FROM day) || '-Q' || EXTRACT(QUARTER FROM day) AS year_qtr,
    EXTRACT(DAYOFYEAR FROM day) AS day_of_year,
    CAST(CASE WHEN EXTRACT(DAYOFWEEK FROM day) > 5 THEN EXTRACT(DAYOFWEEK FROM day)-5 ELSE EXTRACT(DAYOFWEEK FROM day)+2 END AS STRING) AS day_of_week,
    FORMAT_DATE("%A", day) AS day_name,
    FORMAT_DATE("%B", day) AS month,
    FORMAT_DATE("%b", day) AS short_month,
    FORMAT_DATE("%a", day) AS short_day_name,
    CASE WHEN FORMAT_DATE("%a", day) in ('Sat','Sun') THEN True ELSE False END AS is_weekend,
    DATE_TRUNC(day, MONTH) AS start_of_month,
    LAST_DAY(day, MONTH) AS end_of_month,
    EXTRACT(DAY FROM LAST_DAY(day, month)) AS month_length,
    EXTRACT(QUARTER FROM day) AS quarter,
    DATE_TRUNC(day, QUARTER) AS start_of_quarter,
    LAST_DAY(day, QUARTER) AS end_of_quarter,
    DATE_DIFF(CURRENT_DATE(), day, YEAR) AS year_diff, -- difference in years from today 0=today +ve in the past -ve in the future
    DATE_DIFF(CURRENT_DATE(), day, MONTH) AS month_diff, -- difference in months from today 0=today +ve in the past -ve in the future
    DATE_DIFF(CURRENT_DATE(), day, WEEK) AS week_diff, -- difference in weeks from today 0=today +ve in the past -ve in the future
    DATE_DIFF(CURRENT_DATE(), day, DAY) AS day_diff, -- difference in days from today 0=today +ve in the past -ve in the future
FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE('2007-03-30'), DATE_ADD(CURRENT_DATE(), INTERVAL 3 YEAR ), INTERVAL 1 DAY)
    ) AS day
)
SELECT d.date,
    d.year,
    d.week,
    d.month_no,
    d.month_no_fmt,
    d.day_no,
    d.day_no_fmt,
    d.year_month,
    d.year_qtr,
    d.day_of_year,
    d.day_of_week,
    d.day_name,
    d.month,
    d.short_month,
    d.short_day_name,
    d.is_weekend,
    d.start_of_month,
    d.end_of_month,
    d.month_length,
    d.quarter,
    d.start_of_quarter,
    d.end_of_quarter,
    d.year_diff,
    d.month_diff,
    d.week_diff,
    d.day_diff,
    /* 7 days */
    CASE WHEN d.day_diff BETWEEN 0 AND 6
        THEN true ELSE false END AS is_last_7_days
FROM all_days d
-- Test filter remove from view
-- WHERE month_diff=1
;

Example of using the dim date

SELECT
  year_month,
  start_of_month,
  end_of_month,
  COUNT(*) days
FROM
  `projectid`.dataset.dim_date_basic
WHERE year in (2020,2021)  
GROUP BY
  year_month,
  start_of_month,
  end_of_month
ORDER BY   
  year_month

2 Likes