The Easiest Way to Replicate Postgres Data to Snowflake

[Pipelineguy]

PostgreSQL and Snowflake tend to be at different points in the same data architecture. PostgreSQL is responsible for transactions, while Snowflake is the place where the data gets analyzed, joined with other data sources, and reporting is done.

Moving data from one to another once is simple enough. Synchronizing them when PostgreSQL database changes is more difficult. The costs of full table copies grow as data volumes increase, and incremental queries require additional latency and complex logic to account for updates and deletions.

There is a simpler way to do that – Change Data Capture. Rather than constantly querying tables, CDC listens for changes in the database and forwards them downstream.

In this tutorial, we will create a PostgreSQL-to-Snowflake CDC pipeline with Estuary. We will configure PostgreSQL for logical replication, collect changes into Estuary collections, materialize collections to Snowflake, and finally test the pipeline with inserts, updates, and deletes.

Why is it difficult to replicate Postgres to Snowflake​

Copying a PostgreSQL table can easily be done by replicating it frequently in Snowflake.

It is quite useful for a small table. However, if the source table has millions of rows and only a fraction of rows get updated in subsequent runs, moving the whole data each time is inefficient.

A better approach would be incremental extraction. For instance:

SELECT *
FROM customers
WHERE updated_at > :last_sync_time;

This will require less data moved, however, the pipeline now requires proper timestamps of applications. More importantly, whenever a row gets deleted from PostgreSQL, it won’t automatically appear in the updated_at query. Deletes require special handling.

Decreasing the interval reduces polling latency, at the expense of more frequent queries. In the same time, replication itself starts consuming resources in the system it is supposed to monitor.

To solve those issues, one should create CDC stack by his or her own hands. Typical stack includes Postgres logical replication, Debezium, Kafka, and some consumer to save events in Snowflake.

This would allow tracking more reliable data changes. The price to pay is adding new components to deploy, tune, monitor, scale and recover.

Replication of Postgres to Snowflake is not a problem of how to get data from A to B once. This is a problem of continuous synchronization of systems without turning replication layer itself into one more system that must be supported.

How does CDC simplify PostgreSQL to Snowflake data replication?​

The Write-Ahead Log (WAL) feature is used by PostgreSQL in its durability model. The modifications made to the database are first written into the WAL, and then the data pages are written onto the disk.

In case wal_level is set to logical, PostgreSQL will add all the additional data required for logical decoding. Thus, logical replication will be able to provide table-level and row-level change information and not just the physical blocks.

CDC makes use of this stream.

Rather than re-running it many times:

SELECT * FROM customers;

The CDC consumer may monitor such changes as:

INSERT customer 501
UPDATE customer 294
DELETE customer 173

This is the reason why CDC can be very useful to keep the operation database and analysis system in synchronization all the time.

The architecture of Estuary for this tutorial is as follows:

The PostgreSQL connector provided by Estuary captures all database events through CDC and writes them to collections. The collection serves as the source for the materialization in Snowflake.

This is advantageous as the capture process is different from the delivery one. The changes made by PostgreSQL first become a reusable data stream in Estuary as opposed to becoming tied to a single load to Snowflake.

Let's build it.

Step 1: Preparing PostgreSQL for CDC​

Before we begin​

To enable Estuary to capture changes from PostgreSQL, we will have to prepare the database for logical replication. We will require:

• Docker: to run a local PostgreSQL 16 instance that supports logical replication enabled.
• Estuary account: to set up the PostgreSQL capture and Snowflake materialization.
• Snowflake account: to set up the destination database, schema, warehouse, and service user.
• ngrok account: to open the local PostgreSQL instance via a TCP tunnel. Free accounts will require card verification to set up TCP tunnels.
• OpenSSL: to generate the RSA key pair required for Snowflake authentication. On Windows, we can run OpenSSL in Docker.

In our case, we will be using Docker and ngrok so that our setup will not require deploying PostgreSQL in the cloud. We will only use them in our local demonstration. An existing PostgreSQL with logical replication support can also be used instead of these tools.

Start PostgreSQL with logical replication enabled:

docker run --name estuary-postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=postgres \
  -p 5432:5432 \
  -d postgres:16 \
  -c wal_level=logical

Once the container is up and running, connect to PostgreSQL:

docker exec -it estuary-postgres psql -U postgres -d postgres

Check whether logical replication is enabled:

SHOW wal_level;

The output should be:

 wal_level
-----------
 logical

When using an existing self-managed PostgreSQL instead of creating it via Docker, enable it with:

ALTER SYSTEM SET wal_level = logical;

You need to restart PostgreSQL to apply the change.

Create a specific user for Estuary to use for replication:

CREATE USER flow_capture
WITH PASSWORD 'your_password'
REPLICATION;

On PostgreSQL 14 and above, you could provide read access with:

GRANT pg_read_all_data TO flow_capture;

In production, you might want to restrict the permissions more and grant access only to schemas and tables needed by Estuary to capture.

During backfills, Estuary uses a small watermarks table to maintain consistent capture position. Create it explicitly in this case:

CREATE TABLE IF NOT EXISTS public.flow_watermarks (
    slot TEXT PRIMARY KEY,
    watermark TEXT
);

GRANT ALL PRIVILEGES
ON TABLE public.flow_watermarks
TO flow_capture;

We also need a publication to define which tables PostgreSQL should expose via logical replication.

Let’s create a sample customers table that will be used later in the tutorial:

CREATE TABLEcustomers (
    customer_id INT PRIMARY KEY,
    name TEXT,
    plan TEXT
);

INSERT INTO customers VALUES
    (101, 'Jaume', 'standard'),
    (102, 'Dani', 'premium'),
    (103, 'Helen', 'standard');

And create a publication:

CREATE PUBLICATION flow_publication;

ALTER PUBLICATION flow_publication
SET (publish_via_partition_root = true);

ALTER PUBLICATION flow_publication
ADD TABLE public.flow_watermarks, public.customers;

Publication defines which tables should generate logical replication events. In production, it’s better to publish only those tables that you actually need and not to expose all tables.

Now we can check the sample data:

SELECT * FROM customers;

The output should be:

 customer_id | name  |   plan
-------------+-------+----------
         101 | Jaume | standard
         102 | Dani  | premium
         103 | Helen | standard

These records will serve as an easy way to test CDC later. When the pipeline runs, we will update, insert and delete rows in this table and see how these changes reflect in Snowflake.

The process described above slightly differs for managed PostgreSQL services such as Amazon RDS, Aurora, Google Cloud SQL, Azure Database for PostgreSQL, Neon and Supabase. These providers expose logical replication via provider-specific configuration but the concepts remain the same: logical replication, permissions, publication and replication slot.

PostgreSQL being set up, now we can connect it to Estuary and create a capture.

Step 2: Creating a PostgreSQL Capture in Estuary​

After setting up logical replication in PostgreSQL, connect it to Estuary. A capture fetches data from the source system and writes it into Estuary collections, which can subsequently become destinations such as Snowflake.

Since our PostgreSQL is running locally within Docker, we have to make it accessible from Estuary Cloud. In this guide, we used ngrok to open access to PostgreSQL port 5432 via a TCP tunnel.

With the ngrok token set as an environment variable, we launched the TCP tunnel via Docker:

docker run --rm -it -e NGROK_AUTHTOKEN ngrok/ngrok:latest tcp host.docker.internal:5432

Ngrok provides us with a public TCP URL that will forward all connections to our local PostgreSQL. The tunnel needs to remain active while working through this tutorial. Do not expose your database publicly in production environments.

Configure the PostgreSQL Capture​

Go to Estuary dashboard, navigate to Sources, and create a new capture by clicking "+ New Capture". Find PostgreSQL and configure the connection with the database created in Step 1.

In our case, the configuration was as follows:

Field	Value
Connector	PostgreSQL
Capture Name	jaume/postgres-snowflake-demo
Data Plane	AWS: us-east-1 c1
Server Address	<NGROK_HOST>:<PORT>
User	flow_capture
Database	postgres
Password	The replication user's password

The server IP address is the ngrok TCP tunnel public endpoint and not the localhost:5432. This way, the cloud connector can reach your local PostgreSQL database.

Upon entering the connection parameters, click Next to validate the connection and view PostgreSQL tables.

Discover and Configure Collections​

By default, Estuary discovers the PostgreSQL tables that the replication user has access to and associates some tables with collections.

In our case, Estuary has discovered our previously created public.customers table. We enabled it to be the only collection we will be capturing:

jaume/postgres-snowflake-demo/public/customers

Switching to the Collection tab gives us an opportunity to review the inferred schema and the collection key. Estuary correctly identified /customer_id as the key that allows to differentiate between customers records.

Finally, there are settings for the initial backfill and any schema evolution. For our current example, we used default settings to allow Estuary to backfill the existing records before starting processing any further changes.

If schema auto-updates are enabled, then Estuary will take care of the collection schema evolution according to the changes in the source.

Publish and Verify the Capture​

After validating the capture collection, press Next and click on Save and Publish. Estuary tests the connector and publishes the capture.

Upon publishing, the capture first performs backfill by reading the rows present in public.customers. Next, it streams the new changes through logical replication provided by PostgreSQL.

You can see that on the Overview page for your capture.

In our example, the dashbaord says:

• Connector Status: Streaming CDC Events
• Collection: public.customers
• Documents captured: 3
• Data written: 589 B

Those three documents correspond to the three records we created in Step 1: Jaume, Dani, and Helen.

The important detail is that the capture is no longer performing its initial backfill.This means that it will stream PostgreSQL changes through CDC, so no full reload of the whole table will be required when doing any insert, update, and delete operation.

Now that we have our data stored in PostgreSQL as Estuary collection, we can configure Snowflake as our destination.

Step 3: Set Up Snowflake​

We now have the records from PostgreSQL flowing into Estuary; let’s now make Snowflake as the destination of our choice.

Snowflake would require a database, a schema, a virtual warehouse, and a dedicated user having adequate permissions. We’ll also set up key pair authentication to ensure Estuary can make connections without using a username and password.

Create the Snowflake Resources​

Navigate to the Snowflake dashboard and click on Projects > Worksheets. Create a SQL worksheet and then select the ACCOUNTADMIN role for initial configuration.

To know the current role:

SELECT CURRENT_ROLE();

Next, we create the resources that Estuary will use:

USE ROLE ACCOUNTADMIN;

-- Create role
CREATE ROLE IF NOT EXISTS ESTUARY_ROLE;
GRANT ROLE ESTUARY_ROLE TO ROLE SYSADMIN;

-- Create database and schema
CREATE DATABASE IF NOT EXISTS ESTUARY_DB;

CREATE SCHEMA IF NOT EXISTS
    ESTUARY_DB.ESTUARY_SCHEMA;

-- Create warehouse
CREATE WAREHOUSE IF NOT EXISTS ESTUARY_WH
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

-- Create service user
CREATE USER IF NOT EXISTS ESTUARY_USER
    TYPE = SERVICE
    DEFAULT_ROLE = ESTUARY_ROLE
    DEFAULT_WAREHOUSE = ESTUARY_WH;

GRANT ROLE ESTUARY_ROLE TO USER ESTUARY_USER;

We use an X-Small warehouse that pauses automatically after 60 seconds of inactivity. This works well for our small-scale replication workload because the warehouse does not have to be kept running between materializations.

Now, give the dedicated role access to the necessary resources:

GRANT CREATE SCHEMA, MONITOR, USAGE
ON DATABASE ESTUARY_DB
TO ROLE ESTUARY_ROLE;

GRANT ALL PRIVILEGES
ON SCHEMA ESTUARY_DB.ESTUARY_SCHEMA
TO ROLE ESTUARY_ROLE;

GRANT USAGE
ON WAREHOUSE ESTUARY_WH
TO ROLE ESTUARY_ROLE;

Estuary requires case-sensitive quoted identifiers for _meta/op field. Set this explicitly:

ALTER USER ESTUARY_USER
SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

A separate service user ensures we don't give too much administrative privileges to the integration service.

We can verify that the resources have been created with:

SHOW DATABASES LIKE 'ESTUARY_DB';

SHOW SCHEMAS LIKE 'ESTUARY_SCHEMA'
IN DATABASE ESTUARY_DB;

SHOW WAREHOUSES LIKE 'ESTUARY_WH';

SHOW USERS LIKE 'ESTUARY_USER';

SHOW GRANTS TO ROLE ESTUARY_ROLE;

Configure Key-Pair Authentication​

The Estuary connector provided by Snowflake uses JWT (JSON Web Tokens). This needs a private key from Estuary and a public key for the Snowflake user.

Key pairs can be created using OpenSSL:

# Generate private key
openssl genrsa 2048 | openssl pkcs8 \
  -topk8 -inform PEM \
  -out rsa_key.p8 -nocrypt

# Generate public key
openssl rsa -in rsa_key.p8 \
  -pubout -out rsa_key.pub

The generated files are:

• rsa_key.p8: the private key for Estuary.
• rsa_key.pub: the public key that is registered in Snowflake.

Instead of installing OpenSSL on Windows, we used OpenSSL via Docker. The keys are functionally identical.

Open the public key file and extract the Base64 encoded contents, omitting the lines that read BEGIN PUBLIC KEY and END PUBLIC KEY

Afterwards, register the key with the Snowflake service user:

USE ROLE ACCOUNTADMIN;

ALTER USER ESTUARY_USER
SET RSA_PUBLIC_KEY = 'YOUR_PUBLIC_KEY_CONTENT';

Keep the private key safe and never display it in screen shots or commit it to the repository.

To test whether the Snowflake has registered the key, run:

DESC USER ESTUARY_USER;

Search for the RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_FP fields. In the latter there is the fingerprint of the registered public key which can be checked against the locally generated one.

Find the Snowflake Account URL​

The Snowflake account URL that Estuary will use to connect needs to be known.

In the Snowflake account information, locate the account URL. This is how it will appear:

organization-account.snowflakecomputing.com

However, when entering this URL in Estuary, do not include the https:// prefix.

The destination setup is now complete:

Setting	Value
Database	ESTUARY_DB
Schema	ESTUARY_SCHEMA
Warehouse	ESTUARY_WH
Role	ESTUARY_ROLE
User	ESTUARY_USER
Authentication	Key-pair (JWT)
Host	Snowflake account URL

We have everything required in terms of Snowflake resources, permissions, and authentication to set up our destination with the existing PostgreSQL collection.

Now it’s time to create a materialization in Snowflake using Estuary and connect it to jaume/postgres-snowflake-demo/public/customers.

Step 4: Materialize the Data Into Snowflake​

The Snowflake instance is ready, and our PostgreSQL data exists in collections within Estuary. We will now link the two.

This is what is known as materialization within Estuary. The capture will import the data from PostgreSQL into collections, and the materialization will write it to destination tables.

Create the Snowflake Materialization​

On the Estuary dashboard, navigate to Destinations and select + New Materialization.

Search for Snowflake and select the Snowflake materialization connector.

Create a unique name for your materialized instance.
In our example we created:

jaume/postgres-snowflake-demo/destination-snowflake

Next, set up the Snowflake endpoint using the resources created in Step 3.

Field	Value
Host	lyosmpw-md08275.snowflakecomputing.com
Database	ESTUARY_DB
Schema	ESTUARY_SCHEMA
Warehouse	ESTUARY_WH
Role	ESTUARY_ROLE
Authentication	JWT
User	ESTUARY_USER
Private Key	Contents of rsa_key.p8

The field for “Host” needs to be entered with the Snowflake account URL, but without the https:// prefix. In order to authenticate with Snowflake, enter your generated PEM private key here, with both BEGIN and END tags.

Link the PostgreSQL Capture​

Having provided all the connection information, we now instruct Estuary about the data to be ingested into Snowflake.

Under Link Capture, select the PostgreSQL capture from Step 2:

jaume/postgres-snowflake-demo/source-postgres

With this step, we will link the PostgreSQL capture to our materialization and enable auto-addition of destination bindings for any new collections inside this capture.

For our case, we selected the customers collection and mapped it as follows:

Setting	Value
Source Collection	jaume/postgres-snowflake-demo/public/customers
Destination Table	customers
Alternative Schema	ESTUARY_SCHEMA
Delta Updates	Disabled

We kept Delta Updates turned off, so Estuary works in the standard (merge) mode. The incoming updates are merged into Snowflake to keep the current state of each record. It is the right choice for our case, as we need one current row per customer instead of many change events.

In case Delta Updates are enabled, Estuary applies the incoming changes without merging them into destination records. Such an approach allows reducing the merge cost, but the incoming data might require further processing to get the current state of each record.

For delta-update bindings, Estuary also supports Snowpipe Streaming approach to have lower latency during data ingestion by writing the data directly to Snowflake. Our tutorial relies on the standard update with scheduled merges to balance data freshness and warehouse compute cost.

The connector has a Hard Delete feature. In case it is enabled, the records deleted in PostgreSQL are physically deleted in Snowflake. Otherwise, Estuary uses the soft delete mode and keeps the records with metadata about their deletion.

For our tutorial, we enabled Hard Delete so that when we delete a customer in PostgreSQL, it will also be deleted in Snowflake.

Configure the Sync Schedule​

Before publishing, set Sync Schedule.

Changes on the PostgreSQL side are continuously collected, but they do not need to be applied to Snowflake immediately or at the same pace.

Estuary gives us an opportunity to decide what frequency we want to use for the materialization processes. By default, this frequency is set to be every 30 minutes if not specified.

This approach provides a trade-off between freshness of data and compute cost.

A less frequent interval will provide you with a fresher version of the changes in the Snowflake side, but at the same time it will lead to higher compute cost required since the Snowflake warehouse will be activated more frequently.

For workloads that only need periodically refreshed reporting tables, a longer interval may be more economical. For applications requiring fresher analytical data, a shorter interval is more appropriate.

By default, the X-Small warehouse that we already have will automatically get suspended in 60 seconds without any activity.

Publish and Verify the Materialization​

Once you have configured your bindings and endpoints, hit Next and click Save and Publish.

The Estuary materialization is started and will now begin ingesting the data that already exists within our PostgreSQL collection.

We can verify this on the materialization’s Overview page.

In our case, the dashboard displayed:

• Connector Status: Running
• Data Backlog: Current
• Destination Tables: 1
• Documents Processed: 3
• Data Read: 592 B

We can also confirm that Estuary created the destination table automatically.

Open a Snowflake SQL worksheet and execute:

SHOW TABLES IN SCHEMA ESTUARY_DB.ESTUARY_SCHEMA;

Our output indicates a CUSTOMERS table belonging to ESTUARY_ROLE having three rows.

Notice that Snowflake has the table name in uppercase as CUSTOMERS even though we bound the table as customers. This is because unquoted Snowflake identifier names are always capitalized and can be referred to by:

SELECT *
FROM ESTUARY_DB.ESTUARY_SCHEMA.CUSTOMERS;

At this point, our pipeline is connected end to end:

PostgreSQL > Estuary Capture > Estuary Collections > Snowflake Materialization

Now, we'll proceed to test whether the inserts, updates, and deletes in PostgreSQL reflect in Snowflake.

Step 5: Testing the Complete Replication​

Once the PostgreSQL to Snowflake replication is set up, verify that any changes made to PostgreSQL data appear in Snowflake.

We will perform the following actions: adding a customer, updating a customer record, and deleting a customer. This ensures that the pipeline can process changes continuously rather than refreshing tables entirely.

Verify the Initial Data​

Prior to making any changes, open a Snowflake SQL worksheet and run a query against the destination table:

SELECT *
FROM ESTUARY_DB.ESTUARY_SCHEMA.CUSTOMERS;

Our initial result includes the three entries that we had added to PostgreSQL in Step 1:

customer_id	name	plan
101	Jaume	standard
102	Dani	premium
103	Helen	standard

Note that Snowflake has additional metadata columns such as _meta/op and FLOW_PUBLISHED_AT. These columns provide additional information about the records handled by the pipeline.

Test 1: Insert a New Customer​

Add a new customer in PostgreSQL first.

Connect to our local database:

docker exec -it estuary-postgres psql -U postgres -d postgres

Then execute:

INSERT INTO customers
VALUES (104, 'Laura', 'standard');

Check that the new record exists:

SELECT * FROM customers
ORDER BY customer_id;

PostgreSQL now contains four customers.

No changes are required in our Estuary capture connector, as it is already reading from the PostgreSQL logical replication stream. Hence, the insert statement will be captured automatically.

Once the materialization is completed in Snowflake, execute the same query in Snowflake:

SELECT *
FROM ESTUARY_DB.ESTUARY_SCHEMA.CUSTOMERS
ORDER BY CUSTOMER_ID;

Not surprisingly, the new customer 104 is in Snowflake along with the three existing records. No additional query for extraction or reloading the table is required.

Test 2: Update an Existing Customer​

In our second test, we modify Jaume's subscription from standard to premium .

In PostgreSQL, execute:

UPDATE customers
SET plan = 'premium'
WHERE customer_id = 101;

We can verify that there is a change in our source database:

SELECT * FROM customers
WHERE customer_id = 101;

After the update has propagated through Estuary, query Snowflake:

SELECT *
FROM ESTUARY_DB.ESTUARY_SCHEMA.CUSTOMERS
WHERE CUSTOMER_ID = 101;

The ID of the customer remains unchanged, but in the PLAN column, we see premium.

Please note that the _meta/op field contains u, meaning update. The record of the customer was updated by Estuary at the destination rather than creating a new one.

It is convenient for analytics because you need the most up-to-date state of records at the destination.

Test 3: Delete a Customer​

Let us try deleting one record that timestamp-based incremental extraction finds difficult.

Delete customer 102 from PostgreSQL:

DELETE FROM customers
WHERE customer_id = 102;

Check the remaining records:

SELECT * FROM customers
ORDER BY customer_id;

Now our source has Jaume, Helen, and Laura only.

Since we've turned on Hard Delete in our settings for the Snowflake materialization, Estuary will delete the relevant row in Snowflake rather than soft delete it.

After the next synchronization, execute:

SELECT *
FROM ESTUARY_DB.ESTUARY_SCHEMA.CUSTOMERS
ORDER BY CUSTOMER_ID;

The final result contains:

customer_id	name	plan
101	Jaume	premium
103	Helen	standard
104	Laura	standard

Customer 102 has been removed, customer 101got the new subscription, and we have customer 104added.

What did we verify?​

In these three tests, we performed different actions on PostgreSQL, and then we observed the results in Snowflake:

Operation	PostgreSQL Change	Snowflake Result
INSERT	Add customer 104	New customer appears
UPDATE	Change customer 101 to premium	Existing record updated
DELETE	Remove customer 102	Record removed

The important thing is what we did not have to do.

We did not have to schedule a new extraction of the full table, write code to detect the changes, and manually synchronize the destination every time.

Estuary captured the changes by using PostgreSQL’s logical replication and passed them to Snowflake via its collections according to the sync schedule of the materialization.

With the backfill and all three CDC processes successfully checked, our PostgreSQL-to-Snowflake replication pipeline is working end to end.

What happens behind the scenes​

Having done the checks, let's see what happens behind the scenes when there is an update in the PostgreSQL record.

The moment Jaume's subscription was changed from standard to premium, PostgreSQL put the change in its Write-Ahead Log (WAL). The logical decoding function allows the change to be seen by Estuary, and the replication slot monitors how much CDC reader has progressed through the log.

Estuary captures the updated record, stores it in the right collection, and materializes the new state in Snowflake.

Replication consists of two phases of replication:

1. Backfill phase: Estuary reads the existing PostgreSQL records. In our case, it fetched the three original customers.
2. CDC phase: After the backfill phase, Estuary starts reading the changes in the replication stream, and not the entire table again.

Additionally, it's worth mentioning that capturing and materialization are two different processes. The PostgreSQL capturing produces reusable Estuary collections, which means that the same data can be sent to other destinations without creating another CDC connection to PostgreSQL.

Production Considerations​

The demo uses a small PostgreSQL table, and the considerations are different for real databases. Before deploying CDC pipeline in production, you need to consider the following:

• Replication slots and WAL retention: PostgreSQL retains WAL entries required for existing active replication slots. When Estuary no longer consumes changes, the retained WAL entries will use disk space. Monitor replication lag, status of replication slots, and disk utilization. Set appropriate retention limits to limit retained entries.
• Primary keys: PostgreSQL requires replica identity (primary key is default) to locate rows in update and delete operations. The tables that don't have a proper primary key require configuration so that changes propagate downstream.
• Initial backfills: Loading millions of historical rows is significantly harder than streaming hundreds of changes per second. Carefully plan initial backfill process and configure Estuary backfilling parameters accordingly.
• Schema evolution: Changes such as adding new columns or modifying data types might impact downstream schema. Estuary supports automatic schema evolution but test breaking changes to make sure Snowflake consumers are not impacted.
• Network connectivity: ngrok was used for the local demo. In production, use secure connections, such as restricted network access, SSH tunneling, or private networking.
• Snowflake synchronization frequency: Increased frequency of updates means more freshness but also higher compute costs. Make sure the sync is done at an appropriate frequency, based on reporting needs and not minimal interval.
• Monitoring: Monitor both PostgreSQL capture and Snowflake materialization processes. A healthy capture does not mean that downstream database is up-to-date, so make sure to monitor connector status, replication lag, backlog, and errors.

The above considerations are increasingly important as dataset size grows and downstream applications depend on timely and continuous updates.

What to do if PostgreSQL Logical Replication is not possible?​

Some PostgreSQL deployments may not have the ability to do logical replication. Managed databases, permissions, and policies can prevent you from setting up CDC.

For such situations, Estuary has a PostgreSQL Batch Query connector as a viable option.

Unlike the CDC approach where the data is read from the WAL, the batch connector executes queries on PostgreSQL and saves the results in Estuary collections. This can be used to query database views and custom queries.

Here is a quick comparison between the two approaches:

Requirement	PostgreSQL CDC	PostgreSQL Batch
Data freshness	Continuous capture	Scheduled extraction
Insert/update tracking	Automatic change capture	Depends on query design
Delete tracking	Supported	Requires reconciliation logic
Logical replication	Required	Not required
Database views	Not directly supported	Supported
Source database load	Generally lower	Depends on query frequency and volume

CDC is preferred in those tables which change quite frequently since CDC captures insert, update, and delete without reading the entire data set again.

On the other hand, batch extraction is preferred where logical replication is not possible or where there is no requirement of real-time synchronization.

It ultimately boils down to your specific requirements.

Final Thoughts​

Replicating PostgreSQL data into Snowflake is not difficult if you are copying a single table once. The challenge comes from synchronizing the data whenever there are incoming transactions.

Full refreshes become inefficient as the size of the data grows, while polling by timestamps increases the number of queries and makes delete detection harder. Building your own CDC process can solve both of these problems but requires extra effort to maintain.

In our example, we leveraged Estuary to capture changes in PostgreSQL, store them in reusable collections, and materialize the latest records to Snowflake. We have also verified that inserts, updates, and deletes have been propagated without manually reloading the source tables.

For those who would like an easier PostgreSQL to Snowflake data flow, you can try Estuary and build the same pipeline using your own databases.