ADA-C01 Question 270
Single answerUse Snowflake sequences and identify limitationsA data engineering team loads customer records into a Snowflake table from multiple concurrent ingestion pipelines. They want to assign a surrogate key during INSERT using a Snowflake sequence. After several test runs, the team notices that generated IDs are unique but contain gaps, and some values appear to be skipped when transactions are retried or rolled back. The team asks the administrator to explain the behavior and recommend the correct design assumption. Which statement best addresses this scenario?
- A
Snowflake sequences guarantee gap-free, strictly contiguous values as long as the sequence uses an increment of 1.
- B
Snowflake sequences guarantee uniqueness as designed, but they do not guarantee gap-free or continuously ordered values across concurrent operations and rollbacks.
- C
Snowflake sequences can be configured with an ORDER property to enforce gap-free numbering during concurrent inserts.
- D
Snowflake sequences should be replaced with ROW_NUMBER() in the INSERT statement because ROW_NUMBER() guarantees persistent, transaction-safe surrogate keys.
Show answer and explanation
Correct answer: B
Explanation
For SnowPro Advanced: Administrator, the critical point is that Snowflake sequences are designed to provide unique values, not gap-free, consecutive numbering. In real-world ETL and ELT pipelines, especially with concurrent inserts, retries, and rollbacks, gaps are expected and acceptable. Administrators should guide teams to use sequences for surrogate keys only when uniqueness matters and the application does not depend on continuity or business meaning in the generated numbers. This aligns with Snowflake documentation and best practices: sequence values can be consumed without being committed, and you should not rely on them for strictly ordered or contiguous numbering. If a business process truly requires gap-free invoice numbers or regulated document numbering, that requirement typically must be handled outside normal sequence semantics with a carefully controlled serialization process.
- A. Incorrect.
Incorrect. This reflects a common misconception. In Snowflake, sequences are intended to generate unique numeric values, but they are not designed to produce gap-free values. Gaps can occur due to parallelism, preallocation, statement retries, or rolled-back transactions. Using an increment of 1 does not change this limitation.
- B. Correct.
Correct. This is the key operational characteristic administrators must understand. Snowflake sequences are appropriate for surrogate key generation when uniqueness is required, but applications must not assume contiguous numbering. In highly concurrent environments, values may be consumed out of order or skipped, and rolled-back transactions do not 'return' sequence values.
- C. Incorrect.
Incorrect. Snowflake does not provide a sequence setting that guarantees gap-free numbering for concurrent workloads. Candidates may confuse sequence behavior with ordered sequence features from other database platforms. Even if values are generated in a particular order for a given operation, gap-free guarantees are not part of Snowflake sequence semantics.
- D. Incorrect.
Incorrect. ROW_NUMBER() is a query result function, not a replacement for a durable sequence object used for ongoing surrogate key generation across multiple INSERT operations. ROW_NUMBER() can renumber rows differently depending on the query and dataset at execution time, so it is not suitable as a persistent surrogate key strategy for transactional loading.