NetSuite to ClickHouse: SuiteAnalytics Connect, Sync Methods & Ledger Modeling Guide

NetSuite ERP data can be kept in ClickHouse for financial and operational reporting in five ways: export saved searches to CSV, script against SuiteQL over the REST API, query SuiteAnalytics Connect yourself, run an open-source ELT tool, or use a managed platform. An export suits a one-off look; anything finance queries every day needs a scheduled incremental sync. Neither product connects to the other directly.

The fastest read path, SuiteAnalytics Connect (Oracle's ODBC, JDBC and ADO.NET service), is a paid add-on. Every method finds changes by polling last-modified dates, and NetSuite's deletion log doesn't record deleted transaction lines. In ClickHouse, changed rows are inserted again, ReplacingMergeTree keeps the newest, and amounts need Decimal if the ledger is to balance. Estuary supports this route with a SuiteAnalytics Connect connector that syncs changed NetSuite records incrementally and a native ClickHouse materialization.

Choosing a NetSuite to ClickHouse method

The method depends on whether the account has SuiteAnalytics Connect, how fresh finance needs the data, and how much pipeline work you want to own.

Integration platforms are a poor fit: Celigo's ClickHouse connector calls ClickHouse Cloud's management API (services, backups, ClickPipes), not row inserts, so loading tables through it means building HTTP flows yourself. ClickPipes, ClickHouse Cloud's ingestion service, reads Kafka, object storage and a few databases, not NetSuite. For vendors beyond this table, see ETL tools for ClickHouse.

How NetSuite records reach ClickHouse tables

A NetSuite sync reads Connect's metadata (OA_TABLES, OA_COLUMNS, OA_FKEYS) to decide how each table can be read, backfills each table by paging on an integer key, then polls:

• Changed rows by timestamp. Tables with a column NetSuite flags as last-modified (lastmodifieddate, or linelastmodifieddate on transactionLine) are polled for rows changed since the last checkpoint.
• Whole-table re-reads. Tables with a key but no usable last-modified column, such as TransactionAccountingLine, are re-read on a schedule, so their freshness follows that schedule, not the polling interval.
• Snapshots. Tables with no usable key are read in full and compared with the previous run.
• Deletions come from NetSuite's deletion log, the DeletedRecord table, which gives each deleted record's type and internal ID.

In ClickHouse every polled row is an insert. A ReplacingMergeTree table ordered by the NetSuite key keeps the newest version of each row when parts merge, so overlapping polls are harmless, and queries add FINAL to see one row per key before the merge.

NetSuite requirements

Connect is a licensed, read-only add-on module, not available on NetSuite Small Business accounts. It reads the NetSuite2.com data source; the older NetSuite.com source is gone from 2026.1.

• Features. Enable SuiteAnalytics Connect under Setup > Company > Enable Features > Analytics; if it isn't listed, it hasn't been provisioned. Enable token-based authentication (TBA) under SuiteCloud > Manage Authentication.
• Integration record and token. Create an integration record with TBA (consumer key and secret, shown once) and an access token for a dedicated user (token ID and secret, shown once). Sandbox refreshes delete tokens, so recreate them per environment.
• Role. NetSuite2.com rejects the Administrator role except over OAuth 2.0, roles that require two-factor authentication, and IP-restricted roles. The Data Warehouse Integrator role reads all standard and custom data except credit-card data and can't log in to the UI; a custom role with view access to Transactions, Reports, Lists and Setup, and every subsidiary you need, works too. Scope it to the records you need.
• Account ID. From Setup > Company > Company Information; a sandbox looks like 1234567_SB2.
• Time zone. NetSuite2.com returns created and last-modified dates in the user's or company's time-zone preference, so fix the integration user's preference before the first load.

ClickHouse requirements

ClickHouse needs a database, a user, and tables keyed the way NetSuite keys its records.

• Database and user. Keep one ClickHouse database per NetSuite account; the pipeline's user needs DDL and write rights there. ClickHouse Cloud accepts only TLS connections (native protocol on 9440, HTTPS on 8443).
• Keys.ORDER BY the internal id for record tables, and (transaction, id) for transactionLine, because line IDs repeat across transactions.
• Amounts.Decimal(18, 2) holds amounts up to 16 integer digits exactly; ClickHouse recommends Decimal over Float64 for financial data.

sql
CREATE TABLE netsuite.transactionLine
(
    transaction           Int64,
    id                    Int64,
    linelastmodifieddate  DateTime64(3, 'America/Los_Angeles'),
    synced_at             DateTime64(3, 'UTC'),
    is_deleted            UInt8 DEFAULT 0
    -- plus the line columns you select
)
ENGINE = ReplacingMergeTree(synced_at, is_deleted)
ORDER BY (transaction, id);

Partition large tables only by a value that never changes for a row: ClickHouse merges within a partition, so versions of one row split across partitions are never collapsed.

Setting up NetSuite to ClickHouse replication

1. List the tables finance needs (transaction, transactionLine, TransactionAccountingLine, account, accountingPeriod, customer, vendor, item, subsidiary, currency) and the custom fields on them.
2. Confirm the Connect licence, then enable Connect and token-based authentication.
3. Create the integration record, role, user and access token.
4. Connect to NetSuite2.com and run one small test query.
5. Create the ClickHouse tables keyed on internal IDs (composite for line tables), with a sync timestamp as the version column.
6. Backfill table by table, largest last, with a start date if history is long.
7. Poll incrementally on lastmodifieddate, re-read tables without one on a schedule, and read DeletedRecord every cycle.
8. Validate. Compare count() with FINAL against NetSuite row counts. Change a transaction and delete a customer, and check each after one cycle. Finally, total TransactionAccountingLine amounts per account and period in one accounting book, and check them against a NetSuite trial balance filtered the same way.

What happens after the initial load?

Each sync writes changed rows as new versions and ClickHouse keeps the latest per key, but deletes and some tables need their own handling.

• Edits arrive as new row versions, headers and lines by their own last-modified columns. Totals need FINAL, or a view using it: SELECT * FROM netsuite.transaction FINAL.
• Record deletions of transactions, entities, items and custom records appear in DeletedRecord; the pipeline must turn each into a delete for the right table.
• Line deletions aren't logged. Removing a line from a sales order, or deleting a whole transaction, leaves its transactionLine and TransactionAccountingLine rows in ClickHouse until those tables are re-read and rows the re-read didn't return are removed.
• Custom fields carry prefixes such as custbody (transaction body), custcol (line), custentity, custitem and custrecord, and appear only if the role can see them, a new Connect session starts, and your tool selects them.
• NetSuite releases switch NetSuite2.com to the new version as soon as the account upgrades; a column whose type changes needs its ClickHouse table rebuilt.

NetSuite to ClickHouse data type mapping

Two traps break finance reports: amounts live in different currencies on different tables, and list or record fields (entity, item, subsidiary) arrive as internal IDs to join to lookup tables, not as labels (SuiteQL's BUILTIN.DF returns the display value). These types suit hand-built tables:

Amounts, currencies and accounting books

Amount columns decide whether the ClickHouse ledger ties out: sums of Float64 values drift, and converting a float to Decimal later truncates (ClickHouse documents toDecimal64(1.15, 2) returning 1.14). Currencies differ by table: transaction holds transaction-currency amounts, while TransactionAccountingLine holds the subsidiary's base-currency amounts, repeated per accounting book under Multi-Book Accounting. Oracle recommends joining Transaction to Transaction Line to Transaction Accounting Line, in that order, to avoid duplicated rows, and consolidating across subsidiaries needs consolidated exchange rates, which NetSuite sets per accounting period.

Performance, latency and ClickHouse cost

NetSuite is usually the bottleneck: Oracle positions Connect for data that doesn't change often, so poll each table only as often as its users need.

Connect query cost

• Avoid calculated columns. NetSuite computes them for every row, and they, along with some custom columns, can stall a query until it times out. Select the columns you use.
• Page on an indexed integer key. Offset paging re-reads the table for every page.
• Watch query width. Queries over 1,000 columns fail; list columns on wide tables.

Writes and FINAL in ClickHouse

NetSuite polls produce batches that suit ClickHouse, which asks for at least 1,000 rows per insert and about one insert query per second. Joins across transaction, transactionLine and TransactionAccountingLine need FINAL on each side. Partitioning a large ledger table by a month that never changes for a row keeps FINAL work within partitions (with do_not_merge_across_partitions_select_final = 1); ClickHouse advises keeping partitions under about a thousand.

Production considerations

• Retries. Oracle warns that Connect requests can fail while NetSuite is busy or restarting servers. Retry, and keep each query short; re-inserted rows collapse by key.
• Source impact. Keep connections well under NetSuite's limits; load history off-hours.
• Permissions. Re-check the role after admin changes; two-factor authentication or IP restrictions cut it off from NetSuite2.com.
• Monitoring. Alert on each table's checkpoint age and on time since the last ClickHouse insert, and reconcile the trial balance after each period close.

Common problems and troubleshooting

Transaction lines that NetSuite deleted are still in ClickHouse

Line counts in ClickHouse exceed NetSuite's after edits that remove lines. DeletedRecord never lists lines, so nothing tells the pipeline. Check one transaction:

sql
SELECT count() FROM netsuite.transactionLine FINAL WHERE transaction = 123456;

Then compare with NetSuite. To clean up, re-read the line tables on a schedule and run DELETE FROM netsuite.transactionLine WHERE synced_at < '<re-read start>', which removes rows the re-read didn't refresh; lightweight DELETE marks them at once and drops them at the next merge.

Totals that don't match NetSuite reports

Revenue or balances run high: amounts summed across currencies, subsidiaries mixed, several accounting books counted, or a join missing FINAL. Total base-currency amounts from TransactionAccountingLine per subsidiary, for one accounting book, and use consolidated rates across subsidiaries.

Connect sessions dropped or queries that never return

• Connection liveness limit reached: a token-based session passed its 60-minute limit; reconnect.
• TCP/IP error, connection reset by peer: at the same time each day, usually a NetSuite server restart; after two hours of a long query, the idle-connection timeout. Keep TCP keepalive below two hours and below any firewall timeout.
• A query that returns no rows before timing out: usually a calculated or custom column, or a cursor column NetSuite can't use as an index. Remove those columns from the binding before changing its cursor.

Using Estuary for NetSuite to ClickHouse

Here the NetSuite SuiteAnalytics Connect capture feeds Estuary's ClickHouse materialization, which creates ReplacingMergeTree tables over the native protocol, on ClickHouse Cloud or your own servers. Against the concepts above:

• Authentication: NetSuite2.com with token-based authentication; username and password is also accepted, but OAuth 2.0 isn't offered.
• Sync modes: discovery reads Connect's metadata for every table the role can access, which can take 20 minutes or more on large accounts, and enables about 50 common tables by default. Incremental tables poll every Interval (one hour by default), each query stopping two minutes short of now. Keyed tables without a cursor re-read daily at midnight UTC, or on the cron expression in the binding's Schedule; snapshot tables re-read daily and emit deletes for rows that disappeared.
• Deletes:DeletedRecord is in the default table set, and its rows become deletes in the matching collection: transaction types in transaction, item types in item, custom records in their own collection. Lines get no deletes. Deleted NetSuite records stay in ClickHouse, and in FINAL results, as rows whose _meta/op is d: filter them out, or turn on Hard Delete so FINAL skips their tombstones.
• Accounting lines:TransactionAccountingLine is backfilled once; afterwards, each incremental pass of transaction reloads the accounting lines of the transactions it returns. Lines removed from a transaction aren't deleted.
• Keys and columns:transactionLine is keyed on (transaction, id). Calculated, custom and hidden columns are left out of new bindings; Additional Columns brings back the custom fields you name.
• Amounts: the Connect driver decodes every NUMBER and DOUBLE value as a 64-bit float as it reads it, so amounts land as Float64, and digits beyond double precision are gone before Estuary writes anything. For exact cents in sums, set castToString on amount fields. Estuary then writes the shortest decimal text of each value (1.15, not 1.1499999…), and toDecimal64(amount, 2) converts that text exactly in a view.
• Other types: checkboxes stay the strings T and F. TIMESTAMP values without a time zone are written as DateTime64(6, 'UTC') without conversion, so a value in the integration user's zone is labelled UTC.
• Recovery and audits: a restarted capture resumes each table from its saved cursor; for finance audits, Estuary's security page lists SOC 2 Type II, HIPAA and GDPR compliance.

Implementing NetSuite to ClickHouse with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in NetSuite requirements and ClickHouse requirements. Estuary also needs:
   • The account ID and the four TBA values (setup).
   • ClickHouse 25.3 or later, with the materialization's grants, including SELECT on system.columns, system.parts and system.tables, and access from Estuary's IP addresses. The ClickHouse connector has no SSH tunnel option.
2. Create the NetSuite capture. On the Sources page, click New Capture, choose Netsuite SuiteAnalytics, name it, and fill in the endpoint properties:
   • Account: the account ID.
   • Suiteanalytics Data Source: keep NetSuite2.com.
   • Authentication: Token Auth, with Consumer Key, Consumer Secret, Token Id and Token Secret. Username/Password Auth defaults to role ID 3, Administrator, which NetSuite2.com refuses.
   • Advanced: Start Date limits history; Connection Limit (10) and Task Limit (5) cap load on NetSuite.

3. Select tables. Click Next and wait for discovery. Output Collections lists every table the role can see, with about 50 common tables enabled, DeletedRecord among them.
   • Enable the tables you need and disable the rest.
   • Leave each binding's Incremental Cursor and Backfill Cursor as discovered unless a table times out.
   • Keep Automatically keep schemas up to date on.

Click Save and publish. Backfills start, and incremental tables poll on their interval.

4. Create the ClickHouse materialization. On the capture's details page, click Materialize, choose ClickHouse, and fill in the endpoint properties:
   • Address: host:port; 9440 (TLS) is assumed without a port.
   • Database and Authentication (Username and Password).
   • Hard Delete: on, so deleted NetSuite records drop out of FINAL results.

5. Choose the sync schedule.Sync Frequency defaults to 30 minutes, which suits hourly NetSuite polls. Timezone, Fast Sync Start Time and Fast Sync Stop Time limit frequent syncs to working hours; outside them, syncs run every 4 hours.

6. Map collections to tables and keep amounts exact. Click Next, choose a naming convention, and review each binding under Source Collections:
   • Table: the ClickHouse table name.
   • Partition By (advanced): leave blank unless a large table has a value that never changes for a row. Not flow_published_at: it changes with every update, so a row's versions would land in different partitions and never merge.
   • Amount fields: set castToString: true under the binding's fields.include in the spec editor (custom column types), then convert with toDecimal64 in a view.
7. Publish. Click Save and publish. Estuary creates the tables and writes the backfill without waiting for the schedule.
8. Verify that data is landing.
   • The capture details page shows PRIMARY in its Shard Information card; a collection from its Collections list shows Data Preview.
   • The materialization details page charts Data Read.
   • In ClickHouse, run the trial-balance check from Setting up NetSuite to ClickHouse replication with FINAL.

How Estuary handles re-reads, rebuilds and ledger history

Each NetSuite table lands first in its own collection, stored as JSON files in your cloud storage bucket. ClickHouse tables are written from the collections, so rebuilding a table or adding a destination doesn't query NetSuite.

What a rebuild costs NetSuite

A rebuild can only replay what the collection still holds: roughly 20 days of NetSuite documents on trial storage, all of them in a bucket you own.

Line tables after a re-read

A capture backfill re-reads a table from NetSuite into its collection, and the ClickHouse rows it rewrites get a new flow_published_at. Rows deleted in NetSuite aren't rewritten, so DELETE FROM … WHERE flow_published_at < '<backfill start>' removes them: the scheduled-backfill-and-cleanup routine the connector's docs describe for tables without delete tracking.

Shippit replaced Fivetran with Estuary for NetSuite SuiteAnalytics, Salesforce and Intercom data going to Snowflake, not ClickHouse, and its success story says the "monthly bill dropped by 45%".

When another approach may make more sense

• Oracle's prebuilt finance warehouse: NetSuite Analytics Warehouse (Oracle Analytics Cloud on Autonomous AI Lakehouse) needs no pipeline, but keeps data in Oracle's stack.
• Orders and invoices moving between applications: an integration platform such as Celigo, rather than table replication.
• A one-time look at a few saved searches: CSV exports with the Internal ID column, loaded once.
• Fivetran already reads your NetSuite: its NetSuite connector also uses SuiteAnalytics Connect, and its ClickHouse destination (generally available since May 2026) supports ClickHouse Cloud only.
• No Connect add-on: Estuary's NetSuite SuiteQL connector (Beta) reads over REST with token-based authentication. It's full refresh only, re-reading keyed tables daily: a default list of tables plus any you add. A table wider than 100 columns needs its columns listed, or SuiteQL returns no rows.
• ClickPipes required by policy: a ClickPipe can subscribe to the NetSuite collections through Estuary's Dekaf-based ClickHouse Kafka API connector.

Related resources

• Estuary: NetSuite SuiteAnalytics Connect capture, NetSuite SuiteQL capture, ClickHouse materialization, ClickHouse via Dekaf, sync schedules, backfilling data, NetSuite connector, ClickHouse destination
• NetSuite: SuiteAnalytics Connect, NetSuite2.com access, deleted record tracking, Connect sessions
• ClickHouse: ReplacingMergeTree, Decimal, lightweight DELETE

To try the route, see the NetSuite to ClickHouse integration or create a NetSuite capture in the Estuary dashboard. Pricing: estuary.dev/pricing.