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.
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
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
Read great educational content like this and a lot more !
Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too