Ten years of analytics built on Oracle means ten years of accumulated PL/SQL, materialized views, database links, stored procedures, and ETL jobs that nobody fully understands. The schema has four hundred tables. The reporting layer has twelve hundred reports. The stored procedures contain business logic that was written by three different teams over a decade, some of whom have left the company. Nobody can tell you with confidence which procedures are still in use.

A financial services firm with twelve billion dollars in assets under management ran its entire analytics stack on a single Oracle Exadata instance. The system was not broken. Queries returned results. Reports generated on schedule. The compliance team could pull audited data when regulators asked. The problem was cost and constraint. The Oracle license renewal was approaching, the annual cost had tripled over five years, and the Exadata hardware was approaching end-of-life. The firm could not scale compute independently of storage, could not spin up isolated environments for experimentation, and was paying for peak capacity twenty-four hours a day even though analytics demand followed a predictable business-hours pattern.

They wanted to move to Snowflake. The question was not whether to move but how to move ten years of analytics without breaking the reports that the compliance team, the portfolio managers, and the executive committee relied on daily.

Inventory Before Migration

The first mistake teams make in a platform migration is starting with the technology. They pick a migration tool, map Oracle SQL dialect to the target platform, and start converting. This works for simple schemas. For a decade of accumulated analytics, it produces a flood of conversion errors, broken dependencies, and logic discrepancies that take months to untangle.

We started with inventory. Not a technical inventory of tables and columns — the database already had that — but a behavioral inventory of what was actually used. We instrumented the Oracle instance to log every query, every stored procedure call, and every report generation for sixty days. The instrumentation captured the query text, the calling application, the user identity, the execution time, and the result set size.

The sixty-day log revealed a pattern that surprised the data team. Of the four hundred tables in the schema, only one hundred and forty had been queried in the past two months. Of the twelve hundred reports, only three hundred and eighty had been generated. Of the two hundred stored procedures, sixty-seven had been called. The remaining objects — two hundred and sixty tables, eight hundred and twenty reports, and one hundred and thirty-three procedures — were dead. They existed in the schema but nobody used them.

This was not a one-time finding. The dead objects had been accumulating for years because there was no mechanism to deprecate and remove unused analytics artifacts. When a business process changed, the new tables and reports were added but the old ones were never removed. The schema had become a graveyard of abandoned analytics.

The inventory gave us a migration scope that was thirty-five percent of the original schema. We did not need to migrate four hundred tables. We needed to migrate one hundred and forty. We did not need to convert twelve hundred reports. We needed to convert three hundred and eighty. This changed the migration timeline from eighteen months to seven months.

The Parallel Run Architecture

The firm could not tolerate reporting downtime. Portfolio managers needed daily position reports. The compliance team needed auditable data trails. The executive committee needed weekly performance dashboards. Any gap in reporting — even a single day — would trigger regulatory questions and internal escalation.

The migration architecture was a parallel run. Both Oracle and Snowflake ran simultaneously during the migration period. New data was loaded into both platforms. Reports were served from Oracle while Snowflake was being validated. As each report was validated on Snowflake — meaning it produced results identical to Oracle within an acceptable tolerance — it was switched to Snowflake. Oracle continued serving reports that had not yet been validated.

The validation engine was the critical component. For each report, it ran the query against both Oracle and Snowflake and compared the results. The comparison was not a simple row-by-row check because Oracle and Snowflake handled floating-point arithmetic, null sorting, and date formatting differently. The validation engine applied normalization rules: floating-point values were compared to a tolerance of six decimal places, nulls were treated as equal regardless of sort position, and dates were normalized to UTC before comparison.

When the results matched within tolerance for fourteen consecutive runs, the report was marked as validated and switched to Snowflake. When results diverged, the validation engine produced a drift report showing exactly which rows and columns differed, which made root-cause investigation fast. Most divergences were caused by Oracle-specific SQL behavior — implicit type conversions, ROWNUM ordering, and DECODE function semantics — that required explicit rewriting in Snowflake SQL.

The PL/SQL Problem

Stored procedures were the hardest part of the migration. Oracle PL/SQL is a full programming language with cursors, exception handling, package variables, and autonomous transactions. Snowflake’s stored procedure language is JavaScript-based and does not support the same constructs. Direct translation was impossible for roughly forty percent of the procedures.

We categorized the sixty-seven active procedures into three groups.

The first group — twenty-nine procedures — were pure data transformation. They read from staging tables, applied business rules, and wrote to target tables. These were candidates for migration to a transformation tool. We rewrote them as dbt models, which gave us version control, testing, and documentation that the original PL/SQL lacked. The dbt models ran in Snowflake natively and produced identical results.

The second group — twenty-three procedures — were orchestration logic. They scheduled jobs, managed dependencies between transformations, and handled error recovery. These were replaced by the existing orchestration tool, which was already present in the stack but underutilized because the PL/SQL procedures had absorbed the orchestration responsibility. Moving orchestration out of the database and into a purpose-built tool was an improvement, not just a migration.

The third group — fifteen procedures — contained complex business logic with cursors, conditional branching, and iterative calculations that could not be expressed as SQL transformations. These were the hardest. We rewrote them as Snowflake stored procedures in JavaScript, which required careful testing because the behavioral differences between PL/SQL and JavaScript are significant. Exception handling semantics differ. Numeric precision differs. Null propagation differs. Each procedure in this group required individual attention and a validation suite that compared Oracle and Snowflake output across a representative input set.

What We Gave Up

The migration was not cost-free. Three capabilities changed in ways that required adjustment.

First, Oracle’s materialized view refresh mechanism had no direct Snowflake equivalent. The firm had forty-two materialized views that pre-computed expensive joins for frequently accessed reports. In Snowflake, we replaced these with Snowflake’s automatic clustering and result caching. For most queries, Snowflake’s cache was faster than Oracle’s materialized views because the cache was automatic and did not require refresh scheduling. For a few complex aggregation queries, Snowflake was slightly slower because the result had to be computed rather than read from a pre-built view. The performance difference was measurable — two seconds versus five seconds — but did not impact the user experience because none of these queries were in real-time dashboards.

Second, Oracle’s row-level security model used Virtual Private Database, which applied security predicates transparently at the query layer. Snowflake’s row-level security used secure views and row access policies, which required explicit view creation for each security rule. The migration from transparent predicates to explicit views was tedious — we created sixty-two secure views — but the result was more auditable because the security rules were visible as view definitions rather than hidden in database-level predicates.

Third, the Oracle database links that connected the analytics instance to the transactional database were replaced by data sharing through Snowflake shares. This was a genuine improvement for read access but broke the few write-back patterns where analytics stored procedures updated transactional tables. Those three write-back procedures were redesigned to use an event-based pattern instead: the analytics procedure published an event, and the transactional system consumed it. This was more decoupled and more robust, but it required changes to the transactional system that were outside the original migration scope.

The Cost Outcome

Annual Oracle licensing and hardware maintenance was $1.8 million. Snowflake’s consumption-based pricing, after the migration settled, was $640,000 per year for equivalent workloads. The savings of $1.16 million per year funded the migration cost within the first fourteen months.

More important than the direct cost savings was the elasticity. Oracle required provisioning for peak load — end-of-quarter reporting, year-end compliance — which meant the firm paid for peak capacity year-round. Snowflake’s auto-scaling meant the firm paid for capacity only when queries were running. During nights and weekends, compute spend dropped to near zero. During end-of-quarter reporting, compute scaled up automatically and then scaled back down when the reporting surge ended.

The firm also gained the ability to create isolated environments. On Oracle, running an experimental query that consumed significant resources risked impacting production reports. On Snowflake, the data team could create a separate warehouse for experimentation that had no impact on production workloads. This alone accelerated the analytics team’s velocity because they no longer needed to schedule experimentation during off-peak hours.

The Decision Heuristic

If you are considering a database platform migration, start with a sixty-day usage inventory. Do not migrate what nobody uses. The typical finding is that sixty to seventy percent of the schema is dead. Migrating dead objects is pure waste — it consumes time, introduces risk, and produces no value.

For the live objects, run both platforms in parallel. Validate every output before switching. The validation engine is not optional. Without it, you are hoping that the migration produced correct results rather than knowing. In financial services, hope is not an audit strategy.

Finally, do not treat the migration as a translation project. Treat it as a redesign opportunity. Stored procedures that were written ten years ago for a different business process should be redesigned, not rewritten. Materialized views that compensated for a slow database should be replaced by a faster database, not reimplemented. The goal is not to produce the same system on a different platform. The goal is to produce a better system that happens to run on a different platform.