The NetSuite Extraction Guide

NetSuite is the opposite of the SaaS ERPs that have no SQL path at all: it has one, and only one. NetSuite2.com is the analytics source, and every bulk path reads it — the SuiteAnalytics Connect drivers (ODBC, JDBC and ADO.NET) and SuiteQL, in-account or over the REST query service. The legacy NetSuite.com data source was removed as of 2026.1, so the two-sources era is over and there is no second answer to design around.

The live authority for what a record and its columns look like is the in-account Records Catalog — which needs a NetSuite login, and is therefore never cited on this site. That is the reason this reference is pinned the way it is (Analytics Browser 2021.1 · corroborated 2025.2): the Analytics Browser 2021.1 is the last Oracle-authored, publicly reachable, field-level catalog of the same source, and each record here is corroborated for continued existence against the current public SuiteScript Records Browser. Treat both as a map, and the metadata records in the next section as the territory.

Connect is an ordinary driver install and an extraordinary permission model. Access mirrors the UI: a Connect session sees exactly what the role it authenticated as would see on screen, so an extract that comes back short is a permissions question before it is a SQL question, and the same query run under two roles is entitled to return two different row counts. Design for a purpose-built integration role whose permissions you control and can document, not for whichever login was convenient.

The restriction that catches new implementations: the Administrator role cannot be used for Connect access except through OAuth 2.0. Bulk extraction therefore runs under a dedicated role either way — plan the permission set as a deliverable, and re-check it whenever the role is edited, because nothing in the data will tell you a column went dark.

You do not have to invent that role: Oracle names the Data Warehouse Integrator role as the preferred option for transferring data to a warehouse, with all-data reach across the NetSuite2.com source — and two documented overrides worth knowing before an extract comes back short. Credit-card data is excluded even under it, and Global Permissions take precedence: a permission set to None wins over the role, silently.

Discovery is a query, not a document. Two metadata records — oa_tables and oa_columns — list what your own credentials can reach, which is the only listing that is true for your pipeline. One qualification: under the Static Data Model a role can see the structure and names of every available record and field while only being able to read the data it has access to — so the metadata records can list more than your extract will actually return, and a row-count reconciliation is still the test that settles it.

-- Before you plan an extract, ask the source what your own credentials can
-- actually see. Connect exposes two metadata records, and they answer the
-- question a schema browser cannot: visibility is ROLE-scoped, so the catalog
-- your integration role sees is not the catalog the documentation lists.
SELECT * FROM oa_tables;   -- one row per exposed record
SELECT * FROM oa_columns;  -- one row per exposed column

-- Two habits worth forming on day one:
--   1. Snapshot both metadata records into bronze on every run. They are the
--      only cheap early warning that a role change or a NetSuite release
--      removed a column you model on.
--   2. Diff the column list against what your pipeline SELECTs, rather than
--      against what you remember configuring.

SuiteQLis SQL-92 over the same NetSuite2.com source, reachable two ways: in-account from SuiteScript's query module, and over REST at /services/rest/query/v1/suiteql. The REST service caps a response at 100,000 rows and pages with limit and offset — a real constraint, but not the deciding one.

One syntax warning from Oracle before you write joins, because “SQL-92” invites exactly the style Oracle recommends against here: SuiteQL accepts both ANSI SQL-92 and Oracle SQL syntax (not mixed in one query), but against the analytics source Oracle recommends Oracle syntax and warns that queries converted from ANSI risk critical performance issues — timeouts that are not operationally remediable. The one exception runs the other way: right outer joins cannot be written in Oracle syntax, so those stay ANSI. This applies to queries sent to NetSuite; the Databricks SQL on this site runs against the landed copy and stays ordinary ANSI.

The deciding one is operational. A Connect driver is the bulk path: it streams a large result into whatever your ingestion tool already speaks. SuiteQL wins where a driver is overhead — small reference and list records you want to snapshot on a schedule, an ad-hoc pull during modeling, a lightweight service that needs a handful of rows and no ODBC layer on the host. Both read the same rows, so the choice is about plumbing, not about truth.

One SuiteQL feature does not survive the trip into a lakehouse. NetSuite's display-value function resolves an internal id to its label at extraction time — it exists in the query engine, not in your Delta tables. Do not build silver on it: land the ids, land the list records they point at, and decode in the lakehouse with a join you can re-run and audit (quirks #display-values). A decode that only exists at extraction time is a decode you cannot reproduce.

Land driver output as files into a bronze schema under Unity Catalog — Auto Loader for a continuously arriving drop, COPY INTO for scheduled batches. Keep identifiers lowercase exactly as NetSuite2.com renders them (transactionaccountingline, lastmodifieddate): every snippet on this site assumes that spelling, and a re-cased bronze layer breaks the match for no benefit. Then layer as usual — bronze source-aligned, silver cleaned and conformed, gold modeled.

Silver is where two NetSuite habits get fixed once. Check-box columns arrive as 'T' / 'F'VARCHAR strings, never as booleans, so decode them with a CASE at the silver boundary rather than in every downstream query (quirks #tf-booleans). And the spine's two disciplines belong in the silver view, not in the analyst's hands: filter the line-grain read to non-header rows, and reach accounting amounts only through the transaction line with an accounting-book anchor on the join.

-- The bronze-to-silver read every downstream model starts from: line grain,
-- one subsidiary, and the accounting amounts reached the only supported way.
-- Identifiers stay lowercase exactly as NetSuite2.com renders them.
SELECT
  t.id,
  t.tranid,
  t.trandate,
  t.entity,
  tl.item,
  tl.quantity,
  tl.netamount,                      -- transaction currency
  tal.amount,                        -- TAL duplicates once per accounting book (max 5) and is stated in the subsidiary's base currency; TL is transaction currency — see quirks #tal-and-books
  CASE WHEN t.posting = 'T' THEN true ELSE false END AS posts_to_gl
                                     -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
FROM <catalog>.<schema>.transaction t
JOIN <catalog>.<schema>.transactionline tl
  ON tl.transaction = t.id
JOIN <catalog>.<schema>.transactionaccountingline tal
  ON tal.transaction = tl.transaction
 AND tal.transactionline = tl.id
 AND tal.accountingbook = <accounting_book_id>
WHERE
  tl.subsidiary = <subsidiary_id>  -- OneWorld partition — see quirks guide #oneworld
  AND tl.mainline = 'F'  -- line grain; 'T' is the document header row — see quirks guide #transaction-spine
  -- AND t.type = '<type>'  -- discover values in-account: SELECT DISTINCT type
;

-- The GL leg: the accounting line carries an account column, and joining it
-- to the landed chart of accounts is how a document becomes a GL reading.
-- This reference draws NO edge for that join - neither record page attests it
-- - but the join is real, and you write it yourself:
--   JOIN <catalog>.<schema>.account a ON a.id = tal.account

The last two lines of that snippet are a deliberate omission made visible. The accounting line carries an account column, and joining it to the landed accountrecord is the practical path from a document to the chart of accounts — but neither record's page attests that join, so this reference draws no edge for it and states the pointer instead. The same treatment applies to the tax-code and expense-category pointers in the financials module: real columns, no page-attested join, no edge drawn.

Start with the rule, because it is the opposite of what most sources imply: incremental extraction is the exception here, not the default. Seven records in this whole catalog carry a modification stamp — entity, customer, vendor, item, transaction and fulfillmentrequest carry lastmodifieddate, and transactionline carries linelastmodifieddate, which is a different column with a different type (a plain date, not a timestamp — compare it to a DATE literal or you silently widen the window by a day). The other sixty-three records have no watermark column at all, and their table pages say so individually. For those, a scheduled full refresh is not a fallback, it is the design.

One of the sixty-three deserves naming out loud: transactionaccountingline has no modification stamp. The GL exhibit in #landing joins it for base-currency amounts, so an accounting-line table maintained by a watermark you borrowed from the header will drift — the header stamp says nothing about whether the accounting projection under it changed. Refresh the accounting lines for the documents in your window, on the same schedule as the transaction lines.

Where a watermark does exist, it catches inserts and updates only. A deleted row does not arrive with a flag; it simply stops existing, and a watermark-only pipeline keeps it forever. deletedrecord is the ledger of what went away — but read what it actually carries before you design around it: the record type, the name, the script id, and who deleted it and when. The analytics record exposes no internal id for the deleted row, so it is a detection surface rather than a key set, and the keyed anti-join delete other ERPs on this site can write is not buildable from it. Window it, aggregate by type, and let it tell you which landed tables went stale — then refresh those. One coverage limit before you trust a clean result: the ledger tracks only the record types NetSuite enables deletion tracking for, and Oracle's enumerated list for the analytics data source is smaller than the legacy one — so check your own tables against that list first, because a record type outside it loses rows without ever writing a ledger row.

The gap the ledger leaves on top of that is grain. It records deleted records, not deleted lines: remove one line from a surviving order and nothing is written there at all. Line-grain tables therefore need a periodic full refresh, or a re-pull of every document touched in the window keyed on the header id — a design-time decision, not something to discover at quarter close.

-- 1. Inserts and updates, for the SEVEN records that carry a modification
--    stamp. The transaction header carries lastmodifieddate; the line carries
--    linelastmodifieddate, a different column with a different type. MERGE
--    keyed on the record's own id makes a re-pulled overlap window harmless.
MERGE INTO <catalog>.<schema>.transaction AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY id
      ORDER BY lastmodifieddate DESC
    ) AS rn
    FROM <catalog>.<staging_schema>.transaction_batch
  ) WHERE rn = 1
) AS src
ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- 2. Deletes. They never reach the watermark - a deleted row simply stops
--    existing. deletedrecord is the ledger of what went away, and it is a
--    DETECTION surface, not a key set: the analytics record exposes the
--    record type, the name, the script id, and who deleted it and when. It
--    does NOT expose the deleted row's internal id, so a keyed anti-join
--    delete cannot be built from it. Window it and aggregate by type to find
--    out WHICH landed tables went stale since the last load:
SELECT
  dr.recordtypeid,
  dr.type,
  COUNT(*)              AS deleted_rows,
  MAX(dr.deleteddate)   AS last_deletion
FROM <catalog>.<schema>.deletedrecord dr
WHERE dr.deleteddate >= TIMESTAMP '<watermark>'
GROUP BY dr.recordtypeid, dr.type
ORDER BY deleted_rows DESC;

-- 3. Act on it with a refresh, not a DELETE. Any record type appearing above
--    has lost rows your landed copy still holds, so full-refresh the tables
--    it maps to - which for the spine means the whole document family, since
--    every document type shares one table.

-- 4. What the ledger never shows at all: a LINE removed from a surviving
--    document. It records deleted RECORDS, not deleted lines, so line-grain
--    tables (transactionline, transactionaccountingline, inventoryassignment)
--    need a periodic full refresh on a schedule of their own, or a re-pull of
--    every document touched in the window keyed on the header id. See
--    quirks #deletes.

So the shape of a working NetSuite pipeline is two schedules, not one: a watermarked incremental over the seven records that support it, and a full refresh over everything else — plus the line-grain tables, which need the refresh whatever their stamp says. Most of the sixty-three are small reference and configuration records that change rarely and cost little to reload. The temptation to approximate the missing watermark with a date column that means something else — a transaction date, an effective date, a period start — is the one to resist: those columns describe the business event, not the row's last edit, and a row corrected after the fact never moves them.

Two delivered records in this catalog are marked as views — salesordered and salesinvoiced— and their own table pages say the same thing this section does: land the spine instead. They are NetSuite's computations over transaction and transactionline, with estimated-cost and gross-profit columns whose inputs you cannot see, so anything that has to reconcile has to be built from the spine rows underneath them.

Nothing to extract, in two more places. The per-item-type record pages carry joins and no fields at all — every item type shares one wide item record, and the type-specific pages are stubs (quirks #item-polymorphism). And the work-order convenience projections over the spine exist, but cataloging them would teach that work-in-process is a manufacturing-table query when it is a spine query filtered by document type; the record map is how you get from a business document to the table that holds it.

The managed alternative. Oracle sells NetSuite Analytics Warehouse— a pipeline, a warehouse and a semantic model with ready-made KPIs and dashboards, all managed. It is a genuine fork in the road, and it is a build-versus-buy decision rather than a technical one: NSAW gets you reporting fast inside Oracle's model, while Connect into your own lakehouse gets you NetSuite beside every other source you own, on your own grain and your own semantics. If NetSuite is one of several systems a supply chain question spans, the lakehouse path is the one that can answer it.

The managed road into your own lakehouse. There is now a third fork between building on Connect yourself and buying NSAW: Databricks ships a managed Lakeflow Connect NetSuite connector (opens in new tab) that lands NetSuite into Unity Catalog with none of the Connect plumbing. Its documented limits (opens in new tab) decide whether it fits: it reads the NetSuite2.com source only, authenticates with token-based auth only, supports at most 200 tables per pipeline, and cannot ingest tables with 300 or more columns. The limit that interacts with this guide's own advice: it cannot ingest the deleted-record ledger — so the managed path loses the one delete signal the source has, and still needs a small Connect or SuiteQL leg for deletedrecord, or the full-refresh policy #incremental already describes. Verify the current limits on the Databricks pages before you commit; they are dated, and this paragraph was verified August 2026.