Planning an Oracle-to-Hive Data Warehouse Migration with a Graph-Driven Dependency Map

A data warehouse that has run for years is fed by far more than the database. PL/SQL packages, scheduler jobs and a handful of ETL tools all write into the same tables, and each team only knows its own part. Nobody notices the gap until the migration starts and a moved table breaks a job that was never on the list.

Expec Consulting built a graph-driven migration tool to close that gap for teams moving an Oracle 11.2 data warehouse to Hive. The tool reads the warehouse and the lineage exports from ODI, Pentaho and Talend, joins everything into one dependency and lineage graph, and uses that graph to plan the migration in waves and convert the SQL, with a person approving every result.

Two terms come up throughout: lineage is the trail that shows where data comes from and where it goes, and a wave is a batch of objects that migrate together.

Problem Description

Migration planning is usually held back by four things:

• Oracle's DBA_DEPENDENCIES view shows which objects reference each other, but says nothing about which way the data flows

• ETL lineage sits inside ODI, Pentaho and Talend, three tools with three separate views

• Table reads and writes are buried in PL/SQL loops, cursors, exception handlers and package subprograms

• Objects keep being added and dropped while the plan is still being written

The migration order ends up resting on experience and memory, which is hard to defend in a review meeting.

Proposed Solution

The tool puts every source on one map and plans from there.

The diagram reads left to right. Oracle and the three ETL tools are the sources. Metadata reaches the tool in one of two ways: the DB Extractor reads Oracle directly, or, when the database network is closed to outside tools, the DBA runs a standalone exporter and the resulting zip is uploaded together with the ODI, Pentaho and Talend lineage exports. Inside the tool, PostgreSQL keeps the inventory (objects, history, statuses and waves) and Neo4j keeps the graph of dependencies, foreign keys and data flow. What comes out is what the team works with: the migration graph, the wave plan, AI assistant access and a Hive SQL review queue. The Hive or Impala target sits outside the tool and is deployed by the team.

1. One Graph for Oracle and the ETL Tools

Oracle metadata and the ODI, Pentaho and Talend exports load through the same pipeline into PostgreSQL and a Neo4j graph. An ETL job becomes a node next to the tables it reads and writes, so it lands in the same cluster and the same wave as those tables and never gets left behind.

2. A Plan Computed from Real Dependencies

Clusters and migration waves come straight from the graph. A cluster is a group of connected objects, and a wave is the batch of work that moves together. The order of work is something the team can open, check and explain.

3. Read-Only and Human-Reviewed by Design

The tool only reads from Oracle and has no connection to Hive or Impala. Converted SQL goes through a review queue, the deployed, validated and migrated statuses are set by hand, and every status or wave change is logged with the user who made it. Security and change teams can approve it without opening a path into either system.

Implementation Overview

Key features delivered:

1. Complete Metadata Collection from Oracle 11.2

Nineteen extracts cover objects and sizes, DDL, dependencies, SQL history from V$SQL, users and grants, sessions, scheduler jobs, materialized views, redo and workload. When the database network is closed to outside tools, the DBA runs a standalone exporter and hands over a zip file. Either way, the team works from what the database actually contains.

2. Impact Analysis on a Single Page

The Migration Graph shows any object with everything that loads it and everything it feeds. When data moves through a procedure or an ETL job, that step is drawn between the two tables, so the question "what breaks if this moves?" has a visible answer. Red lines show data flow, blue lines show foreign keys and grey lines show other dependencies.

The same view works at the level of a single procedure. Here an ODI procedure reads one table and feeds another, with the owning users and the package it depends on drawn around it.

3. Lineage Read Directly from PL/SQL and ETL Exports

• Every procedure, function, package and trigger parsed statement by statement for the tables it reads and writes

• Variables that carry query results followed through the code

• Each package subprogram mapped on its own

• SQL built at run time flagged as unmapped, with the reason, so reviewers know exactly where to look

• ODI packages and procedures, Pentaho jobs and transformations, and Talend jobs and components linked to the Oracle tables they touch

Each ETL tool has its own node types, but they all appear in the same graph. The daily job from each tool is shown below.

ODI	Pentaho	Talend

4. Dependency-Safe Wave Planning

The wave planner never places an object ahead of anything it depends on. Objects caught in a dependency cycle share a wave, because they cannot be ordered against each other. Teams preview a plan before applying it and can check the saved waves for violations whenever the graph changes. That keeps broken loads from being discovered after cut-over.

5. Oracle-to-Hive Conversion with a Review Queue

Oracle DDL and DML are converted to Hive by explicit rules, one wave at a time. Constructs with no exact Hive equivalent, such as CONNECT BY, ROWNUM and the (+) outer join, are routed to review, and a person approves each result before it goes into the deploy script. Plain SQL converts automatically, and the hard cases are visible from the start.

6. Change Tracking Across Reloads

Each reload is compared with the previous one. New, changed, dropped and returning objects are reported, dropped objects go into a review queue, and migration statuses and wave assignments carry over. A project that runs for months stays in step with a source that keeps moving.

7. AI Access through MCP

The application has no AI built in. Its functions are exposed as 43 MCP tools (MCP, the Model Context Protocol, is an open standard that lets AI clients call an application's functions), so an AI client such as Claude Code, Cline or a local Ollama model can answer questions like "which tables are affected if this fact table changes?" The AI runs with the permissions of the person asking, and a read-only mode hides every tool that writes.

Outcomes

One Map of the Whole Warehouse Oracle code, captured SQL and three ETL tools appear in a single graph. Teams stop comparing notes across tools and look at the same picture.

Faster, Safer Impact Analysis For any table, the team sees what loads it, what reads it and which job sits in between, on one page.

A Migration Order the Team Can Defend Waves follow the actual dependencies and are checked for violations, so the plan survives review and objects don't break after they move.

Full Control over Source and Target Nothing is written to Oracle or Hive, every conversion is approved by a reviewer, and every change is logged by user.

Why Expec Consulting?

Expec Consulting brings hands-on experience in data platform migration:

• Oracle data warehouse analysis down to PL/SQL lineage

• ETL metadata integration across ODI, Pentaho and Talend

• Graph-based dependency modelling with Neo4j

• Migration planning and SQL conversion for Hive

• Read-only tooling that fits enterprise security and change processes

We help organizations plan a data warehouse migration they can see, check and explain.

Facing a similar challenge? Talk to us. Expec Consulting has solved this at enterprise scale before.