Snowflake Hybrid Tables: Designing Unified OLTP+OLAP Workloads

Hybrid Tables are the centerpiece of Snowflake's Unistore architecture, a table type that delivers low-latency row-level operations (OLTP) and large-scale analytical queries (OLAP) on the same table. A PRIMARY KEY is mandatory, and the combination of row-oriented storage and indexes lets single-row lookups and point updates complete in milliseconds.

Hybrid Tables Architecture

Standard Table
  └─ columnar micro-partitions
       └─ optimised for large scans and aggregation
       └─ inefficient for single-row lookups

Hybrid Table
  ├─ row-oriented storage (with a primary key index)
  │    └─ single-row SELECT / UPDATE / DELETE completes in milliseconds
  │    └─ supports referential integrity (FOREIGN KEY)
  │
  └─ columnar storage (synced in the background)
       └─ SELECT * and aggregate queries read from here
       └─ Snowflake syncs row -> column automatically

CREATE HYBRID TABLE Syntax

-- A basic Hybrid Table (a primary key is required)
CREATE OR REPLACE HYBRID TABLE app_db.public.users (
  user_id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(100),
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  last_login TIMESTAMP_NTZ,
  metadata VARIANT
);

-- Hybrid Table with a composite primary key
CREATE OR REPLACE HYBRID TABLE app_db.public.user_sessions (
  user_id INT,
  session_id VARCHAR(64),
  started_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  expires_at TIMESTAMP_NTZ,
  ip_address VARCHAR(45),
  user_agent VARCHAR(500),
  PRIMARY KEY (user_id, session_id),
  FOREIGN KEY (user_id) REFERENCES app_db.public.users(user_id)
);

-- Add a secondary index
CREATE INDEX idx_users_email ON app_db.public.users (email);
CREATE INDEX idx_sessions_expires ON app_db.public.user_sessions (expires_at);

Primary Key Design Guidelines

DML Operation Patterns

-- Insert a single row (low latency)
INSERT INTO app_db.public.users (user_id, email, display_name)
VALUES (1001, '[email protected]', 'Alice');

-- Point update by primary key (millisecond latency)
UPDATE app_db.public.users
SET last_login = CURRENT_TIMESTAMP(), status = 'active'
WHERE user_id = 1001;

-- Delete a single row by primary key
DELETE FROM app_db.public.users WHERE user_id = 1001;

-- MERGE (upsert): the application guarantees idempotency
MERGE INTO app_db.public.users t
USING (SELECT 1001 AS user_id, '[email protected]' AS email, 'Alice' AS name) s
ON t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET t.last_login = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (user_id, email, display_name) VALUES (s.user_id, s.email, s.name);

-- Analytical (OLAP) queries can run against the same table
SELECT
  DATE_TRUNC('DAY', created_at) AS signup_date,
  COUNT(*) AS new_users,
  COUNT_IF(status = 'active') AS active_users
FROM app_db.public.users
GROUP BY signup_date
ORDER BY signup_date DESC;

Concurrency Control and Locking

Hybrid Tables include a row-level locking mechanism. Standard Snowflake tables handle concurrency via snapshot isolation (MVCC) at the micro-partition level, but Hybrid Tables take pessimistic locks at the row level and serialize concurrent updates to the same row.

• Concurrent DML on different rows runs in parallel
• Concurrent UPDATEs on the same row will wait for the lock
• Deadlock detection automatically aborts one of the transactions involved
• Read queries (SELECT) do not take locks and therefore do not block writes

Referential Integrity Constraints

-- FOREIGN KEY constraints (enforced only between Hybrid Tables)
CREATE OR REPLACE HYBRID TABLE app_db.public.orders (
  order_id INT PRIMARY KEY AUTOINCREMENT,
  user_id INT NOT NULL,
  total_amount NUMBER(12,2) NOT NULL,
  order_status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  FOREIGN KEY (user_id) REFERENCES app_db.public.users(user_id)
);

-- NOT NULL, UNIQUE and DEFAULT constraints are supported too
-- CHECK constraints, however, are not supported

Standard Tables vs. Hybrid Tables

Limitations and Caveats

• A PRIMARY KEY definition is mandatory; you cannot create a Hybrid Table without one
• Clustering Keys cannot be set
• Materialized Views cannot be created
• FOREIGN KEYs are only valid between Hybrid Tables (not between Hybrid and standard tables)
• Time Travel and Fail-safe behavior may differ from standard tables
• Better suited to small, high-frequency DML than large batch INSERTs of millions of rows

Best Practices

• Only use Hybrid Tables for OLTP-style tables: Limit to tables that are dominated by point operations, such as user masters, session management, and inventory counters.
• Keep using standard tables for large-scale analytics: For fact tables with billions of rows, standard tables plus Clustering Keys are the right fit.
• Add secondary indexes carefully: More indexes mean slower DML, so limit them to the columns that are queried frequently.
• Enforce data integrity with FOREIGN KEYs: Enforce integrity at the database layer in addition to the application layer to keep inconsistent data out.

Check Your Understanding

Frequently Asked Questions