Nightly Item Import That Creates Duplicates
Every night at 01:00 an operations analyst uploads the full item master from a product information management (PIM) system into NetSuite: about 38,000 rows in one CSV with columns SKU, Description, Base Price, Active Status, and PIM_ID. PIM_ID is mapped to the item's External ID. Three weeks after go-live the item list has roughly 1,200 apparent duplicates, some differing only by trailing spaces or capitalization in the SKU. Import runtime has grown from 12 to 55 minutes, and merchandising complains that item-creation emails arrive for items that already existed. The import uses Data Handling 'Add' and has the 'Run Server SuiteScript and Trigger Workflows' option checked. The PIM is the system of record for descriptions and active status; NetSuite is the system of record for price. There is no middleware, and the file is uploaded by hand.
1. Identify the root causes of the duplicates and the runtime growth.
Read the worked answer
The duplicates are a matching failure, not a data-volume problem:
- Data Handling 'Add' never matches existing records. It creates a new item for every row that does not match on the chosen key, and if External ID matching is not actually in play, every nightly run duplicates the entire file. The option must be Add or Update with External ID mapped as the match field.
- Dirty keys. Trailing spaces and capitalization differences in SKU/PIM_ID break exact matching. A key of ' ABC-100' and 'ABC-100' are different values to the matcher; normalization must happen before load, not after.
- PIM changes to the key itself. If the PIM rewrites SKU when an item is renamed, the External ID no longer matches and a new item is created.
Runtime growth follows from the duplicates (more records to scan and update) and from workflows and server scripts firing on every created record - each duplicate triggers creation emails and any item-triggered automation, multiplying the 55 minutes. The single 38,000-row file also has no checkpointing; one bad batch restarts the whole run.
2. Design the corrected load so it is idempotent and safe to rerun.
Read the worked answer
Idempotency is the requirement: running the file twice must produce the same state, not twice the records.
- Normalize before load: trim whitespace, enforce a single casing convention for PIM_ID/SKU, reject rows with blank or duplicate keys, and fail the file rather than load it if the key column is not unique.
- Map PIM_ID to External ID and use Data Handling 'Add or Update', with overwrite limited to PIM-owned fields (Description, Active Status). Do not let the import overwrite price, which NetSuite owns - mapping Base Price with overwrite enabled would silently reverse the system-of-record decision.
- Split the file into batches and load sandbox first against a refreshed copy; compare counts before and after, and reconcile the count of items with an External ID to the PIM row count.
- Handle workflows deliberately. If creation emails must stop, disable the specific email action or gate it on a custom flag rather than unchecking 'Run Server SuiteScript and Trigger Workflows', which would also skip scripts that populate required fields.
- For real durability, move to a SuiteScript RESTlet using
record.upsertwith the external ID, which returns per-record success and failure instead of a file-level result.
3. Describe the cleanup and the ongoing controls you would put around this integration.
Read the worked answer
Cleanup first, before the next scheduled run. Stop the nightly load so duplicates do not grow. Identify duplicates with a saved search grouping items by External ID or normalized SKU and counting records greater than one. Merge where the records are truly the same item and one has transactions; delete only records with no dependent transactions. Never mass-delete items that appear on sales orders or inventory adjustments - deactivate or merge instead.
Ongoing controls:
- A post-load reconciliation: PIM active row count versus NetSuite items with a non-empty External ID, with tolerance zero and an alert on variance.
- A duplicate-detection saved search emailed daily to the integration owner.
- Validation in the load process: key uniqueness, blank-key rejection, row-count threshold alerts, and a failure notification.
- Documentation of field ownership (PIM owns description and active status; NetSuite owns price) so future mappings do not quietly overwrite the other system.
- Rehearse in sandbox monthly, since a PIM schema change is the most likely cause of the next incident.