30 October 2018        Add to Favorites   Report

How to build a calendar DIM_DATE table in Snowflake 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 Snowflake.

Query:

CREATE OR REPLACE TABLE MY_DATE_DIMENSION (
MY_DATE DATE NOT NULL
,YEAR SMALLINT NOT NULL
,MONTH SMALLINT NOT NULL
,MONTH_NAME CHAR(3) NOT NULL
,DAY_OF_MON SMALLINT NOT NULL
,DAY_OF_WEEK VARCHAR(9) NOT NULL
,WEEK_OF_YEAR SMALLINT NOT NULL
,DAY_OF_YEAR SMALLINT NOT NULL
)
AS
WITH CTE_MY_DATE AS (
SELECT DATEADD(DAY, SEQ4(), '2000-01-01') AS MY_DATE
FROM TABLE(GENERATOR(ROWCOUNT=>10000)) -- Number of days after reference date in previous line
)
SELECT MY_DATE
,YEAR(MY_DATE)
,MONTH(MY_DATE)
,MONTHNAME(MY_DATE)
,DAY(MY_DATE)
,DAYOFWEEK(MY_DATE)
,WEEKOFYEAR(MY_DATE)
,DAYOFYEAR(MY_DATE)
FROM CTE_MY_DATE
;

Copied