Data Migration Integrity and Reconciliation

Context

Data migration work often fails not during the copy itself, but during validation: missing rows, broken relationships, duplicate business keys, and transformed values that no longer match expectations. For a data analyst working with Veeva Vault data, the interviewer wants to hear how you would use SQL to prove the migrated data is complete and accurate.

Core question

Explain how you would approach a migration from a legacy source into a PostgreSQL-based target to ensure data integrity and accuracy. Your answer should cover how you would validate row counts, business-key uniqueness, null handling, referential integrity, and transformed fields after load. You should also describe how you would reconcile source and target data, identify mismatches, and decide what should block release versus what can be triaged.

Scope guidance

Go beyond high-level project management. The expected answer is a practical SQL-focused framework: what checks you would run before, during, and after migration, what queries you would write, and how you would communicate migration quality with measurable evidence.

Pre-migration profiling

Before moving data, you should profile the source to understand row counts, null rates, duplicate business keys, invalid dates, and orphaned relationships. This establishes a baseline so post-migration checks are compared against known source conditions rather than assumptions.

SELECT COUNT(*) AS row_count,
       COUNT(*) FILTER (WHERE external_id IS NULL) AS null_external_id,
       COUNT(DISTINCT external_id) AS distinct_external_id
FROM source_accounts;

Business-key reconciliation

Primary keys often change during migration, so reconciliation should usually be based on stable business keys such as external IDs, document numbers, or CRM account codes. This helps detect missing, duplicated, or incorrectly transformed records across systems.

SELECT s.external_id
FROM source_accounts s
LEFT JOIN target_accounts t
  ON s.external_id = t.external_id
WHERE t.external_id IS NULL;

Referential integrity validation

A successful migration is not just about row counts; parent-child relationships must still be valid. You should verify that every migrated child row points to an existing parent row and that no orphan records were introduced.

SELECT c.account_id, c.contact_id
FROM target_contacts c
LEFT JOIN target_accounts a
  ON c.account_id = a.account_id
WHERE a.account_id IS NULL;

Transformation and field-level checks

When fields are standardized, mapped, or reformatted, you need SQL checks that compare expected transformed values to actual target values. This is especially important for status mappings, date normalization, and derived columns.

SELECT s.external_id, s.status_code, t.account_status
FROM source_accounts s
JOIN target_accounts t
  ON s.external_id = t.external_id
WHERE CASE s.status_code
        WHEN 'A' THEN 'active'
        WHEN 'I' THEN 'inactive'
        ELSE 'unknown'
      END <> t.account_status;

Exception reporting and release thresholds

Not every mismatch has the same severity. Strong migration practice defines blocking issues, such as missing rows or broken foreign keys, separately from non-blocking issues, such as optional-field formatting differences, and reports both with counts and examples.

SELECT 'missing_accounts' AS check_name, COUNT(*) AS issue_count
FROM (
  SELECT s.external_id
  FROM source_accounts s
  LEFT JOIN target_accounts t
    ON s.external_id = t.external_id
  WHERE t.external_id IS NULL
) q;