The Easiest Way to Replicate Postgres Data to Snowflake

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: PostgreSQL-to-Snowflake CDC flow through Estuary 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 PostgreSQL configured with wal_level=logical for logical replication 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 Customers sample table which we need to replicate from PostgreSQL to Snowflake 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. Selecting the PostgreSQL capture connector 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 : 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. Setting up PostgreSQL capture through Estuary using the database connection details 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. PostgreSQL customers collection with its automatically inferred schema and customer_id key 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. Successful validation and publication of the PostgreSQL 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 PostgreSQL capture successfully backfilled three customer records and transitioned to streaming CDC events 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; Creating the Snowflake resources for Estuary using a SQL worksheet with the ACCOUNTADMIN role 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; Verifying that the ESTUARY_DB database was successfully created in Snowflake 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. Verifying the registered RSA public key and its fingerprint for the ESTUARY_USER service account 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. Locating the Snowflake account URL required to configure the Estuary materialization 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. Selecting the Snowflake materialization connector in Estuary 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. Configuring the Snowflake destination with its account host, database, schema, warehouse, and dedicated role 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 Linking the PostgreSQL capture to the Snowflake materialization and mapping the customers collection to its destination table 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 Snowflake materialization running successfully in Estuary, with three PostgreSQL records processed and no outstanding data backlog 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. The CUSTOMERS table automatically created in Snowflake by the Estuary materialization 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 The three original PostgreSQL customer records successfully replicated into Snowflake 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; Snowflake automatically gets the new customer record once it is loaded into PostgreSQL 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; PostgreSQL confirms that customer 101 now has the premium subscription After the update has propagated through Estuary, query Snowflake:SELECT * FROM ESTUARY_DB.ESTUARY_SCHEMA.CUSTOMERS WHERE CUSTOMER_ID = 101; Snowflake reflects the updated subscription for customer 101, with the operation metadata indicating an update 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. PostgreSQL confirms that customer 102 has been removed from the source table 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; Final Snowflake table after processing the insert, update, and delete operations from PostgreSQL. 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. Lifecycle of a PostgreSQL UPDATE event, from WAL and replication slot through Estuary Capture and Collections to Snowflake Replication consists of two phases of replication: Backfill phase: Estuary reads the existing PostgreSQL records. In our case, it fetched the three original customers. 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.