Snowflake : Create an array with a series of numbers within a given range [ARRAY_GENERATE_RANGE()]


Snowflake’s ARRAY_GENERATE_RANGE() function allows you to create an array with a series of numbers within a given range (i.e., from a start number up to, but not including, an end number) at a specified interval. The syntax for the function is ARRAY_GENERATE_RANGE(start, end, step) where:

  1. start is the start of the sequence
  2. end is the stop of the sequence (exclusive)
  3. step is the step size for incrementing from start to end

Here’s a detailed example:

Suppose you want to create an array of numbers starting from 1, ending at 10, with a step of 2. The SQL command will look like this:


The output will be:

[1, 3, 5, 7, 9]


  1. The start value is 1, so the array starts with 1.
  2. The step value is 2, so each subsequent value is incremented by 2 (1, 3, 5, 7, 9).
  3. The end value is 10. Since the ARRAY_GENERATE_RANGE function does not include the end value, the array stops at 9 which is the last number before 10 that fits the pattern.

You can use this function with a TABLE to generate an array for each row. For instance:

CREATE TABLE my_table AS (
    SELECT column1 AS start_val,
           column2 AS end_val,
           column3 AS increment_val
    FROM some_other_table

SELECT start_val,
       ARRAY_GENERATE_RANGE(start_val, end_val, increment_val) AS sequence
FROM my_table;

In this example, a new table my_table is created from some_other_table with columns start_val, end_val, and increment_val. Then, ARRAY_GENERATE_RANGE() is used to generate an array for each row of my_table, using start_val, end_val, and increment_val as arguments for the function.

Snowflake important urls to refer

  1. Snowflake Official Page
Author: user

Leave a Reply