How to Load Postgres to Snowflake: 3 Easy Steps

In the current technological landscape, businesses often look to capitalize on the benefits of both analytical and transactional information with integrations such as Postgres to Snowflake data types. For financial operations, PostgreSQL is a commonly used relational database, and Snowflake provides its users with scalable and effective data warehousing and analytical capabilities. By integrating these two platforms, companies could optimize performance, gain insights from real-time data, and make informed choices. In this article, we have provided you with 3 efficient methods for both skilled and non-technical users to move data from PostgreSQL to Snowflake.

Methods to Connect Your PostgreSQL Database to Your Snowflake Warehouse

Method 1: Using ETL Tools to Move Data from Postgres to Snowflake
Method 2: Write a Custom Code to Move Data from Postgres to Snowflake
Method 3: Using a Snowflake Connector to Move Data from Postgres to Snowflake

Method 1: Use Hevo ETL to Move Data From Postgres to Snowflake With Ease

Step 1: Set up PostgreSQL as the Source

1. Log in to your Hevo account.
   
2. On the dashboard, click “+ Create Pipeline”.
   
3. Choose PostgreSQL as your source.
   
4. Enter the required connection details:
   
   • Hostname
     
   • Port
     
   • Database name
     
   • Username and password
     
5. Click Test Connection to verify connectivity.
   
6. Once verified, click Save and Continue.

Step 2: Set up Snowflake as the Destination

1. After adding your source, select “Add Destination”.
   
2. Choose Snowflake from the list of available destinations.
   
3. Enter the following Snowflake credentials:
   
   • Warehouse name
     
   • Database name
     
   • Schema
     
   • Role (if applicable)
     
   • Account identifier
     
   • Username and password
     
4. Click Test Connection to confirm everything is working.
   
5. Hit Save and Continue to finalize your destination setup.

Step 3: Start the Data Load

1. Once both source and destination are configured, select the tables you want to sync.
   
2. Set the sync mode (Full Load, Incremental, or CDC based on your use case).
   
3. Enable the pipeline and watch your data flow from PostgreSQL to Snowflake in real time.

Here are some additional features that Hevo provides, which may help your integration:

•  Incremental Data Load: Hevo enables the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Schema Management: Hevo eliminates the tedious task of schema management, automatically detecting t
• Scalable Infrastructure: Hevo has in-built integrations for 150+ sources that can help you scale your data infrastructure as required.

Method 2: Write a Custom Code to Move Data from Postgres to Snowflake

This method gives you complete control over how you migrate data from PostgreSQL to Snowflake. Here’s a step-by-step breakdown of how to do it using custom code.

Step 1: Extract Data from PostgreSQL

Why this matters:

The quality and granularity of your extract step dictate everything downstream, schema mapping, performance, error handling, and incremental updates.

Option 1: Using the COPY TO Command (Preferred for CSV exports)

PostgreSQL’s COPY TO is a native, high-performance command that writes table data (or SELECT query output) directly to a CSV or text file.

COPY employees TO 'C:/tmp/employees.csv' WITH DELIMITER ',' CSV HEADER;
COPY (SELECT * FROM contacts WHERE age < 45)TO 'C:/tmp/young_contacts.csv' WITH DELIMITER ',' CSV HEADER;

Why use COPY TO?

• It’s faster than looping through query results via Python or JDBC.

• Supports SELECT queries, which means you can filter, transform, or pre-aggregate on the fly.

• Output is CSV-compatible, which aligns well with Snowflake’s default bulk loaders.

Exporting Multiple Tables Automatically

You can build a procedure using PL/pgSQL to loop through table names and export each into a separate CSV file.

CREATE OR REPLACE FUNCTION table_to_csv(path TEXT) RETURNS void AS $$DECLARE    tables RECORD;    statement TEXT;BEGIN    FOR tables IN        SELECT (schema || '.' || table_name) AS table_with_schema FROM tables_to_extract    LOOP        statement := 'COPY ' || tables.table_with_schema ||                      ' TO ''' || path || '/' || tables.table_with_schema || '.csv''' ||                     ' DELIMITER '','' CSV HEADER';        EXECUTE statement;    END LOOP;    RETURN;END;$$ LANGUAGE plpgsql;
-- Run this:SELECT table_to_csv('/home/user/dump');

Making it Incremental

You don’t always want to export entire tables. For better performance and cost, export only changed rows using timestamp logic:

1. Add a last_pull_time column in your metadata table (tables_to_extract).
   
2. Update it after each successful sync.
   
3. Use it in your query dynamically:

-- Within the same loopstatement := 
'COPY (SELECT * FROM ' || tables.table_with_schema ||             ' WHERE last_modified_time_stamp > ''' || tables.last_pull_time || ''')' ||             ' TO ''' || path || '/' || tables.table_with_schema || '.csv''' ||             ' DELIMITER '','' CSV HEADER';

This way, you’re minimizing file size, reducing unnecessary loads, and ensuring Snowflake only gets the delta.

Option 2: Using pg_dump (Less Preferred)

If you need a full logical dump including insert statements (e.g., for audit purposes or backup), use:

pg_dump --column-inserts --data-only --table=table_name db_name > table_name.sql

• Downsides: Output is not directly usable for Snowflake.
  
• Next step: Write a script (Python/Bash) to parse the SQL inserts into CSV format.
  
• Use case: Best suited for one-time migration or archiving

Step 2: Handle Data Type Conversion

Why this matters:

Postgres and Snowflake don’t always treat data types the same. Without proper handling, you might face errors during load or incorrect data representation.

Common areas to watch:

• Timestamps: Snowflake supports many formats, but they must be explicitly defined via FILE FORMAT if not standard.
  
• Booleans: In Snowflake, ‘true’/’false’ must be handled as strings or mapped properly.
  
• JSON: Snowflake expects VARIANT types for semi-structured data, flattening might be required.

You can either:

• Clean data during extraction using SELECT statements (e.g., TO_CHAR for timestamps), or
  
• Define proper file format configurations during staging.

Step 3: Stage Files for Loading into Snowflake

Before Snowflake can ingest your data, it needs to reside in a staging area, either internal to Snowflake or in cloud storage (like S3 or Azure Blob).

a. Internal Stage

Each user and table is automatically allocated an internal stage for data files. It is also possible to create named internal stages.

• The user was named and accessed as ‘@~.’
• The name of the table stage will be the same as that of the table.
• The user or table stages can’t be altered or dropped.
• The user or table stages do not support setting file format options.

As mentioned above, Internal Named Stages can be created by the user using the respective SQL statements. It provides a lot of flexibility while loading data by giving you options to assign file formats and other options to named stages.

While running DDL and commands like load data, SnowSQL is quite a handy CLI client that can be used to run those commands and is available in Linux/Mac/Windows. Read more about the tool and options.

Best for small to medium workloads or one-off loads.

CREATE OR REPLACE STAGE my_postgres_stageCOPY_OPTIONS = (on_error = 'skip_file')FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);

To upload files:

PUT file:///tmp/postgres_data/data/employees.csv @my_postgres_stage;

You can enable:

• PARALLEL option for faster uploads
  
• Automatic file compression (auto_compress = TRUE)

SnowSQL CLI is the best way to execute these commands.

b. External Stage

Amazon S3 and Microsoft Azure are external staging locations currently supported by Snowflake. We can create an external stage with any of those locations and load data to a Snowflake table.

To create an external stage on S3, IAM credentials have to be given. If the data is encrypted, then encryption keys should also be given.

CREATE OR REPLACE STAGE postgres_ext_stageURL='s3://your-bucket-name/data/'CREDENTIALS = (aws_key_id='XYZ' aws_secret_key='ABC')ENCRYPTION = (master_key='your_key_here');

Upload files using:

• AWS CLI
  
• Python SDK (boto3)
  
• Web console

Step 4: Load Staged Files into Snowflake Tables

Use the COPY INTO command for high-speed bulk loading. Requires a running virtual warehouse in Snowflake.

From Internal Stage:

COPY INTO target_tableFROM @my_postgres_stage;

From External Stage: 

COPY INTO target_tableFROM @postgres_ext_stage/mydata.csvFILE_FORMAT = (TYPE = 'CSV');

Load with pattern:

COPY INTO target_tableFROM @my_postgres_stageFILE_FORMAT = (TYPE = 'CSV')PATTERN = '.*[.]csv';

This allows you to load multiple files in one command.

Step 5: Update or Merge Data in Final Table

Once your data lands in Snowflake, you’ll often load it into a staging (intermediate) table first. From there, you’ll apply changes to the final table.

Option 1: Update existing rows + insert new ones

-- Update existing recordsUPDATE final_table tSET value = s.valueFROM intermed_table sWHERE t.id = s.id;
-- Insert new recordsINSERT INTO final_table (id, value)SELECT id, valueFROM intermed_tableWHERE id NOT IN (SELECT id FROM final_table);

Option 2: Delete + Insert

Simple and effective if you don’t need to preserve existing records that have not changed.

DELETE FROM final_tableWHERE id IN (SELECT id FROM intermed_table);
INSERT INTO final_table (id, value)SELECT id, value FROM intermed_table;

Option 3: Use MERGE (Recommended)

Best for maintaining atomic upserts:

MERGE INTO final_table tUSING intermed_table s ON t.id = s.idWHEN MATCHED THEN UPDATE SET t.value = s.valueWHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

Limitations of Using Custom Scripts for Postgres to Snowflake Connection

Here are some of the limitations associated with using custom scripts to connect PostgreSQL to Snowflake.

Complexity

This method requires a solid understanding of PostgreSQL and Snowflake, including their respective data types, SQL syntax, and file-handling capabilities. Some may find this to be a challenging learning curve because not everyone has substantial familiarity with SQL or database management.

Time-consuming

It can take some time to write scripts and troubleshoot any issues that may arise, especially with larger databases or more complex data structures.

Error-prone

In human scripting, mistakes can happen. A small error in the script might result in inaccurate or corrupted data.

No Direct Support

You cannot contact a specialized support team if you encounter issues. For help with any problems, you will need to rely on the manuals, community forums, or internal knowledge.

Scalability Issues

Time-consuming, especially when network bandwidth is limited. Methods of direct data transmission could be quicker.

Inefficiency with Large Datasets

It might not be the most effective method to move big datasets by exporting them to a file and then importing them again, especially if network bandwidth is limited. Methods of direct data transmission could be quicker.

Method 3: Using a Snowflake Connector to Move Data from Postgres to Snowflake

Snowflake now offers an official Snowflake Connector for PostgreSQL, available as an app on Snowflake Marketplace. It simplifies real-time and batch replication through a Docker-based agent and supports both Initial Loads and Change Data Capture (CDC) via Postgres WAL logs.

This method is ideal if:

• You want a fully managed, low-maintenance integration.
  
• You prefer an officially supported, no-code/low-code route.
  
• You need ongoing, real-time replication without building custom pipelines.

Step 1: Install the Connector App in Snowsight

1. Log in to Snowsight. (Snowflake’s modern UI).
   
2. Go to Data Products → Marketplace.
   
3. Search for Snowflake Connector for PostgreSQL.
   
4. Click Get → Choose your Warehouse → Click Get Application.

The connector will now appear under Apps in your Snowsight sidebar.

Step 2: Set Up the Event Table (For Logging and Monitoring)

This table logs connector events like sync status, failures, and CDC progress. It’s crucial for observability and troubleshooting.

Run the following in a Snowsight worksheet:

CREATE EVENT TABLE IF NOT EXISTS my_connector_event_table CHANGE_TRACKING = TRUE;
ALTER ACCOUNT SET EVENT_TABLE = my_connector_event_table;

Step 3: Deploy the Docker Agent

Snowflake’s connector uses a Docker-based agent to connect to PostgreSQL and transfer data securely.

a. Pull the latest agent image:

docker pull snowflakedb/pg-cdc-agent:latest

b. Create the agent-config.json file:

{
"agent_id": "my_agent_id",
"log_level": "INFO",
"postgres": {
"host": "your_postgres_host",
"port": 5432,
"username": "your_pg_user",
"password": "your_pg_password",
"database": "your_pg_database"
},
"snowflake": {
"account": "your_snowflake_account",
"user": "your_snowflake_user",
"private_key_path": "/path/to/private_key.p8",
"role": "ACCOUNTADMIN",
"warehouse": "your_warehouse",
"database": "your_sf_database",
"schema": "your_sf_schema"
}
}

 Use key pair authentication for Snowflake instead of passwords.

Step 4: Run the Docker Agent

Launch the connector agent in detached mode:

docker run -d \
 -v $(pwd)/agent-config.json:/config/agent-config.json \
 snowflakedb/pg-cdc-agent:latest --config /config/agent-config.json

Step 5: Configure Source & Destination in Connector UI

1. In Snowsight, go to Apps → Your Installed Connector.
   
2. Click Open → Create Source.
   
3. Provide your PostgreSQL connection details and select which table(s) to replicate.
   
4. Choose your replication mode:
   
   • Initial Load Only: One-time snapshot
     
   • CDC Only: Stream ongoing changes (after you’ve already loaded data)
     
   • Initial Load + CDC: Snapshot first, then stream new changes

Step 6: Start Replication

Once configuration is done, click Start in the Connector UI.

Behind the scenes, the connector will:

• Establish a secure connection to your PostgreSQL database.
  
• Extract data and monitor for row-level changes using WAL logs.
  
• Load and sync the data into your specified Snowflake database and schema.

Replication is continuous, and retry logic is built in for fault tolerance.

Step 7: Monitor & Verify

Use this SQL to verify replication:

SELECT * FROM your_sf_database.your_sf_schema.your_table LIMIT 10;

To monitor events and issues:

SELECT * FROM my_connector_event_table ORDER BY timestamp DESC;

Additional Resources for PostgreSQL Integrations and Migrations

• PostgreSQL to Oracle Migration
• Connect PostgreSQL to MongoDB
• Connect PostgreSQL to Redshift
• Integrate Postgresql to Databricks
• Export a PostgreSQL Table to a CSV File

What is Postgres?

Postgres is an open-source Relational Database Management System (RDBMS) developed at the University of California, Berkeley. It is widely recognized for its reliability, robust features, and performance, and has been in use for over 20 years.

Postgres not only supports object-relational data but also supports complex structures and a wide variety of user-defined data types. This gives PostgreSQL a definitive edge over other open-source SQL databases, such as MySQL, MariaDB, and Firebird.

Businesses rely on Postgres as their primary data storage/data warehouse for online, mobile, geospatial, and analytics applications. Postgres runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.

Key Features of Postgres

• Reliable & Fault-Tolerant: Features like write-ahead logging ensure data integrity and high availability.
• Security-First Design: Includes advanced access controls and supports major security standards like LDAP and GSSAPI.
• Flexible & Developer-Friendly: Supports complex data types and offers full control for custom database setups.
• Open-Source & Cross-Platform: Free to use and runs smoothly on all major operating systems.
• Trusted by Top Companies: Used by Apple, Spotify, Facebook, and more for everyday data operations.

What is Snowflake?

Snowflake is a fully managed, cloud-based data warehouse that helps businesses modernize their analytics strategy. Snowflake can query both structured and unstructured data using standard SQL. It delivers results of user queries spanning Gigabytes and Petabytes of data in seconds.

Snowflake automatically harnesses thousands of CPU cores to quickly execute queries for you. You can even query streaming data from your web, mobile apps, or IoT devices in real-time.

Snowflake comes with a web-based UI, a command-line tool, and APIs with client libraries that make interacting with Snowflake pretty simple. Snowflake is a secure platform that meets the most stringent regulatory standards, including HIPAA, FedRAMP, and PCI DSS. When you store your data in Snowflake, your data is encrypted in transit and at rest by default, and it’s automatically replicated, restored, and backed up to ensure business continuity. Plan smarter with our Snowflake pricing calculator – accurate, fast, and reliable.

Key Features of Snowflake 

Ever since the Snowflake Data Warehouse got into the growing cloud Data Warehouse market, it has established itself as a solid choice. It offers five editions, going from ‘standard’ to ‘enterprise’. This is a good thing as customers have options to choose from based on their specific needs.

• It has some cool querying features like undrop, fast clone, etc. These might be worth checking out as they may account for a good chunk of your day-to-day data operations.
• The ability to separate storage and compute is something to consider, and how that relates to the kind of data warehousing operations you’d be looking for.
• Snowflake is designed in a way to ensure the least user input and interaction required for any performance or maintenance-related activity. This is not a standard among cloud DWHs. For instance, Redshift needs user-driven data vacuuming.

Use Cases of Postgres to Snowflake Data Replication

Let’s look into some use cases of Postgres-Snowflake replication.

Backup and Disaster Recovery – Transfer your Postgres data to a dependable and secure cloud environment provided by Snowflake. You can be assured that your data is constantly accessible and backed up, ensuring business continuity even in the event of unforeseen events.

Transferring Postgres data to Snowflake- Transfer your data to Snowflake’s endlessly scalable cloud platform with ease. Take advantage of easy performance enhancements, cost-effectiveness, and the capacity to manage large datasets.

Data Warehousing- Integrate data into Snowflake’s data warehouse from numerous surces of sources, including Postgres. This can help uncover hidden patterns, provide a deeper understanding of your company, and enhance strategic decision-making.

Advanced Analytics- Utilize Snowflake’s quick processing to run complex queries and find minute patterns in your Postgres data. This can help you stay ahead of the curve, produce smart reports, and gain deeper insights.

Artificial Intelligence and Machine Learning- Integrate your Postgres data seamlessly with Snowflake’s machine-learning environment. Allowing you to develop robust models, provide forecasts, and streamline processes to lead your company toward data-driven innovation.

Collaboration and Data Sharing- Colleagues and partners can securely access your Postgres data within the collaborative Snowflake environment. Hence, this integration helps promote smooth communication and expedite decision-making and group achievement.

Conclusion

In this guide, we’ve walked you through the essentials of migrating data from PostgreSQL to Snowflake. As modern data warehouse solutions, such as Snowflake, become central to analytics workflows, setting up a secure, efficient, and scalable migration process becomes crucial. Given the complexity surrounding cloud infrastructure, data governance, and metadata management, selecting the right approach is vital.

To simplify this process, you can utilize a no-code pipeline platform like Hevo. With Hevo, you can automate your PostgreSQL to Snowflake migration in real-time, without writing a single line of code, ensuring a smooth, hassle-free data transfer experience.

Find out how to move data from Azure PostgreSQL to Snowflake to enhance your analytics capabilities. Our resource provides simple steps for effective data migration and integration.

If you are looking for an ETL tool that facilitates the automatic migration and transformation of data from Postgres to Snowflake, then Hevo is the right choice for you. Hevo is a No-code Data Pipeline. It supports pre-built integration from 150+ data sources at a reasonable price. With Hevo, you can perfect, modify and enrich your data conveniently.

FAQ on PostgreSQL to Snowflake