06-reference/research

netsuite suiteql budget actuals feasibility

2026-07-26·research-brief·source: deep-research·by Ray Data Co (deep-research synthesis)
netsuitesuiteqlerp-integrationvariance-analysisbrigade-rail

NetSuite SuiteQL over REST for budget-vs-actuals: what can be established without an account, and the test plan for when we get one

The question

Confirm against a live NetSuite account whether SuiteQL over REST can return budget records joined to actuals, and what the practical row/timeout ceiling is.

The live-account test remains outstanding and was NOT run for this brief. RDCO has no NetSuite account, sandbox, or partner instance as of 2026-07-26. Nothing below was executed against a real NetSuite instance. What this brief delivers instead is (1) everything establishable from vendor documentation and independent practitioner evidence, (2) a copy-pasteable test plan with pass/fail criteria and outcome routing, and (3) a provisional answer with an explicit confidence label. This question is BLOCKING for the ab-managerial-accounting brigade: the answer selects between a native API pull, a transaction-line reconstruction, and a paid licensed-ODBC dependency.

Evidence in this brief is tagged: [DOC] = vendor primary documentation, [PRACT] = independent practitioner report, [INFER] = Ray's inference, [UNKNOWN] = could not be sourced, deliberately not guessed.

What we already know (from the vault)

What the web says

Convergences and contradictions

Synthesis for RDCO

Provisional answer, explicitly NOT live-verified. SuiteQL over REST can very probably return budget records, and can very probably return actuals, and the two can very probably be joined - but the honest confidence split is uneven and should be carried that way into scoping. Budget-table readability via SuiteQL: MEDIUM-HIGH confidence (one named practitioner source at column level, one probable first-party Oracle usage, no contradicting source). Actuals readability via TransactionAccountingLine: HIGH confidence (multiple independent sources, widely used pattern). A single-statement budget-joined-to-actuals result set: LOW-MEDIUM confidence (no source demonstrates it; the best source explicitly deferred it to a post that never appeared). The 100,000-row ceiling without SuiteAnalytics Connect: HIGH confidence, Oracle primary, quoted verbatim. The timeout ceiling: UNKNOWN, no number from any named source, and I am deliberately not producing one.

The ceiling is probably not binding for the actual use case, and that matters more than the ceiling itself. [INFER] The brigade does not need to stream raw transaction lines; it needs a pre-aggregated grid at account × period × dimension × scenario. A mid-market client at 200-800 GL accounts, 12 periods, and say 20 departments is 48,000 to 192,000 dense cells - so the dense cross-product straddles the 100,000 line, but real grids are sparse because most accounts never post to most departments. The practical read is that a server-side-aggregated pull (GROUP BY in the SuiteQL statement, one row per populated cell) lands comfortably under 100,000 for a single fiscal year at a single client, while a client-side-aggregated pull (fetch raw lines, sum locally) blows through it on any real transaction volume. That single architectural choice - aggregate in SuiteQL, not in Python - is what decides whether NetSuite is API-ingestible or ODBC-gated, and it is decidable before the test. Design the adapter that way regardless of outcome.

Route the three ingestion paths off the test, not off a guess. Path A, native API pull: selected if the budget tables are readable with a normal integration role and a server-side-aggregated budget-joined-to-actuals statement returns correct numbers under 100,000 rows within an acceptable wall clock. Path B, transaction-line reconstruction: selected if actuals are readable but budgets are not (permission-blocked, table absent, or the client's budget simply does not live in NetSuite - which the parent brief flags as the common mid-market case anyway). Under Path B the budget adapter becomes a separate integration against Excel or a planning tool, and the NetSuite adapter only ever delivers the actuals half. Path C, paid SuiteAnalytics Connect: selected only if the aggregated pull still exceeds 100,000 rows or times out. [INFER] Path B is the most likely real-world outcome for reasons that have nothing to do with SuiteQL - the parent brief already established that for most mid-market teams the budget does not live in the ERP. That reframes the test's value: it is less "can we do this" and more "for the subset of clients who do budget inside NetSuite, is the happy path available."

The commercial framing should change now, before the test. Under any outcome, NetSuite ingestion is a two-adapter build (actuals adapter plus a budget adapter that may or may not point at NetSuite), while Intacct is one call. That difference is real, it is knowable today, and it should be priced today. Do not quote a NetSuite client the Intacct SLA, and do not wait for a live test to make that distinction. What the test changes is narrower: whether a NetSuite engagement carries a client-side license dependency (Path C) or not. That is a contract-risk question, and it is worth surfacing to any prospect as a discovery question - "does your budget live in NetSuite, and do you have SuiteAnalytics Connect?" - which incidentally is a faster way to get the answer than acquiring a test account.

Test plan (run when a live account is available)

Prerequisites

  1. NetSuite account with the REST Web Services feature enabled (Setup > Company > Enable Features > SuiteCloud).
  2. An integration record and access token. TBA (OAuth 1.0a) or OAuth 2.0 machine-to-machine. Do not use a UI login.
  3. A role carrying, at minimum: REST Web Services permission, SuiteAnalytics Workbook permission [PRACT, verify], and Transactions - Set Up Budgets [PRACT, Dietrich - this is the one most likely to be missing on a stock integration role, and its absence is itself a finding].
  4. Every request: POST /services/rest/query/v1/suiteql, header Prefer: transient [DOC, required], Content-Type: application/json, body {"q": "<statement>"}.

Sourcing a live account [INFER]: cheapest legitimate paths, in order - (a) an existing client or prospect grants a read-only integration role in their sandbox, which doubles as a discovery conversation; (b) an Oracle NetSuite SuiteCloud Developer Network / partner NFR development account; (c) a contact whose employer runs NetSuite. Note that (a) also answers the more important commercial question (does the budget live in NetSuite) at the same time.

Step 0 - Schema discovery (do this first; it de-risks every statement below)

Column and foreign-key names below are reconstructed from a 2023 practitioner post and are not verified against the current Records Catalog. Resolve them empirically before assuming any statement is correct.

SELECT * FROM budgets WHERE ROWNUM <= 1

SELECT * FROM budgetsmachine WHERE ROWNUM <= 1

SELECT * FROM transactionaccountingline WHERE ROWNUM <= 1

Also open the Records Catalog in the account UI (Analytics > Datasets > New, or the SuiteAnalytics record browser) and confirm whether budget, budgetimport, and budgetexchangerate appear as SuiteQL-addressable tables. [UNKNOWN] No source consulted for this brief confirms budgetimport or budgetexchangerate exposure to SuiteQL. Record what you find.

Dialect note to resolve here: SuiteQL is Oracle-flavored. Confirm whether ROWNUM, FETCH FIRST n ROWS ONLY, WITH (CTEs), and FULL OUTER JOIN are each accepted. Practitioner reports on CTE support are inconsistent and I found no primary source. If CTEs are unavailable, the Step 4 statement must be rewritten with inline subqueries.

Step 1 - Budget side readable?

SELECT
  b.id, b.year, b.subsidiary, b.department, b.category, b.account, b.total
FROM budgets b
WHERE ROWNUM <= 10

PASS: rows return with recognizable budget amounts. FAIL modes to distinguish and record separately: (a) HTTP 403 / permission error -> role lacks "Transactions - Set Up Budgets", fixable, retry with an amended role; (b) "invalid record type" / unknown table -> the table is not SuiteQL-addressable in this NetSuite version, a real capability finding; (c) zero rows -> the account has no budgets loaded, test is inconclusive, find another account.

Step 2 - Period-level budget grain

SELECT
  bm.period, bm.account, bm.amount
FROM budgetsmachine bm
WHERE ROWNUM <= 10

Then resolve the FK from budgetsmachine back to budgets using the Step 0 output and run the joined form:

SELECT
  ap.periodname, b.subsidiary, b.department, b.category,
  a.acctnumber, a.acctname, bm.amount
FROM budgets b
INNER JOIN budgetsmachine bm ON bm.<budget_fk> = b.id
INNER JOIN accountingperiod ap ON ap.id = bm.period
INNER JOIN account a ON a.id = b.account
WHERE ap.startdate >= TO_DATE('2026-01-01','YYYY-MM-DD')

PASS: monthly budget amounts by account and department. This is the make-or-break step for Path A - annual-only budget (Step 1 only, no period grain) is not sufficient for monthly variance commentary.

Step 3 - Actuals side, server-side aggregated

SELECT
  t.postingperiod        AS period_id,
  tal.account            AS account_id,
  tl.department          AS department_id,
  tl.class               AS class_id,
  tl.subsidiary          AS subsidiary_id,
  SUM(tal.amount)        AS actual_amount
FROM transactionaccountingline tal
INNER JOIN transactionline tl
        ON tl.transaction = tal.transaction
       AND tl.id = tal.transactionline
INNER JOIN transaction t
        ON t.id = tal.transaction
WHERE t.posting = 'T'
  AND tal.posting = 'T'
GROUP BY t.postingperiod, tal.account, tl.department, tl.class, tl.subsidiary

PASS: aggregated actuals, one row per populated cell. Assert before proceeding: tie the sum for one revenue account and one expense account for one period against the same figures in the NetSuite UI Income Statement. This is the sign-convention check the parent brief flags as the single most embarrassing available failure. Record whether revenue comes back negative.

Step 4 - The actual question: budget joined to actuals in one statement

SELECT
  ap.periodname,
  a.acctnumber,
  a.acctname,
  b.department,
  SUM(bm.amount)      AS budget_amount,
  SUM(act.actual_amount) AS actual_amount,
  SUM(act.actual_amount) - SUM(bm.amount) AS variance
FROM budgets b
INNER JOIN budgetsmachine bm ON bm.<budget_fk> = b.id
INNER JOIN accountingperiod ap ON ap.id = bm.period
INNER JOIN account a ON a.id = b.account
LEFT JOIN (
  SELECT t.postingperiod AS period_id, tal.account AS account_id,
         tl.department AS department_id, SUM(tal.amount) AS actual_amount
  FROM transactionaccountingline tal
  INNER JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
  INNER JOIN transaction t ON t.id = tal.transaction
  WHERE t.posting = 'T' AND tal.posting = 'T'
  GROUP BY t.postingperiod, tal.account, tl.department
) act
  ON act.period_id = bm.period
 AND act.account_id = b.account
 AND act.department_id = b.department
GROUP BY ap.periodname, a.acctnumber, a.acctname, b.department

PASS: a budget-vs-actual grid returns, and spot-checked cells match the NetSuite UI Budget vs. Actual report. FAIL modes and what each means: (a) parser rejects the derived table -> rewrite without the subquery, or accept two separate pulls joined client-side, which is still Path A, just less elegant; (b) rows return but budget and actual columns never co-populate -> grain mismatch (budget at department only, actuals at department+class+project). This is the expected realistic failure and it is a data problem, not an API problem. It matches the parent brief's named "budget grain below actual grain" landmine, and the fix is to aggregate actuals up to the budget's grain, not to push the API harder; (c) HTTP 5xx or connection reset after a long wait -> record the elapsed time, that is the timeout number nobody has published.

Step 5 - Ceiling probe (measure, do not assume)

Run the Step 3 statement without GROUP BY (raw transaction lines) against the largest available fiscal year, then:

  1. limit=1000, offset=0. Record wall-clock milliseconds.
  2. Walk offset in increments of 1000 (offset must be divisible by limit [DOC]). Record wall clock per page and note whether latency degrades with offset depth.
  3. At offset=99000, then attempt offset=100000. Capture the exact error string. This is the empirical test of Oracle's 100,000-result statement.
  4. Separately, run one deliberately expensive statement (wide column list, no WHERE, multi-table join) and record either its completion time or its failure mode and elapsed time. This is the only way to produce the timeout number, which no source publishes.

Record all timings in a table and file them back into this brief. A single number is not enough - report p50 and worst-case page latency, because the adapter's retry and chunking policy depends on the tail.

Outcome routing

Test result Path selected
Steps 1-4 PASS, Step 5 confirms the aggregated pull is well under 100k and completes in acceptable wall clock Path A - native SuiteQL/REST pull. Build the adapter against the canonical intermediate schema. NetSuite becomes a same-tier-as-Intacct integration with more code.
Step 1 or 2 FAILS (permission-blocked, table absent, or annual-only grain), Step 3 PASSES Path B - transaction-line reconstruction. NetSuite adapter delivers actuals only; budget is a separate integration (Excel or planning tool). Scope and price as two integrations.
Step 4 FAILS on grain mismatch only Path A with a grain-normalization layer. Not a path change; add an explicit aggregate-to-budget-grain step and surface the lost granularity to the client during scoping.
Step 5 shows the aggregated pull exceeds 100k rows or times out Path C - SuiteAnalytics Connect (paid ODBC). Client-side license dependency. Becomes a commercial gate in the SOW, not an engineering task.

Why this is in the vault

This is the blocking dependency for the ab-managerial-accounting brigade's NetSuite adapter: it converts an untested scoping assumption inherited from [[2026-07-19-midmarket-variance-analysis-data-inputs]] into a documented capability floor (SuiteQL over REST is first-party documented, budget tables are practitioner-attested, the row ceiling is 100,000 without a paid Connect license) plus a runnable test that routes the build between three concretely different adapter architectures and two different SOW shapes. It also establishes the pre-test design decision - aggregate server-side in SuiteQL, never client-side - which is actionable on the adapter build today without waiting for an account.

Open follow-ups

Related

Sources

Vault

Web (fetched and read)

Web (surfaced in search, NOT fetched — claims limited to titles)

Access-blocked (flagged, not retried)

Explicitly excluded from evidence

Verification note: No query in this brief was executed against a live NetSuite instance. RDCO has no NetSuite account as of 2026-07-26. All SuiteQL statements above are constructed from documentation and a 2023 practitioner post; table names, column names, and foreign-key names are unverified against the current Records Catalog and Step 0 of the test plan exists specifically to correct them. The 100,000-result ceiling is Oracle-primary and quoted verbatim. No timeout figure appears in this brief because no named source publishes one.