Data Transformation Testing

-- Source: customers.name (full name in one column)
-- Target: customers.first_name, customers.last_name

-- Test 1: Normal two-word names
SELECT s.customer_id, s.name,
       t.first_name, t.last_name,
       CONCAT(t.first_name, ' ', t.last_name) AS reconstructed
FROM source_db.customers s
JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE s.name != CONCAT(t.first_name, ' ', t.last_name)
LIMIT 20;

-- Test 2: Names with more than two words
-- "Aamir Khan Shah" — where does last name start?
SELECT s.customer_id, s.name,
       t.first_name, t.last_name
FROM source_db.customers s
JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE LENGTH(s.name) - LENGTH(REPLACE(s.name, ' ', '')) > 1;

-- Test 3: Single-word names (no space)
SELECT s.customer_id, s.name,
       t.first_name, t.last_name
FROM source_db.customers s
JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE s.name NOT LIKE '% %';

-- Source: date_of_birth stored as VARCHAR "15-01-1990"
-- Target: date_of_birth as DATE type

-- Test 1: Check for invalid dates that could not convert
SELECT customer_id, date_of_birth
FROM target_db.customers
WHERE date_of_birth IS NULL;

-- Cross-reference with source — were these NULL originally?
SELECT s.customer_id,
       s.date_of_birth AS source_dob_varchar,
       t.date_of_birth AS target_dob_date
FROM source_db.customers s
JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE s.date_of_birth IS NOT NULL
  AND t.date_of_birth IS NULL;
-- Any rows here = conversion failures (data loss)

-- Test 2: Verify dates match
SELECT s.customer_id,
       s.date_of_birth AS source_dob,
       DATE_FORMAT(t.date_of_birth, '%d-%m-%Y') AS target_dob_formatted
FROM source_db.customers s
JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE s.date_of_birth != DATE_FORMAT(t.date_of_birth, '%d-%m-%Y')
LIMIT 20;

-- Migration rule: NULL status → "ACTIVE"
-- But wait — were all NULLs supposed to become ACTIVE?

-- Count NULLs in source
SELECT COUNT(*) AS null_status_in_source
FROM source_db.accounts
WHERE status IS NULL;

-- Count new ACTIVE in target that were NULL in source
SELECT COUNT(*) AS null_to_active
FROM source_db.accounts s
JOIN target_db.accounts t ON s.account_id = t.account_id
WHERE s.status IS NULL AND t.status = 'ACTIVE';

-- These two numbers should match
-- If null_to_active < null_status_in_source, some NULLs got a different value

-- Source: balance DECIMAL(12,4) — four decimal places
-- Target: balance DECIMAL(12,2) — two decimal places

-- Find records where rounding changed the value
SELECT s.account_id,
       s.balance AS source_balance,
       t.balance AS target_balance,
       s.balance - t.balance AS rounding_diff
FROM source_db.accounts s
JOIN target_db.accounts t ON s.account_id = t.account_id
WHERE ROUND(s.balance, 2) != t.balance;

-- Total financial impact of rounding
SELECT
  SUM(s.balance) AS source_total,
  SUM(t.balance) AS target_total,
  SUM(s.balance) - SUM(t.balance) AS total_rounding_loss
FROM source_db.accounts s
JOIN target_db.accounts t ON s.account_id = t.account_id;

-- Check for encoding corruption (garbled text)
SELECT customer_id, name
FROM target_db.customers
WHERE name LIKE '%?%'
   OR name LIKE '%\\ufffd%'
   OR LENGTH(name) != CHAR_LENGTH(name) * 1;  -- Multi-byte mismatch

-- Compare character lengths
SELECT s.customer_id,
       s.name AS source_name,
       t.first_name AS target_name,
       LENGTH(s.name) AS source_bytes,
       LENGTH(t.first_name) AS target_bytes
FROM source_db.customers s
JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE LENGTH(s.name) != LENGTH(t.first_name) + LENGTH(t.last_name) + 1;