NetSuite to Snowflake: how to load ERP and GL data your finance team can trust

How do you connect NetSuite to Snowflake?

You connect them with an extract layer that reads NetSuite records and writes them to Snowflake on a schedule. There are four realistic options: manual saved-search CSV exports, SuiteAnalytics Connect over ODBC or JDBC, SuiteQL and SuiteTalk web services queried programmatically, or a managed integration platform that runs the incremental load for you. Everything else is a variation on those.

The honest comparison matters here, because the cheapest-looking option usually costs the most in analyst hours.

Method	Freshness	Effort and limits	Best for
Saved search CSV export	Whenever someone remembers	No code, but row caps on exports, manual re-upload, no audit trail	One-off analysis, never production
SuiteAnalytics Connect (ODBC/JDBC)	On demand, query-time	Licensed separately from your NetSuite subscription, typically per seat or connection; queries are read-only and can be slow on large transaction tables	Teams with existing ETL tooling that speaks JDBC
SuiteQL / REST or SOAP (SuiteTalk)	As often as you schedule it	Real engineering: auth, pagination, governance units, concurrency caps, retry logic, schema drift	Teams with a data engineer to own it
Managed connector	Scheduled, minutes to hourly	Watermarking, upserts, retries, and alerting handled; you configure the field map	Finance teams who want the data, not the plumbing

Whichever route you pick, land raw first and transform in Snowflake. That ELT ordering means a change to how you define net revenue is a SQL change, not a re-extract against a rate-limited ERP. If you're still weighing the two orderings, see ETL vs ELT.

What is SuiteAnalytics Connect?

SuiteAnalytics Connect is NetSuite's data access service that exposes your account through ODBC, JDBC, and ADO.NET drivers, so external tools can query NetSuite records with SQL. It is licensed separately from the core NetSuite subscription and is generally provisioned per user or per connection, which is what makes people think twice before pointing five tools at it.

It's genuinely useful for analysts who want ad hoc SQL against ERP data without waiting on a pipeline. As a production extraction path it has rough edges: query performance on large transaction tables degrades, concurrent connections are constrained, and a long-running analytical query can contend with the same resources your scheduled load needs. Most teams end up using it for exploration and something more purpose-built for the nightly load.

Can you query NetSuite data with SQL?

Yes. SuiteQL is NetSuite's SQL-like query language, available through the REST web services and inside SuiteScript, and it lets you select from record tables with joins and filters rather than pulling whole records one at a time. Combined with SuiteAnalytics Connect for driver-based access, it means you can express extraction as queries instead of record loops.

The catch is governance. NetSuite meters API work in usage units and caps concurrent requests per account, so a query that scans every transaction since inception will either time out or starve your other integrations. The fix is the watermark pattern: filter on lastModifiedDate greater than the high-water mark from your last successful run, page through the result, and only advance the watermark when the batch has committed in Snowflake. That turns a multi-million-row table into a few thousand changed rows per run.

Store the watermark with a small safety overlap, a few minutes back from the true maximum, to catch records committed during the previous run's window. The overlap creates duplicates by design, which is fine because the load is an upsert.

How should you model NetSuite data in Snowflake?

Model it as a star schema: one transaction fact table plus dimensions for entities, items, accounts, and subsidiaries. Finance questions are almost always "amount, sliced by something," and that shape answers them cheaply. Keep the raw landing tables close to the NetSuite field names so a new custom field is an added column, not a broken load.

NetSuite record.field	Snowflake table.column	Type and notes
Transaction.internalId	FCT_TRANSACTION.transaction_id	NUMBER, primary key, MERGE key
Transaction.tranDate	FCT_TRANSACTION.tran_date	DATE, normalize the account timezone before casting
Transaction.postingPeriod	FCT_TRANSACTION.period_id	NUMBER, join to DIM_PERIOD, not the same as tran_date
Transaction.amount	FCT_TRANSACTION.amount	NUMBER(18,4), transaction currency
Transaction.exchangeRate	FCT_TRANSACTION.exchange_rate	NUMBER(18,8), the rate actually applied
derived (amount × rate)	FCT_TRANSACTION.base_amount	NUMBER(18,4), consolidated currency, usually USD
Transaction.posting	FCT_TRANSACTION.is_posting	BOOLEAN, filter non-posting docs out of GL reporting
Entity.internalId	DIM_ENTITY.entity_id	NUMBER, customers and vendors
Item.internalId	DIM_ITEM.item_id	NUMBER
Account.internalId	DIM_ACCOUNT.account_id	NUMBER, carry acctType for statement grouping
Subsidiary.internalId	DIM_SUBSIDIARY.subsidiary_id	NUMBER, carry base currency per subsidiary
lastModifiedDate	FCT_TRANSACTION.loaded_at	TIMESTAMP_NTZ, watermark and dedupe ordering key

Custom fields deserve a plan of their own. NetSuite implementations accumulate custbody and custcol fields, and they change without warning when an admin ships a workflow. Land them in a VARIANT column or auto-add typed columns, but don't hard-code a fixed column list and hope. The same discipline applies to the adjacent spend data finance wants next to the GL, where tools that categorize company spend automatically can fill in the card and vendor detail the ERP records only in summary.

How do you handle multi-currency and multi-subsidiary data in the warehouse?

Store three things per transaction line: the amount in the transaction currency, the amount in the subsidiary's base currency, and the exchange rate NetSuite applied. Never recompute consolidated amounts from a spot rate table after the fact, because NetSuite's rate at posting time is the one the auditors will check. Then key every fact row to a subsidiary so eliminations and per-entity reporting stay possible.

Two more distinctions trip up warehouse builds. First, accounting period is not transaction date: an invoice dated March 31 can post to a different period, and GL reports run by period, so your fact table needs both. Second, posting versus non-posting: sales orders, quotes, and purchase orders live in the same transaction record type as invoices and journals but never hit the GL. Sum them together and your revenue number will be spectacularly wrong.

Closed periods are the reason you can't simply append. Controllers reopen a period, adjust a journal, and re-close it, which changes a row you loaded three weeks ago. Because the extract is keyed on lastModifiedDate, that edited record comes back in the next batch and the MERGE corrects history in place.

How do you handle deletes, edits, and duplicate rows?

Use MERGE on internalId, and dedupe the incoming batch before merging with ROW_NUMBER() OVER (PARTITION BY internal_id ORDER BY last_modified_date DESC) = 1 so only the newest version of each record lands. That makes the load idempotent: replaying a failed run updates the same rows rather than double-counting revenue. Deletes need a separate pass against NetSuite's deleted-records log, flagging rows as is_deleted rather than hard-deleting them.

Soft deletes matter for finance more than for most domains. If a journal disappears from the warehouse silently, the variance shows up as an unexplained swing in a close package with no audit trail. A deleted flag plus a loaded_at timestamp lets you answer "what changed since the last close, and when."

Then reconcile. Build a query that sums base_amount by account and period for posting transactions only, and compare it to the NetSuite trial balance for the same subsidiary and period. Run it as a scheduled check, not a one-time validation. A tie-out that passed in January will fail in June the first time someone adds a subsidiary you never mapped.

How often should you sync NetSuite to Snowflake?

Hourly for transactions during the month, every fifteen minutes during close if your team works that fast, and nightly for slow dimensions like accounts, subsidiaries, and items. Anything faster rarely changes a decision and does consume governance units that your other NetSuite integrations need. Match cadence to the report that consumes the table.

The bigger risk isn't latency, it's a load that stops quietly. Dashboards keep rendering with stale data, and nobody notices until the controller asks why revenue flatlined on the 4th. Alert on run failure, on zero-row batches during business hours, and on a watermark that hasn't advanced. Pair that with a per-record log so a failed batch is diagnosable instead of mysterious.

The NetSuite to Snowflake integration handles the watermark, the MERGE, retries, alerting, and the log, so the load keeps running through close without someone watching it. If your reporting also depends on CRM data, the Salesforce to Snowflake connector lands pipeline next to the GL in the same warehouse, and the ETL and ELT platform runs both on flat pricing rather than per-row billing that spikes at month end. If you're currently evaluating heavier iPaaS tooling, the Celigo alternative comparison covers where the cost differences show up.