COF-C03 Question 43
Single answerSequencesA data engineering team is loading order records from multiple concurrent ETL processes into a Snowflake table named ORDERS. The team needs a simple way to generate surrogate keys for a new column ORDER_ID without coordinating values across the ETL jobs. They create a sequence named ORDER_SEQ and use ORDER_SEQ.NEXTVAL in INSERT statements. After several test runs, the team notices gaps in the generated ORDER_ID values and asks whether this indicates a problem with data consistency. Which statement best explains this behavior?
- A
This is expected behavior because Snowflake sequences guarantee uniqueness, not gap-free or contiguous numbering, especially with concurrent transactions.
- B
This indicates the sequence was created incorrectly; recreating it with a larger START value will eliminate gaps during concurrent inserts.
- C
This happens only when the warehouse is suspended and resumed; keeping the warehouse running continuously will ensure contiguous sequence values.
- D
This means some INSERT transactions failed permanently; Snowflake sequences reuse skipped values only after a COMMIT is issued.
Show answer and explanation
Correct answer: A
Explanation
Snowflake sequences are appropriate for generating surrogate keys in concurrent loading scenarios because they provide unique values without requiring ETL job coordination. However, they are not suitable when the business requires gap-free numbering. Candidates should know that sequence-generated values can have gaps and that this is expected behavior rather than a sign of inconsistency. This aligns with Snowflake documentation and best practices: use sequences for uniqueness, not for enforcing continuous numbering. If a business process truly requires gap-free numbering, that requirement typically needs a different design approach and often introduces serialization or process constraints.
- A. Correct.
Correct. In Snowflake, sequences are designed to generate unique values, but they do not guarantee gap-free or perfectly consecutive numbering. Gaps can occur for legitimate reasons such as concurrent use, rolled-back transactions, or values being reserved and not ultimately visible in final row order. For surrogate keys, uniqueness is the important property, so this behavior is normal and does not by itself indicate data corruption or inconsistency.
- B. Incorrect.
Incorrect. The START value only determines the initial value generated by the sequence. It does not change Snowflake's fundamental sequence behavior or make values contiguous under concurrent workloads. A common misconception is that sequence settings can enforce gap-free numbering, but Snowflake sequences are not intended for that purpose.
- C. Incorrect.
Incorrect. Warehouse suspend/resume behavior is not the cause of normal sequence gaps. Sequence generation semantics are not designed to provide contiguous values based on warehouse uptime. This distractor reflects a misunderstanding that compute lifecycle affects sequence continuity in a way that guarantees no gaps, which is not true.
- D. Incorrect.
Incorrect. Snowflake does not reuse sequence values to fill gaps after COMMIT. Once a sequence value is generated, applications should not expect that unused or skipped numbers will be reassigned. Failed or rolled-back transactions can contribute to gaps, but the platform does not backfill those values.