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)
  └─ 列指向マイクロパーティション
       └─ 大規模スキャン・集計に最適化
       └─ 単一行ルックアップは非効率

Hybrid Table
  ├─ 行指向ストレージ（主キーインデックス付き）
  │    └─ 単一行 SELECT / UPDATE / DELETE がミリ秒で完了
  │    └─ 参照整合性制約（FOREIGN KEY）をサポート
  │
  └─ 列指向ストレージ（バックグラウンド同期）
       └─ SELECT * / 集計クエリはこちらから読み取り
       └─ Snowflakeが自動的に行→列の同期を実行

CREATE HYBRID TABLE Syntax

-- 基本的なHybrid Table（主キー必須）
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
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)
);

-- セカンダリインデックスの追加
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 INTO app_db.public.users (user_id, email, display_name)
VALUES (1001, '[email protected]', 'Alice');

-- 主キーによるポイント更新（ミリ秒レベル）
UPDATE app_db.public.users
SET last_login = CURRENT_TIMESTAMP(), status = 'active'
WHERE user_id = 1001;

-- 主キーによる単一行削除
DELETE FROM app_db.public.users WHERE user_id = 1001;

-- MERGE（upsert）: アプリケーション側の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);

-- 分析クエリ（OLAP）も同一テーブルで実行可能
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制約（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, DEFAULT 制約もサポート
-- ただしCHECK制約は未サポート

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