An existing [Confirm: source system / platform & version] was planned to be migrated to a [Confirm: target system / platform & version]. The business driver was [Confirm: regulatory, modernization, consolidation, vendor change, cost, or other], and the migration covered [Confirm: scope — subset of tables, full database, specific business domain].

Because downstream consumers — reports, operational workflows and integrated systems — depended on the migrated data, data integrity became a primary quality concern. The migration workstream required an independent QE validation stream to establish, with evidence, whether the data arriving in the target matched expectations and transformation rules agreed with the business and the migration engineering team.

My engagement focused on the SQL-based validation part of that workstream: profiling the source, reconciling record counts between source and target, and performing field-level reconciliation against the agreed mapping.

Any legacy migration carries a predictable set of data-quality risks. The list below shows the risks a validation workstream should plan to cover. Which of these actually materialised on this project — and how we investigated them — I describe behind the placeholders in sections D and F.

• Records missing from targetRows in the source that never arrived — filtered, dropped, or lost in-flight.
• Unexpected record-count differencesCounts don’t reconcile at expected grain. Agreed exclusions and transformations have to be accounted for first.
• Duplicate business keysSame key appearing more than once in either side; corrupts joins and downstream aggregates.
• Field-level mismatchesRow exists in both sides but a given field disagrees — wrong value, wrong format, wrong casing.
• Incorrect transformations or mappingsMapping specification applied incorrectly, or the mapping itself is wrong against business intent.
• Null-handling and data-type differencesNulls in source becoming empty strings in target (or vice versa); precision, scale and date-type drift.
• Existing source-data quality issuesPre-existing dirty data that migrates faithfully but was never valid; needs explicit decision on carry-forward vs clean-up.
• Referential-integrity problemsChild records in target with no parent; orphaned foreign keys; broken lookup chains.
• Incorrect business aggregates / reportsRow-level data looks right but downstream sums, averages or counts are wrong because of grain, filter or join differences.

The activities below are what I personally executed on this project. Items beyond this list — migration architecture, ETL authoring, production cutover, programme management — belong to other workstreams or roles.

1. Hands-on data profiling Confirmed

   I ran SQL queries against the source database to establish a baseline of what the data actually looked like — completeness, distributions, nulls, duplicates, out-of-range values — before any comparison with the target. Profiling results informed the mapping conversations and flagged pre-existing quality issues that needed an explicit carry-forward or clean-up decision.

2. Source-to-target record-count validation Confirmed

   I compared row counts between source and target at the grain agreed with the migration team — whole table, by business key, by load date, by partition. I did not treat a count match as proof of correctness; mismatches became investigation items with a specific root-cause question attached to each.

3. Field-level data reconciliation Confirmed

   I compared corresponding fields in matched source and target records against the agreed mapping and transformation rules. The reconciliation accounted for data-type differences, null handling, date and precision rules, and any business transformations expected on the path from source to target.

4. Reviewing validation results & investigating discrepancies Confirmed, to the extent noted below

   Where SQL profiling and reconciliation surfaced discrepancies, I investigated them against the source data, the mapping specification and (where applicable) the ETL behaviour, and worked with the migration engineering team on the specific items I raised. [Confirm: ownership of defect triage, escalation path, final sign-off authority].

Illustrative SQL — not actual project queries

Query shapes a QE engineer would typically run during each layer. These are illustrative examples, not the actual queries used on this project; schema and table names are fictional.

-- Profile a key source column: completeness, cardinality, distribution
SELECT
    COUNT(*)                              AS total_rows,
    COUNT(customer_id)                    AS non_null_rows,
    COUNT(*) - COUNT(customer_id)       AS null_rows,
    COUNT(DISTINCT customer_id)         AS distinct_values,
    MIN(customer_id)                      AS min_val,
    MAX(customer_id)                      AS max_val
FROM   src_customers;

-- Expect zero-row result if counts match at the agreed grain
WITH src AS (
    SELECT 'customers' AS entity, COUNT(*) AS src_cnt FROM src_customers
    WHERE status IN ('ACTIVE', 'DORMANT')      -- agreed migration filter
), tgt AS (
    SELECT 'customers' AS entity, COUNT(*) AS tgt_cnt FROM tgt_customer
)
SELECT  src.entity,
        src.src_cnt,
        tgt.tgt_cnt,
        tgt.tgt_cnt - src.src_cnt AS delta
FROM    src JOIN tgt ON src.entity = tgt.entity
WHERE   src.src_cnt <> tgt.tgt_cnt;

-- Business key must be unique in target; any row returned = defect
SELECT  customer_id, COUNT(*) AS occurrences
FROM    tgt_customer
GROUP BY customer_id
HAVING  COUNT(*) > 1;

-- Join on the agreed business key; return any field-level mismatch
-- NULL-safe comparisons prevent false negatives when both sides are NULL
SELECT  s.customer_id,
        s.email       AS src_email,
        t.email       AS tgt_email,
        s.last_name   AS src_lname,
        t.last_name   AS tgt_lname,
        s.updated_at  AS src_updated,
        t.updated_at  AS tgt_updated
FROM    src_customers s
JOIN    tgt_customer  t ON s.customer_id = t.customer_id
WHERE   COALESCE(s.email,      '') <> COALESCE(t.email,      '')
   OR   COALESCE(s.last_name,  '') <> COALESCE(t.last_name,  '')
   OR   COALESCE(s.updated_at, '1900-01-01') <>
       COALESCE(t.updated_at, '1900-01-01');

-- Orphans: child rows in target whose parent didn't migrate
SELECT  o.order_id, o.customer_id
FROM    tgt_order o
LEFT   JOIN tgt_customer c ON o.customer_id = c.customer_id
WHERE   c.customer_id IS NULL;

Validation generated structured evidence — profiling summaries, source-vs-target count reconciliations, field-level diff reports and documented exceptions. Specific project numbers and the final acceptance decision are intentionally held back here and marked for confirmation.

To confirm in interview

• Tables / entities validated: [Confirm: count]
• Approximate record volume (if disclosable): [Confirm: order of magnitude]
• Types of discrepancies identified: [Confirm: mapping, transform, data-quality, source-side]
• How discrepancies were investigated / resolved: [Confirm: triage path and collaborators]
• Final acceptance outcome: [Confirm: outcome — passed, conditional, deferred, etc.]
• Lessons learned: [Confirm: two or three specific lessons from this engagement]

Data validation evidence does more than confirm individual records. In a migration programme it feeds defect triage, risk assessment, acceptance decisions and release-readiness reporting to leadership. Profiling results shape the mapping conversation; reconciliation reports gate the next cut-over rehearsal; exception logs become the backlog for the migration-engineering team and the business owners who sign off.

• SQL-based technical validationHands-on queries beat opinions. The data either reconciles or it doesn’t, and the reasons become inspectable.
• Structured reconciliation evidenceCount matches, field-level diffs and exception logs become the audit trail a release decision rests on.
• Risk-based testingScope validation effort against where mapping complexity, business criticality and historical data quality concentrate.
• Clear ownership of data discrepanciesEvery exception has a classification, an owner and a next step. “Looks wrong” is not a status.
• Evidence-based migration acceptanceAcceptance decisions turn on documented evidence, not on whether the ETL job ran green.