Hybrid Tables & Unistore: Transactional Workloads on Snowflake

Snowflake has long been the go-to for cloud data warehousing and analytics, but its architecture was originally optimized for large-scale analytical queries, not for high-frequency transactional workloads. Enter Hybrid Tables and the Unistore architecture. Hybrid Tables allow you to perform low-latency point lookups, inserts, updates, and deletes—typical of OLTP systems—while still benefiting from Snowflake's columnar storage for analytics. This means you can unify your operational and analytical data in a single platform, eliminating the need for separate databases and complex ETL pipelines. In this tutorial, you'll learn what Hybrid Tables are, how they work under the hood, and how to use them effectively in production. We'll cover creation, indexing, transactions, and common pitfalls. By the end, you'll be able to design a real-time order processing system that also powers dashboards and reports, all within Snowflake.

What Are Hybrid Tables?

Hybrid Tables are a new table type in Snowflake that combine row-oriented storage for low-latency transactional operations with columnar storage for analytical queries. They are designed for workloads that require both fast point lookups (e.g., get order by ID) and efficient aggregations (e.g., total sales by day). Unlike standard Snowflake tables, which are purely columnar and optimized for large scans, Hybrid Tables use a hybrid storage engine that stores the most recent data in a row format (the 'hot' store) and older data in columnar format (the 'cold' store). This allows you to perform high-frequency inserts, updates, and deletes with sub-second latency, while still running complex analytical queries on the same data. Hybrid Tables support ACID transactions, indexes (primary key, unique, and secondary), and foreign keys. They are ideal for real-time data ingestion, event streaming, and operational reporting.

-- Create a Hybrid Table for orders
CREATE OR REPLACE HYBRID TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING',
    total_amount NUMBER(10,2),
    INDEX idx_customer (customer_id)
);

-- Insert sample data
INSERT INTO orders VALUES (1, 101, '2025-03-01', 'SHIPPED', 250.00);
INSERT INTO orders VALUES (2, 102, '2025-03-02', 'PENDING', 150.00);

-- Point lookup (fast)
SELECT * FROM orders WHERE order_id = 1;

-- Analytical query (also fast)
SELECT status, COUNT(*) FROM orders GROUP BY status;

Creating and Managing Hybrid Tables

Creating a Hybrid Table is similar to creating a standard table, but you use the HYBRID keyword. You can define primary keys, unique keys, foreign keys, and secondary indexes. Indexes are crucial for performance on point lookups and joins. Snowflake automatically maintains these indexes. You can also set clustering keys, but Hybrid Tables have their own automatic clustering based on the primary key. To alter a Hybrid Table, you can add or drop indexes, but some operations (like changing column types) may require recreating the table. Hybrid Tables support standard DML (INSERT, UPDATE, DELETE, MERGE) with full ACID guarantees. They also support time travel and cloning, similar to standard tables.

-- Add a secondary index
CREATE INDEX idx_status ON orders (status);

-- Drop an index
DROP INDEX idx_customer;

-- Add a foreign key (requires a primary key on the referenced table)
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Use MERGE for upsert
MERGE INTO orders AS target
USING (SELECT 1 AS order_id, 'CANCELLED' AS status) AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET status = source.status
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_date, status) VALUES (1, 101, '2025-03-01', 'CANCELLED');

-- Show indexes
SHOW INDEXES IN TABLE orders;

Transactional Behavior and Concurrency

Hybrid Tables support ACID transactions with snapshot isolation. This means each transaction sees a consistent snapshot of the data as of the start of the transaction. Concurrent transactions do not block each other on reads, but writes to the same row may cause conflicts. Snowflake uses optimistic concurrency control: if two transactions try to update the same row, one will succeed and the other will fail with a 'conflict' error. You can retry the failed transaction. For high-concurrency workloads, design your application to handle retries. Hybrid Tables also support locking at the row level for DML operations. You can use BEGIN TRANSACTION and COMMIT to group multiple operations. Note that Hybrid Tables do not support serializable isolation; they use read committed snapshot isolation.

-- Start a transaction
BEGIN;

-- Deduct inventory
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100 AND quantity > 0;

-- Insert order
INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (3, 103, CURRENT_DATE, 'CONFIRMED', 99.99);

-- Commit
COMMIT;

-- If conflict occurs, catch and retry
-- Example in application code (pseudo)
-- try:
--     execute('COMMIT')
-- except SnowflakeError as e:
--     if 'conflict' in str(e):
--         execute('ROLLBACK')
--         retry()

Performance Optimization: Indexing and Query Tuning

To get the best performance from Hybrid Tables, you need to design your indexes and queries carefully. For point lookups, always filter on indexed columns (preferably the primary key). For range scans, consider using a secondary index on the range column. Use EXPLAIN to verify that the query uses an index (look for 'index scan' in the plan). Avoid full table scans on Hybrid Tables for large tables; they are slower than on standard columnar tables. For analytical queries that scan many rows, Snowflake may fall back to the columnar store, which is still efficient. You can also use clustering keys to co-locate related data, but Hybrid Tables automatically cluster by primary key. If you frequently query by a non-primary column, consider creating a secondary index or a materialized view.

-- Use EXPLAIN to check index usage
EXPLAIN SELECT * FROM orders WHERE order_id = 1;
-- Look for 'Index Scan' in the output

-- Create a secondary index for range queries
CREATE INDEX idx_order_date ON orders (order_date);

-- Query that benefits from index
SELECT * FROM orders WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31';

-- For analytical queries, Snowflake may use columnar store
SELECT customer_id, SUM(total_amount) FROM orders GROUP BY customer_id;

Unistore Architecture: How It Works Under the Hood

Unistore is the architectural foundation that enables Hybrid Tables. It unifies transactional and analytical processing into a single engine. In Unistore, data is stored in two tiers: a row store for recent or frequently accessed data, and a column store for historical or bulk data. The row store is optimized for point operations (inserts, updates, deletes, point queries) using a B-tree-like structure. The column store is optimized for scans and aggregations. Snowflake automatically manages the movement of data between tiers based on age and access patterns. You can configure the retention period for the row store using the DATA_RETENTION_TIME parameter. Unistore also provides a unified query engine that can seamlessly query both tiers, so you don't need to worry about where data resides. This architecture allows Snowflake to handle mixed workloads without sacrificing performance.

-- Check row store retention (default 14 days)
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME' IN TABLE orders;

-- Set row store retention to 7 days
ALTER TABLE orders SET DATA_RETENTION_TIME = 7;

-- View table storage details
SELECT * FROM INFORMATION_SCHEMA.TABLE_STORAGE WHERE TABLE_NAME = 'ORDERS';

Real-World Use Case: Real-Time Order Processing

Consider an e-commerce platform that needs to process orders in real time and also generate sales reports. With Hybrid Tables, you can insert orders as they come in, update inventory, and query the latest order status—all with low latency. At the same time, you can run analytical queries to compute daily revenue, top products, etc., without needing a separate analytics database. Here's a simplified implementation: create a Hybrid Table for orders and another for inventory. Use transactions to ensure consistency. For reporting, you can create a materialized view or simply run aggregations on the Hybrid Table. Because Hybrid Tables support both workloads, you eliminate the need for data replication and ETL.

-- Create inventory table
CREATE OR REPLACE HYBRID TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample inventory
INSERT INTO inventory VALUES (100, 50, CURRENT_TIMESTAMP);

-- Process an order (in application)
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100 AND quantity > 0;
IF (SELECT quantity FROM inventory WHERE product_id = 100) >= 0 THEN
    INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
    VALUES (4, 104, CURRENT_DATE, 'CONFIRMED', 29.99);
    COMMIT;
ELSE
    ROLLBACK;
    RAISE 'Insufficient inventory';
END IF;

-- Real-time dashboard query
SELECT COUNT(*), SUM(total_amount) FROM orders WHERE order_date = CURRENT_DATE;

Limitations and Best Practices

Hybrid Tables have some limitations. The row store is limited to 10 GB per table (as of writing). If you need more, consider partitioning or using multiple tables. Hybrid Tables do not support all Snowflake features, such as search optimization, materialized views (though you can use standard views), or external tables. They also have a maximum of 10 indexes per table. For best practices: use primary keys for unique identification, limit secondary indexes to essential columns, keep transactions short, monitor storage with INFORMATION_SCHEMA, and test concurrency under load. Also, be aware that Hybrid Tables incur additional costs for the row store and indexes. Evaluate your workload to ensure the benefits outweigh the costs.

-- Monitor row store usage
SELECT TABLE_NAME, ROW_STORE_BYTES / 1024 / 1024 AS ROW_STORE_MB
FROM INFORMATION_SCHEMA.TABLE_STORAGE
WHERE TABLE_CATALOG = 'MY_DB' AND TABLE_SCHEMA = 'PUBLIC';

-- Check index count
SELECT COUNT(*) AS index_count FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'ORDERS';

-- Drop unused index
DROP INDEX IF EXISTS idx_unused;