A dbt Model for NetSuite Reporting That Ties to the GL

Finance teams in energy, infrastructure and other multi-entity groups often ask for the same thing: balance sheet, income statement and budget reporting in the group currency, that ties back to NetSuite. A layered dbt model over NetSuite data is a reliable way to get there. This is the structure I use.

Layer 1: raw and staging

Land NetSuite data in your warehouse with an ETL tool such as Fivetran, using SuiteAnalytics Connect (the NetSuite2.com data source). Staging models should be thin: rename columns, cast types and filter obvious noise, with one staging model per source table. Resist the temptation to put business logic here.

You can also skip the staging data stage by build an analytics dataset directly in NetSuite

Layer 2: intermediate dimensions and the GL fact

This is where the NetSuite-specific knowledge lives. Build clean dimensions for accounts, subsidiaries, accounting periods and classifications, then a GL fact at transaction-line level from the accounting lines. Getting posting transactions, accounting books and period handling right here saves every downstream report.

Layer 3: consolidated marts

Marts for the balance sheet, income statement and budget versus actual sit on top of the GL fact. For multi-currency groups the hard parts are:

• Using the right exchange rate type for each account class (historical, average and period-end).

• Calculating the cumulative translation adjustment so the balance sheet balances.

• Rolling retained earnings forward correctly between years.

If these are modelled once and tested, the group balance sheet closes without manual plug journals.

Make reconciliation a test, not a task

The difference between a dashboard people trust and one they check in Excel is automated tie-out. Add tests that compare model totals to NetSuite’s own trial balance and income statement by period and subsidiary, and fail the run when they differ. Keep a transaction-level drill-down so any number can be traced back.

Keep reporting categories in data, not in formulas

A mapping table that rolls GL accounts into standard reporting categories, with explicit priority rules, lets finance change the presentation without touching SQL. It also removes manual categorisation from the month-end process.

If you want NetSuite data you can reconcile, see my NetSuite data warehouse reporting service.