COF-C03 Question 44
Single answerSequencesA data engineering team loads order records into a Snowflake table from several parallel ETL processes. Each new row needs a unique ORDER_ID generated inside Snowflake so that concurrent inserts do not produce duplicates. The team also wants to avoid relying on ETL-side logic for key generation. Which Snowflake approach best meets these requirements?
- A
Create a sequence and use sequence_name.NEXTVAL in the INSERT statements that populate the table
- B
Create a view over the table and use ROW_NUMBER() to generate ORDER_ID values during queries
- C
Use a TIMESTAMP column with CURRENT_TIMESTAMP as the ORDER_ID because timestamps are guaranteed to be unique across concurrent sessions
- D
Use sequence_name.CURRVAL in each INSERT so every session can reuse the most recently generated value safely
Show answer and explanation
Correct answer: A
Explanation
For parallel data loads that require Snowflake-managed unique identifiers, sequences are the appropriate feature. The typical pattern is to create a sequence and reference sequence_name.NEXTVAL during INSERT operations or as part of a column default. This avoids ETL-side coordination and supports concurrent writers. Candidates should also know an important practical nuance: Snowflake sequences are intended to produce unique values, but they do not guarantee gap-free or fully contiguous numbering, especially in distributed or concurrent environments. By contrast, ROW_NUMBER() is useful for analytic queries, not for durable key assignment, and CURRVAL does not create a new value. Snowflake documentation on CREATE SEQUENCE and sequence semantics emphasizes using NEXTVAL to generate sequence values and not expecting contiguous results.
- A. Correct.
Correct. A Snowflake sequence is designed to generate unique numeric values and is appropriate for surrogate keys such as ORDER_ID. Using sequence_name.NEXTVAL in INSERT statements lets Snowflake generate values centrally, which works well for concurrent loading processes. Sequences are not intended to guarantee gap-free numbering, but they do provide uniqueness for generated values when used properly.
- B. Incorrect.
Incorrect. ROW_NUMBER() is a query-time window function, not a persistent key-generation mechanism for inserted rows. It can renumber rows depending on query logic and ordering, so it is not suitable for assigning durable surrogate keys in a table. This is a common misconception because ROW_NUMBER() produces sequential-looking values, but those values are not stable identifiers for DML inserts.
- C. Incorrect.
Incorrect. CURRENT_TIMESTAMP can help record load time, but it should not be used as a surrogate key generator. Concurrent sessions can produce values that are very close in time, and timestamp-based identifiers are not the recommended Snowflake mechanism for guaranteed unique numeric keys. This distractor reflects a common but risky pattern of using timestamps as identifiers.
- D. Incorrect.
Incorrect. CURRVAL returns the current value of the sequence for the session after NEXTVAL has been called in that session; it does not generate a new unique value for each inserted row. Reusing CURRVAL would risk duplicate IDs within the session's inserts and does not satisfy the requirement for unique values across rows. The misconception here is confusing retrieval of the current session value with generation of a new sequence value.