Postgres changes into Snowflake, as they happen.
Every committed change appended to a table through Snowpipe Streaming's REST API — seconds of latency, no stage or file batching, no connector cluster to run.
The Snowflake sink appends each batch of changes as rows to a Snowpipe Streaming channel on a pipe you own. Each row is the change JSON envelope, so the pipe's COPY maps op, table, data, old, seq and idempotency_key onto columns, or lands the whole thing in a VARIANT. Authentication is key-pair JWT; Waltail exchanges it for a token scoped to ingest only.
Set up the target once in Snowflake — a table, a streaming pipe into it, and a user whose public key Waltail signs with:
create table cdc.changes (
op varchar, tbl varchar, data variant, old variant,
seq number, idempotency_key varchar, commit_lsn varchar
);
create pipe cdc.waltail_pipe as
copy into cdc.changes (op, tbl, data, old, seq, idempotency_key, commit_lsn)
from (select $1:op, $1:table, $1:data, $1:old, $1:seq, $1:idempotency_key, $1:commit_lsn
from table(data_source(type => 'STREAMING')));
-- openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
-- openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
alter user waltail_loader set rsa_public_key = '<contents of rsa_key.pub without the BEGIN/END lines>';
grant usage on database analytics to role waltail_role;
grant usage on schema cdc to role waltail_role;
grant operate on pipe cdc.waltail_pipe to role waltail_role;Paste the private key (rsa_key.p8, unencrypted) into the console as the sink's key.
How delivery works
| Aspect | Behaviour for Snowflake |
|---|---|
| Rows | One channel per sink on the pipe, named WALTAIL_<sink id>. Each batch is one POST …/rows of newline-delimited change JSON; the offset token is the batch's last seq. |
| Batching | Up to the sink's batch size per request (default 100; Snowflake accepts up to 16 MB). |
| Ordering | Changes to the same row never overlap; rows in a request are in commit order. Order across rows is by seq once landed. |
| Idempotency | Delivery is at-least-once; dedupe on idempotency_key (e.g. QUALIFY ROW_NUMBER() OVER (PARTITION BY idempotency_key ORDER BY seq) = 1, or a scheduled MERGE). |
| On failure | The channel is dropped and reopened on the next attempt, so a stale continuation token never wedges the sink. Any 2xx is accepted. A 429 pauses the sink for the Retry-After period (30 s if absent) without spending a retry or metering egress. A failed request is retried up to 5 times with exponential backoff (30 s, 1, 2, 4 min, with jitter). After the fifth failure the message is parked as undelivered, the console shows it with the last error, and one click replays it — later changes to the same row wait behind it so order is preserved. |
| Connection check | Authenticates with the key, opens a throwaway channel WALTAIL_CHECK on the pipe, and drops it — key, user and pipe in one go. |
Set up in three steps
-
Connect your database
Paste a connection string and press Test. Waltail checks the version (12+), that
wal_levelislogical, that the user may replicate, and that a slot is free — and shows the fix for anything that fails. Then it creates its own publication and replication slot. Connection guide → -
Add Snowflake as a destination
Pick the tables to stream, choose Snowflake, and fill in Account identifier, User, Private key, Database, Schema, Pipe. Press Test — Waltail checks it can reach and write to Snowflake before anything is saved.
-
Change a row
Insert or update a row. The console shows the first event as it is captured and delivered; from then on, every committed change follows within about a second. Have rows that already exist? Run a backfill — confirm the estimate and they arrive through Snowflake as
readevents.
Configuration
Fields as the console asks for them. Secrets are encrypted at rest and never shown again.
| Field | Console label | Required | Notes |
|---|---|---|---|
account | Account identifier | Yes | Org-account form, e.g. myorg-myaccount — the part before .snowflakecomputing.com. |
user | User | Yes | The user the public key is assigned to. |
private_key | Private key | Yes | Unencrypted PKCS#8 or PKCS#1 PEM (rsa_key.p8). Stored encrypted. |
database | Database | Yes | |
schema | Schema | Yes | |
pipe | Pipe | Yes | A Snowpipe Streaming pipe (DATA_SOURCE(TYPE => 'STREAMING')). |
Every sink also has two tuning settings: batch size (messages per request, default 100) and rate limit (messages per second, default unlimited). Changes to one row are never in flight twice at once.
Example
Every change is this JSON envelope — data is the row after the change, old the row before it when replica identity provides it, null otherwise:
{
"op": "update",
"table": "public.orders",
"data": { "id": 42, "status": "shipped", "total": 19.99 },
"old": { "id": 42, "status": "paid", "total": 19.99 },
"seq": 137,
"idempotency_key": "9f1c3d2e-6b4a-4c7e-9d0f-2a1b3c4d5e6f",
"commit_lsn": "0/1A2B3C8"
}Questions
Is this classic Snowpipe or Snowpipe Streaming?
Snowpipe Streaming (the high-performance REST API): rows are appended directly, no stage or files. If you would rather land files, point the S3 sink at a bucket with an auto-ingest pipe on it.
Can it write straight into my production table with merges?
Not yet. Rows land in an append-only change table; a scheduled task or dynamic table keeps the current-state table up to date from it, which is the pattern Snowflake recommends.
Which privileges does the user need?
USAGE on the database and schema, and OPERATE on the pipe, through the user's default role.
Does the key have to be unencrypted?
Yes — Waltail signs with it directly. Generate it with -nocrypt or decrypt a copy before pasting.