How to create a cyclic sequence in snowflake data warehouse

Question

I am looking for Sequence with a Cycle in Snowflake data warehouse like in Oracle. I guess Snowflake data warehouse doesn't have this built-in. Any idea how to implement ?

Answer 1

While Snowflake doesn't support it today (please consider filing a feature request in the Snowflake community forums), you can (mostly) simulate it by using a UDF, for example:

create or replace sequence seq;
create or replace function cyclic_seq() returns int as 'mod(seq.nextval, 3)';
create or replace table x(s string, i int default cyclic_seq());

insert into x(s) values('a');
insert into x(s) values('b');
insert into x(s) values('c');
insert into x(s) values('d');
insert into x(s) values('e');
insert into x(s) values('f');

select * from x;
---+---+
 S | I |
---+---+
 a | 1 |
 b | 2 |
 c | 0 |
 d | 1 |
 e | 2 |
 f | 0 |
---+---+