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

Three words that engineers use interchangeably and shouldn't:
Migration is a one-time move — different system, different schema, run once, archive the scripts.
Conversion is what happens inside a migration when the shape of the data has to change (types, keys, business rules).
ETL is a continuous integration pattern — extract, transform, load — running forever between two systems that both stay alive.

This post is about how I've done all three, in real projects, across two eras of tooling.

---

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 ETL against. If that describes your app, close this tab — you don't have a data-movement problem, and adding Talend or MuleSoft would only cost you complexity you don't need.

The rest of this post is about the projects where the shape is genuinely harder — two migration/conversion war stories from real professional work, plus a MuleSoft ↔ Salesforce integration project (sf_crm_app) I built on a Salesforce Dev org as a portfolio piece. The war-story schemas are illustrative — generic e-commerce table names — to preserve client anonymity, but the patterns and techniques are exactly what shipped.

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.
ETL forever, when both systems have to stay alive together.

Three different problems. Three different tools. Getting them mixed up is why so many teams over-engineer a migration by dropping Talend into it forever when they only needed to run it once.

---

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 MuleSoft, no Talend, no CDC pipeline. There was CSV, PL/SQL.

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. The dump table was designed to accept anything so the load never failed at the boundary.

• 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 Sybase fails the whole batch, and you spend the night debugging character encoding. When the boundary is loose, the load always succeeds and the transformation becomes the place where problems surface — which is exactly where you want them to surface, under a WHEN OTHERS handler, not on a shared drive at 2 AM.

The transformation loop

For each row we needed:

1. Generate a composite key of the shape MMM + DDD + YYYYMMDD + SEQ — three chars from the master category, three from the detail, the load date, and a per-day sequence.
2. Apply business-logic rewrites (status mapping, currency normalisation, department codes) using explicit cursors so we could log exactly which row we were on.
3. Handle failures without stopping the batch. This is where PL/SQL exception handling actually earns its keep — DUP_VAL_ON_INDEX, RAISEd user-defined exceptions, and WHEN OTHERS as the safety net.

Shape of what the loop looked like:

CREATE OR REPLACE PROCEDURE load_from_dump IS
    CURSOR c_dump IS SELECT * FROM stg_dump_customer;   -- explicit cursor
    v_new_id     VARCHAR2(20);
    v_seq        NUMBER;
    invalid_dept EXCEPTION;                              -- user-defined 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);

            -- composite key: MAST + DETAIL + YYYYMMDD + SEQ
            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;

What the log table was really for

The load_exceptions table was the product of the load, not a side effect. After each run:

1. Query load_exceptions grouped by reason code — you immediately see whether the failure mode is data-driven (INVALID_DEPT) or key-driven (DUP_VAL_ON_INDEX) or a truly novel error caught by WHEN OTHERS.
2. Sit with the domain owner and resolve them — either fix the source, add a mapping rule, or accept the row as legitimately excluded.
3. Re-run only the resolved rows against the dump table.

This is the pattern that makes legacy data conversion survivable: loose ingest, strict transformation, exhaustive exception logging, human-in-the-loop resolution. It is not glamorous, but it is honest, and it kept a very messy amalgamation on schedule.

The forward-linking insight

This is what data conversion looked like in the era where business logic lived inside the database itself. You wrote the conversion in the same language the source system used — PL/SQL. You ran it once. You archived the scripts. The "ETL platform" was a directory of .sql files, a shared drive, and a whiteboard.

The reason I open with this story is that engineers who have not lived through it tend to reach for Talend or MuleSoft the moment they see "two databases." For a one-time amalgamation — no ongoing sync, no live coexistence — PL/SQL scripts + dump tables + a log table are frequently the right tool. Talend earns its price when two systems have to stay alive and keep talking to each other. It does not earn its price for a one-time move.

---

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

Fast-forward a decade. Different project, same fundamental problem — merging two datasets into one schema — with entirely different tooling. This story is what a migration looks like when you own Postgres on both sides.

Two teams. Different assumptions from day one:

• Team A — used email address as the primary key across most entities. Types were inconsistent: some columns NUMBER, some ENUM-shaped text, some plain VARCHAR with no domain. Naming conventions varied per developer.
• Team B — used UUID as the primary key, standard snake_case naming, clean types.

The business wanted a single Postgres schema. Before we could even think about writing migration scripts, we had to answer: what does the target schema actually look like?

Phase 1 — schema-design analysis (the unglamorous part)

My team walked every table on both sides, checked for duplicates, and produced a proposed unified schema. It took a few iterations. Rules we settled on:

• UUID everywhere as the primary key — Team B's convention won.
• Standardised naming — snake_case, singular table names, id / created_at / updated_at on every table.
• Data-type standardisation — enums promoted to real Postgres ENUM types with explicit domains; number-vs-text ambiguity resolved per column with the domain owner.
• No duplicates — where both teams had a customer table, we merged into one canonical entity.

Halfway through the schema review, I had a working session with the CTO. He raised two requirements the team hadn't planned for:

1. Soft-delete on every entity — a deleted_at timestamp column plus updated queries that respect it.
2. Audit logging — an append-only log table capturing who changed what, when, and the before/after value.

Both requirements sound small but they change every INSERT/UPDATE/DELETE path in the codebase. I picked them up and guided the team through the retrofit — schema changes, Prisma model updates, repository-layer wrappers to honour deleted_at, a generic modularized code at backend level as per the CTO requirements.

Phase 2 — dependency-ordered migration

Once the schema was agreed, the actual data move ran in a strict, dependency-ordered set of phases:

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

Every phase generates its own temp mapping table so subsequent phases can rewrite foreign keys deterministically. This is the discipline that separates a working migration from a shell script that half-fails and leaves inconsistent state.

Phase 3 — the patterns that carried the project

A. Build the ID mapping once, up front. New UUIDs are minted only inside the mapping table, and every downstream FK rewrite joins against it. Do this any other way and you'll produce duplicate UUIDs and orphan children.

BEGIN;

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,}$';

CREATE UNIQUE INDEX ON tmp_user_map(old_key);

Note the patterns compressed into that one temp table:

• LOWER(TRIM(...)) for case-insensitive natural key,
• NULLIF(TRIM(...), '') to normalise empty strings to NULL,
• COALESCE(...) for defaults,
• CASE for enum normalisation (single-letter codes → real enum),
• regex validation (~*) for email and phone shape — bad rows silently drop,
• soft-delete carried through so migrated rows keep their history.

B. Load master rows with idempotent upsert. The script must be safe to run twice.

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;

The EXCLUDED keyword is the modern Postgres idiom for "the row I was trying to insert" — combined with ON CONFLICT DO UPDATE, this is one clean statement doing what would previously have taken PL/SQL exception handling for DUP_VAL_ON_INDEX.

C. Child inserts use the map — currency in cents, slugs from names.

INSERT INTO app.products (id, category_id, created_by, name, slug, price_cents, deleted_at, created_at)
SELECT
    gen_random_uuid(),
    (SELECT new_id FROM tmp_cat_map  WHERE old_key = p.category_slug),
    (SELECT new_id FROM tmp_user_map WHERE old_key = LOWER(TRIM(p.created_by_email))),
    p.name,
    LOWER(REGEXP_REPLACE(p.name, '[^A-Za-z0-9]+', '-', 'g')),   -- slug from name
    ROUND(p.price_dollars * 100)::BIGINT,                        -- dollars → cents
    p.deleted_at,
    COALESCE(p.created_at, NOW())
FROM staging.team_a_products p
WHERE p.name IS NOT NULL
ON CONFLICT (slug) DO UPDATE
SET name        = EXCLUDED.name,
    price_cents = EXCLUDED.price_cents,
    deleted_at  = EXCLUDED.deleted_at;

Currency stored as integer cents (not decimal dollars) is a small choice that pays back forever — no floating-point rounding, no locale ambiguity, sums are exact.

D. Grandchildren respect the order. Once orders exist, order_items migrate joining both the order map and the product map — strict master → child → grandchild ordering, with NOT EXISTS for rerunnability.

INSERT INTO app.order_items (id, order_id, product_id, qty, unit_price_cents)
SELECT
    gen_random_uuid(),
    om.new_id,                     -- from tmp_order_map
    pm.new_id,                     -- from tmp_product_map (may fall back to NULL)
    oi.qty,
    ROUND(oi.unit_price * 100)::BIGINT
FROM staging.team_a_order_items oi
JOIN tmp_order_map   om ON om.old_key = oi.order_id::TEXT
LEFT JOIN tmp_product_map pm ON pm.old_key = oi.product_slug
WHERE NOT EXISTS (
    SELECT 1 FROM app.order_items x
    WHERE x.order_id   = om.new_id
      AND x.product_id = pm.new_id
);

COMMIT;

The NOT EXISTS guard is what makes the load rerunnable. The LEFT JOIN on products (instead of INNER JOIN) is deliberate — we'd rather migrate an order line with a null product reference and clean it up in a second pass than silently drop the line.

Phase 4 — post-migration reconciliation

Every phase writes to a reconciliation view — a lightweight audit of counts and orphaned refs:

• expected_from_source vs loaded_into_target per table,
• rows that failed a regex validation (email, phone, UUID shape),
• children with NULL FKs pending a second pass.

Sitting with the domain owner and walking the reconciliation view is the modern equivalent of the PL/SQL load_exceptions table from Chapter 1. Same discipline, different toolkit. Same insight: the exceptions and the reconciliation are the product, not a side effect.

What made this survivable

Four principles carried the project across the finish line:

1. Loose ingest, strict transformation. Staging tables tolerate anything; the temp maps enforce the target contract.
2. The map is the source of truth. New UUIDs are minted once, in the mapping table, and every downstream write joins that map. No duplicate keys, no orphan children.
3. Strict master → child → grandchild ordering with fallback strategies at each join.
4. Idempotent upserts everywhere. ON CONFLICT DO UPDATE ... EXCLUDED makes reruns safe.

Everything above ran once. Once the two teams' data was consolidated, the scripts were archived. From that day on, the app writes and reads directly from the unified Postgres schema.

If instead we had decided to keep both Team A's and Team B's databases live and sync between them forever, we would have needed a MuleSoft or Talend layer running continuously, and every schema change on either side would have become a coordinated release. We chose migration precisely to avoid that recurring cost.

---

Chapter 3 — The seven techniques, decoded

If you take away nothing else from Chapters 1 and 2, take these seven patterns. Each one is a single decision that separates a migration that finishes on schedule from one that grinds through six months of debugging.

1. Preserve valid UUIDs, generate new ones for invalid IDs. Not every legacy row has a broken ID. If the source already has a valid UUID (36 characters, hex + dashes), reuse it. Only mint a new UUID for rows where the source ID is missing or malformed.

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

Why it matters: preserves referential integrity where it already exists. Cheaper than regenerating everything and rewriting every FK downstream.

2. Empty strings as NULL for text-to-date casts. Legacy text columns very often store '' where you'd expect NULL. ''::date throws an error and kills the batch.

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

Why it matters: one CASE saves the entire load from failing on the first dirty row.

3. Idempotent upsert with ON CONFLICT ... EXCLUDED. Every insert should be safe to run twice.

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

Why it matters: the migration becomes rerunnable. Partial failures are fixable. Stakeholders trust the script.

4. Keep the legacy integer ID alongside the new UUID. When the old schema used integer PKs and the new one uses UUIDs, don't throw the old key away — store it in a legacy_uid column.

INSERT INTO app.categories (id, name, category_uid)
SELECT
    gen_random_uuid()   AS id,
    c.name,
    c.id                AS category_uid   -- old integer preserved
FROM legacy.categories c;

Why it matters: FK rewrites become trivial JOIN ... ON dc.category_uid = p.subcategory_id.

5. NULL::uuid placeholder for foreign keys that need a second pass. Not every FK can resolve on the first pass. Leave them NULL, then run a second UPDATE pass once every table exists.

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

-- Pass 2: resolve parent_id
UPDATE app.categories child
SET parent_id = parent.id
FROM app.categories parent, legacy.categories lc
WHERE child.category_uid = lc.id
  AND parent.category_uid = lc.parent_id;

Why it matters: forward-references and self-references force phased migration. Trying to resolve them in one pass produces circular-dependency failures.

6. Cross-schema FK resolution via LEFT JOIN on a natural key. The old system stored the user's email in a user_id field. The new system needs the actual user UUID. Because users were migrated first, they now exist keyed by email.

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

The LEFT JOIN (not INNER JOIN) is deliberate: if a user record didn't survive validation, we'd rather migrate the address with a NULL user_id and clean it up later than silently drop the address.

Why it matters: this is the core mechanic of every cross-schema FK rewrite.

7. Currency in integer cents, not decimal dollars. Money math on floats is broken. 0.1 + 0.2 does not equal 0.3 in floating point.

(p.original_price * 100)::bigint AS original_price_cents,
(p.listing_price  * 100)::bigint AS listing_price_cents

Why it matters: eliminates rounding errors forever, matches the pattern used by every production payment processor.

The mental model, in six moves

Every phase of a real migration is just six moves, repeated per table:

1. (optional) build tmp_<table>_map  (old_id → new_uuid)
2. INSERT INTO target_table (id, ..., legacy_uid, ...)
   SELECT
       CASE valid_uuid THEN reuse ELSE gen_random_uuid(),
       cleaned + validated columns (regex, NULLIF+TRIM, CASE),
       JOIN previously-migrated parents ON natural key,
       NULL::uuid for FKs pending phase 2,
       cents = dollars * 100 for money
   FROM source_table
   WHERE row passes validation
   ON CONFLICT (natural_key) DO UPDATE SET ... EXCLUDED

And the order is where discipline shows: users → categories → addresses → products → orders → order_items → reviews. Each downstream phase joins the already-migrated upstream to resolve its FKs. Break that order and you fight your own foreign keys.

---

Chapter 4 — Enterprise integration: when two systems stay alive together

Migration and conversion are one-time projects. Integration is forever. When two systems have to keep talking to each other — the source of truth for enrollment stays in one place, and the CRM for advising and dashboards stays in another — you cannot solve it with a one-time script. You need a middleware layer that translates continuously.

This is where MuleSoft and Talend earn their license fees.

My portfolio project sf_crm_app implements this pattern: a MuleSoft flow that receives a Banner-shaped payload and upserts records into Salesforce.

Scope, explicitly: In this repo I built the MuleSoft ↔ Salesforce slice — the MuleSoft flow (HTTP listener, OAuth2, DataWeave transform, PATCH upsert) and the Salesforce side (Apex triggers, LWC, custom objects, tests). The Banner side is simulated by an HTTP payload posted to MuleSoft, not a real Ellucian instance. Azure SQL Replica and DIM/FACT tables are the broader industry context this pipeline sits inside — most large universities also run a nightly warehouse leg with Talend or Azure Data Factory — but that leg is not part of the repo. It's referenced to show where Talend earns its price, not because I built it.

The Salesforce data model

Three custom objects, external-ID enabled for idempotent upsert from the outside world:

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 (work_flow_Stu_Enro_Cour.xml)

Five things no framework does for free:

1. HTTP listener accepts the (simulated) Banner payload,
2. OAuth2 client-credentials handshake into Salesforce,
3. DataWeave 2.0 transform (Banner field shape → Salesforce field shape),
4. PATCH upsert by External ID so re-runs are idempotent,
5. foreach with per-record logging so a bad row does not kill the batch.

%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: "##.##" }
}

Notice the shape of the mapping: on the left, Salesforce API names (Student_External_Id__c, FirstName__c). On the right, external field names (STU_ID, FIRST_NAME) that a Banner or Banner-like system would emit. The middleware exists precisely because those two sides use different names for the same concept.

The Apex trigger that enforces business rules on the Salesforce side

Even after the record lands in Salesforce, business rules that live inside the CRM (capacity, waitlist, GPA-based status) run in Apex. These rules cannot live in the middleware — MuleSoft has no idea how many students are enrolled in a course right now. Only Salesforce knows.

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 capacity/waitlist rule as the PL/SQL cursor in Chapter 1 — implemented a second time, in a second language, because Salesforce owns its own writes. That duplication is exactly what "reconciling multiple systems of record" costs. There is no way to avoid it. There is only more or less discipline about where the rule lives.

---

Chapter 5 — MuleSoft vs Talend

sf_crm_app uses MuleSoft for the (simulated Banner) → Salesforce hop because that hop is API-shaped and near real-time. Talend plays the same integration role but shines on the other side of a large data platform — nightly loads into an Azure DIM/FACT warehouse, deduping, data-quality rules, governance and lineage. They are cousins, not rivals — a full enterprise platform usually has both.

Pick MuleSoft when	Pick Talend when
Real-time API orchestration	Batch loads into DIM/FACT warehouses
SaaS connectors matter more than DWH loaders	Heavy data-quality + governance requirements
Event-driven traffic (not batch)	ELT into cloud DWH (Snowflake, Synapse, BigQuery)
The integration is between apps	The integration is between an app and analytics

The rule of thumb: if the target is another operational system, lean MuleSoft; if the target is a warehouse, lean Talend.

---

Chapter 6 — What stays the same across all three (migration, conversion, ETL)

Zoom out from the case studies and something quieter shows up: the tools change, the engineering concerns don't. Whether you are writing a PL/SQL WHEN OTHERS handler in a Sybase → Oracle conversion, a Postgres ON CONFLICT DO UPDATE ... EXCLUDED in a schema consolidation, or a MuleSoft PATCH upsert with a DataWeave transform, you spend your time on the same four problems.

The four engineering constants

Concern	Legacy conversion (PL/SQL)	Modern migration (Postgres)	Continuous integration (MuleSoft / Talend / SF)
1. Data-loss prevention	COMMIT/ROLLBACK boundaries, load_exceptions table, dump-table pattern so bad rows never fail the load	Staging schemas, NOT EXISTS guards, reconciliation views, WAL replication	Retry queues, dead-letter queues, at-least-once delivery, message durability
2. Data integrity	Constraints, unique indexes, PRAGMA EXCEPTION_INIT, master → child ordering	Foreign keys, UNIQUE constraints, legacy_uid cross-references, dependency-ordered phases	External ID upsert, PATCH semantics, master-detail relationships
3. Exception handling	WHEN DUP_VAL_ON_INDEX, WHEN OTHERS, user-defined exceptions, RAISE_APPLICATION_ERROR, exception log tables	try/catch, error middleware, typed error responses, reconciliation views	AuraHandledException in Apex, MuleSoft on-error-continue / on-error-propagate
4. Idempotency	Guarded inserts, NOT EXISTS checks, sequence-based composite keys	ON CONFLICT ... DO UPDATE ... EXCLUDED, unique keys on natural identifiers, temp mapping tables	PATCH upsert by External ID (idempotent by design), correlation IDs

The interesting thing is that the names change but the intent doesn't:

• DUP_VAL_ON_INDEX in PL/SQL, ON CONFLICT DO UPDATE in Postgres, and PATCH upsert by External ID in Salesforce are the same idea wearing three different costumes. All three say: "this operation must be safe to repeat."
• A load_exceptions table in an Oracle conversion, a reconciliation view in a Postgres migration, and a MuleSoft dead-letter queue are the same idea: never let a failure disappear silently.
• Master → child → grandchild ordering in PL/SQL, dependency-ordered phases in Postgres, and External ID cascades in Salesforce are the same idea: resolve parents before children, or you'll fight your own foreign keys.

The five things I watch for in every design review

Regardless of the era or the stack, there are five things I run past every design conversation. If any of them is missing, the ticket goes back:

1. Where does a partial failure land? If the batch dies at row 4,732 of 10,000, do the first 4,731 stay written or roll back? Is there a manifest of what failed? Can I re-run just the failed rows?
2. Is every write idempotent? If the same event fires twice — network retry, message replay, user double-click, cron overlap — do I end up with one row or two?
3. Are foreign keys real constraints, or wishes in a comment? If a child row references a parent that doesn't exist, does the database catch it, or does the app find out at read time?
4. Where does an unexpected exception surface? Log-only? User-facing? Alert? Silent swallow? Who is responsible for looking at it, and how quickly?
5. What does "one system of record" actually mean here? Which table, in which schema, on which server, is the authority for this piece of data? Every derived cache, replica, warehouse, and integrated CRM has to point back to that answer.

If a system has good answers to those five, the architecture on top of it almost doesn't matter. If a system has bad answers, no amount of framework churn will save it.

---

Conclusion — the tools change, the engineering does not

Migration is a one-time move. Sybase → Oracle. Two Postgres schemas into one. You script it, run it, reconcile it, archive it, and you never run it again. Migration is a project.

Conversion is what happens inside a migration when the shape of the data has to change — text-to-date casts, dollars-to-cents, single-letter codes to enum values, integer PKs to UUIDs. Conversion is where the CASE statements, the regex validation, the NULLIF+TRIM normalisation and the temp mapping tables live.

ETL is continuous. Banner → MuleSoft → Salesforce, or Banner → Talend → DIM/FACT warehouse, runs every day, forever, because both sides of the pipeline stay alive. ETL is an operating cost.

Confusing these three is the most expensive mistake I keep watching engineers make. Teams drop Talend into a migration and pay a license forever for a job that should have been a one-time PL/SQL script. Or they hand-roll ETL as a nightly cron and call it a "migration" until the nightly cron becomes the system of record.

Two eras, three problems, one rule for choosing:

Migrate once, when you're moving between systems.
Convert inside a migration, when the shape has to change.
ETL forever, when both systems have to stay alive together.

And four constants that don't care which of the three you're doing:

Never lose a record. Never break a relationship. Never swallow an exception. Never write a non-idempotent load.

Get those four right, in whichever era you're working in, and the architecture on top almost picks itself.

---

Source repos:

• MuleSoft ↔ Salesforce integration case study: https://github.com/Sanganu/sf_crm_app
• Direct-updates counter-example (MERN, no migration/ETL needed): https://github.com/Sanganu/arichuvadi-clmgt

---

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