+1 (415) 997-4269
PowderInsights

Oracle to Snowflake Migration: SnowConvert, PL/SQL Redesign, CDC Parallel Runs, and Reconciliation

Oracle is still the last big on-premises warehouse in a lot of estates. Teams migrate Teradata and SQL Server first because those are "obviously" analytical, then discover that a 20-year-old Oracle Exadata or 19c data mart is carrying the finance close, a nightly PL/SQL batch nobody has read since 2014, and a dozen materialized views that downstream reports depend on.

This playbook covers the Oracle-specific parts of a Snowflake migration: what SnowConvert does and does not handle, how PL/SQL maps onto Snowflake Scripting and Snowpark, the CDC options for a parallel run, and the data-type and NULL semantics that quietly corrupt reconciliation if you ignore them.

Phase 0: inventory before you touch anything

Pull the facts out of the Oracle data dictionary rather than asking people what is used.

-- Objects by type and schema
SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN')
GROUP BY owner, object_type
ORDER BY 1,2;

-- Biggest segments: where the data (and the cost) actually is
SELECT owner, segment_name, ROUND(SUM(bytes)/1024/1024/1024,1) gb
FROM dba_segments GROUP BY owner, segment_name ORDER BY gb DESC FETCH FIRST 50 ROWS ONLY;

-- What is genuinely queried (requires Diagnostics Pack licensing)
SELECT o.owner, o.object_name, COUNT(*) execs
FROM dba_hist_sql_plan p
JOIN dba_objects o ON o.object_id = p.object#
GROUP BY o.owner, o.object_name ORDER BY execs DESC;

Classify every object into migrate, rebuild, or retire. In a typical Oracle mart 30-50% of objects are staging leftovers and one-off copies that should never reach Snowflake. Retiring them is the cheapest work in the whole programme.

Also inventory the things that have no Snowflake equivalent and need a design decision, not a translation:

  • DBMS_SCHEDULER jobs → Snowflake tasks, or your existing orchestrator (Airflow, dbt, Openflow)
  • Database links → ingestion pipelines or Snowflake data sharing
  • Oracle Spatial / Text / Advanced Queuing → rethink, not convert
  • Sequences used for surrogate keys → Snowflake sequences or hash keys
  • Fine-grained access control (VPD) and Oracle Label Security → Snowflake row access policies and masking policies

Phase 1: schema conversion with SnowConvert

SnowConvert (free from Snowflake, with an AI-assisted edition) reads DDL and PL/SQL extracts and emits Snowflake SQL plus a conversion report. Give it a clean extract:

# Extract DDL with Oracle's metadata API, one file per schema
expdp sales/pw schemas=SALES content=metadata_only directory=dp dumpfile=sales.dmp
impdp sales/pw sqlfile=sales_ddl.sql directory=dp dumpfile=sales.dmp

snowct oracle --input ./ddl --output ./converted --generate-report

What converts well: tables, views, constraints, sequences, most SQL (CONNECT BY, analytic functions, MERGE), and a large share of straightforward PL/SQL procedures.

What always needs hands:

OracleSnowflake treatment
NUMBER with no precisionSnowConvert emits NUMBER(38,18); profile the real values and tighten — wide decimals bloat storage and slow joins
VARCHAR2(n BYTE)VARCHAR(n) is character-based; multibyte data that fit in Oracle bytes still fit, but length checks in app code change
DATE (has a time component)TIMESTAMP_NTZ, not DATE. This one silently breaks joins and filters
CLOB / BLOBVARCHAR/BINARY up to 16 MB; anything larger goes to a stage and is referenced by URL
Empty string '' = NULLSnowflake treats '' as a real empty string. Any IS NULL logic on text columns must be re-tested
ROWID, ROWNUMROW_NUMBER()/QUALIFY; ROWID has no equivalent — find the natural key
Materialized views with fast refreshDynamic tables (declarative, incremental) in almost every case
Partitioning / local indexesNothing to migrate: micro-partitions plus a clustering key on the high-cardinality filter column, if needed at all
Global temporary tablesSnowflake temporary or transient tables, scoped to the session

A worked example of the DATE trap:

-- Oracle: TRUNC strips time from a DATE
SELECT * FROM orders WHERE TRUNC(order_date) = DATE '2026-03-01';

-- Snowflake equivalent that stays sargable (pruning-friendly)
SELECT * FROM orders
WHERE order_date >= '2026-03-01'::TIMESTAMP_NTZ
  AND order_date <  '2026-03-02'::TIMESTAMP_NTZ;

Phase 2: PL/SQL — translate, or redesign?

Three honest options, and the right answer is usually a mix:

  1. Snowflake Scripting. Closest 1:1 target for procedural logic; SnowConvert maps BEGIN/EXCEPTION/END, cursors, and local variables. Good for batch control logic you must preserve verbatim for audit reasons.
  2. Snowpark (Python). Better for anything with real algorithmic content, loops over business rules, or external calls. Testable in CI with pytest.
  3. Declarative rewrite. Most Oracle batch PL/SQL is a row-by-row implementation of what is actually a MERGE plus a few UPDATEs. Rewriting it as set-based SQL or dbt models is more work up front and roughly an order of magnitude faster at runtime.

A cursor loop converted literally:

CREATE OR REPLACE PROCEDURE sales.refresh_customer_tier()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  c CURSOR FOR SELECT customer_id, ltv FROM sales.customer_metrics;
  n INT DEFAULT 0;
BEGIN
  FOR r IN c DO
    UPDATE sales.customers SET tier = CASE WHEN r.ltv > 100000 THEN 'GOLD' ELSE 'STANDARD' END
     WHERE customer_id = r.customer_id;
    n := n + 1;
  END FOR;
  RETURN 'rows: ' || n;
END;
$$;

...and the version you should actually ship:

MERGE INTO sales.customers t
USING sales.customer_metrics s ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET tier = IFF(s.ltv > 100000, 'GOLD', 'STANDARD');

Rule of thumb: convert literally to get the parallel run green, then refactor the top 20 procedures by runtime once reconciliation passes. Do not try to do both at the same time — you lose the ability to say which change broke a number.

Phase 3: moving the data

History (one-time bulk). Unload to Parquet and COPY INTO. Parquet keeps types; CSV loses them and makes NULL-versus-empty-string worse.

-- Oracle 21c+/23ai: direct Parquet unload to object storage
BEGIN
  DBMS_CLOUD.EXPORT_DATA(
    credential_name => 'OCI_CRED',
    file_uri_list   => 'https://.../bucket/orders/',
    format          => '{"type":"parquet","maxfilesize":"256000000"}',
    query           => 'SELECT * FROM sales.orders');
END;
/

On older versions use sqluldr2, Oracle Data Pump plus a conversion step, or a vendor extract tool. Target 100-250 MB compressed files; one 400 GB file loads on a single thread and ruins your weekend.

COPY INTO raw.orders
FROM @lake_stage/orders/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
ON_ERROR = ABORT_STATEMENT;

Ongoing change (CDC for the parallel run). Options, roughly in order of how much Snowflake-native they are:

  • Openflow (Snowflake's managed NiFi-based service) with a database connector — increasingly the default for Oracle CDC into Snowflake when you want the pipeline inside the platform
  • LogMiner / GoldenGate writing to object storage, consumed with Snowpipe
  • Debezium → Kafka → Snowpipe Streaming if a Kafka backbone already exists
  • Vendor CDC (Fivetran, Qlik, HVR, Striim) where supportability matters more than cost

Whatever you choose, require Oracle supplemental logging on the source tables and agree who owns archive-log growth. That conversation, not the Snowflake side, is what delays most Oracle CDC projects.

Phase 4: reconcile, in public

Run Oracle and Snowflake in parallel for at least two full business cycles (including a month-end close) and publish a daily scorecard.

-- Snowflake side of a row/aggregate/hash comparison
SELECT 'orders' AS table_name,
       COUNT(*)                       AS row_count,
       SUM(amount)                    AS sum_amount,
       SUM(HASH(order_id, customer_id, order_date, amount, status)) AS content_hash
FROM sales.orders
WHERE order_date < CURRENT_DATE;

Checklist for reconciliation that actually finds bugs:

  • Row counts and keyed anti-joins both ways, not just counts
  • Sum and min/max per numeric column, with an explicit tolerance for NUMBER rounding
  • Content hashes on a canonicalised projection (trim text, cast timestamps to a fixed precision, normalise '' to NULL)
  • Known-divergence register: every accepted difference written down with a reason and an owner
  • Report-level comparison, not just table-level — BI tools reintroduce their own joins and filters

Automate it as Data Metric Functions or a dbt test suite so it runs unattended every morning.

Phase 5: cutover and decommission

  • Freeze Oracle DDL early; schema drift during a migration is the top cause of slipped dates
  • Repoint BI and extracts behind views so cutover is a view swap, not a hundred tool edits
  • Keep Oracle read-only for one close cycle after cutover, then archive and stop paying for it — the business case only lands when the licence actually goes away
  • Rebuild security as Snowflake RBAC up front (functional roles, access roles), not as a copy of Oracle grants
  • Set resource monitors and budgets before go-live; the first month after cutover is when unoptimised converted SQL meets elastic compute

Realistic sizing

For a mid-size Oracle mart — a few hundred tables, 10-30 TB, a hundred or so PL/SQL programs — plan on roughly: 2-3 weeks of inventory and design, 4-6 weeks of conversion and data movement, 6-8 weeks of parallel running and reconciliation, and a cutover weekend. The conversion tooling is rarely the long pole; agreeing what "the numbers match" means is.

PowderInsights runs Oracle-to-Snowflake migrations end to end — assessment, SnowConvert output remediation, PL/SQL redesign, CDC, and reconciliation. Get in touch with your object counts and we will tell you honestly which phase will hurt.