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.

Practice

1. What is the main purpose of a sequence in Snowflake?

easy

A. To create backup copies of tables

B. To store large text data efficiently

C. To automatically generate unique numeric values

D. To manage user permissions

Solution

1. Step 1: Understand what a sequence does

   A sequence generates numbers automatically, usually for unique IDs.

2. Step 2: Identify the correct purpose

   Among the options, only automatic unique number generation matches the sequence's role.

3. Final Answer:

   To automatically generate unique numeric values -> Option C

4. Quick Check:

   Sequence = unique number generator [OK]

Hint: Sequences generate unique numbers automatically [OK]

Common Mistakes:

• Confusing sequences with tables
• Thinking sequences store text data
• Assuming sequences manage permissions

2. Which of the following is the correct syntax to get the next value from a sequence named order_seq?

easy

A. SELECT order_seq.NEXTVAL();

B. SELECT NEXTVAL('order_seq');

C. SELECT NEXTVAL(order_seq);

D. SELECT order_seq.nextval;

Solution

1. Step 1: Recall Snowflake sequence syntax

   Snowflake uses sequence_name.NEXTVAL() dot notation to get the next value.

2. Step 2: Match syntax to options

   Only SELECT order_seq.NEXTVAL(); uses the correct syntax.

3. Final Answer:

   SELECT order_seq.NEXTVAL(); -> Option A

4. Quick Check:

   Use seq_name.NEXTVAL() syntax [OK]

Hint: Use seq_name.NEXTVAL() [OK]

Common Mistakes:

• Omitting quotes around sequence name
• Passing non-string to NEXTVAL()
• Missing parentheses on NEXTVAL call

3. Given the sequence user_seq starting at 1000 and incrementing by 5, what will be the output of these two queries executed in order?

SELECT NEXTVAL('user_seq');
SELECT NEXTVAL('user_seq');

medium

A. 1005 and 1010

B. 1000 and 1001

C. 1000 and 1005

D. 1 and 2

Solution

1. Step 1: Understand sequence start and increment

   The sequence starts at 1000 and adds 5 each time NEXTVAL is called.

2. Step 2: Calculate the two NEXTVAL calls

   First call returns 1005, second call returns 1010.

3. Final Answer:

   1005 and 1010 -> Option A

4. Quick Check:

   Start=1000, increment=5, next two values = 1005, 1010 [OK]

Hint: First value = start + increment, next adds increment again [OK]

Common Mistakes:

• Assuming first NEXTVAL returns start value
• Confusing start value with first increment
• Thinking sequence resets after one call

4. You created a sequence with:
CREATE SEQUENCE invoice_seq START = 1 INCREMENT = 1;
But when you run SELECT NEXTVAL('invoice_seq'); you get an error. What is the most likely cause?

medium

A. Sequence name must be uppercase

B. Sequence was not committed or created properly

C. NEXTVAL function does not exist in Snowflake

D. You must specify schema name in NEXTVAL

Solution

1. Step 1: Check sequence creation and commit

   Sequences must be created successfully and committed before use.

2. Step 2: Identify common error cause

   If NEXTVAL errors, often the sequence does not exist or creation failed.

3. Final Answer:

   Sequence was not committed or created properly -> Option B

4. Quick Check:

   Sequence must exist before NEXTVAL call [OK]

Hint: Ensure sequence creation succeeded before NEXTVAL [OK]

Common Mistakes:

• Assuming case sensitivity causes error
• Thinking NEXTVAL is unsupported
• Forgetting to commit or check creation

5. You want to create a table orders with an auto-incrementing order_id using a sequence order_seq. Which approach correctly assigns order_id during insert?

hard

A. Create sequence and use INSERT INTO orders VALUES (order_seq + 1, ...);

B. Create sequence and use INSERT INTO orders VALUES (NEXTVAL('order_seq'), ...);

C. Create sequence and manually increment order_id in application code

D. Create sequence and set order_id default to order_seq.NEXTVAL()

Solution

1. Step 1: Understand how to use sequences in inserts

   Sequences provide unique numbers via NEXTVAL function during insert statements.

2. Step 2: Evaluate options for assigning order_id

   Only B correctly sets the order_id column default to order_seq.NEXTVAL() for automatic assignment on insert (when column omitted).

3. Final Answer:

   Create sequence and set order_id default to order_seq.NEXTVAL() -> Option D

4. Quick Check:

   Column DEFAULT seq.NEXTVAL() [OK]

Hint: Set column DEFAULT seq_name.NEXTVAL() [OK]

Common Mistakes:

• Trying to use sequence name directly without NEXTVAL
• Manually incrementing IDs in app code
• Using invalid syntax like order_seq + 1