Data Migration, Conversion, and ETL — What I Actually Did, Across Two Eras

Sangeetha Kalia — Fri, 31 Jul 2026 19:26:58 +0000

Data Migration, Conversion, and Integration — What I Actually Worked On Across Two Eras

The first time I had to move data between two live production systems, I spent three days convinced the migration was clean — until the reconciliation reports ran wrong and we traced it back to a silent key collision at 2am. No error. No rollback. Just bad data sitting quietly in the target table.

That experience changed how I think about every data movement problem I've touched since.

Three words get used interchangeably in most engineering conversations. They shouldn't:

Migration is a one-time move — different system, different schema, run once, reconcile, archive the scripts.

Conversion is what happens inside a migration when the shape of the data has to change: types, keys, business rules, validation rules.

Integration is a continuous pattern — data keeps flowing because two systems stay alive over time.

This post is about how I've worked across all three — in real professional work, and in a hands-on portfolio project.

Before we start — a calibration

Not every app needs any of this.

My MERN school-management app, arichuvadi-clmgt, is a straightforward direct-updates system: board members, instructors, and students all read and write the same MongoDB, and there is nothing to migrate, no schema to convert, and no external system to integrate against.

If that describes your app, close this tab — you don’t have a data-movement problem, and adding middleware would only buy you complexity you don’t need.

The rest of this post is about the projects where the shape is genuinely harder — two migration/conversion stories drawn from real professional work, plus an integration project (sf_crm_app) I built on a Salesforce Dev org as a portfolio piece.

A quick scope note, because this matters:

the professional migration/conversion stories here are based on real company work
the schemas and sample table names are illustrative so I can preserve client anonymity
the original company codebases are not public
where I link a public Postgres repo, it should be read as a smaller public mimic of selected migration patterns, not as the full proprietary implementation

The one-line rule I want you to leave with:

Migrate once, when you’re moving between systems.

Convert inside a migration, when the shape has to change.

Integrate continuously, when both systems have to stay alive together.

Three different problems. Three different kinds of engineering discipline. Getting them mixed up is how teams overcomplicate a one-time migration or under-design an ongoing integration.

Chapter 1 — Data conversion in the legacy world: PL/SQL, cursors, and dump tables in a Sybase → Oracle amalgamation

A group I worked with had two systems — one running on Sybase, one on Oracle — and the business wanted them amalgamated into a single Oracle schema.

There was no middleware layer, no streaming pipeline, no managed orchestration platform. There was CSV, PL/SQL, and a lot of careful handling.

What we had was:

CSV files exported from Sybase
dump tables in Oracle — every column declared as VARCHAR2(4000), no constraints, no validations, no NOT NULL
PL/SQL scripts to read the dump tables and produce the real, typed, constrained rows in the destination tables

Why the ugly varchar-max dump tables?

Because when the boundary is strict, one bad row from the source can fail the whole load. When the boundary is loose, the load succeeds and the transformation becomes the place where problems surface — which is exactly where you want them to surface, under controlled exception handling, with a log you can inspect and rerun from.

The transformation loop

For each row we needed to:

Generate a composite key of the shape MMM + DDD + YYYYMMDD + SEQ
Apply business-logic rewrites such as status mapping, currency normalization, and department-code validation
Handle failures without stopping the batch

A simplified shape of that loop looked like this:

CREATE OR REPLACE PROCEDURE load_from_dump IS
    CURSOR c_dump IS SELECT * FROM stg_dump_customer;
    v_new_id     VARCHAR2(20);
    v_seq        NUMBER;
    invalid_dept EXCEPTION;
BEGIN
    FOR r IN c_dump LOOP
        BEGIN
            SELECT NVL(MAX(seq),0)+1 INTO v_seq
              FROM customer
             WHERE load_date = TRUNC(SYSDATE);

            v_new_id := r.master_code
                     || r.detail_code
                     || TO_CHAR(SYSDATE,'YYYYMMDD')
                     || LPAD(v_seq, 4, '0');

            IF r.dept_code NOT IN ('SALES','OPS','HR','FIN') THEN
               RAISE invalid_dept;
            END IF;

            INSERT INTO customer (customer_id, dept_code, ...)
            VALUES (v_new_id, r.dept_code, ...);

        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                INSERT INTO load_exceptions
                  VALUES (r.source_id, 'DUP_VAL_ON_INDEX',
                          'Composite key collision: '||v_new_id, SYSDATE);
            WHEN invalid_dept THEN
                INSERT INTO load_exceptions
                  VALUES (r.source_id, 'INVALID_DEPT',
                          'Unknown dept_code: '||r.dept_code, SYSDATE);
            WHEN OTHERS THEN
                INSERT INTO load_exceptions
                  VALUES (r.source_id, SQLCODE, SQLERRM, SYSDATE);
        END;
    END LOOP;
    COMMIT;
END;

The point is not the exact syntax. The point is the pattern:

loose ingest
strict transformation
explicit exception handling
rerun based on logged failures, not guesswork

What the log table was really for

The load_exceptions table was not a side effect. It was part of the actual deliverable.

After each run:

Group exceptions by reason code
Review them with the domain owner
Decide whether to fix the source, extend the mapping rules, or deliberately exclude the row
Rerun only the resolved cases

That is what made the conversion survivable. Not elegance — discipline.

The lasting lesson

This is what data conversion looked like when business logic lived inside the database.

You wrote the transformation close to the data, ran it once, reconciled it, and archived it.

It was not glamorous, but it was honest and operationally clear.

For a one-time amalgamation, that was the right shape of solution.

Chapter 2 — Data migration in the modern world: consolidating two teams onto one Postgres schema

Fast-forward to a later project. Different tooling, same underlying problem: two teams, two datasets, one target schema.

This chapter is based on a real company migration in a Postgres environment, but the examples here are anonymized and simplified for public writing.

A separate public repo, dataMigration, shows a much smaller Supabase/Postgres mimic of some of the same migration ideas. It is a reference implementation, not the original company codebase or the full migration scope.

Two teams. Different assumptions from day one:

Team A used email address as the primary key across much of its data model. Types were inconsistent and naming conventions varied.
Team B used UUID as the primary key, cleaner naming, and more standardized typing.

The business wanted a single Postgres schema.

Before any migration script could be trusted, we first had to answer the harder question:

What should the target schema actually be?

Phase 1 — schema design analysis

The first phase was not coding. It was analysis.

We walked the tables, checked overlaps, identified duplicates, and agreed on a unified target model.

The rules we settled on looked like this:

UUID everywhere as the target primary-key strategy
standardized naming
data-type standardization
canonical entities instead of duplicate business tables

Halfway through that schema review, two additional requirements came in from leadership:

soft delete on every entity
audit logging for field-level traceability

Those sound small when written as bullets. They are not small.

They affect table design, repository logic, update paths, delete semantics, and validation.

I was personally involved in the requirement discussions, schema review, migration planning, and technical direction around those changes, while implementation was carried out across the DB team.

Phase 2 — dependency-ordered migration

Once the target schema was agreed, the data move followed a strict dependency order:

phase 1:  users
phase 2:  categories
phase 3:  addresses
phase 4:  products
phase 5:  orders
phase 6:  order_items
phase 7:  reviews

The important idea was simple:

parents first
children second
grandchildren after that
foreign keys resolved through deterministic mappings
reruns must be safe

That is the difference between a migration and a pile of hopeful SQL files.

Phase 3 — patterns that made the migration survivable

A. Build the ID mapping once

New UUIDs should be minted in exactly one place and reused downstream through mapping logic. Otherwise you invite duplicate IDs and orphaned children.

A simplified shape looked like this:

CREATE TEMP TABLE tmp_user_map AS
SELECT
    LOWER(TRIM(u.email)) AS old_key,
    gen_random_uuid()    AS new_id,
    COALESCE(NULLIF(TRIM(u.first_name), ''), 'Unknown') AS first_name,
    COALESCE(NULLIF(TRIM(u.last_name),  ''), '')        AS last_name,
    CASE UPPER(TRIM(u.role))
        WHEN 'A' THEN 'ADMIN'
        WHEN 'C' THEN 'CUSTOMER'
        WHEN 'S' THEN 'STAFF'
        ELSE 'CUSTOMER'
    END::user_role AS role,
    CASE
        WHEN u.phone ~ '^\+?[0-9\s\-()]{7,20}$' THEN u.phone
        ELSE NULL
    END AS phone,
    u.deleted_at AS deleted_at
FROM staging.team_a_users u
WHERE u.email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

There are several important patterns compressed into that one step:

LOWER(TRIM(...)) for key normalization
NULLIF(TRIM(...), '') to handle empty-string cleanup
COALESCE(...) for defaults
CASE for enum normalization
regex validation for data shape
carrying deletion state forward instead of losing it

B. Make reruns safe

The load had to be idempotent.

INSERT INTO app.users (id, email, first_name, last_name, role, phone, deleted_at, created_at)
SELECT new_id, old_key, first_name, last_name, role, phone, deleted_at, NOW()
FROM tmp_user_map
ON CONFLICT (email) DO UPDATE
SET first_name = EXCLUDED.first_name,
    last_name  = EXCLUDED.last_name,
    role       = EXCLUDED.role,
    phone      = EXCLUDED.phone,
    deleted_at = EXCLUDED.deleted_at;

This is one of the biggest differences between “a script that runs” and “a migration you can trust.”

If a batch is interrupted, you need a path to rerun without duplicating or corrupting data.

C. Respect dependency order in child loads

Once parent entities existed, child tables could be loaded by joining against previously built maps.

The exact table names and domain details here are anonymized, but the pattern stayed the same:

resolve parent IDs first
join through mapping tables
allow controlled fallback where needed
avoid silently losing child rows

D. Reconciliation matters as much as the load

Each phase needs reconciliation artifacts:

expected source counts
loaded target counts
validation failures
unresolved references
rows held for second-pass cleanup

In other words, the migration is not finished when the insert completes.

It is finished when the business can trust the reconciliation.

One detail that matters more than it looks: empty strings vs nulls

One of the recurring cleanup issues in migrations is how systems represent “missing” values.

For example, legacy text columns often contain '' where the target logic expects NULL.

That matters because:

casts can fail
uniqueness can behave differently
joins can behave differently
validation can behave differently

A safe pattern looked like this:

CASE
    WHEN u.dob IS NULL OR trim(u.dob) = '' THEN NULL
    ELSE u.dob::date
END AS dob

This kind of cleanup is small in code but large in effect.

One careful CASE can save an entire batch from failing on dirty source data.

What made this migration workable

Four principles carried the project:

Loose ingest, strict transformation
A single mapping source of truth
Dependency-ordered loading
Idempotent reruns

Everything above describes a one-time migration.

Once the consolidation was complete, the scripts were archived and the unified schema became the system of record.

Chapter 3 — Seven practical migration techniques I keep coming back to

If I strip away all project-specific detail, the migration side keeps collapsing into a few repeatable patterns.

1. Preserve valid UUIDs, generate new ones for invalid IDs

CASE
    WHEN u.id ~* '^[0-9a-fA-F-]{36}$' THEN u.id::uuid
    ELSE gen_random_uuid()
END AS id

Why it matters: don’t rewrite a valid key unnecessarily.

2. Normalize empty strings before casting

CASE
    WHEN u.dob IS NULL OR trim(u.dob) = '' THEN NULL
    ELSE u.dob::date
END AS dob

Why it matters: dirty text columns should not kill the batch.

3. Use idempotent upsert patterns

INSERT INTO app.users (email, name, phone, ...)
SELECT ...
ON CONFLICT (email) DO UPDATE SET
    name  = EXCLUDED.name,
    phone = EXCLUDED.phone;

Why it matters: partial failure becomes recoverable.

4. Preserve legacy IDs when they help reconciliation

INSERT INTO app.categories (id, name, category_uid)
SELECT
    gen_random_uuid(),
    c.name,
    c.id
FROM legacy.categories c;

Why it matters: migration is easier to validate when you keep a bridge back to the source.

5. Use placeholder foreign keys when a second pass is required

INSERT INTO app.categories (id, name, parent_id, category_uid)
SELECT gen_random_uuid(), c.name, NULL::uuid, c.id
FROM legacy.categories c;

Why it matters: not every relationship can be resolved in one pass.

6. Resolve foreign keys through deterministic joins

INSERT INTO app.addresses (id, user_id, street, city, ...)
SELECT
    gen_random_uuid(),
    du.id,
    a.street, a.city, ...
FROM legacy.addresses a
LEFT JOIN app.users du
    ON du.email = a.user_id;

Why it matters: children should attach to already-migrated parents in a traceable way.

7. Store money in integer cents, not floating-point dollars

(p.original_price * 100)::bigint AS original_price_cents

Why it matters: exact arithmetic beats cleanup later.

Chapter 4 — Integration when both systems stay alive

Migration and conversion are one-time efforts.

Integration is different.

Data keeps moving because the systems on both sides remain active.

My portfolio project sf_crm_app demonstrates that pattern as a MuleSoft ↔ Salesforce operational integration.

Scope, explicitly: In this repo I built the MuleSoft-to-Salesforce slice — the MuleSoft flow (HTTP listener, OAuth2, DataWeave transform, PATCH upsert) and the Salesforce side (Apex triggers, LWC, custom objects, and tests). The upstream side is simulated by an HTTP payload posted to MuleSoft, not a live external system.

The Salesforce data model

Three custom objects, external-ID enabled for rerunnable upserts:

Student__c        GPA, Status, Student_External_Id__c, enrollment history
Course__c         Capacity, Credits, Department, Active flag
Enrollment__c     junction ↔ Student__c ↔ Course__c

The MuleSoft flow

The flow handles:

receiving a simulated upstream payload
authenticating into Salesforce
transforming field names and shapes
upserting by External ID
logging per record so one bad record does not kill the whole batch

A simplified shape of the transform looked like this:

%dw 2.0
output application/json
---
{
  Student_External_Id__c: payload.STU_ID,
  FirstName__c:           payload.FIRST_NAME,
  LastName__c:            payload.LAST_NAME,
  Status__c:              payload.STATUS default "Active",
  GPA__c:                 payload.GPA as Number { format: "##.##" }
}

The core idea is simple: the external system and Salesforce do not speak the same field language, so the integration layer translates.

Business rules still belong to the owning system

Even after the record lands in Salesforce, some rules still belong there because Salesforce owns the current operational state.

A simplified Apex example:

public with sharing class EnrollmentTriggerHandler extends TriggerHandler {
  public override void beforeInsert() {
    Set<Id> courseIds = new Set<Id>();
    for (Enrollment__c e : (List<Enrollment__c>) Trigger.new) courseIds.add(e.Course__c);

    Map<Id, Course__c> byId = new Map<Id, Course__c>(
      [SELECT Id, Capacity__c, (SELECT Id FROM Enrollments__r)
         FROM Course__c WHERE Id IN :courseIds WITH USER_MODE]
    );

    for (Enrollment__c e : (List<Enrollment__c>) Trigger.new) {
      Course__c c = byId.get(e.Course__c);
      if (c.Enrollments__r.size() >= c.Capacity__c) e.Status__c = 'Waitlist';
    }
  }
}

This is the same broad lesson as the earlier conversion story: data movement alone is not enough.

You also need to know where the business rule belongs.

What this integration project does and does not cover

What it does include:

MuleSoft flow design
DataWeave transformation
External-ID upsert into Salesforce
Apex business logic
LWC components
tests

What it does not include:

a live upstream production system
a full enterprise integration landscape
every system that might sit around the CRM in a real institution

That boundary matters.

Public portfolio code should show the slice you built, not pretend to be the whole organization.

Chapter 5 — What stays the same across all three

The tools change.

The engineering concerns don’t.

Whether the work is a legacy conversion, a one-time migration, or an ongoing integration, I keep coming back to the same four constants.

1. Prevent data loss

know where partial failures land
know what commits and what rolls back
know how to rerun safely

2. Protect data integrity

respect foreign keys
validate target shape
do not trust dirty source data

3. Make failures visible

exception logs
reconciliation reports
explicit review paths

4. Design for idempotency

safe reruns
conflict-aware writes
deterministic mappings

The names of the tools change.

The job does not.

A PL/SQL exception log, a Postgres reconciliation pass, and a rerunnable External-ID upsert are all solving the same deeper problem:

never let failure become invisible

never let a retry become corruption

Five review questions I bring to every design discussion

Regardless of the stack, I want clean answers to these:

Where does a partial failure land?
Is the write safe to repeat?
Are the relationships enforced or merely assumed?
Where do unexpected exceptions surface?
What is the actual system of record?

If a design is weak on those five, it is weak no matter how modern the tooling looks.

Conclusion — the tools change, the engineering does not

Migration is a one-time move.

Conversion reshapes the data inside that move.

Integration keeps data flowing when both systems remain alive.

Confusing those three leads to expensive mistakes:

building a long-lived integration for a one-time move
treating ongoing operational sync like a one-off script
ignoring reconciliation because “the insert succeeded”

Across all three, the engineering discipline stays the same:

Never lose a record.

Never break a relationship.

Never hide an exception.

Never write a load you can’t safely rerun.

Get those right, and the architecture gets easier.

Public reference repos

Integration portfolio project: https://github.com/Sanganu/sf_crm_app
Direct-updates counter-example (MERN, no migration/integration layer needed): https://github.com/Sanganu/arichuvadi-clmgt
Postgres migration reference mimic (a smaller public illustration of selected migration patterns): https://github.com/Sanganu/dataMigration

If you've dealt with a messy migration or integration — I'd genuinely like to hear what patterns you reached for. Drop it in the comments.

Written by Sangeetha Kaliaperumal · working across PL/SQL, Postgres, MERN, and the Salesforce ecosystem.

DEV Community: Sangeetha Kalia