Most Snowflake accounts still have a small transactional system bolted on somewhere: a Postgres instance holding feature flags, an app database behind a customer portal, a lookup service that a pipeline calls, or a "state table" that a scheduler hammers with single-row updates. Every one of those is a separate database to secure, replicate, back up and join across.
Snowflake now offers two ways to pull that work inside the platform: Hybrid Tables (Unistore) for row-oriented tables that live alongside your analytic tables, and Snowflake Postgres, a managed PostgreSQL service inside the Snowflake perimeter. They solve overlapping but different problems, and picking the wrong one is an expensive mistake.
This tutorial walks through both, with working SQL and a decision rule.
Why standard Snowflake tables hurt for OLTP
A normal Snowflake table is columnar and immutable at the micro-partition level. A single-row UPDATE rewrites a whole micro-partition; concurrent writers to the same table serialise and can deadlock; a primary-key point lookup still scans partition metadata. That design is why analytic scans are fast — and why 500 single-row upserts per second is the wrong workload for it.
Hybrid Tables change the storage engine underneath, not the SQL you write.
Hybrid Tables: row store inside Snowflake
A Hybrid Table stores data in a row-oriented store with a real primary-key index, gives you row-level locking, and enforces constraints. Snowflake also keeps a columnar copy in the background so analytic queries over the same table stay reasonable.
CREATE OR REPLACE HYBRID TABLE app.order_state (
order_id NUMBER NOT NULL PRIMARY KEY,
customer_id NUMBER NOT NULL,
status VARCHAR(20) NOT NULL,
updated_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
INDEX idx_customer (customer_id)
);
Three things are different from a standard table and all three matter:
PRIMARY KEYis required and enforced (standard Snowflake tables accept constraints but do not enforce them).- Secondary indexes exist, and point lookups use them.
UPDATE/DELETEtake row locks, so two sessions touching different rows do not block each other.
Point read and write patterns now behave the way an application expects:
-- point lookup: milliseconds, index seek
SELECT status FROM app.order_state WHERE order_id = 90210;
-- concurrent single-row upsert, no partition rewrite
MERGE INTO app.order_state t
USING (SELECT 90210 AS order_id, 'SHIPPED' AS status) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status, updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, status)
VALUES (s.order_id, 1, s.status);
The real payoff is the hybrid join: transactional state and warehouse history in one query, no ETL hop, one governance model.
SELECT s.order_id, s.status, h.lifetime_value
FROM app.order_state s -- hybrid (row store)
JOIN analytics.customer_360 h -- standard (columnar)
ON h.customer_id = s.customer_id
WHERE s.status = 'PENDING';
Multi-statement transactions work across both table types:
BEGIN;
UPDATE app.order_state SET status = 'CANCELLED' WHERE order_id = 90210;
INSERT INTO analytics.order_events VALUES (90210, 'CANCELLED', CURRENT_TIMESTAMP());
COMMIT;
Where Hybrid Tables stop
Be honest with your architects about the limits before you design around them:
- Storage is priced differently from standard tables and is generally more expensive per TB — hybrid tables are for hot, bounded state, not history.
- Big bulk loads are slower than
COPY INTOon a standard table; stage the data, thenINSERT ... SELECT, and expect it to be a different order of magnitude. - Feature coverage lags standard tables in places (large-object handling, some clone/replication semantics, external table style tricks). Check the current docs against your requirement rather than assuming parity.
- They do not turn Snowflake into a general-purpose application database. There is no connection-pooling story for thousands of app connections, and latency is measured in single-digit-to-tens of milliseconds, not microseconds.
Good fits: order/job state, entitlements and feature flags, small dimension tables that an app edits, deduplication and idempotency keys for pipelines, "current value" tables that dashboards read.
Bad fits: a high-write chat backend, a session store, anything needing sub-millisecond latency or Postgres extensions.
Snowflake Postgres: the other half of the answer
For workloads that really are applications, Snowflake now offers a managed PostgreSQL service (the product line that came out of the Crunchy Data acquisition). It is genuine Postgres — extensions, pgvector, connection pooling, the wire protocol your ORM already speaks — provisioned and governed inside your Snowflake account rather than in a separate cloud console.
The architectural argument is not "Postgres is faster than Hybrid Tables." It is:
- One identity and RBAC perimeter instead of two.
- Application data sitting next to warehouse data, so the analytics path is a short one instead of a Fivetran connector plus a landing schema.
- One vendor relationship, one support channel, one network policy set.
A typical pattern for a customer-facing data product:
- The application runs against Snowflake Postgres (users, tenants, sessions, saved views).
- Change data flows into Snowflake analytic tables for history and modelling.
- Hybrid Tables hold the state the pipelines need to read and write at low latency.
- Streamlit in Snowflake or a Native App serves the UI over both.
The decision rule
| Question | Answer |
|---|---|
| Do I need Postgres extensions, an ORM, or thousands of app connections? | Snowflake Postgres |
| Do I need low-latency row reads/writes joined to warehouse data in one SQL statement? | Hybrid Tables |
| Is this append-mostly history queried in bulk? | Standard Snowflake tables |
| Is this a queue or event stream? | Streams & Tasks / Snowpipe Streaming, not either of the above |
Put another way: Hybrid Tables are for transactional data that analytics needs. Snowflake Postgres is for applications that happen to live near analytics.
A migration checklist that keeps you out of trouble
- Measure the workload first. Reads per second, writes per second, p95 latency budget, row count, growth rate. Most "we need OLTP" workloads turn out to be under 50 writes per second and fit comfortably in a Hybrid Table.
- Bound the hot set. Keep hybrid tables small by archiving into standard tables on a schedule; a task that moves closed orders out nightly is usually enough.
- Test concurrency, not just correctness. Run your real write pattern with real parallelism; row locking removes most contention but hot-key patterns still serialise.
- Re-check the cost model. Hybrid table storage plus the warehouse serving point lookups is a different cost shape than a
t3.largePostgres box. Model it before committing. - Keep governance in one place. Masking policies, tags and row access policies apply to hybrid tables too — reuse the Horizon policies you already have instead of writing app-side filtering.
- Have a rollback. Dual-write for a sprint, compare, then cut over.
Where teams get this wrong
The two failure modes we see most often: dropping an entire operational database into Hybrid Tables because "it's all Snowflake now" and being surprised by the storage bill, and the opposite — standing up yet another external Postgres for a 200-row state table that a Hybrid Table would have handled with zero new infrastructure. Both are avoidable with an hour of workload measurement.
PowderInsights architects and builds Snowflake platforms end to end, including Unistore/Hybrid Table designs, operational-workload consolidation and the pipelines that connect them. Get in touch with the workload you are trying to move and we will tell you which of the three storage options it belongs in — and roughly what it will cost.