DataStage migration testing checklist: seven checks before cutover

A converted DataStage job can finish successfully and still produce the wrong answer. Migration testing needs to prove that the replacement preserves the agreed data contract: which records arrive, how values change, what gets rejected, and when downstream users can rely on the output. This checklist turns those questions into repeatable evidence.

Start with the same inputs and an explicit contract

Freeze a representative input extract, including reference and lookup tables. Run DataStage and the replacement against those same versions, with the same parameters and business date. Comparing yesterday's DataStage output with today's target output mixes source changes with migration defects. For incremental jobs, record the exact lower and upper extraction bounds and the initial destination state. Keep these with the job export, converted code version, runtime settings, and run identifiers.

Define the output grain, business key, required columns, decimal precision and scale, timestamp meaning, permitted rejects, and expected delivery window. Mark deliberate changes separately with an owner and rationale. Use the DataStage migration guide for the wider migration sequence; the checks below are a practical validation framework, not a claim that any one tool executes every test automatically.

1. Schema and conversion rules

Compare column names, types, nullability, lengths, and decimal definitions before comparing rows. Test the largest valid value, smallest valid value, overflow, and one extra fractional digit. Record whether each conversion rounds, truncates, rejects, or fails. Casting both outputs to a smaller scale just to make them match can conceal a defect. Include character encoding and significant whitespace in the contract; trimming every string is not a neutral comparison.

2. Row counts and rejected records

Check counts for the complete output and by business partition, such as processing date and region. An extra row in one partition can cancel a missing row elsewhere. Reconcile accepted and rejected records with the input using the job's actual rules: filters, joins, and aggregation can legitimately change cardinality. Inspect reject reasons and error records, including malformed dates and failed lookups, instead of treating a successful job status as sufficient.

The SQL examples assume two immutable comparison tables, legacy_orders and target_orders, loaded into one query engine. Both expose order_id, amount, and status using agreed comparable types. The syntax uses common SQL supported by Databricks SQL, Fabric Warehouse, and Snowflake. Adapt table names and permissions; do not query two changing production outputs.

SELECT 'legacy' AS dataset, COUNT(*) AS row_count,
       COUNT(*) - COUNT(amount) AS amount_nulls,
       SUM(amount) AS total_amount
FROM legacy_orders
UNION ALL
SELECT 'target', COUNT(*), COUNT(*) - COUNT(amount), SUM(amount)
FROM target_orders;

An empty or entirely null amount column can produce a null sum. Keep the row and null counts alongside the total so that replacing an unknown value with zero cannot hide the difference.

3. Business keys and duplicate multiplicity

For an order header table, require a non-null, unique order_id. For order lines, use the complete line key instead. Run the following on both comparison tables:

SELECT order_id, COUNT(*) AS occurrences
FROM legacy_orders
GROUP BY order_id
HAVING COUNT(*) > 1 OR order_id IS NULL;

If duplicates are legitimate, compare the count of each complete business row on both sides. A distinct set comparison alone loses duplicate multiplicity. Do not remove duplicates merely to make a test pass; a Lookup or Join may have changed how many output records each input produces.

4. Null handling and row-level values

Compare null counts per column, then compare actual values using explicit null handling. Ordinary SQL equality and inequality do not identify every difference involving null; Databricks documents this behavior in its NULL semantics reference. Avoid a substitute such as COALESCE(status, '') when empty strings are valid values.

After the unique, non-null key check passes, this query returns missing records and differences in the two example payload columns. Extend its predicates to every contractual output column.

SELECT COALESCE(l.order_id, t.order_id) AS order_id,
       l.amount AS legacy_amount, t.amount AS target_amount,
       l.status AS legacy_status, t.status AS target_status
FROM legacy_orders l
FULL OUTER JOIN target_orders t ON l.order_id = t.order_id
WHERE l.order_id IS NULL OR t.order_id IS NULL
   OR l.amount <> t.amount
   OR (l.amount IS NULL AND t.amount IS NOT NULL)
   OR (l.amount IS NOT NULL AND t.amount IS NULL)
   OR l.status <> t.status
   OR (l.status IS NULL AND t.status IS NOT NULL)
   OR (l.status IS NOT NULL AND t.status IS NULL);

String equality follows the comparison engine's collation and padding rules. If case or trailing spaces carry meaning, configure a suitable comparison or compare explicit byte representations.

5. Business totals and decimal precision

Compare totals and extrema by meaningful groups: currency, accounting period, account, and product. Check money at the agreed decimal scale; do not silently substitute floating-point arithmetic. Test rounding boundaries, negative credits, and large aggregates. Any tolerance needs a business reason and a recorded threshold. A matching grand total cannot prove that amounts were assigned to the correct customers, so retain the row-level checks.

6. Dates, time zones, and transformation edge cases

Distinguish an instant from a local wall-clock value before normalizing timestamps. Test midnight, month end, leap day, daylight-saving transitions, and fractional seconds. Snowflake's timestamp documentation distinguishes local-session, timezone-free, and offset-aware types; a matching displayed timestamp does not by itself prove equivalent meaning. Document the timezone of source values that carry no offset. Add fixtures for the derivations covered in DataStage function translation edge cases, including blank strings, missing lookups, and repeated delimiters.

7. Incremental execution, restart, and delivery

Run the same batch twice and verify the agreed result remains stable. Inject failure after a partial write, restart, and inspect every destination. Test an update, a deletion where supported, a late-arriving record, and a historical backfill. Check dependent jobs, file delivery, permissions, and completion within the agreed window using representative volume. Carry the evidence into the cutover and rollback plan.

Make acceptance a recorded gate

No unexplained schema, key, row-level, reject, or business-total differences remain.
Approved exceptions identify the affected data, owner, reason, and acceptance threshold.
Representative normal, boundary, incremental, and restart runs meet the agreed delivery window.
The data owner and operations owner approve an evidence bundle with reproducible inputs, queries, results, code versions, and a rehearsed rollback procedure.

Samples can help diagnose a failure, but a passing sample is not proof of full equivalence. If full comparisons are impractical, document the untested scope and the explicit acceptance decision.