Consolidating QuickBooks across multiple entities in BigQuery and dbt

Your group runs four companies, and each one keeps its own QuickBooks file. Every month someone exports four P&Ls, pastes them into a spreadsheet, fixes the account names by hand and emails the result. It takes two days. Nobody is sure it's right.

The fix is not a better spreadsheet. It's a warehouse that does the consolidation the same way every month, with tests that tell you when it doesn't.

Why the spreadsheet stops working

QuickBooks is built to keep one company's books. It is not built to answer questions across companies, and three things break the moment you try.

Each entity has its own chart of accounts. "Software subscriptions" in one file is "IT expenses" in another and split across three accounts in a third. Each entity can have its own currency, fiscal year and class or location structure. And QuickBooks lets anyone edit a closed month, so last quarter's consolidated number can quietly change after the board has seen it.

A spreadsheet hides all three. A warehouse makes them explicit, which is the point.

This post assumes each company's QuickBooks data already lands cleanly in a warehouse. If it doesn't yet, start with what the QuickBooks API leaves out and how to layer it, then come back here for the part that spans entities.

The consolidation playbook

1. Land every entity, tagged. Sync each QuickBooks company into BigQuery, through Fivetran or the QuickBooks API, and stamp every row with an entity id. Check what the connector actually brings: in one build, the managed connector didn't sync department data, which was the dimension the product depended on, so we went to the API for it.

2. Map every chart of accounts to one reporting structure. This is the real work. Keep a mapping table, one row per entity account, pointing to a shared reporting account and line. Store it as a dbt seed so finance can review changes in a pull request instead of a spreadsheet. Unmapped accounts should fail the build, not fall into "Other".

3. Normalise the calendar and the currency. Convert P&L lines at the transaction-date rate and balance sheet lines at the period-end rate, and record which rate you used. If entities run different fiscal years, report on calendar months and derive each entity's fiscal periods from them.

4. Map classes, locations and departments the same way as accounts. They are just as inconsistent across files, and they are usually what management actually wants to slice by.

5. Eliminate intercompany. Tag the accounts, customers and vendors that represent other group entities. Eliminate those balances in the consolidated mart, and keep the eliminated lines visible in their own table so the accountant can check them.

6. Freeze closed periods. Snapshot each entity's ledger at month-end close with dbt snapshots. When someone edits a closed month in QuickBooks, you see the change as a difference against the snapshot instead of discovering it in next quarter's numbers.

7. Reconcile every month, automatically. Each entity's trial balance in the warehouse must net to zero and tie to the trial balance QuickBooks reports for the same month. Make that a test, not a habit.

8. Control who sees which entity. A group CFO sees everything. An entity manager sees their own company. Apply row-level security on the entity id in the warehouse or the BI layer, never by maintaining separate copies.

Step 7 in practice: the test that catches most problems

-- tests/assert_trial_balance_nets_to_zero.sql
-- Fails if any entity's ledger lines don't net to zero for a month.
-- Catches broken syncs, missing journal lines and bad currency conversion.
select
  entity_id,
  date_trunc(posting_date, month) as period,
  round(sum(amount_reporting_currency), 2) as net
from {{ ref('fct_gl_lines') }}
group by 1, 2
having abs(round(sum(amount_reporting_currency), 2)) > 0.01

Pair it with a second check that compares each entity's monthly totals against QuickBooks' own trial balance report. The first test proves the ledger is internally consistent. The second proves it matches the books the accountant signs.

What this looks like in production

We run this pattern for a finance analytics platform serving 80+ customers, each with several connected companies. QuickBooks data lands in BigQuery, dbt consolidates it by entity and by client, and every dashboard is filtered by row-level security so each user sees only the companies they're allowed to.

The part that took longest was not the pipeline. It was the account mapping, and agreeing who owns it. Once finance owned the mapping table and changes went through review, the consolidated numbers stopped being argued about. That pattern is the same one behind any single source of truth: the definitions matter more than the tooling.

With consolidated, tested numbers underneath, you can put an AI financial analyst on top and trust what it says.

What to do next

If your month-end still involves exporting files and fixing account names by hand, start with step 2. Build the mapping table for your two largest entities and see how many accounts don't line up. That number tells you how much your current consolidation is guessing.

If you want a second pair of eyes on it, book a 30-minute call.

One P&L is a data model, not a spreadsheet.