Migrating from Oracle to Snowflake

Oracle is where a lot of data warehouses grew up. It's reliable, it's fast when tuned, and it's deeply, expensively entangled with your infrastructure. That last part is why teams start looking at Snowflake: the license renewals, the DBAs, the storage arrays, the fact that scaling means buying hardware quarters in advance.

Snowflake flips the model — storage and compute separate, you pay per second of query, and there's no server to patch. But "lift the tables and go" is not the migration. This is what the move actually involves.

Why teams move off Oracle

• Cost that scales with usage, not capacity. Oracle bills you for peak capacity you provision. Snowflake bills for compute you actually consume, and suspends warehouses when idle.
• Elastic concurrency. Month-end reporting no longer fights ETL for the same box. Spin up a separate virtual warehouse; they don't contend.
• No infrastructure to run. No RAC, no ASM, no patching, no DBA on call for storage.
• A modern transformation story. Oracle transformations live in PL/SQL packages and materialized views. Snowflake pairs cleanly with dbt for version-controlled, tested SQL.

The data-type mapping

This is the unglamorous core of the migration. Get it wrong and your numbers drift silently. The key conversions:

The DATE-carries-time trap is the single most common source of "the totals don't match" after an Oracle migration. Map it to TIMESTAMP_NTZ and validate.

PL/SQL doesn't come along for free

This is where "migration" turns into "rebuild," and it's worth being honest about it upfront. Snowflake has no PL/SQL. Your options for the procedural logic:

• Set-based logic → dbt models. Most of what lives in PL/SQL packages is really transformation logic that should be a SELECT. Rewrite it as dbt models. This is the bulk of the work and the biggest long-term win.
• Procedural logic → Snowflake Scripting or stored procedures (SQL, JavaScript, or Python). Reserve this for genuinely procedural steps — loops, dynamic SQL, control flow.
• Sequences → IDENTITY columns or SEQUENCE objects. Snowflake has both; behavior differs slightly (no guaranteed gap-free ordering).
• MERGE works in Snowflake — syntax is close, but validate the matched/not-matched branches.

Automated converters (Snowflake's SnowConvert, for one) get you a first pass on the DDL and simpler procedures. Don't trust them blind — treat the output as a draft to review, especially anything touching dates, nulls, or implicit type coercion.

Getting the data across

Two patterns, depending on whether this is a one-time cutover or an ongoing sync:

1. Bulk historical load. Export Oracle tables to Parquet or CSV on cloud storage (S3/GCS/Azure), then COPY INTO Snowflake. Fast, cheap, one-time.
2. Ongoing CDC during parallel-running. Fivetran or Airbyte with Oracle log-based CDC keeps Snowflake current while both systems run. This is what makes parallel validation possible without freezing the Oracle source.

Raw tables land in Snowflake, register as dbt sources, and transformation starts clean.

Transformation: rebuild in dbt, don't port materialized views

Oracle materialized views and PL/SQL packages become dbt models, layered:

-- models/staging/stg_orders.sql
SELECT
    order_id,
    CAST(order_date AS TIMESTAMP_NTZ)  AS order_ts,   -- Oracle DATE had a time part
    order_total::NUMBER(18,2)          AS order_total,
    LOWER(TRIM(customer_email))        AS customer_email
FROM {{ source('oracle_erp', 'raw_orders') }}
WHERE order_id IS NOT NULL

Staging models handle the type mapping in one place. Mart models hold the joins and business logic. Tests run in CI. The transformation layer becomes readable and reviewable — the opposite of chasing logic through nested PL/SQL packages.

Parallel validation: the non-negotiable step

Two to four weeks running Oracle and Snowflake side by side. Compare daily:

• Row counts on critical tables.
• Aggregate values — revenue, counts, balances — to the cent.
• The same dashboards pointed at both.

When the numbers match for a full week, cut over. Not before. This is the step teams are tempted to skip to hit a deadline, and it's the one that costs the most when skipped.

What the stack looks like after

Timeline

For a medium Oracle warehouse (a few hundred tables, moderate PL/SQL):

The PL/SQL rebuild dominates. A warehouse that's mostly tables and views moves fast; one with thousands of lines of procedural logic takes longer — and is where the biggest quality upgrade lives.

Proof it works

We run this destination for real. ShotVet — a multi-clinic veterinary group — runs on a Snowflake + dbt + Sigma stack we built, and it onboards new clinics 10× faster than the setup it replaced. The pattern is repeatable: clean warehouse layer, business logic in tested SQL, no infrastructure to babysit.

Weighing Oracle → Snowflake — or Oracle → BigQuery, or whether to keep the transformation layer in dbt? We've run all three. Book a discovery call and we'll scope your PL/SQL estate honestly. Related: Oracle → BigQuery and the legacy stack migration playbook.