Sequences & Auto-Increment
In data warehousing, you often need to generate unique numerical identifiers for rows. These are commonly known as Surrogate Keys. Snowflake provides two primary mechanisms to generate these keys: AUTOINCREMENT columns and standalone SEQUENCE objects.
Because Snowflake is a distributed, parallel processing engine, generating sequential numbers works differently than in a traditional single-node database like MySQL or PostgreSQL.
Topics Covered
AUTOINCREMENT/IDENTITYColumns- Standalone
SEQUENCEObjects - Gaps in Sequences
- Sequence vs UUIDs
1. AUTOINCREMENT / IDENTITY Columns
The easiest way to generate a surrogate key is to define an AUTOINCREMENT (or IDENTITY, which is synonymous) column when creating a table.
Let's assume we are building a dimension table for a retail store chain in India.
1. Database Setup
(Run this in your Snowflake worksheet)
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_KEYS_DB;
USE SCHEMA BEEMS_KEYS_DB.PUBLIC;
-- Create a table with an Auto-Increment column starting at 100, incrementing by 1
CREATE OR REPLACE TABLE STORE_DIM (
STORE_SK NUMBER AUTOINCREMENT START 100 INCREMENT 1,
STORE_NAME STRING,
CITY STRING
);
-- Insert data
INSERT INTO STORE_DIM (STORE_NAME, CITY) VALUES
('Metro Store', 'Mumbai'),
('Smart Bazaar', 'Delhi'),
('Reliance Fresh', 'Chennai');
SELECT * FROM STORE_DIM;
Note that you do not specify the STORE_SK in the INSERT statement; Snowflake generates it automatically.
2. Standalone SEQUENCE Objects
Sometimes, you need multiple tables to share the same sequence of numbers. For example, if you have two separate staging tables loading data into a master dimension, they cannot both use their own AUTOINCREMENT column without generating conflicting IDs.
To solve this, you create an independent SEQUENCE object.
(To verify your active role before creating objects, run SELECT CURRENT_ROLE();)
-- 1. Create a standalone Sequence
CREATE OR REPLACE SEQUENCE CUST_SEQ START = 1 INCREMENT = 1;
-- 2. Create the target table (without autoincrement)
CREATE OR REPLACE TABLE CUSTOMER_MASTER (
CUST_SK NUMBER,
NAME STRING
);
-- 3. Use the sequence in INSERT statements using NEXTVAL
INSERT INTO CUSTOMER_MASTER (CUST_SK, NAME) VALUES
(CUST_SEQ.NEXTVAL, 'Arjun'),
(CUST_SEQ.NEXTVAL, 'Priya');
-- 4. Another table can use the exact same sequence
CREATE OR REPLACE TABLE ARCHIVED_CUSTOMERS (
CUST_SK NUMBER,
NAME STRING
);
INSERT INTO ARCHIVED_CUSTOMERS (CUST_SK, NAME) VALUES
(CUST_SEQ.NEXTVAL, 'Rohan'); -- This will get ID 3
The "Gap" Problem
In traditional databases, if you insert 5 rows, you expect IDs 1, 2, 3, 4, 5.
In Snowflake, sequences are not gap-free. If you insert rows using a sequence, you might get 1, 2, 3, and the next insert might give you 1024, 1025.
Why does this happen? Snowflake executes inserts using multiple compute nodes in parallel. To prevent these nodes from locking each other while waiting for the next number, Snowflake pre-allocates large blocks of numbers to each node. If a node shuts down or doesn't use all its allocated numbers, those numbers are lost, creating a gap.
Do not use Snowflake Sequences or Auto-Increment if the business requires strictly sequential, gap-free numbers (like Invoice Numbers).
Generating Gap-Free Numbers
If you absolutely must generate gap-free invoice numbers, you cannot use a sequence. You must calculate the MAX() value of the table during the insert. This is incredibly slow and forces Snowflake to process the insert sequentially, destroying performance.
-- Anti-Pattern: Do not do this unless strictly required by the business
INSERT INTO INVOICES (INVOICE_ID, AMOUNT)
SELECT
COALESCE((SELECT MAX(INVOICE_ID) FROM INVOICES), 0) + ROW_NUMBER() OVER(ORDER BY AMOUNT),
AMOUNT
FROM STAGED_INVOICES;
Sequence vs UUID
Because of the gap issue and the slight performance overhead of coordinating sequences across nodes, modern Data Engineering often abandons numeric sequences entirely in favor of UUIDs (Universally Unique Identifiers) for surrogate keys.
Snowflake provides a native function UUID_STRING() to generate these.
CREATE OR REPLACE TABLE PRODUCT_DIM (
PRODUCT_SK STRING DEFAULT UUID_STRING(),
PRODUCT_NAME STRING
);
INSERT INTO PRODUCT_DIM (PRODUCT_NAME) VALUES ('Laptop'), ('Smartphone');
SELECT * FROM PRODUCT_DIM;
UUIDs are 36-character strings. They are generated completely independently without any cross-node coordination, making them significantly faster than Sequences for massive data loads.
Summary
AUTOINCREMENT/IDENTITYis the easiest way to generate numeric keys for a single table.- A
SEQUENCEobject can be shared across multiple tables. - Snowflake sequences are NOT gap-free. They are optimized for parallel performance, not strict counting.
- If you don't need numeric keys, use
UUID_STRING()for maximum performance and infinite scalability.