Data Migration: The Make-or-Break Phase
Data migration is often the riskiest part of a NetSuite implementation. Poor strategy leads to go-live delays, data quality issues, and costly rework. This guide covers strategy, extraction, transformation, load sequence, validation, tool choices, and cutover considerations. Whether you're migrating from QuickBooks, SAP, or a legacy ERP, the principles apply.
Phase 1: Strategy and Scope
Define what data you're migrating: chart of accounts, subsidiaries, departments, locations, customers, vendors, items, open transactions (AR, AP, POs, SOs), and historical data. Decide on history depth—many implementations migrate only open transactions and 1–2 years of history to reduce scope and risk. Document data owners (who signs off on each dataset), quality criteria (what "good" looks like), and cutover timing. Create a migration workbook that tracks status by object type.
| Data Type | Typical Load Order | Complexity |
|---|---|---|
| Chart of accounts | 1 | Low |
| Subsidiaries, departments | 2–3 | Low |
| Customers, vendors | 4 | Medium |
| Items | 5 | Medium–High |
| Open transactions | 6–7 | High |
| Historical | 8 | High |
Phase 2: Extraction
Extract from the legacy system into CSV or an intermediate format. Use the legacy system's export tools, direct database access (with DBA support), or an ETL tool. Ensure extraction is repeatable—you'll run it multiple times for test loads and final cutover. Validate row counts and key totals (e.g., sum of AR balance) before proceeding. Capture extraction timestamps for audit. For incremental loads, track "last extracted" to avoid duplicates.
Phase 3: Transformation
Map source fields to NetSuite fields. Handle format changes: dates (MM/DD/YYYY vs. YYYY-MM-DD), amounts (decimal separators), and IDs. Create lookup mappings (legacy customer ID to NetSuite internal ID) so transaction loads can resolve references. Use scripts (Python, Excel VBA, or ETL tools) to transform. Document all mappings in a spreadsheet for audit and future reference. Validate transformed data—spot-check records, run balance checks.
Phase 4: Load Sequence
Load in dependency order: 1) Chart of accounts, 2) Subsidiaries, 3) Departments and locations, 4) Customers and vendors, 5) Items, 6) Opening balances (if applicable), 7) Open transactions (bills, invoices, POs, SOs), 8) Historical transactions (if needed). Violating order causes foreign key and reference errors. For transactions, load in order: POs and SOs first (they may reference each other), then bills and invoices, then payments and applications.
Phase 5: Validation
Compare loaded data to source: record counts, total amounts, key reconciliations (AR aging, AP aging, inventory). Run NetSuite reports and compare to legacy. Fix data issues, reload affected records, and re-validate. Plan for 2–3 test loads before production. Document validation results for sign-off. Common validation reports: Customer/Item balance summary, AR/AP aging, Open PO/SO totals.
Tools: CSV Import vs. SuiteScript vs. Third-Party
CSV Import: Built-in, good for simple loads. Limited to 5000 rows per file in some cases; chunk if needed. Use for master data and smaller transaction sets. SuiteScript: Full control, good for complex logic (e.g., multi-currency, custom validations) and large volumes. Requires development and testing. Third-party (Celigo, Jitterbit, etc.): Pre-built connectors, less custom code. Evaluate cost vs. flexibility and support.
Cutover Considerations
Freeze legacy data entry during cutover. Run final extraction, transform, and load. Validate immediately. Have a rollback plan—what if critical data is wrong? Keep legacy system read-only for 2–4 weeks. Allocate 1–3 days for cutover depending on volume and complexity. Communicate clearly to all stakeholders.
Common Pitfalls
Duplicate records: Run de-duplication before load. Invalid references: Ensure all foreign keys (customer, item, subsidiary) exist before loading transactions. Date formats: NetSuite expects specific formats; test with sample dates. Multi-subsidiary: Every record needs correct subsidiary; defaults can cause wrong assignment.
YRK Consulting has led data migrations for multi-entity implementations. Contact us for implementation support.