How to Estimate a Data Warehouse Migration

The hardest question at the start of any Teradata, Oracle, or Netezza migration isn't *how* — it's *how big*. Estimates built on a table count are almost always wrong, because the cost is in the logic, not the tables. Here's a way to size a migration that holds up.

Count the right things

Tables and views are the easy inventory, but they're rarely the effort. Count instead:

• **Convertible artifacts** — queries, stored procedures, ETL jobs, scripts. This is your real unit of work.
• **Procedural code** — packages, procedures, functions, BTEQ/nzsql scripts. These take far longer per unit than plain SQL.
• **ETL pipelines** — Informatica mappings, SSIS packages, DataStage jobs. Each is a small program.
• **DDL** — for schema conversion and data-type mapping.

A warehouse with 500 tables might have 2,000 queries, 300 stored procedures, and 80 ETL jobs. The 380 procedural artifacts, not the 500 tables, set the timeline.

Score complexity, don't average it

Not all artifacts are equal. A SELECT with a couple of joins is minutes; a 900-line package with cursors, dynamic SQL, and exception handling is days. Bucket every artifact:

• **Simple** — straight SQL, standard functions. High automation, low review.
• **Moderate** — window functions, `MERGE`, moderate procedural logic. Automated conversion + targeted review.
• **Complex** — cursors, dynamic SQL, arbitrary embedded code (Java/C#/SAS macros), deep nesting. Automated first pass, heavy human review.

The complex bucket is usually 10–20% of artifacts but 50%+ of the effort. Estimating without this split is how projects run 2–3× over.

Weight by dialect distance

Some conversions are near-mechanical (Netezza → Snowflake is largely ANSI); others are structural (Oracle packages → Snowflake Scripting, SAS DATA steps → PySpark). The further the source and target models are apart, the more per-artifact review. A realistic estimate weights each source→target pair, not a single blended rate.

Budget for verification, not just conversion

Most estimates stop at "convert the code" and forget the biggest risk: a conversion that runs but returns different numbers. Budget explicitly for **validation** — running source and converted logic against the same data and comparing results. On a serious migration this is 20–30% of the effort, and skipping it is how wrong numbers reach production three months post-cutover.

A defensible estimate looks like

effort = Σ (artifacts_in_bucket × per-artifact_rate × dialect_weight) + validation_overhead + orchestration_rework

— computed per source→target pair, per complexity bucket, with a named line for validation. That's an estimate you can defend in a steering committee, not a number pulled from a table count.

Get the inventory automatically

The inventory and complexity scoring are exactly the kind of thing worth automating up front — scanning the repo, counting real artifacts by category and dialect, and flagging the hard ones before anyone quotes a date. That scan-and-score step is where [JarvisX](/) starts every migration, and it's what turns a guess into an estimate.