How to Migrate From On-Premise to Snowflake in PostgreSQL

How do I migrate my on-premise PostgreSQL database to Snowflake?

Move data, schema, and workloads from an on-premise PostgreSQL cluster to Snowflake’s cloud data platform using staged files, COPY INTO, and validation steps.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does a PostgreSQL ➜ Snowflake migration involve?

It consists of exporting on-prem tables, staging the files, recreating schema in Snowflake, bulk-loading with COPY INTO, validating row counts, and switching your applications to the new endpoint.

Which steps should I follow?

1️⃣ Assess schema & extensions.
2️⃣ Export data to CSV/Parquet with COPY.
3️⃣ Upload files to an internal or external Snowflake stage.
4️⃣ Recreate tables in Snowflake.
5️⃣ Load data with COPY INTO.
6️⃣ Re-create constraints, views, and grants.
7️⃣ Run validation queries.
8️⃣ Cut over traffic and monitor.

How do I export an Orders table from PostgreSQL?

Run psql -c "\COPY Orders TO '/tmp/orders.csv' CSV HEADER" on the on-prem server.Repeat for Customers, Products, and OrderItems.

How do I create a Snowflake stage for uploads?

In Snowflake, run CREATE OR REPLACE STAGE ecommerce_stage; for an internal stage or configure an external S3/GCS/Azure stage.

How do I bulk-load CSV files into Snowflake tables?

Use COPY INTO: COPY INTO Orders FROM @ecommerce_stage/orders.csv FILE_FORMAT=(TYPE=CSV SKIP_HEADER=1);

How do I keep data in sync during a long cut-over?

Export the initial full load, switch on logical replication (e.g., Debezium ➜ Kafka ➜ Snowpipe) for catch-up changes, then finalize.

How do I verify that data landed correctly?

Compare row counts: SELECT 'orders' AS table, COUNT(*) FROM Orders UNION ALL SELECT 'orders', COUNT(*) FROM postgres.public.orders@onprem_db;

Best practices for a smooth migration

• Export to compressed Parquet to reduce load time.
• Match column collations & time zones.
• Use multi-threaded SNCOPY or Snowpipe for large files.
• Automate validation with dbt or pytest.

When should I switch applications?

Cut over after validation passes, replication lag is zero, and connection strings are updated in a feature flag or YAML config for easy rollback.

Why How to Migrate From On-Premise to Snowflake in PostgreSQL is important

How to Migrate From On-Premise to Snowflake in PostgreSQL Example Usage


-- Full example: move Orders from Postgres ➜ Snowflake
-- On-prem server
\COPY Orders TO '/tmp/orders.csv' CSV HEADER;

-- Local machine
snowsql -q "PUT file:///tmp/orders.csv @ecommerce_stage AUTO_COMPRESS=TRUE;"

-- Snowflake session
CREATE OR REPLACE TABLE Orders (
  id INTEGER,
  customer_id INTEGER,
  order_date DATE,
  total_amount NUMBER(12,2)
);

COPY INTO Orders
  FROM @ecommerce_stage/orders.csv.gz
  FILE_FORMAT=(TYPE=CSV SKIP_HEADER=1 DATE_FORMAT='YYYY-MM-DD');

-- Validate
SELECT COUNT(*) AS snowflake_rows FROM Orders;
-- Compare with on-prem count recorded earlier

How to Migrate From On-Premise to Snowflake in PostgreSQL Syntax


-- 1. Export from PostgreSQL (on-prem)
\COPY Customers TO '/exports/customers.csv' CSV HEADER;
\COPY Orders    TO '/exports/orders.csv'    CSV HEADER;
\COPY Products  TO '/exports/products.csv'  CSV HEADER;
\COPY OrderItems TO '/exports/order_items.csv' CSV HEADER;

-- 2. Upload to Snowflake internal stage
dsnowsql -f put.sql
-- put.sql
PUT file:///exports/*.csv @ecommerce_stage AUTO_COMPRESS=TRUE;

-- 3. Re-create tables in Snowflake
CREATE OR REPLACE TABLE Customers (
  id           INTEGER,
  name         STRING,
  email        STRING,
  created_at   TIMESTAMP
);
-- Repeat for Orders, Products, OrderItems

-- 4. Load data into Snowflake tables
COPY INTO Customers
  FROM @ecommerce_stage/customers.csv.gz
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY='"');

COPY INTO Orders
  FROM @ecommerce_stage/orders.csv.gz
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

-- 5. Validate counts
SELECT 'customers' AS table, COUNT(*) FROM Customers;

Common Mistakes

Ignoring column collations—text sorted differently in Snowflake can break reports. Fix: specify <code>COLLATE 'en-ci'</code> or use <code>UPPER()</code> in queries.
Loading CSV without specifying <code>DATE_FORMAT</code> and <code>TIME_FORMAT</code>, causing nulls. Fix: always match formats in the <code>FILE_FORMAT</code> clause.