Sequences and auto-increment in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Sequence Behavior in Snowflake

What will be the output of the following Snowflake SQL commands?

CREATE SEQUENCE seq_test START = 5 INCREMENT = 3;
SELECT seq_test.nextval AS val1;
SELECT seq_test.nextval AS val2;

Choose the correct pair of values returned by the two SELECT statements.

Snowflake

CREATE SEQUENCE seq_test START = 5 INCREMENT = 3;
SELECT seq_test.nextval AS val1;
SELECT seq_test.nextval AS val2;

A val1 = 5, val2 = 8

B val1 = 3, val2 = 6

C val1 = 5, val2 = 5

D val1 = 8, val2 = 11

💡 Hint

Remember the sequence starts at 5 and increments by 3 each time nextval is called.

The sequence starts at 5, so the first nextval returns 5. The second nextval adds the increment 3, returning 8.

❓ Configuration

intermediate

Configuring a Sequence with MAXVALUE in Snowflake

You want to create a sequence in Snowflake that starts at 1, increments by 1, and stops generating values after reaching 10. Which CREATE SEQUENCE statement correctly enforces this behavior?

A CREATE SEQUENCE seq_limit START = 1 INCREMENT = 1 MAXVALUE = 10 CYCLE = FALSE;

B CREATE SEQUENCE seq_limit START = 1 INCREMENT = 1 MAXVALUE = 10 CYCLE = TRUE;

C CREATE SEQUENCE seq_limit START = 1 INCREMENT = 1 NO MAXVALUE;

D CREATE SEQUENCE seq_limit START = 1 INCREMENT = 1 MAXVALUE = 10;

💡 Hint

To stop the sequence after reaching the max value, cycling must be disabled.

Setting MAXVALUE to 10 limits the sequence to 10. CYCLE = FALSE prevents it from restarting after reaching max.

❓ Architecture

advanced

Choosing Sequence Usage for Distributed Inserts

You have a Snowflake table receiving inserts from multiple distributed clients simultaneously. You want to assign unique IDs automatically without conflicts or gaps. Which approach is best?

A Use UUIDs generated by the clients instead of sequences.

B Generate IDs client-side using timestamps and random numbers.

C Use a single Snowflake sequence with NEXTVAL for all inserts.

D Create multiple sequences, one per client, and merge IDs later.

💡 Hint

Snowflake sequences are designed to handle concurrent access safely.

A single Snowflake sequence guarantees unique, ordered IDs even with concurrent clients, avoiding conflicts and gaps.

❓ security

advanced

Sequence Access Control in Snowflake

Which Snowflake privilege must a user have to be able to use NEXTVAL on a sequence?

A MODIFY privilege on the sequence

B USAGE privilege on the sequence

C SELECT privilege on the sequence

D OWNERSHIP privilege on the sequence

💡 Hint

Check which privilege allows using sequence values without changing ownership.

The USAGE privilege allows a user to call NEXTVAL on a sequence without needing ownership.

❓ service_behavior

expert

Sequence Behavior After Reaching MAXVALUE with CYCLE Enabled

Consider this Snowflake sequence:

CREATE SEQUENCE seq_cycle START = 1 INCREMENT = 2 MAXVALUE = 7 CYCLE = TRUE;

What will be the sequence of values returned by five consecutive calls to NEXTVAL?

Snowflake

CREATE SEQUENCE seq_cycle START = 1 INCREMENT = 2 MAXVALUE = 7 CYCLE = TRUE;
SELECT seq_cycle.nextval;
SELECT seq_cycle.nextval;
SELECT seq_cycle.nextval;
SELECT seq_cycle.nextval;
SELECT seq_cycle.nextval;

A 1, 3, 5, 7, 3

B 1, 3, 5, 7, 9

C 1, 3, 5, 7, 5

D 1, 3, 5, 7, 1

💡 Hint

With CYCLE enabled, the sequence restarts after reaching MAXVALUE.

The sequence starts at 1, increments by 2: 1,3,5,7. After 7 (max), it cycles back to start at 1 again.