How to Migrate from Snowflake to Oracle in PostgreSQL

How do I migrate data from Snowflake to Oracle without losing integrity?

Move tables, schemas, and data from Snowflake into Oracle using staged files, external tables, and INSERT-SELECT pipelines.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why migrate from Snowflake to Oracle?

Cost control, on-prem compliance, and consolidating workloads often drive teams to shift data from Snowflake back to Oracle. Understanding the end-to-end steps prevents data loss and downtime.

What are the high-level steps?

1) Export data from Snowflake to cloud storage.2) Transfer staged files to the Oracle server.3) Create matching Oracle schemas.4) Load data with external tables or SQL*Loader.5) Validate counts and constraints.

How do I export Snowflake tables quickly?

Use the COPY INTO command with a named stage. Compressing files and using a single partition speeds up small tables.

Example

COPY INTO @mystage/customers.csv
FROM Customers
FILE_FORMAT=(TYPE=CSV HEADER=TRUE)
SINGLE=TRUE;

How do I move files to the Oracle host?

Download staged files with SnowSQL or cloud CLI, then transfer via SCP or a cloud bucket mapped to the Oracle server.

How do I recreate schemas in Oracle?

Translate Snowflake data types (e.g., VARCHAR to VARCHAR2, TIMESTAMP_NTZ to TIMESTAMP) and create tables before loading.

Customers table

CREATE TABLE customers (
id NUMBER PRIMARY KEY,
name VARCHAR2(255),
email VARCHAR2(255),
created_at TIMESTAMP
);

What is the fastest loading technique?

External tables avoid intermediate staging. Point the LOCATION to the CSV directory and INSERT-SELECT into the target table.

External table + bulk insert

CREATE DIRECTORY load_dir AS '/u01/load/customers';
CREATE TABLE customers_ext
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS (
records delimited by newline
fields terminated by ','
missing field values are null
)
LOCATION ('customers.csv')
)
REJECT LIMIT UNLIMITED;

INSERT /*+ APPEND */ INTO customers
SELECT * FROM customers_ext;

How do I migrate related tables safely?

Migrate dimension tables first, then fact tables. Disable foreign keys during load and enable afterward to avoid constraint failures.

How can I validate the migration?

Compare row counts and checksums. Sample queries ensure numeric totals match between Snowflake and Oracle.

Checksum example

-- Snowflake
SELECT SUM(TOTAL_AMOUNT) AS sf_sum FROM Orders;
-- Oracle
SELECT SUM(total_amount) AS ora_sum FROM orders;

Best practices for large datasets?

Parallelize COPY & EXPORT, compress files with GZIP, use APPEND hint in Oracle, and commit in batches to reduce redo.

Why How to Migrate from Snowflake to Oracle in PostgreSQL is important

How to Migrate from Snowflake to Oracle in PostgreSQL Example Usage


-- Migrate Customers
a) Snowflake export
COPY INTO @mystage/customers.csv FROM Customers FILE_FORMAT=(TYPE=CSV HEADER=TRUE) SINGLE=TRUE;

b) Oracle load
CREATE DIRECTORY load_dir AS '/u01/load/customers';
CREATE TABLE customers_ext ORGANIZATION EXTERNAL (... LOCATION ('customers.csv'));
INSERT /*+ APPEND */ INTO customers SELECT * FROM customers_ext;

How to Migrate from Snowflake to Oracle in PostgreSQL Syntax


-- 1. Export from Snowflake
COPY INTO @mystage/<table>.csv
  FROM <schema>.<table>
  FILE_FORMAT=(TYPE=CSV HEADER=TRUE FIELD_OPTIONALLY_ENCLOSED_BY='"')
  SINGLE=TRUE | MAX_FILE_SIZE = <bytes>;

-- 2. Transfer file to Oracle host (SCP / rsync / cloud CLI)

-- 3. In Oracle, create matching table
CREATE TABLE <table> (
  id           NUMBER PRIMARY KEY,
  name         VARCHAR2(255),
  email        VARCHAR2(255),
  created_at   TIMESTAMP
);

-- 4. Create external table for load
CREATE DIRECTORY load_dir AS '<server_path>';
CREATE TABLE <table>_ext
  ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY load_dir
    ACCESS PARAMETERS (...)
    LOCATION ('<table>.csv')
  );

-- 5. Bulk insert
INSERT /*+ APPEND */ INTO <table>
SELECT * FROM <table>_ext;

Common Mistakes

Ignoring data-type mismatches: Snowflake TIMESTAMP_NTZ maps to Oracle TIMESTAMP, not DATE. Always map types explicitly to prevent truncation.
Loading with conventional INSERTs: row-by-row inserts are slow and generate excessive redo. Use external tables or SQL*Loader with APPEND for bulk performance.