Snowflake flatten on 2 dates

Using generated calendar and BETWEEN AND:

WITH calendar AS (
  SELECT DATEADD(day, ROW_NUMBER() OVER(ORDER BY seq8()), '1999-12-31'::DATE) AS d
  FROM TABLE(GENERATOR(ROWCOUNT => 365*100))
)
SELECT t.id, c.d
FROM calendar c
JOIN t
  ON c.d BETWEEN t.start_date AND t.end_date
ORDER BY t.id, c.d;

Output:

Lukasz answer is how you should do it (but the "date" table should be a public table with years woorth of data in it) so it's just a JOIN/BETWEEN as he notes.

But if we are going the the realm of UDF, lets use a UDTF, this emits a row per day in the range of values:

CREATE OR REPLACE FUNCTION date_range ( start_date date, end_date date)
  RETURNS TABLE ( day date )
  LANGUAGE JAVASCRIPT
  AS '{        
      processRow: function get_params(row, rowWriter, context){
           var t_date = new Date(row.START_DATE);

           while (t_date <= row.END_DATE){
                rowWriter.writeRow({DAY: t_date.valueOf()});
                t_date.setDate(t_date.getDate() + 1);
           }
        }
     }';

then we can just do a cross join to that:

SELECT 
    v.id,
    d.day
FROM VALUES 
    (1, '2021-05-10', '2021-05-12'),
    (2, '2021-07-05', '2021-07-09')
    v(id, start_date, end_date)
    ,TABLE(date_range(to_date(v.start_date), to_date(v.end_date))) d;

gives:

that can be rewritten, with a CTE to act and a quick source of "data" to show how the work to use this is rather simple:

WITH fake_data(id, start_date, end_date) AS (
    SELECT column1, to_date(column2), to_date(column3)
    FROM VALUES 
        (1, '2021-05-10', '2021-05-12'),
        (2, '2021-07-05', '2021-07-09')
)
SELECT 
    f.id
    ,d.day as date
FROM fake_data AS f,
    TABLE(date_range(f.start_date, f.end_date)) d;

which gives the same results again.

I use the UDF to get the list, this avoids the need to generate a calendar table, but with a bit more coding.

UDF:

create or replace function date_list(start_date string, diff float)
returns string
language javascript
as 
$$
    function get_date_number(num) {
        if(num < 10) {
            return '0' + num;
        }
        
        return num;
    }
    
    retVal = START_DATE;
    if(DIFF > 0) {
        for(i=1; i<=DIFF; i++) {
            var date = new Date(START_DATE);
            date.setDate(date.getDate() + i);
            retVal += ',' + 
                      date.getFullYear() + '-' + 
                      (get_date_number(date.getMonth()+1)) + '-' + 
                      get_date_number(date.getDate());
        }
    }
    
    return retVal;
$$;

Then run the query:

with t as (
    select 
        id, 
        strtok_to_array(
            date_list(
                start_date, 
                end_date - start_date
            ), 
            ','
        ) as date_list 
    from test
)
select id, f.value::string as date from t,
lateral flatten(input => t.date_list) f;

The result:

+----+------------+
| ID | DATE       |
|----+------------|
|  1 | 2021-05-10 |
|  1 | 2021-05-11 |
|  1 | 2021-05-12 |
|  2 | 2021-07-05 |
|  2 | 2021-07-06 |
|  2 | 2021-07-07 |
|  2 | 2021-07-08 |
|  2 | 2021-07-09 |
+----+------------+