How to Sync NetSuite Records to MongoDB for Analytics

Mirror NetSuite data in MongoDB for flexible querying and dashboard building.

What This Integration Does

Many analytics and BI tools can't connect to NetSuite directly, or the connection is too slow for interactive dashboards. This Spojit workflow runs on a schedule, pulls fresh data from NetSuite via SuiteQL, and writes it into MongoDB collections your dashboards can query freely.

The workflow is re-runnable - each pass upserts records by primary key so re-running won't create duplicates. You can also scope each run to recently-updated records so a single run doesn't have to re-sync the entire dataset.

Prerequisites

A NetSuite connection with SuiteQL / REST access enabled.
A MongoDB connection (Atlas or self-hosted) with write access to the target database.
A target MongoDB database name (e.g., netsuite_mirror) and a collection per entity type.

Step 1: Schedule Trigger

Drop a Trigger node onto the canvas and set its type to Schedule. A Schedule trigger uses a 5-field cron expression plus an IANA timezone. Pick a cadence that matches how fresh your dashboards need to be: 0 2 * * * in Australia/Sydney works well for nightly refreshes, or */15 * * * * for near-real-time. The trigger's output is {{ trigger.scheduledAt }}, the time this run fired, which you use to compute an incremental window.

Step 2: Transform - Compute the Sync Window

Add a Transform node to build this run's window from the trigger time:

syncEnd - set to {{ trigger.scheduledAt }}, the time this run fired.
syncStart - subtract a fixed lookback (slightly longer than your cron interval so nothing slips between runs) from syncEnd using the date connector's subtract tool.

These two values drive the SuiteQL WHERE clauses in the next step, making each run incremental. Overlapping the lookback window is safe because the upsert in Step 5 is idempotent.

Step 3: Query NetSuite with SuiteQL

Add a Connector node pointing at the netsuite connector and pick the run-suiteql tool. Write a query for each entity you want to mirror, scoped to the sync window. Example for customers updated since the last run:

SELECT id, entityid, companyname, email, datecreated, lastmodifieddate
FROM customer
WHERE lastmodifieddate >= TO_TIMESTAMP('{{ syncStart }}', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
  AND lastmodifieddate < TO_TIMESTAMP('{{ syncEnd }}', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

Repeat (or fan out with a Parallel node) for each entity you want to mirror - typically customer, transaction (orders + invoices), item, and vendor.

Step 4: Transform - Shape Records for MongoDB

NetSuite returns flat tabular rows. Use a Transform node to:

Rename columns from SCREAMING_CASE to camelCase for cleaner Mongo documents.
Set _id to the NetSuite id - this is what makes upserts idempotent.
Stamp syncedAt with the current timestamp so dashboards can show data freshness.

Step 5: Upsert to MongoDB

Add a Connector node pointing at the mongodb connector and pick the update-documents tool. Configure:

Database: netsuite_mirror
Collection: matches the entity (e.g. customers)
Filter: { "_id": "{{ record._id }}" }
Update: { "$set": {{ record }} }
Upsert: true - inserts the document if it doesn't exist yet

Wrap this in a Loop node that iterates over the records from the Transform step.

Step 6: Error Handling and Notifications

Add a Condition node after the upsert to check the result count. On failure (or zero records written), route to a Connector node on the slack connector and call the send-message tool so on-call sees the sync break. Because Step 5 is idempotent, you can simply let the schedule fire again on the next tick to recover from a transient NetSuite or MongoDB error.

Tips

Stay under NetSuite's API limits - SuiteQL is far faster than per-record REST calls and won't exhaust your concurrency budget.
Index for your dashboards - _id is indexed automatically; also add indexes on syncedAt and any field your dashboards filter or sort on.
Schema drift - MongoDB is schemaless, but dashboards aren't. When you add a new NetSuite field, run a one-shot full refresh to backfill it.

Common Pitfalls

Timezones - NetSuite lastmodifieddate is stored in account timezone. Convert both syncStart and syncEnd to that timezone or you'll miss records that fall on the boundary.
Soft deletes - SuiteQL SELECTs skip inactive records by default. If you also want to mirror deletions, add a separate pass that targets isinactive = 'T' and marks the Mongo doc archived: true.
Pagination on huge entities - transaction can return more than a single SuiteQL page. Loop until the result set is empty rather than relying on a single call.

Testing

Run the workflow once manually with a tiny sync window (the last 5 minutes). Inspect a sample document in MongoDB - field names, types, and _id should look right. Then turn the schedule on.