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.
Can I use a Snowflake sequence as a primary key default?
Yes. Assign the sequence NEXTVAL as the default value for a column: `ALTER TABLE t ALTER COLUMN id SET DEFAULT seq.NEXTVAL`. This moves key generation into the database layer for all insert operations.
How do I reset a sequence after truncating the target table?
Use `ALTER SEQUENCE seq_name SET VALUE = 0` to reset to the initial value. The next NEXTVAL call returns the START value (or START + INCREMENT if SET VALUE is above it).
Related