How to Validate Data After Migration Without Losing Sleep

At 2 a.m., a Shopify Plus replatform can look perfect. The export finished, the import completed, the row counts match, and someone posts a celebratory message in the release channel. By mid-morning, customer service is handling complaints because migrated variants are showing the wrong compare-at prices, inventory is detached from locations, and a handful of redirects lead customers into dead ends.

That kind of incident doesn't mean the migration tool failed. It means the team validated movement instead of meaning. How to validate data after migration properly requires more than proving that records arrived. You need to prove that values, relationships, business rules, audit history, and storefront behaviour still work together.

The UK National Archives migration guidance treats validation as a formal acceptance step. It recommends testing continuity against original requirements and acceptance criteria, quality checking the new environment, and retaining old digital information until the transfer has passed verification. That mindset is useful in ecommerce, where deleting or retiring the old platform before reconciliation can turn a recoverable discrepancy into a customer-facing incident.

When Migration Night Goes Sideways

The dangerous bugs are rarely the ones that stop an import. A failed job attracts attention. A completed job with subtly wrong data gets celebrated.

Consider a typical replatform cutover. Products and variants are present in the target, the product count agrees with the source, and a spot check finds the expected hero images. The team approves the launch. Later, buyers see misleading price comparisons because a transformation applied currency rounding differently to a group of variants. The records exist, but their commercial meaning has changed.

That failure belongs to the first class, silent data corruption. The destination accepts the value, the field has the expected type, and a count query returns nothing suspicious. Only a comparison of source and target values, evaluated against the intended pricing rule, exposes it.

Three failure classes row counts miss

Business logic drift is different. A tax flag may migrate as a valid Boolean while the destination checkout interprets it under a different tax configuration. Inventory may be numerically present but allocated to the wrong location. A discount condition can survive the move as text while no longer triggering the same promotion.

Reference breakage affects relationships rather than individual fields. A variant can retain its SKU while losing its inventory item relationship. An order can retain its customer identifier while pointing to a destination record created by a flawed identity mapping. Product URLs, collection references, translations, and redirects can all break while the underlying tables remain populated.

Practical rule: A matching row count proves that records crossed the boundary. It doesn't prove that customers, prices, stock, tax, or links still behave correctly.

The Grumspot incident response planning guide is useful context here because post-migration validation should connect directly to an incident process. Every failed check needs an owner, a severity, a disposition, and a decision about whether the team fixes forward or stops the release.

Migration teams working across cloud platforms also benefit from understanding the broader Software Modernization Intelligence on cloud perspective. A platform move changes dependencies and operating assumptions, not just storage locations. For a Shopify Plus launch, that means validating the integrations around the storefront as carefully as the catalogue itself.

Building a Validation Plan Before You Cutover

Validation should be designed before the first production transfer. Start with a written inventory of entities, fields, relationships, transformations, exclusions, and acceptance criteria. For every rule, define whether a failure blocks launch, creates a warning, or gets recorded for later remediation.

The National Archives checklist recommends using original requirements and acceptance criteria as the benchmark, then refining and re-testing when requirements aren't met. That is a better operating model than inventing a pass standard after the results arrive.

Build the layers in order

1. Row counts and table totals. Compare products, variants, customers, orders, line items, inventory records, redirects, and other agreed entities. Break totals down by useful dimensions such as market, status, product type, or location. This catches missing batches and unexpected filters quickly, but it won't detect a wrong value in a present row.

2. Field-level null and type checks. Check required fields, numeric ranges, date formats, Boolean values, email syntax, encoding, and unexpected truncation. A field can be non-null and still be unusable, so pair presence checks with semantic checks.

3. Referential integrity. Test every important parent-child relationship. Variants must resolve to products, line items must resolve to orders, inventory items must resolve to stock records, and redirect targets must resolve to valid destination paths. The ONS quality framework describes a staged approach that checks data on receipt, validates processed components, compares the compiled target with expected relationships and totals, then re-checks against later feedback and independent sources.

4. Business rules. Test price positivity, SKU uniqueness, inventory constraints, tax class consistency, customer identity rules, order totals, and market-specific behaviour. The migration becomes an ecommerce validation exercise rather than a database exercise.

5. Behavioural checks. Query the live or staging storefront and exercise search, product pages, cart, checkout, account access, redirects, subscriptions, promotions, and fulfilment hand-offs. A database can pass while the storefront still exposes stale or inaccessible data.

Assign an owner to every check and record the source extract time. Freeze or control source changes during the comparison window. If the source keeps changing while the target is measured, the team can't distinguish a real migration defect from an expected delta.

For platform decisions, the distinction between compare custom and pre built PCs offers a useful analogy. The right choice depends on requirements, dependencies, and control points, not on whether a solution appears complete out of the box. Your Shopify migration guide should therefore include validation ownership and evidence requirements, not only import mechanics.

Sample Queries and Checks That Catch Real Bugs

A useful validation query returns the failing records, not just a green or red summary. Summary checks tell stakeholders whether a problem exists. Exception queries tell engineers what to repair.

Products and variants

For relational sources, compare product and variant attributes after normalising known representation differences:

SELECT
  s.product_id,
  s.variant_id,
  s.sku,
  s.price AS source_price,
  t.price AS target_price,
  s.compare_at_price AS source_compare_at,
  t.compare_at_price AS target_compare_at,
  s.inventory_quantity AS source_inventory,
  t.inventory_quantity AS target_inventory
FROM source_variants s
JOIN target_variants t
  ON t.source_variant_id = s.variant_id
WHERE COALESCE(s.sku, '') <> COALESCE(t.sku, '')
   OR ABS(COALESCE(s.price, 0) - COALESCE(t.price, 0)) > :price_tolerance
   OR COALESCE(s.compare_at_price, 0) <> COALESCE(t.compare_at_price, 0)
   OR COALESCE(s.inventory_quantity, 0) <> COALESCE(t.inventory_quantity, 0);

Run a separate relationship check for inventory_item_id. A variant with a correct price and SKU still fails validation if its inventory item doesn't resolve to the expected stock record or location.

Customers and orders

Customer checks should identify duplicate identity, not merely missing records:

SELECT LOWER(TRIM(email)) AS email_key, COUNT(*) AS records
FROM target_customers
GROUP BY LOWER(TRIM(email))
HAVING email IS NULL OR COUNT(*) > 1;

For orders, calculate the line-item subtotal from quantity and unit price, then compare it with the historical order total after documenting how discounts, shipping, tax, and refunds are represented:

SELECT
  o.order_id,
  o.historical_total,
  SUM(li.quantity * li.unit_price) AS calculated_subtotal
FROM target_orders o
JOIN target_line_items li ON li.order_id = o.order_id
GROUP BY o.order_id, o.historical_total
HAVING ABS(
  COALESCE(o.historical_total, 0) -
  COALESCE(SUM(li.quantity * li.unit_price), 0)
) > :order_tolerance;

Use the Shopify Admin API with cursor pagination to count variants per product. Flag products whose target count differs from the source count, as well as products with no variants where the source required them. Don't rely on a single page of API results.

Data Object	Risk Field	Why It Breaks	Check Pattern
Product variant	compare_at_price	Currency conversion and rounding rules can alter the commercial comparison value	Compare source and target after applying the approved currency rule
Variant	weight	Unit conversion and decimal precision can change fulfilment calculations	Convert both values to a common unit and compare
Variant	taxable	Boolean mapping can invert or default silently	Check allowed values and compare by market
Product or variant	Tax overrides	Country-specific rules may not map to the destination model	Join market, tax class, and override records
Variant	inventory_item_id	Identifier remapping can detach stock from the sellable variant	Resolve every identifier to its expected stock record
Customer	Email	Case, whitespace, nulls, and duplicates affect identity	Normalise, validate format, and test uniqueness
Order	Line-item totals	Discounts, tax, shipping, and refunds may be represented differently	Recalculate components and compare to the source ledger
Redirect	Destination path	Handles and URL structures often change during replatforming	Request each mapped path and classify the response

The MHRA data integrity guidance is especially relevant for high-risk fields. It expects migration procedures to be carefully designed and validated, with audit trails and attributable changes. Ecommerce teams should apply the same discipline to prices, stock, customer identity, and order history.

Reconciliation Methods and Acceptable Thresholds

Reconciliation answers a harder question than “did the import finish?” It asks whether the destination tells the same business story as the source.

Use several comparison methods together. Counts identify missing or duplicated entities. Aggregates expose systematic drift. Joins locate relationship failures. Hashes provide a compact comparison for selected normalised fields, provided the team defines canonical formatting first.

The ONS administrative-data methodology describes checks for counts, duplication, replication, metadata, requirements, and prior extracts. It also notes that there isn't one universal gold standard, so independent sources matter. For a commerce migration, those sources might include the source platform, ERP, warehouse system, payment records, and storefront responses.

Choose the threshold by risk

Don't use one global tolerance. A tiny metadata discrepancy can be harmless, while one wrong price or customer identity can create a serious incident.

Data Type	Hard-Fail Threshold	Soft-Warn Threshold	Reconciliation Method
Price and compare-at price	Any unexplained commercial mismatch	None unless the source itself is marked unresolved	SKU-level value comparison and product-level net totals
Inventory	Any unexplained sellable-stock mismatch	Documented source or timing variance	Reconcile on-hand, committed, and available quantities by location
Customer identity	Any duplicate or mislinked identity	Non-critical profile metadata difference	Normalised email and phone comparison, then source joins
Orders	Any unexplained order loss or total distortion	Documented historical representation difference	Count, line-item calculation, tax, refund, and payment reconciliation
Redirects	Any critical redirect loop or missing revenue path	Non-critical legacy URL exception	Automated request checks and destination classification
Metadata	None if it affects search or legal content	Unresolved tags or optional metafield differences	Field-level comparison with an approved exclusion list

For inventory, an organisation may choose a numeric tolerance only after understanding its stock model, location timing, and source reliability. The important rule is to approve the threshold before testing, document every exception, and never let a convenient tolerance hide a transformation defect.

ONS methodology provides a practical example of rule-based validation. It specifies checks such as valid age ranges, allowed sex values, and origin and destination relationships, then evaluates failures before controlled correction or imputation. The transferable lesson is simple: define the rule first, measure exceptions second, and record how each exception was handled.

Teams integrating Shopify with an ERP can use the same approach when reviewing Shopify ERP integration guidance. Reconcile the system that owns each field, rather than assuming Shopify is automatically authoritative for every value.

Automating Checks So Sleep Becomes Optional

Manual reconciliation fails under pressure because people compare different extracts, overwrite spreadsheets, and lose the context behind a decision. Put the checks in version-controlled code and make every run produce a durable result.

A practical pipeline has five parts:

• Trigger: Run after each dry run, during the controlled freeze window, and after production cutover.
• Extract: Query the source and destination APIs or databases using a recorded extraction timestamp.
• Compare: Apply normalisation, relationship checks, aggregates, and business rules.
• Persist: Write rule results, counts, deltas, and exception identifiers to a results table.
• Alert: Exit with a non-zero status for hard failures and send the failing rows to the team responsible.

A small Python or Node service is enough to establish the pattern. The implementation matters less than the controls around it:

def run_check(check):
    source = fetch_source(check)
    target = fetch_target(check)
    result = compare(source, target, check.rule)
    save_result(check.name, result)
    return result

results = [run_check(check) for check in CHECKS]

if any(result.hard_fail for result in results):
    send_alert("Migration validation failed", attach_csv(results))
    raise SystemExit(1)

Use GitHub Actions for pull requests that change mapping logic, then run the same checks from a scheduled job during the freeze. The code should be idempotent, so repeating a failed run doesn't duplicate results or create conflicting writes.

Design for API and data failures

Shopify Admin API calls can be rate-limited or interrupted. Add bounded retries with backoff, persist cursors, and record the request window used for each entity. If a job stops after products but before orders, a restart should resume safely or deliberately rerun the entire check.

Create a dead-letter table for records that repeatedly fail validation. Store the entity key, rule name, source value, target value, error category, retry count, and disposition. That gives engineers a queue they can work through instead of a vague alert saying “migration failed”.

For complex estates, the high-risk SharePoint migration guidance is a useful reminder that migration validation must account for permissions, traceability, and edge cases, not only transferred content. The same principle applies when a Shopify store depends on ERP, fulfilment, search, tax, and customer-account services.

Audit Trails and Evidence Stakeholders Want

A green dashboard isn't sufficient evidence for a finance lead, compliance reviewer, or agency partner. Stakeholders want to know what was checked, against which versions, by whom, when, and how exceptions were resolved.

Treat each reconciliation run as an immutable artefact. Capture:

• Timestamp: The source and target extraction times, plus the validation run time.
• Operator: The person or service account that initiated the run.
• Query identity: A hash or version reference for the source query, target query, and comparison code.
• Scope: Entities, markets, locations, date ranges, exclusions, and migration batch.
• Result: Source count, target count, delta, failed rule, affected keys, and severity.
• Disposition: Approved, remediated, accepted with rationale, or blocked.

Store results in an append-only validation table or generate a signed report for each approval gate. Don't let an engineer edit the original result after the fact. If a threshold changes, create a new rule-set version and rerun the check.

A sign-off cover page can be concise:

Entity	Rule	Approved Threshold	Observed Variance	Status	Approver
Products	Variant and price reconciliation	Pre-approved rule	Recorded delta	Pass or warn	Name and signature
Inventory	Location-level stock comparison	Pre-approved rule	Recorded delta	Pass or block	Name and signature
Customers	Identity and duplicate check	Pre-approved rule	Recorded delta	Pass or block	Name and signature
Orders	Totals and refunds	Pre-approved rule	Recorded delta	Pass or warn	Name and signature
Redirects	Destination response check	Pre-approved rule	Recorded delta	Pass or block	Name and signature

The evidence pack should show that old data remained available until the target was quality checked, which aligns with the National Archives acceptance approach. For regulated environments, retain attributable audit trails and change history, following the integrity principles described in the earlier MHRA reference.

Cutover Checklist and Common Questions

Use this as a working release checklist, then attach the completed validation report to the change record.

T-48 hours

• Full validation re-run: Execute counts, field rules, relationships, business rules, and storefront tests.
• Stakeholder sign-off: Obtain approval from commerce, finance, operations, customer service, and engineering owners.
• Rollback window confirmed: Verify that the source remains preserved and that the rollback decision-maker is available.

T-24 hours

• Delta sync: Transfer approved changes made since the last baseline.
• Cache purge: Clear application and storefront caches according to the release plan.
• Redirect smoke tests: Test priority paths, product URLs, collection URLs, account routes, and content pages.
• Customer service briefed: Give support teams known limitations, escalation routes, and the first checks to perform.

T-2 hours to T-0

• Read-only control: Stop uncontrolled source edits and capture the final extraction.
• Monitoring active: Start error, checkout, payment, inventory, and redirect monitoring.
• DNS cutover: Switch traffic only after the hard-fail checks have passed.
• Payment handshake: Place an approved test transaction and confirm the downstream order flow.

After launch

• T+2 hours: Validate the first live order, confirm inventory movement, inspect checkout errors, and run a 404 sweep.
• T+48 hours: Repeat full reconciliation, triage remaining warnings, and schedule the post-mortem while evidence is fresh.

Common questions

How should you set a threshold when legacy data is unreliable? Use separate pass, warn, and fail rules by data type. Establish a baseline from the cleanest available extract, document known source defects, and block launch on unexplained price, stock, payment, or identity failures.

How should you sample records for manual review? Use random stratified sampling across risk groups, such as high-value products, markets, order states, and inventory locations. For a statistically valid manual review, use at least 30 records per stratum or 5% of the population, whichever is larger, as specified in the validation plan.

When should you roll back rather than hotfix? Roll back for data loss, payment failures, broken order creation, severe inventory corruption, or redirect loops beyond the agreed fail threshold. Hotfix cosmetic defects, SEO metadata issues, or other non-revenue problems when the underlying records remain safe and the change is reversible.

---

Grumspot supports Shopify Plus migrations with staging validation, migrated data sampling, image mapping checks, metadata review, and broader QA and UAT. If your cutover evidence needs a practical engineering review, visit Grumspot to discuss the reconciliation checks, storefront tests, and sign-off pack your launch requires.