Snowflake
Snowflake Sequence Generator
Snowflake sequences are database objects, not ETL transformations. They are useful when the warehouse should own generated IDs.
When to Use It
- Use `CREATE SEQUENCE` when key generation belongs in the database layer.
- Call `NEXTVAL` in inserts, merges, or default expressions when new rows need unique numeric IDs.
- Prefer one sequence per key domain so ID ranges stay easy to reason about.
Important Syntax
| Syntax | Purpose | Example |
|---|---|---|
| CREATE SEQUENCE | Define the sequence object. | CREATE SEQUENCE customer_seq START = 1 INCREMENT = 1; |
| NEXTVAL | Advance and return the next value. | customer_seq.NEXTVAL |
| ALTER SEQUENCE | Adjust behavior later. | ALTER SEQUENCE customer_seq SET INCREMENT = 10; |
Warehouse-Owned Key Flow
- 1Create the sequence object in the schema that owns the target table.
- 2Reference `sequence_name.NEXTVAL` inside the insert or merge statement.
- 3Validate that downstream ETL does not also generate a competing key.
- 4Document the sequence owner so loads and DDL stay aligned.
Code
Create and use a sequence
CREATE OR REPLACE SEQUENCE customer_seq START = 1 INCREMENT = 1;
INSERT INTO dim_customer (customer_key, customer_name)
SELECT customer_seq.NEXTVAL, customer_name
FROM stg_customer;FAQ
Why are there gaps in sequence values?
Gaps are normal. Uniqueness matters more than perfectly contiguous numbering.
Should ETL and Snowflake both generate keys?
No. Pick one owner for the surrogate key lifecycle or the model will drift.
Related