In most data warehouse implementations, a date dimension is created to standardize dates and make time bucketing easier. In this quick example I will discuss how to create a simple query that can generate a table for a Date Dimension.

Creating the Ranges & Session Variables

ALTER SESSION SET  WEEK_START = 0;
SET (START_DATE,END_DATE) = ('1995-01-01','2025-01-01');
SET NUM_OF_DAYS = (SELECT DATEDIFF(DAY, TO_DATE($START_DATE), TO_DATE($END_DATE)));

This code block will execute 3 statements. The first statement will force the first day of the week to be Sunday. By default, the first day of the week is set to Monday. The second statement declares 2 variables for the range that the dimension table will cover. The third statement calculates the amount of days between the range. This last will be used by the GENERATOR function later on.

Dimension Creation

CREATE OR REPLACE TABLE DATE_DIM as 
WITH  DATE_DIM AS 
(
SELECT
    ROW_NUMBER() OVER( ORDER BY SEQ4()) -1 AS  DATE_DIM_ID
    ,TO_DATE(DATEADD(DAY,DATE_DIM_ID, TO_DATE($START_DATE))) AS DATE
FROM
     TABLE(GENERATOR(ROWCOUNT => $NUM_OF_DAYS))
)
    SELECT CONCAT(DATE_DIM_ID+10,YEAR(DATE),TO_CHAR(DATE,'MM'),TO_CHAR(DATE,'DD')) AS DATE_SK
        , DATE
        , DECODE(DAYNAME(DATE),
                 'Mon','Monday', 
                 'Tue','Tuesday', 
                 'Wed', 'Wednesday', 
                 'Thu','Thursday',
                 'Fri', 'Friday', 
                 'Sat','Saturday', 
                 'Sun', 'Sunday')  AS DAY_NAME
        , DAYNAME(DATE) AS DAY_NAME_ABV
        , DAYOFWEEK(DATE) DAY_OF_WEEK
        , MONTH(DATE) AS MONTH_NUM
        , CONCAT(YEAR(DATE),TO_CHAR(DATE,'MM')) AS MONTH_ID
        , TO_CHAR(DATE,'MMMM') AS MONTH_NAME
        , MONTHNAME(DATE) AS MONTH_NAME_ABV
        , DATEADD(DAY, 1,LAST_DAY( DATEADD(MONTH,-1,DATE),MONTH)) as First_Day_Month
        , LAST_DAY(DATE, MONTH) LAST_DAY_MONTH
        , QUARTER(DATE) AS QUARTER_NUM
        , CONCAT(YEAR(DATE),'Q',QUARTER(DATE)) AS QUARTER_ID
        , DATEADD(DAY, 1,LAST_DAY( DATEADD(QUARTER,-1,DATE),QUARTER)) as FIRST_DAY_QUARTER
        , LAST_DAY(DATE,QUARTER) AS LAST_DAY_QUARTER
        , YEAR(DATE) AS YEAR
         FROM DATE_DIM;

And this is it! The code above, generates a range of sequential numbers be leveraging the GENERATOR table function with a Row_Number window function that is used to aggregate those sequential days to the starting date variable. Once the range is calculated, I added calculations based on the Date Column like:

  • Date Surrogate Key
  • Day Of the Week
  • Day Name
  • Day Name Abbreviation
  • Month Number
  • Month Name
  • Month Name Abbreviation
  • Month ID (Concatenation of Year + Month)
  • Quarter
  • Quarter ID (Concatenation of Year + Quarter)
  • Last and First day of Month and Quarter
  • Year

A few caveats

  • In order to get the full month name, the DECODE function was used along with DAYNAME like shown below
DECODE(DAYNAME(DATE),
                 'Mon','Monday', 
                 'Tue','Tuesday', 
                 'Wed', 'Wednesday', 
                 'Thu','Thursday',
                 'Fri', 'Friday', 
                 'Sat','Saturday', 
                 'Sun', 'Sunday')  AS DAY_NAME
  • Instead of using MONTH() or DAY() in concatenations, I used TO_CHAR(DATE,’MM’) or TO_CHAR(DATE,’DD’) to preserve the double digits necessary for proper convention.

Results

And here it is, a quick and easy way of creating a Date Dimension in Snowflake!

Published by Carlos Serrano

Leave a comment