NetSuite to Snowflake: Trial Balance and GL Reports

NetSuite's native reporting struggles with large transaction volumes. Saved searches slow down, formatting options limit what you can build, and producing a board-ready financial package becomes a manual effort. Moving reporting to Snowflake and Tableau solves both problems, but the data model requires careful handling.

The most common mistake I see is pulling from the transactions table directly and expecting it to match your NetSuite trial balance. It won't. Here's how to build a TB and GL report in Snowflake that actually reconciles.

Start with the Right Tables

The transactions table alone isn't enough. You need three core tables working together:

• transactions, the header record with trandate, postingperiod, subsidiary, and currency
• transactionlines, line-level detail including account, debitamount, creditamount, and memo
• transactionaccountinglines (TAL), the records that actually post to the general ledger

The TAL table is critical. It contains the amounts that hit your financial statements, including tax lines, intercompany eliminations, and currency revaluations that don't appear on the transaction lines themselves. As NetSuite's documentation explains, amount fields in transaction accounting lines store monetary values in the base currency of the subsidiary assigned to the transaction, and can only be consolidated for accounting purposes.

You also need:

• accounts for account names and types
• accountingperiods to match NetSuite's period logic exactly
• subsidiaries if you're consolidating multiple entities

Filter Out the Noise

Your Snowflake data is a raw dump of NetSuite, which means it includes records you don't want on financial statements. Two flags matter most:

WHERE tal.posting = 'T'          -- posted transactions only
AND t.isdeleted = 'F'            -- exclude deleted transactions

Without the isdeleted filter, you'll see voided transactions, deleted vendor bills, and reversed journal entries sitting in your table. They won't match NetSuite's reports because NetSuite excludes them by default. Transaction records don't have an isinactive field, so the isdeleted flag on the transactions table is the correct filter.

Build the Trial Balance Query

Here's a working SuiteQL query structure that forms the foundation for both TB and GL reports:

SELECT 
    ap.periodname AS posting_period,
    acct.acctnumber AS account_number,
    acct.acctname AS account_name,
    acct.accttype AS account_type,
    sub.name AS subsidiary,
    SUM(tal.debitamount) AS total_debits,
    SUM(tal.creditamount) AS total_credits,
    SUM(tal.debitamount - tal.creditamount) AS net_amount
FROM transactionaccountinglines tal
INNER JOIN transactions t ON tal.transactionid = t.id
INNER JOIN accounts acct ON tal.accountid = acct.id
INNER JOIN accountingperiods ap ON tal.postingperiodid = ap.id
INNER JOIN subsidiaries sub ON tal.subsidiaryid = sub.id
WHERE tal.posting = 'T'
AND t.isdeleted = 'F'
GROUP BY 
    ap.periodname,
    acct.acctnumber,
    acct.acctname,
    acct.accttype,
    sub.name
ORDER BY ap.periodname, acct.acctnumber;

This gives you the trial balance by period, account, and subsidiary. The transactionaccountinglines table is the key to making it reconcile, because it reflects the actual posted ledger activity.

The Retained Earnings Problem

Your trial balance won't tie out until you handle retained earnings. NetSuite doesn't store retained earnings as a GL balance. It's calculated as the sum of all prior-period income statement activity.

For each period, you need to calculate:

-- Retained earnings as of period start
SELECT 
    ap.periodname,
    SUM(CASE 
        WHEN acct.accttype IN ('Income', 'Expense', 'OtherIncome', 'OtherExpense')
        THEN (tal.debitamount - tal.creditamount)
        ELSE 0 
    END) AS prior_earnings
FROM transactionaccountinglines tal
INNER JOIN transactions t ON tal.transactionid = t.id
INNER JOIN accounts acct ON tal.accountid = acct.id
INNER JOIN accountingperiods ap ON tal.postingperiodid = ap.id
WHERE tal.posting = 'T'
AND t.isdeleted = 'F'
AND ap.startdate < :current_period_start
GROUP BY ap.periodname;

Then add that figure to your trial balance as a Retained Earnings line for the current period. NetSuite reports retained earnings as the sum of cumulative net income plus amounts posted directly to the retained earnings account through journal entries. Your auditors will thank you for having this documented in the query logic rather than buried in a spreadsheet.

Currency Gets Complicated

If you have subsidiaries in different currencies, your SQL needs to handle consolidated exchange rates. NetSuite applies different rates depending on whether you're looking at the transaction currency, the subsidiary currency, or the parent consolidated currency.

The transactionaccountinglines table stores amounts in the base currency of the subsidiary assigned to the transaction. For consolidated reporting, you need to join to the exchange rate tables or use the consolidated exchange rate fields that NetSuite stores on the accounting line.

NetSuite assigns two rate types to each account: a general rate type used for the income statement, balance sheet, and other general purposes, and a cash flow rate type used for cash flow statements. Your query needs to replicate this logic to match the consolidated trial balance.

This is where I've seen teams lose hours. The data won't reconcile to NetSuite's consolidated trial balance because NetSuite uses period-end rates for balance sheet accounts and weighted average rates for income statement accounts.

Validate Before You Build Dashboards

Before you spend time in Tableau, reconcile one closed period line by line. Export the native NetSuite trial balance for a period that's locked and compare it against your Snowflake query output.

Check these three things first:

1. Account-level totals, every account balance should match to the penny
2. Subsidiary breakdown, confirm each entity ties out before consolidating
3. Period boundaries, NetSuite's accounting periods may not align to calendar months if you use 4-4-5 or custom periods

If your totals are off, the usual culprits are:

• Missing the isdeleted filter
• Pulling from transactionlines instead of transactionaccountinglines
• Not handling intercompany eliminations
• Forgetting to exclude non-posting records like estimates or purchase orders

Connect Tableau to Snowflake

Once your SQL produces a trial balance that reconciles, connecting Tableau is straightforward:

1. Open Tableau Desktop and select Snowflake under Connect
2. Enter your Snowflake server URL
3. Choose authentication, Username/Password or OAuth if configured
4. Select your Warehouse, Database, and Schema from the dropdowns
5. Drag your TB view or the underlying tables onto the canvas

For governance, create a dedicated Snowflake role for your Tableau users. Don't grant them the same role your data engineering team uses. A read-only role scoped to the reporting schema is the control here.

Build the GL Report View

For the GL report, you'll want the same query structure but without the account-level grouping. Keep the transaction number, line number, date, memo, and posting period in the output. This gives you drill-down capability in Tableau from the TB to individual transactions.

Add filters for:

• Posting period
• Subsidiary
• Account number range
• Transaction type (journal, invoice, bill, etc.)

This is where the formatting advantages of Tableau show up. You can build a financial statement package with proper indentation, subtotals by account type, and drill-down links that NetSuite's native reports simply can't match.

Start With One Period

Don't try to build everything at once. Pick the most recent closed period, build the TB query, reconcile it against NetSuite, then expand to the GL detail. Once those two reconcile, add the period comparison and variance analysis in Tableau.

One closed period validated line by line is worth more than a dashboard that's 95% accurate. The last 5% is where the reconciliation headaches live, and you want to find those before your CFO does.