How to Generate a Series in Snowflake

How to Generate a Series in Snowflake

We were recently working on a Snowflake data warehousing project. We had brought the data into our Enterprise Data Warehouse or EDW layer and were looking to add a Date Dimension.

A Date dimension is essential to every data model within a data warehouse. It helps us analyze our data (by way of slicing and dicing) by different time periods, such as calendar months, years, or even fiscal months and years, etc. Some experts, especially in the finance domain, refer to this as Date intelligence. This is very important for your reporting and advanced analytical needs.

In traditional database systems Oracle SQL or MS SQL Server, we would use sequences or recursive CTEs (Common Table Expressions) to generate this date dimension. In Snowflake, however, we can use the generator function.

Let’s understand the syntax first:

GENERATOR(ROWCOUNT => <count> [, TIMELIMIT => <sec>])

• If only the Rowcount parameter is used, the function generates <count> number of records.
• If only the Timelimit parameter is used, the function generates records until <sec> has/have lapsed.
• If both are used, whichever parameter executes/finishes first.

Let’s take a quick example to demonstrate the above:

SELECT seq4() as seq_gen
FROM TABLE(GENERATOR(rowcount => 10))
ORDER BY seq_gen;

SEQ_GEN

0

1

2

3

4

5

6

7

8

9

The above query quickly generates numbers between 0 and 9. Yes, the starting value from a generator is 0, and the ending value is <count> – 1.

______________________________________________________

Let’s extrapolate what we’ve learned so far to create the Date Dimension. Please note, the below example has been taken from here with minor modifications.

SELECT TO_DATE('2014-12-31','YYYY-MM-DD') AS start_date, 

seq1() AS sl,
ROW_NUMBER() OVER(ORDER BY sl) AS row_numbers,
DATEADD(DAY, row_numbers, start_date) AS v_date
FROM TABLE(GENERATOR(rowcount => 7305));

• The ‘start_date’ column identifies the generator table’s start date value. Currently, it is set to 31st December 2014. Please note that the table’s actual start date will be 1st Jan 2015, as we’ll note below.
• Seq1, 2, 4, 8, 10 – any can be used to generate a sequence of records.
• ROW_NUMBER() window function is used to ensure there are no gaps in the sequence. A known drawback of the sequence seq<number>() function is that it can have gaps in it. The window function helps to resolve that.
• The DATEADD() function helps to traverse through the days.
• TABLE(GENERATOR()) function generates the required date values. Currently, it is set to 7305 days or ~20 years. Please modify as required.

This is what the table looks like. It will continue until it reaches the final value of 31st Dec 2034.

Now, we can add more and more attributes to this dimension:

SELECT TO_DATE('2014-12-31','YYYY-MM-DD') AS start_date, --Modify for preferred table start date
seq1() AS sl,
ROW_NUMBER() OVER(ORDER BY sl) AS row_numbers,
DATEADD(DAY, row_numbers, start_date) AS v_date,
CASE
    WHEN DAYNAME(v_date) = 'Sun'
        AND dayname(v_date) = 'Sat'
    THEN 'Not-Weekday'
    ELSE 'Weekday'
END AS weekday_indicator,

DATE_TRUNC('week', v_date) AS week_begin_date,

LAST_DAY(v_date,'week') AS week_end_date

FROM TABLE(GENERATOR(rowcount => 7305)) --Set to generate 20 years. Modify as required
;

The “weekday_indicator” column indicates whether a particular day is a weekday or a weekend, assuming there is a 5-day workweek.

Or how about the powerful use of functions to get the week to begin and end dates.

As you will note, the generator function can work without any issues no matter how many more attributes you add to the date dimension.

That is how to generate a series in Snowflake. Should you need to build the entire date dimension, please find this useful link here .

Up Next:

Learn more about how to Write Data into Snowflake