Migration
Oracle to Snowflake migration: converting PL/SQL and avoiding the classic gotchas
How to move an Oracle data warehouse to Snowflake: PL/SQL to Snowflake Scripting, empty strings and DATE semantics, sequences, CONNECT BY, materialized views and triggers, change data capture and reconciliation.
Hemant Walchale
· 2 min read
Oracle-based warehouses move to Snowflake to cut licence and Exadata costs and to get elastic, low-maintenance compute. Snowflake supports more Oracle-style SQL than most platforms (including CONNECT BY and sequences), which helps. A few semantic differences still change results if nobody plans for them.
This is the approach we recommend.
1. Inventory
List schemas, tables, views, materialized views, packages, procedures, functions, triggers, database links and scheduler jobs, plus consumers. Use Oracle’s statistics and dependency views to find what is used, and retire the rest.
2. Know the differences
| Oracle | Snowflake | What to watch |
|---|---|---|
Empty string '' is NULL |
Empty string and NULL differ |
Normalize text columns explicitly; filters and joins change otherwise. |
DATE includes time |
DATE is date only |
Map to TIMESTAMP_NTZ unless the time is always midnight. |
NUMBER, VARCHAR2 |
NUMBER, VARCHAR |
Mostly direct; check precision on money columns. |
| Sequences | Sequences | Supported, but values and gaps will differ; never reconcile on surrogate keys. |
CONNECT BY, START WITH |
Supported | Test results, including ordering. |
ROWNUM |
ROW_NUMBER(), LIMIT |
Rewrite pagination and top-N queries. |
| PL/SQL packages and procedures | Snowflake Scripting procedures | Convert with tooling, rewrite the rest. |
| Triggers | Streams and Tasks, or pipeline logic | Move the logic out of the database. |
| Materialized views | Dynamic Tables, or materialized views (single-table only) | Re-specify refresh behaviour. |
| Database links | Data sharing or ingestion pipelines | Replace cross-database access. |
DBMS_SCHEDULER jobs |
Snowflake Tasks or your orchestrator | Rebuild schedules. |
3. Keep data in sync with CDC
Load history in bulk through cloud storage, then keep Snowflake current during the parallel run with a log-based change data capture tool, applying changes with MERGE or Streams and Tasks.
4. Reconcile on business keys
Compare row counts, totals and row-level differences by domain, matching rows on business keys rather than sequence-generated IDs. Most differences trace back to empty strings, DATE mapping and implicit conversions.
5. Retire Oracle
Schedule waves back from the Oracle support renewal date and decommission each domain’s objects and jobs on cutover day.
The short version
Retire unused objects, map DATE and empty strings deliberately, convert PL/SQL to Snowflake Scripting with tooling, move triggers into Streams and Tasks, keep data in sync with CDC, reconcile on business keys and switch off Oracle by domain.
Prefer Databricks? See Oracle to Databricks. Browse all migration guides or estimate effort with the migration estimator.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Snowflake. Unfamiliar terms are explained in the data engineering glossary.