ADA-C01 Question 271
Single answerUse Snowflake sequences and identify limitationsA data engineering team is loading customer orders into a Snowflake table from several parallel ETL streams. They want each inserted row to receive a surrogate key generated by a Snowflake sequence. After several test runs, the team notices that the generated IDs are unique, but there are gaps in the numbering and the values do not strictly reflect commit order across concurrent sessions. The team asks the Snowflake administrator which statement best describes this behavior and the correct design implication.
Which statement should the administrator provide?
- A
This is expected behavior because Snowflake sequences guarantee uniqueness, but they do not guarantee gap-free or transaction-ordered values under concurrent use.
- B
This indicates sequence corruption; recreating the sequence will ensure future values are gap-free and aligned to commit order.
- C
Snowflake sequences only produce gap-free values when used in a table default expression, not when referenced directly in INSERT statements.
- D
The issue occurs because the sequence was not created with ORDER; recreating it with an ORDER property will guarantee values match commit order across sessions.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests a core administrative and architectural point about Snowflake sequences: they are appropriate for generating unique surrogate keys, but they have important limitations. Specifically, Snowflake sequences do not guarantee gap-free output, and under concurrent processing their values should not be interpreted as proof of row insertion order, transaction order, or commit order. This makes them suitable for technical identifiers but unsuitable when the business requires contiguous invoice numbers or strict sequencing semantics.
A best-practice recommendation is to use sequences for uniqueness only, and to store separate business timestamps or ordering attributes when true event sequencing is required. Snowflake documentation on sequences emphasizes uniqueness and notes that generated values may not be contiguous. Administrators should guide teams away from relying on sequence values for auditing order or legal numbering requirements.
- A. Correct.
Correct. In Snowflake, sequences are designed to generate unique values, but they are not intended to provide gap-free numbering. Gaps can occur for several reasons, including rolled-back transactions, parallel consumption, and internal allocation behavior. In concurrent workloads, sequence values also should not be used to infer exact transaction or commit order. This is the key limitation administrators and developers must understand when designing surrogate keys.
- B. Incorrect.
Incorrect. Gaps in sequence-generated values are normal and do not indicate corruption. Recreating the sequence does not change the fundamental behavior of Snowflake sequences. A common misconception is that gaps mean failed sequence state, but in practice uniqueness is the guarantee, not continuous numbering or commit-order alignment.
- C. Incorrect.
Incorrect. Snowflake does allow sequences to be used directly in SQL statements and in default expressions, but using a default does not change sequence semantics. Whether referenced through NEXTVAL in an INSERT or via a column default, the sequence still does not guarantee gap-free values.
- D. Incorrect.
Incorrect. Snowflake sequences do not support an ORDER property that guarantees commit-order sequencing across sessions in the way implied here. This distractor reflects confusion with sequence behavior in some other database platforms. In Snowflake, the correct design approach is to treat sequences as unique-number generators, not as a source of serialized business ordering.