Custom ETL or AI? Choosing the Right Strategy for Legacy Data Migration

Suppose you inherit a storage unit. From a friend you hardly know.

You find boxes everywhere. None of them are labeled. They could be either a treasure or trash.

You've got two options to find out. Go through every box yourself. The process is slow, but that way you won't miss anything. Or you can call an estate company to clear it out fast. This is a quick process, but they might throw away your grandma's photo with the junk while cleaning out the storage unit.

Now, that's what happens with data migration, too. Except the boxes are legacy old databases - the ones nobody's touched in years, sitting on some outdated server, month after month, nobody remembers setting up.

And the scary part? Well, most data migrations don't fail because of poor planning. They fail because nobody knows what's inside those legacy databases created years ago. In fact, studies show that nobody spots hidden data problems until the migration is underway. And by then, it's too late to plan around it. At that point, it's just an expensive cleanup job.

So here's the call. If you come across a situation like that, would you rather comb through every database yourself? Or let AI do the expensive cleanup job?

Well, both get you to the same place. But how you get there is really what makes all the difference.

So, let's first dig into the old-school way.

What happens when you use custom ETL

Now, in this case, your developers write every script themselves. They map every column. They build every custom extract-transform-load (ETL) script from scratch for seamless migration.

Nothing moves unless a developer decides it should. That's the whole point, really.

So, why this matters: You have total control over the migration process. If something's in the data pipeline, a human put it there for a reason they can explain. And that’s what matters when the stakes are really high - financial records, medical records, customer ID numbers, anything where a mistake means a lawsuit, not just a bad day. When someone asks "why is this data mapping done this way," there's an actual answer. A person, a decision, a code you can point to.

But the messy part is: It’s exhausting. Your developers spend weeks decoding some system a developer built in 2016 and never explained before he left the company. They're not building anything new now. They're just trying to figure out what that developer meant. Every undocumented column is a small query, a question mark, and there are hundreds of them. It drags on. Budgets drain. Your best people, the ones who should be building new things, get stuck playing detective instead.

Imagine letting AI take control

AI doesn't just rewrite your legacy old code. AI reads it. It understands it. It figures out how your data connects. Then builds new pipelines - at blazing-fast speed.

Stuff that took a senior engineer four weeks? AI does it in a single prompt.

So, why you should consider it beneficial: Because AI guarantees speed. Jaw-dropping speed. No more writing boilerplate code manually, no more mapping data columns one by one. IDC found AI-assisted migrations cut timelines by 30 to 50% versus doing it manually. That's several weeks turning into just a few days. Your team ships result quickly, caters to customer needs without failing predefined timelines, and developers get to focus on core engineering tasks instead of spending a whole quarter buried in scripts nobody outside engineering will ever notice.

But AI may not be the best option because it lies. Not on purpose - it's just guessing, and sometimes the guess is wrong. A coding script can look fine and flawless. And AI can still quietly flip a number from 68 to 86, round a decimal incorrectly, or miss an edge case buried three joins deep. The main problem is that nothing about it looks bad or broken. Everything seems perfect. The mistake may not show up today. But sadly, it shows up one month later, sitting quietly inside a sales report someone's about to present to management.

The Actual Legacy System

I worked on an old order system built in 2016. I found it was a giant table. There were no docs. The developer who had built it left in 2017.

-- Legacy table (MySQL)
CREATE TABLE ord_mst (
  ord_id   INT,
  cust_nm  VARCHAR(50),
  ord_dt   VARCHAR(6),    -- '050319' = 5 March 2019? Or 3 May? Nobody knows.
  amt      INT,           -- 45050 means $450.50. Yes, cents.
  sts      CHAR(1)        -- 'A', 'C', 'X', 'P'... no legend anywhere
);

Every column was a confusion. That's the "detective work" I spoke about earlier.

I had to incorporate AI in multiple sections

I fed AI the schema, some sample rows, and the legacy procedures. In just a few minutes, the AI helped me draft the new table and a very useful transformation logic. That was grunt work engineers used to spend weeks on, and AI came to the rescue and completed it in a few days.

-- AI-drafted target table (Postgres)
CREATE TABLE orders (
  order_id      INT PRIMARY KEY,
  customer_name TEXT NOT NULL,
  order_date    DATE,
  amount_usd    NUMERIC(12,2),
  status        TEXT
);

-- AI-drafted transformation
INSERT INTO orders
SELECT ord_id,
       TRIM(cust_nm),
       TO_DATE(ord_dt, 'DDMMYY'),
       amt / 100.0,
       sts
FROM legacy.ord_mst;

However, although it looked clean on the surface, I noticed a problem.

I had my engineers run tasks manually to verify if the AI was 100% right

My team of engineers double-checked the business rules the AI couldn’t know. My engineers asked the finance team what sts really meant. Turns out 'X' meant canceled, and 'C' meant completed. And AI had guessed just the opposite. Now, that was quite a blunder. So, my engineers opened the legacy code structure to confirm the date format.

-- Engineer-written fix: real status meanings, confirmed with finance
CASE sts
  WHEN 'A' THEN 'active'
  WHEN 'C' THEN 'completed'
  WHEN 'X' THEN 'cancelled'
  WHEN 'P' THEN 'pending'
  ELSE 'unknown_review'   -- never guess, flag it
END AS status

That unknown_review line was a savior. Bad rows got instantly flagged instead of remaining mislabeled.

We made some mistakes and some important trade-offs

Now, I want to be honest here. Yes, AI made real mistakes in the project. It guessed the date format as DDMMYY, but the legacy format used MMDDYY. So, every March order became a May order. Speed led to compromise in multiple areas: I spent the time I saved on reviewing key sections. And it was worth it because we caught grave mistakes before go-live; otherwise, they could have led to even more blunders.

-- Caught in review: date format was wrong
-- Before (AI):       TO_DATE(ord_dt, 'DDMMYY')
-- After (engineer):  TO_DATE(ord_dt, 'MMDDYY')

Validating the final result

I ran reconciliation checks between the legacy structure and the newer format: row counts, totals, checks on real orders.

-- 1. Row counts must match
SELECT (SELECT COUNT(*) FROM legacy.ord_mst) AS old_rows,
       (SELECT COUNT(*) FROM orders)         AS new_rows;

-- 2. Money must match to the cent
SELECT (SELECT SUM(amt)/100.0 FROM legacy.ord_mst) AS old_total,
       (SELECT SUM(amount_usd) FROM orders)        AS new_total;

-- 3. Any rows that fell through the cracks?
SELECT * FROM orders WHERE status = 'unknown_review';

Only after our counts and totals matched did I touch production.

Giving the same conditions, would you pick custom ETL or AI?

Don't overthink it. Ask these six questions. We'll use our ord_mst migration as the yardstick.

What to Consider	Go Custom ETL If...	Go AI Migration If...
Data Sensitivity	A wrong number hits real money. Think bank records or health data. Our amt column fed finance reports. One bad total, and it’s a red flag.	If a value is off, nobody gets sued.
Codebase Condition	Columns like sts and ord_dt come with a data dictionary. Or the developer who built everything still works there.	It looks like ord_mst. Cryptic names. Zero docs. The developer doesn’t work there anymore. Let AI draft the first pass.
Hidden Business Rules	The meaning lives only in people's heads. Does 'X' mean cancelled or completed? AI guessed wrong on ours.	The rules are written down somewhere AI can read, like stored procedures or clear code comments.
Team Capacity	Your engineers have time to hand-write and review every mapping.	Your team's stretched thin. AI drafts in minutes. Engineers only review the risky parts.
Data Volume and Cost of Mistakes	You're moving millions of rows. One wrong date format (DDMMYY vs MMDDYY) can change the game for the worse and fixing that can be cumbersome later.	It's a fairly manageable dataset. You can re-run the load in minutes if something looks like a mistake.
Ability to Validate	You have no authentic old totals to compare against. You can't prove the output is right, so a human must prepare the logic.	You can reconcile easily. Row counts match. Totals match to the cent. Odd rows get flagged (like our unknown_review).

Now here's something you must notice. Most real projects won't land on one side of the table.

For example, look at ours. The data was sensitive (money). The codebase was a mess (no docs). The business rules were hidden (sts). But we could validate everything with simple reconciliation queries.

So what did I do? I split it. I let AI draft the tables and transformations. While I let my engineers own the status codes, the date format, and the final checks.

That's the pattern I followed. Wherever a row leaned left, I put a human there. Wherever it leaned right, I let AI run the tasks.

Here’s what I’d suggest

Don't pick a side. Pick a split.

Let AI do the monotonous, repetitive, low-risk tasks. Let your people own the sensitive logic and the risky parts. That's it. That's the whole playbook.

Imagine a self-driving car on the highway. Cruising and covering miles.

But you still keep a hand near the wheel. Don’t you?

Remember to go for blazing-fast where speed doesn't hurt you and get cautious where mistakes actually cost you. Once you do both, you stop choosing between broke and burned out.