# Postgres to Snowflake CDC: Setup Notes and the Errors You Will Hit

Most Postgres to Snowflake guides stop at "it works". This one is the setup in the order it has to happen, plus every error the pipeline threw on the way, with the exact message and the fix.

The pipeline: Postgres logical replication, read by [OLake](https://github.com/datazip-inc/olake) (open source, Apache 2.0), written as Apache Iceberg tables into Snowflake's own catalog. Snowflake sees them as Snowflake-managed tables, so there is no stage, no `COPY INTO` and no Kafka.

The run behind this post: Postgres 16, OLake UI with the Postgres source on v1.1.0, an X-Small Snowflake warehouse. It used 8 customers and 40 orders, then 12 changes.

## 1. Postgres: four settings, one restart

```sql
ALTER SYSTEM SET wal_level = 'logical';      -- needs a restart
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET wal_sender_timeout = 0;     -- so a long first snapshot is not cut off

CREATE ROLE olake WITH LOGIN REPLICATION PASSWORD '<password>';
GRANT USAGE ON SCHEMA public TO olake;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO olake;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO olake;

CREATE PUBLICATION olake_pub FOR TABLE customers, orders;
SELECT pg_create_logical_replication_slot('olake_slot', 'pgoutput');
```

Check before moving on:

```sql
SHOW wal_level;                                         -- logical
SELECT slot_name, plugin, active FROM pg_replication_slots;
SELECT pubname, puballtables FROM pg_publication;
```

On RDS, Aurora, Azure or Cloud SQL, `wal_level` is a parameter-group or server setting, not `ALTER SYSTEM`.

### Error: the replication connection is refused

```text
FATAL: must be superuser or replication role to start walsender
```

The user has `SELECT` but not the `REPLICATION` attribute. Run `ALTER ROLE olake WITH REPLICATION;`. On RDS and Aurora, `REPLICATION` is reserved, so grant the `rds_replication` role instead.

### Not an error yet, but it will be

- **Tables without a primary key** replicate inserts only. Give them `ALTER TABLE <table> REPLICA IDENTITY FULL;` so updates and deletes carry the old row.
- **One slot and one publication per job.** Two jobs sharing a slot read each other's changes, and both lose data.
- **The slot holds WAL until it is read.** Pause a job for a week and Postgres keeps a week of WAL. Set `max_slot_wal_keep_size` and alert on `pg_replication_slots.wal_status`.

## 2. Snowflake: database, role, service user, network policy, token

Run once as `ACCOUNTADMIN`:

```sql
CREATE WAREHOUSE IF NOT EXISTS OLAKE_WH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
CREATE DATABASE IF NOT EXISTS OLAKE_DEMO;
ALTER DATABASE OLAKE_DEMO SET CATALOG = 'SNOWFLAKE';
ALTER DATABASE OLAKE_DEMO SET EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED';

CREATE ROLE IF NOT EXISTS OLAKE_ROLE;
GRANT USAGE, CREATE SCHEMA ON DATABASE OLAKE_DEMO TO ROLE OLAKE_ROLE;
GRANT USAGE ON WAREHOUSE OLAKE_WH TO ROLE OLAKE_ROLE;
GRANT ROLE OLAKE_ROLE TO ROLE SYSADMIN;

CREATE USER IF NOT EXISTS OLAKE_SVC TYPE = SERVICE DEFAULT_ROLE = OLAKE_ROLE;
GRANT ROLE OLAKE_ROLE TO USER OLAKE_SVC;

USE SCHEMA OLAKE_DEMO.PUBLIC;
CREATE NETWORK RULE IF NOT EXISTS olake_ip
  MODE = INGRESS TYPE = IPV4 VALUE_LIST = ('<OLake egress IP>/32');
CREATE NETWORK POLICY IF NOT EXISTS olake_network_policy
  ALLOWED_NETWORK_RULE_LIST = ('OLAKE_DEMO.PUBLIC.olake_ip');
ALTER USER OLAKE_SVC SET NETWORK_POLICY = olake_network_policy;

ALTER USER OLAKE_SVC ADD PROGRAMMATIC ACCESS TOKEN OLAKE_PAT
  ROLE_RESTRICTION = 'OLAKE_ROLE' DAYS_TO_EXPIRY = 30;
```

The two database settings are what make every table Snowflake-managed, on Snowflake's own storage. You don't need an S3 bucket, an IAM role or an external volume of your own.

### Error: the token is refused before it exists

```text
Network Policy is required when creating a programmatic access token for user OLAKE_SVC
```

Snowflake issues a programmatic access token to a service user only under a network policy. Create the rule and the policy first, then the token. On a laptop the egress IP is your public IP (`curl -s https://checkip.amazonaws.com`). On a server, it is the NAT gateway or public IP.

### Error: the destination test fails at the token exchange

OLake exchanges the token at `/v1/oauth/tokens` before anything else, so a bad token fails there. On a later rerun of this setup, the cause was a home IP that had changed since the rule was written, and the exchange came back `unauthorized_client`. Adding the new IP to `olake_ip` fixed it. The other two causes, in order of likelihood:

1. **Wrong scope or role.** The scope must be `session:role:OLAKE_ROLE`, matching the token's `ROLE_RESTRICTION`.
2. **Expired token.** The default is 15 days. Re-issue it with `ALTER USER ... ADD PROGRAMMATIC ACCESS TOKEN`.

Test outside the UI with the same call OLake makes:

```bash
curl -s -X POST "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog/v1/oauth/tokens" \
  -H 'Content-Type: application/x-www-form-urlencoded' \
  --data-urlencode grant_type=client_credentials \
  --data-urlencode scope=session:role:OLAKE_ROLE \
  --data-urlencode client_secret=<your PAT>
```

A good token returns JSON with an `access_token`.

## 3. OLake: source, destination, job

OLake UI runs from the Docker Compose install and listens on `http://localhost:8000`.

**Source.** Pick Postgres, turn on **Enable CDC**, and enter the slot and publication from step 1. If Postgres runs in another container, the host is `host.docker.internal`.

![OLake source form for Postgres with Enable CDC on and the replication slot and publication filled in](https://cdn.hashnode.com/uploads/covers/66dd6cb576b27dcdc2f3b5a9/f02cd063-0379-41cd-8321-bacadafb17f9.png)

**Set up source** runs the connection test and the CDC checks before it saves. All four required checks have to pass. This is where a missing `wal_level` or a missing slot shows up, before any data moves.

![Source test showing the connection, 4 of 4 required CDC checks and 1 of 1 optional setting passed](https://cdn.hashnode.com/uploads/covers/66dd6cb576b27dcdc2f3b5a9/2743b9cf-54c9-4522-9921-87bc866ea326.png)

**Destination.** Pick Apache Iceberg, catalog type **Horizon**, and authentication **Programmatic Access Token (PAT)**. The catalog URL is `https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog`, the warehouse is `OLAKE_DEMO`, and the scope is `session:role:OLAKE_ROLE`.

**Job.** Set the target query engines to Snowflake, plus DuckDB or Spark if they will read the tables. OLake picks the update and delete format every listed engine can read and sets it on each stream. In this run that was positional deletes.

![OLake job page with Snowflake, DuckDB and Spark as target query engines](https://cdn.hashnode.com/uploads/covers/66dd6cb576b27dcdc2f3b5a9/68bd8737-ccfc-439d-a8f3-83f15a65dec9.png)

### Error: Snowflake shows stale rows

A job created without a target query engine defaults to equality deletes, and Snowflake does not read those. The updates land, but Snowflake keeps showing the old rows.

Fix: edit the job's streams and set the target query engine to Snowflake. OLake switches the upsert type, and on the next sync it rewrites the existing delete files into the new format.

## 4. Query it, and the last error

The first sync copied 8 customers and 40 orders in 41 seconds, container start included. The schema is named after the job, the database and the source schema, so `pg_to_snowflake` on database `shop` became `pg_to_snowflake_shop_public`.

### Error: the table "does not exist"

```text
SQL compilation error:
Object 'OLAKE_DEMO.PG_TO_SNOWFLAKE_SHOP_PUBLIC.CUSTOMERS' does not exist or not authorized.
```

Snowflake upper-cases unquoted names, and OLake keeps the lowercase Postgres names. Quote them:

```sql
SELECT "id", "name", "city", "tier", "_op_type", "updated_at"
FROM OLAKE_DEMO."pg_to_snowflake_shop_public"."customers"
ORDER BY "id";
```

The rule runs both ways: if you create the database as `"olake_demo"` in quotes, OLake must be given `olake_demo`, not `OLAKE_DEMO`.

## 5. The Postgres to Snowflake change sync

12 changes went in: 7 inserts and 5 updates. The second sync read only those 12 records (589 bytes) from the slot and finished in 23 seconds.

For `customers` that meant one new data file with 4 rows, plus one small delete file that retires the 2 old versions. The sync log's commit report shows it: `addedDataFiles=1`, `addedRecords=4`, `addedPositionalDeleteFiles=1`, `addedPositionalDeletes=2`.

![OLake sync log with the Iceberg commit report for the change sync](https://cdn.hashnode.com/uploads/covers/66dd6cb576b27dcdc2f3b5a9/c021d187-97d1-4f60-b49e-9783a7b66c6e.png)

Each row carries `_op_type`: `r` for snapshot rows, `c` for inserts and `u` for updates. A deleted row stays as a marker with `_op_type = 'd'` and null fields, so filter it out in queries that should not see deletes.

![Snowsight after the change sync: updates marked u, inserts marked c, snapshot rows r](https://cdn.hashnode.com/uploads/covers/66dd6cb576b27dcdc2f3b5a9/04c87dc5-a7d0-4d10-b633-e9eac6a87a07.png)

## FAQ

### How long does the access token last?

The setup above asks for 30 days, and 365 is the most Snowflake allows. An expired token fails the next sync at the token exchange, before any data moves. Put the renewal on a calendar.

### Do I have to refresh the tables in Snowflake after each sync?

No. With Snowflake's own catalog the tables are Snowflake-managed, and Snowflake sees each new snapshot as soon as the sync commits. A refresh is only needed in the AWS Glue variant, where Snowflake reads the tables through a catalog integration.

### Can DuckDB read the tables when the Snowflake account is on Azure?

No. DuckDB attaches REST catalogs backed by S3, S3 Tables and Google Cloud Storage only. Pick Spark or Trino as the second engine there.

### What happens when a column is added or changed in Postgres?

A column added in Postgres appears in the Snowflake table after the next sync. A type that gets wider, such as `int` to `bigint`, is applied as well. A type that gets narrower or is incompatible fails the sync, which keeps the table from being corrupted.

## Things that are not errors but will surprise you

- **`numeric` arrives as `FLOAT`.** The order totals still matched Postgres to the cent, but round at query time when you need exact decimal arithmetic.
- **Timestamps become `TIMESTAMP_LTZ(6)` in UTC.** `json`, `jsonb`, `uuid` and arrays arrive as `VARCHAR`.
- **Freshness is a schedule,** down to one minute, not sub-second streaming.
- **Small files add up.** A table that gets a new file every minute wants compaction. Snowflake compacts manifests for free, and data-file compaction is up to the writer side.

The full guide has the data type table, the AWS Glue variant and the DuckDB and Spark connection steps: [Postgres to Snowflake on olake.io](https://olake.io/docs/integrations/postgres-to-snowflake/).