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

  1. Application writes or reads one row by key
  2. Request hits the hybrid table's row store
  3. 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.
  4. Enforced constraints reject duplicate keys or broken foreign keys
  5. Data is copied asynchronously to columnar storage
  6. 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 lookup

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic