How to Query Sage Intacct Data with AI When Dimensions Don't Land as Tables

Sage Intacct's own marketing page for its financial-reporting engine is titled Multi-dimensional accounting, and the pitch on it reads:

Sage Intacct is the only multi-dimensional financial management system that lets you tag transactions with business context using dimensions such as location, department, project, customer, vendor, employee, item, and class. Dimensions replace complex, hard-to-maintain chart of accounts segments, so you can slice and dice your financial data on the fly.

The whole product identity is those dimensions. Every finance question worth asking of an Intacct estate is a pivot by one of them: revenue by department, spend by project, receivables by customer, an entity-by-entity close.

Replicate Intacct into a warehouse and the dimensions do not come with. The Fivetran connector lands exactly seven tables, and none of them is a dimension table. Every dimension arrives as a pair of denormalized strings on the transaction that carries it, and the join to a lookup table with a hierarchy is gone.

That is a different failure mode from the classic warehouse trap. The sum is not wrong. The buckets are wrong, and quietly wrong, in a way that survives review because the total across all buckets still ties.

Before you start

• Sage Intacct replicated into a warehouse. Postgres, Snowflake, BigQuery and Redshift all work.
• Read access. A read-only role is the right one.

The trial does not help here. Sage offers a self-serve 30-day trial with a personalized sample company, useful for touring the product in the UI. It does not include a Web Services subscription, and Fivetran's connector requires one plus a Sender ID and Sender Password provisioned by Sage, plus a Web Service User with the Admin role. There is no path from the trial to a warehouse without upgrading to a paid contract.

Nothing in a warehouse yet? Fivetran's connector, backed by Intacct's XML Web Services API, is the standard route. Sage's own Intacct Data Cloud for Snowflake is the zero-ETL alternative and it lands the same tables. Casing differs by extract tool: lowercase on BigQuery and Postgres, uppercase on Snowflake. Fivetran's transform package normalises to snake_case regardless.

So the queries below run against your own replicated schema, and they compute the gap on your data rather than quoting a number from ours.

The question

"What was our revenue by department, on posted invoices, this quarter?"

That is the first Intacct question anyone asks of a replicated estate. Every part of it is legitimate. Departments are one of the eight standard dimensions. Invoices post to Income accounts through the general ledger. Posted status lives on the header. One value per department, this quarter.

Getting from the tables to the number is where it goes wrong twice, in two directions that cancel each other out cosmetically.

What breaks

Here is the obvious query, against Fivetran's landed schema. Every join is on the correct key, it runs without complaint, and the result comes back neatly grouped:

select ari.departmentname as department,
       sum(ari.amount)    as revenue
from ar_invoice ar
join ar_invoice_item ari
  on ari.recordkey = ar.recordno
where ar.state = 'Posted'
  and ar.whenposted >= date_trunc('quarter', current_date)
group by 1
order by 2 desc;

Three things are wrong and none of them errors.

ari.amount is not revenue. An AR invoice can touch several accounts on one line: deferred revenue, sales tax, discounts, and the recognised income account. Summing the line amount adds the sales tax and any offsets alongside the income figure.

ari.departmentname is a snapshot from when Fivetran last synced. A department renamed since is bucketed under the old name for old rows and the new name for new ones, split across two rows in the result. The two are the same department.

Some invoices belong to the top-level company and land with a null departmentid. The query drops them without saying so.

Three counting queries establish the size of the gap against your own warehouse:

-- 1. how many dimensions the estate actually uses on AR line items
select count(distinct departmentname) as distinct_departments,
       count(distinct locationname)   as distinct_locations,
       count(distinct classname)      as distinct_classes,
       count(distinct projectname)    as distinct_projects,
       count(distinct customername)   as distinct_customers,
       sum(case when departmentname is null then 1 else 0 end) as ar_lines_no_dept
from ar_invoice_item;

-- 2. the drift signature: (id, name) pairs where the same id has been seen
-- under more than one name over time, on any of the eight standard dims
select 'department' as dim,
       departmentid as id, count(distinct departmentname) as names_seen
from ar_invoice_item
where departmentid is not null
group by 1, 2
having count(distinct departmentname) > 1
union all
select 'location', locationid, count(distinct locationname)
from ar_invoice_item
where locationid is not null
group by 1, 2 having count(distinct locationname) > 1
order by names_seen desc
limit 20;

-- 3. the naive line-item sum against the signed GL sum, side by side
select
  'naive_line_amount'                              as method,
  sum(ari.amount)                                  as figure
from ar_invoice ar
join ar_invoice_item ari
  on ari.recordkey = ar.recordno
where ar.state = 'Posted'
  and ar.whenposted >= date_trunc('quarter', current_date)
union all
select
  'signed_gl_income'                               as method,
  sum(gd.creditamount - gd.debitamount)            as figure
from gl_detail gd
join gl_account ga
  on ga.accountno = gd.accountno
where ga.accounttype    = 'incomestatement'
  and ga.category ilike '%revenue%'
  and gd.entry_state    = 'Posted'
  and gd.entry_date >= date_trunc('quarter', current_date);

The first query tells you how many buckets each pivot is about to split into, and how many rows silently disappear because the tag is null. The second returns any dimension id that has been recorded under more than one name, one row per drift event. Any row here is a bucket the first query is going to double-count. The third puts the naive line-item revenue and the corrected signed-GL revenue on adjacent rows so the finance team can see where they disagree.

Why it breaks

Seven tables land, and none of them is a dimension

Point Fivetran at a Sage Intacct account and count what lands. Fivetran's own dbt package src_sage_intacct.yml is the definitive list of tables the connector produces, and it holds seven: gl_detail, gl_account, gl_batch, ap_bill, ap_bill_item, ar_invoice, and ar_invoice_item.

What is not there matters as much. There is no location table, no department table, no class, no project, no customer, no vendor, no item, no employee, and no entity. Every one of Sage's own eight standard dimensions is missing, along with any user-defined dimension the estate has added.

The dimensions are still on the data, but only on the transaction rows that carry them, as a pair of columns. ar_invoice_item.departmentid and ar_invoice_item.departmentname. ap_bill_item.classid and ap_bill_item.classname. gl_detail.customerid and gl_detail.customername. Whichever dimensions are relevant to whichever transaction, denormalized alongside it, and never elsewhere.

The id half is stable. The name half is a snapshot at extract time. If the department is renamed in Intacct, the name in the warehouse drifts. If the department is merged with another, the old name persists on old rows. If it belonged to a group, the group is nowhere, because department groups live in the platform's dimension catalog and the catalog is not replicated.

The vendor's own transform package silently confirms it

The strongest evidence that this is the fault line is what Fivetran's own dbt package does with the tables it ships. The intermediate model that produces Intacct's shipped profit-and-loss report is int_sage_intacct__general_ledger_balances.sql, and its GROUP BY clause is legible:

group by account_no, account_title, book_id,
         category, classification, currency,
         entry_state, account_type,
         date_month, date_year

Ten columns. None of them is a dimension. No department, no location, no project, no class. Fivetran's own P&L is the account view of the world, not the dimension view. Every dimension is dropped on the way to the shipped number, because there is no clean way to aggregate on a denormalized string that may or may not still be the right name.

That is a second, independent citation that the dimension trap is real: the same team that lands the tables also declines to touch the dimensions in the one model that would have needed them.

The joins that look right but aren't

Two more traps sit next to the dimension one, and they fire even on estates that never rename anything.

ap_bill_item.recordkey, ap_bill_item.recordno and ap_bill_item.recordid all exist and they mean different things. recordno is the item's own primary key. recordid is the item's display document number, a string. recordkey is the foreign key back to the parent bill's recordno. Introspection that matches on column-name equality finds recordid = recordid between item and header and produces a join that runs and returns the wrong rows. Fivetran's transform explicitly joins on recordkey, and the description on ap_bill.recordid in src_sage_intacct.yml spells it out: "ID of bill. Maps to GL_DETAIL.RECORDID, which has all the records belonging to that bill."

gl_detail.recordid is polymorphic. The same recordid value can exist on an ap_bill.recordid and on an ar_invoice.recordid; nothing in the schema disambiguates it except gl_detail.recordtype, described in the connector docs as "Type of record, for example AP Bill". Join gl_detail to ap_bill on recordid alone and any AR invoice whose internal document number collides with a bill's silently attaches. The right form of that edge is:

gl_detail.recordid   = ap_bill.recordid
AND gl_detail.recordtype = 'APBill'

Every relationship declaration for either polymorphic edge has to carry the recordtype filter. Without it the join is not a foreign key in the ordinary sense, and calling it one gives an agent permission to use it that way.

The polymorphic edge, made safe by the recordtype filter. Both directions get the same treatment.

The debits and credits split, and the amount column deceives

gl_detail has three amount-shaped columns and only one of them is signed. debitamount and creditamount are both non-negative. amount is the unsigned magnitude. Every posted transaction produces at least two rows on gl_detail, a debit and its offset credit ("each has its offset entry to balance," per src_sage_intacct.yml), one on the debit column and one on the credit column. A SUM(amount) across the raw table adds both halves of every entry and returns roughly twice the transaction volume; a SUM(creditamount) - SUM(debitamount) on the raw table returns roughly zero, because every entry balances by construction.

The signed amount that means something is SUM(creditamount - debitamount) filtered to the account type that carries the sign convention you want. Income accounts are normal-credit: revenue posts on the credit side and refunds on the debit side, so the signed sum is the true revenue. Expense accounts are normal-debit, and the signed sum on them is negative if you compute credit minus debit. gl_account.normalbalance (documented as "Debit or credit") is what tells you which sign to apply.

The three boundaries the app enforced

Inside Intacct, three mechanisms hid these traps.

The dimension resolver. When an Intacct report groups by "Department," the engine looks up each transaction's departmentid against the platform DEPARTMENT table for the current name, walks the department-group hierarchy for roll-ups, and applies any restriction rules on the running user's role. Replication captures the id and the name-at-extract-time on the transaction, and does not capture the table, the hierarchy, or the rules. This is documented on the vendor's own developer portal at Sage Intacct Developer, Dimensions: the platform ships dimensions as objects with their own APIs, and the connector reads none of them.

The multi-entity switcher. In Intacct, a user chooses a login context: an entity, an entity group, or the top level. Every list, dashboard, and report is filtered against that context. The top level shows consolidated numbers with intercompany eliminations, and an entity view shows that entity's book. Replication copies transactions with a megaentityid tag and no context: the same table now serves every entity and the top level, and nothing in the schema tells the query which view it is answering. Sage's own developer docs at Sage Intacct Developer, Entities name the boundary directly: "Entity is a type of location (a dimension) that is available in multi-entity shared companies only."

The multi-book layer. Multi-Book Accounting stores parallel books on the same transaction, keyed by gl_detail.bookid. Standard reports pick a book from the user's role. Fivetran lands the bookid column and does not land the accountingbook catalog table, so a naive SUM on a multi-book estate sums every book at once. This is the same pattern as NetSuite's Multi-Book trap one column over, and it fires on every multi-book Intacct estate.

Every one of the three is a boundary the application enforced and the warehouse did not. Getting an Intacct answer right against a replicated schema is mostly a matter of re-establishing those three boundaries by hand.

The fix

Declare the joins, and type the polymorphic edge

ap_bill, ap_bill_item, ar_invoice, ar_invoice_item, gl_detail, and gl_account all sit in the same finance subject area. The four ordinary edges declare cleanly. The two polymorphic edges from gl_detail need the recordtype filter written into the declaration, or the join is unsafe:

relationships:
  - from_table: ap_bill_item
    from_column: recordkey
    to_table: ap_bill
    to_column: recordno
    relationship: many_to_one
    description: |
      recordkey is the FK to the parent bill's recordno. All three of
      recordno, recordid and recordkey exist on ap_bill_item and mean
      different things (own PK, display doc id, FK to parent); a name-match
      join is wrong. Source: fivetran/dbt_sage_intacct
      src_sage_intacct.yml (ap_bill_item.recordkey).

  - from_table: ar_invoice_item
    from_column: recordkey
    to_table: ar_invoice
    to_column: recordno
    relationship: many_to_one
    description: |
      Same shape as ap_bill_item.recordkey. Source as above.

  - from_table: gl_detail
    from_column: accountno
    to_table: gl_account
    to_column: accountno
    relationship: many_to_one
    description: |
      gl_detail joins gl_account on accountno (the business key), not on
      gl_account.recordno (the surrogate). Source:
      src_sage_intacct.yml gl_detail.accountno.

  - from_table: gl_detail
    from_column: recordid
    to_table: ap_bill
    to_column: recordid
    relationship: many_to_one
    join_type: LEFT
    filter: gl_detail.recordtype = 'APBill'
    description: |
      gl_detail.recordid also appears on ar_invoice.recordid; recordtype
      disambiguates. Do not declare the join without the filter. Source:
      src_sage_intacct.yml gl_detail.recordid ("ID of record, which
      corresponds to the bill or invoice ID"), gl_detail.recordtype
      ("Type of record, for example AP Bill"), and the parallel language
      on ap_bill.recordid and ar_invoice.recordid.

  - from_table: gl_detail
    from_column: recordid
    to_table: ar_invoice
    to_column: recordid
    relationship: many_to_one
    join_type: LEFT
    filter: gl_detail.recordtype = 'ARInvoice'
    description: |
      Mirror of the above.

Two of those descriptions carry a citation, and that is deliberate. The recordkey foreign key is not what column-name introspection reaches for, and the polymorphic edges are outright wrong without the recordtype filter. Both are the kind of thing that gets deleted six months later by someone who thinks they are simplifying. The sentence next to the declaration is what stops them.

Lift the dimensions into entities, and describe what is missing

The eight standard dimension columns live only on the transaction tables. They have no target table to join to, so they cannot be declared as relationships. They can be declared as entities: a set of columns that name the same business thing across tables, which is what the semantic model uses to let a pivot cross tables at all. The declaration also carries the honest note about what the connector did not land:

entities:
  - name: department
    columns:
      - ap_bill_item.departmentid
      - ap_bill_item.departmentname
      - ar_invoice_item.departmentid
      - ar_invoice_item.departmentname
    description: |
      Department is one of the eight Intacct standard dimensions. Present
      only as denormalized (id, name) pairs on transaction line tables;
      no department table is landed by the connector. The department-group
      hierarchy is not in the replicated schema. Name is a snapshot at
      extract time and drifts on rename.

  - name: entity
    columns:
      - ar_invoice.megaentityid
      - ar_invoice.megaentityname
      - gl_batch.megaentityid
      - gl_batch.megaentityname
      - gl_batch.megaentitykey
    description: |
      "Entity is a type of location (a dimension) that is available in
      multi-entity shared companies only" (Sage Intacct Developer,
      Entities). Fivetran lands the tag on AR invoices and GL batches; the
      entity catalog and the consolidation hierarchy are not in the
      replicated schema. Nulls mean the transaction was created at the top
      level.

The same shape declares location, class, project, customer, vendor, item and employee. Every one carries the same note about what did not land alongside the id and the name, because the note is what an agent will use to answer honestly when a reader asks about the hierarchy.

The metric carries the sign convention

Getting the revenue query right this once is not the same thing as making the schema safe. The next revenue question written against this warehouse will be written from scratch and will re-run the naive shape unless the signed-sum convention travels with the metric, not with the person who last wrote SQL:

name: gl_revenue
calculation: |
  Signed sum of gl_detail credit minus debit for Income accounts on posted
  entries. Sign convention taken from gl_account.normalbalance. Source:
  src_sage_intacct.yml gl_account.accounttype ("Income statement or balance
  sheet"), gl_account.normalbalance ("Debit or credit"),
  gl_detail.creditamount, gl_detail.debitamount, gl_detail.entry_state.
bindings:
  PostgreSQL: |
    SUM(CASE
          WHEN gl_account.accounttype     = 'incomestatement'
           AND gl_account.category ILIKE '%revenue%'
           AND gl_detail.entry_state      = 'Posted'
          THEN gl_detail.creditamount - gl_detail.debitamount
          ELSE 0
        END)
source_tables: [gl_detail, gl_account]
primary_table: gl_detail
default_filters:
  - "gl_detail.entry_state = 'Posted'"
other_names: [total revenue, gl revenue, income]
confidence: proposed
review_state: unreviewed

default_filters on gl_detail.entry_state = 'Posted' is the machine-readable form of the boundary the standard report enforced by picking a role. Any query that reaches gl_revenue inherits it whether the author remembered or not. Any query that reaches gl_detail any other way should trigger a warning, because the metric on top of it did.

Read review_state: unreviewed. The declaration is a proposal until a person signs it, because the one judgment in here is not a schema fact.

That signature is what makes the answer auditable months later. Rules enforced where the metric is defined survive the next person, the next agent, and the next question. A convention that lives in someone's memory does not.

What the schema cannot decide for you

Which accounts count as revenue is per-org. gl_account.category ILIKE '%revenue%' is a fine default and wrong at some clients, who bucket contra-revenue, deferred revenue, or non-operating revenue under names that do not contain the string. Someone in finance names the set once, and the metric definition stops pretending it is universal.

Which entity or entity group to consolidate against is not derivable from the warehouse either. The multi-entity hierarchy lives in Intacct's platform tables that the connector does not land. If the reader wants a consolidated view they either export the hierarchy from Intacct manually and load it as a dimension table, or they hardcode the roll-up. Nothing in Fivetran will supply it.

Custom dimensions (user-defined dimensions, or UDDs) come through as custom_<name> columns on the transaction tables that carry them, controlled by Fivetran's sage_gl_pass_through_columns variable, and have no catalog at all. What each UDD means is per-org, and the description on its column is where that meaning has to live.

Book choice on Multi-Book Accounting estates is the same shape as the NetSuite case. gl_detail.bookid is landed and no accountingbook catalog is. Add a default_filter on bookid if the estate runs Multi-Book, and do not add one if it does not, because a single-book estate breaks under a filter that assumes a catalog.

Reproduce it yourself

Sage Intacct has no reproducible free instance. The self-serve trial does not include Web Services access, and Fivetran's connector requires it. So the reproduction path here is route two: your own replicated schema.

If you have Fivetran's Sage Intacct connector landing to a warehouse today, run the three counting queries from the top of this post against it. The first tells you how many buckets each pivot is about to split into, and how many rows silently drop for want of a tag. The second returns any dimension id whose name has drifted across syncs, one row per drift event; every row is a bucket the naive query is about to double-count. The third puts the naive revenue and the corrected signed-GL revenue on adjacent rows for the same period, so you can see where they disagree.

If the first query returns any nulls, the naive GROUP BY departmentname is dropping those rows. If the second returns any rows, the estate has renames and the naive pivot is splitting them. If the third returns two figures that do not tie, you have the size of the fan-out from summing the line amount instead of the signed GL amount.

Three notes on what you will be looking at. Column casing at the warehouse level depends on your destination: lowercase on BigQuery and Postgres, uppercase on Snowflake. Fivetran's transform package normalises to snake_case downstream. And gl_detail.bookid will be present regardless of whether Multi-Book is enabled; the test for whether Multi-Book is on is whether more than one distinct value appears, since Fivetran does not land the accountingbook catalog either way.

Related traps in the same schema

The dimension trap is the one worth fixing first, and it is not alone. Three more sit in the same seven tables, each documented and each its own kind of wrong.

Multi-book on gl_detail. bookid is landed and no accountingbook catalog is. On a Multi-Book estate a naive SUM across gl_detail sums every book at once, the same failure mode NetSuite's transactionaccountingline carries. Add a default_filter on bookid at the metric layer, keyed to the primary book, or the metric definition on top of it inherits the fan-out.

Currency on the amount columns. gl_detail.amount, debitamount and creditamount are stored in transaction currency. Consolidated reporting in Intacct converts through the Statistical currency and the reporting currency, and the conversion rates live in a table Fivetran does not land as part of the core connector. Summing amounts across transactions in different currencies adds euros to dollars. This is a metric-layer decision, not a join-layer one, and it belongs in the same description as the sign convention.

Recordtype drift on gl_detail. The set of values on gl_detail.recordtype extends beyond APBill and ARInvoice to journal entries, adjustment memos, revenue-recognition entries, and any other transaction the estate posts. A polymorphic edge declared only for the two document types leaves the rest as unattributed rows in gl_detail. The right treatment is to enumerate every recordtype in the estate (select distinct recordtype from gl_detail) and either declare an edge for it or document why it does not need one.

Find out which dimensions your warehouse has stopped resolving.

agami-core is source-available. Point it at your replicated Sage Intacct and see which relationships come back inferred, which come back empty, and which metrics need the signed-sum convention attached before an agent goes near them.

Get agami-core or tell us which Intacct pivot you stopped trusting

Frequently asked questions

Does this only affect estates with lots of dimensions? No. Every replicated Sage Intacct estate has this shape, because it is in the connector, not in the tenant. The size of the drift is proportional to how many dimensions the estate uses and how often the dimension names change; the existence of the trap is not.

How can I tell if my estate has drift without asking Finance? The second counting query at the top of the post is the test. Any row it returns is a dimension id that has been recorded under more than one name across syncs, which is a rename or a merge inside Intacct that the warehouse has captured as two distinct buckets.

Isn't this what Sage's Finance Intelligence Agent is for? Sage's Finance Intelligence Agent runs inside Intacct through Sage Copilot, so it inherits the dimension resolver, the multi-entity switcher, and the multi-book filter that the app enforces. That is what makes it credible on in-app questions. The scope this post addresses is different: a warehouse copy of the tables, queried by an agent that is not inside Intacct and does not see any of those boundaries.

Why put the dimension columns in entities: if there is no table to join to? Because the pivot still has to cross tables. A revenue-by-department question starts on ar_invoice_item.departmentname and lands on gl_detail through the recordid polymorphic edge; a spend-by-department question starts on ap_bill_item.departmentname and lands on the same gl_detail through the mirror edge. Declaring department as an entity with columns on both sides is what lets the model recognise that the two pivots are on the same business thing without a target table.

Is this specific to Fivetran or to one warehouse? No. The cause is upstream of the warehouse. Sage Intacct's Web Services API returns dimensions as separate objects and transactions as tag-carrying rows, and any replication path (Fivetran, Airbyte, the Intacct Data Cloud share for Snowflake, custom API pulls) that lands the transaction tables without also landing the dimension objects reproduces the same pattern. Column casing and table names vary by extract tool and destination, but the trap does not.

References

1. Fivetran dbt package: src_sage_intacct.yml
2. Fivetran dbt package: int_sage_intacct__general_ledger_balances.sql
3. Fivetran dbt package: sage_intacct__ap_ar_enhanced.sql
4. Sage Intacct product: Multi-dimensional accounting
5. Sage Intacct Developer: Dimensions
6. Sage Intacct Developer: Entities
7. Sage Intacct help: Multi-entity company overview
8. Sage Intacct 2026 R1: Finance Intelligence Agent Early Adopter docs
9. Fivetran: Sage Intacct connector
10. agami-core on GitHub