How to build a calendar DIM_DATE table in Bigquery without using any source table?

The calendar table is used extensively in reporting to generate weekly / monthly /quarterly reports. To build a calendar table, you don't have to start from scratch, you can use the below query to build a Calendar table in BigQuery.

CREATE OR REPLACE TABLE Dataset2.MY_DATE_DIMENSION (
   MY_DATE          DATE        NOT NULL
  ,YEAR             INT64    NOT NULL
  ,MONTH            INT64    NOT NULL
  ,MONTH_NAME       STRING     NOT NULL
  ,DAY_OF_MON       INT64    NOT NULL
  ,DAY_OF_WEEK      INT64  NOT NULL
  ,WEEK_OF_YEAR     INT64    NOT NULL
  ,DAY_OF_YEAR      INT64    NOT NULL
)
CLUSTER BY MY_DATE,YEAR,MONTH;

And then insert the date values:

INSERT INTO Dataset2.MY_DATE_DIMENSION
SELECT
         MY_DATE
        ,EXTRACT(YEAR FROM MY_DATE)
        ,EXTRACT(MONTH FROM MY_DATE)
        ,FORMAT_DATETIME("%B", DATETIME(MY_DATE))
        ,EXTRACT(DAY FROM MY_DATE)
        ,EXTRACT(DAYOFWEEK FROM MY_DATE)
        ,EXTRACT(WEEK FROM MY_DATE)
        ,EXTRACT(DAYOFYEAR FROM MY_DATE)
    FROM 
    (
    SELECT DATE_ADD('2000-01-01',INTERVAL param DAY) AS MY_DATE
      FROM unnest(GENERATE_ARRAY(0, 10000, 1)) as param
    )
;

nVector

posted on 16 Sep 20