sales-i to HubSpot: a keyed batch migration validated in a sandbox

The deadline was a contract date, not a preference. That layer, sales-i, has no database of its own, so the four files are effectively a snapshot of the ERP’s customer, contact, opportunity and call data, and once the layer went away there was no second export to go back for.

Two built-in import routes existed, and neither fit. The no-code importer caps a job at about two objects, so a four-object load with six association types becomes a chain of manual, click-driven imports that nobody can repeat for a sandbox and then a production portal. The imports API can do the whole thing in one job, but records loaded that way are not filterable in lists, reports and workflows afterwards, and activities cannot be updated once created, which removes the fix path on a load nobody gets perfect first time.

The data closed a third route. Email could not be the contact key: 5,036 contacts have no email address at all, and 416 email addresses are shared by 912 contacts. One fact made the rest workable. Referential integrity across the four files was clean, with no orphans on any foreign key, so a keyed load in parent-first order would resolve every link.

Clean in code, then upsert each object on a custom property that holds its source identifier, then build associations in a pass of their own. The first phase creates those properties and the deal pipeline, reads back the generated stage identifiers, and stamps a source system and a migration batch on every record, which makes the cohort queryable and reversible later.

Companies load first, keyed on the account number, so contacts, deals and calls resolve against records that already exist. Contacts key on the source contact id: the first holder of a shared email address keeps it, later collisions move to a text property, and contacts with no email address load anyway.

Calls were the object that forced a design change. Activities carry no unique property, so they cannot be upserted at all. The loader creates them and deduplicates against a map of source note ids read from the portal, which keeps a re-run safe. Each call needs an explicit historical timestamp or the log collapses onto the day of the load, and an association or it is invisible on a record.

Deals take internal pipeline and stage identifiers rather than labels, and the 105 rows with a blank stage land in a deliberate stage. Under all of it, a batch that returns a 4xx is split and retried down to single records, and a contact with an invalid email address is salvaged by blanking the address and keeping the record.

The scope then changed. The loader was validated in early June. Two days later the plan was revised to load accounts and 2026 invoices from a Pronto snapshot rather than from the export’s customer and opportunity files, dropping the sales-i opportunities. A second transform and loader were built for that source, on the same keyed mechanism.

That first validation run settled five things documentation alone had not. A company can carry a unique property, so the search then upsert workaround older guidance implies was unnecessary. Calls cannot be upserted by any key, so they are created and deduplicated instead. Call duration is stored in milliseconds. The stage mapping lands on the right internal identifiers, and a second full run updates in place rather than duplicating.

The full sandbox run reconciled exactly: 7,942 companies, 10,861 contacts, 3,598 deals and 26,006 calls, the transform output matching the portal on every object, with zero failed records. Every contact, deal and call carried its company association. Web-shop accounts were left out by agreement, accounts with no purchase activity since 2020 were dropped, and the products and line items phase stopped on a missing API scope.

Four days later the pipeline was re-pointed at an aggregate export from the ERP, one authoritative customer set instead of the four files. That run reconciled at 12,176 companies and 10,797 line items with zero failures. The loader then gained a recurring mode: an incremental pass on a last-change watermark with a daily reconcile. Production cutover waited on the client’s sign-off.

Python with its standard library and one HTTP client, files in, one JSON record per line out. Writes go to the HubSpot CRM batch endpoints for companies, contacts and deals, the create endpoint for calls, and the version 4 default association endpoint, all on API version 2026-03. There is no hosted service, and the recurring pass runs the same code again on a watermark rather than on a scheduler of its own.

Operations is a runbook rather than a dashboard: dry run and read the payloads, smoke test a couple of hundred records, load the sandbox in full, run it again to prove idempotency, reconcile, then swap the token and run the same code against production. Rollback is a query on the migration batch stamp, then an archive of the cohort, then a corrected re-run.

Nothing streams. Files are profiled, cleaned into one record per line, and loaded object by object in parent-first order, with each phase gated on its own counts. Associations are built afterwards from identifiers resolved out of the load responses, so no edge is guessed. A reconciliation step compares the source against the portal before the next phase starts, and no child object loads before the parent it will hang from.

That is the mapping as built for the export. Three of the four survived the scope change: the opportunity mapping was dropped, and the deals in the reconciled load are 2026 invoices from the ERP’s sales history instead. Each remaining key is the source system’s own identifier, held in a custom property that HubSpot enforces as unique, which is what lets a second run update rather than duplicate. Calls are the exception the portal forces: activities cannot hold a unique property, so their key lives in the loader instead, as a map of source note ids read back from the portal before each run.

Transform legend: clean is trim and null-token handling, phone is a best-effort E.164 rewrite, country normalizes 18 spellings, date to ms is epoch milliseconds with the 1900 sentinel nulled, dt to iso is ISO 8601, min to ms converts minutes.

Twelve rows of the field mapping: