Hybrid Tables (Unistore)
Snowflake · Tables & Data Handling
Theory
A hybrid table is a Snowflake table built for fast single-row reads and writes, the kind an application makes. Its primary store is a row store, and Snowflake copies the data asynchronously to columnar object storage for large scans, so the same table serves both point lookups and analytics. Unlike standard tables, a hybrid table must have a PRIMARY KEY, and PRIMARY KEY, UNIQUE, FOREIGN KEY and NOT NULL constraints are enforced. Secondary indexes, declared with INDEX, are updated as rows change, and locking is row-level for high concurrency.
For example, an order-status table where an app updates one order at a time and looks it up by order_id is a good fit, and you can still join it to standard tables in the same query. Snowflake calls this transactional plus analytical pattern Unistore.
Why we use it
Standard tables are tuned for big scans and batch loads, not thousands of small updates per second. Hybrid tables let lightweight applications and serving workloads run inside Snowflake without a separate operational database and sync pipeline.
When to use it
Use hybrid tables for app state, workflow metadata and low-latency lookups by key that need enforced keys and many concurrent small writes. Keep standard tables for analytics and bulk loading. Hybrid tables are not available on trial accounts or in every region.
How it works
- Application writes or reads one row by key
- Request hits the hybrid table's row store
- Does the query filter on the primary key or an index? Yes: Fast index lookup, row-level lock. No: Falls back to a larger scan.
- Enforced constraints reject duplicate keys or broken foreign keys
- Data is copied asynchronously to columnar storage
- Analytical queries join it with standard tables
Example
CREATE HYBRID TABLE order_status (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
status VARCHAR(20),
updated_at TIMESTAMP_NTZ,
INDEX idx_customer (customer_id)
);
INSERT INTO order_status VALUES (101, 7, 'PLACED', CURRENT_TIMESTAMP());
UPDATE order_status SET status = 'SHIPPED' WHERE order_id = 101;
SELECT * FROM order_status WHERE order_id = 101; -- index lookupKey points
- CREATE HYBRID TABLE needs a PRIMARY KEY
- PRIMARY KEY, UNIQUE, FOREIGN KEY and NOT NULL are enforced
- Secondary indexes are declared with INDEX and kept in sync on writes
- Cannot be temporary or transient
- Usually a larger storage footprint than a standard table
- Not available on trial accounts; check region availability