Xero to BigQuery: how to load accounting data your team can actually query

How do I connect Xero to BigQuery?

You connect Xero to BigQuery by authenticating to the Xero Accounting API with OAuth 2.0, reading the objects you care about, and loading them into BigQuery tables through the load or streaming API. Xero has no native BigQuery export, so something in the middle has to move the data: a managed connector, or a script you run on a schedule.

The OAuth flow returns an access token and a tenant id that identifies the specific Xero organization you are reading. Every request carries that tenant id, which matters the moment you have more than one entity. From there you page through each endpoint (roughly 100 records per page via the page parameter), respect the rate limits (a per-minute cap, a daily app cap, and a limit on concurrent calls), and land the results. A first run is a full backfill of every object; after that you switch to incremental so you are not re-reading the whole ledger every night. If you would rather not maintain the auth refresh, paging, retry, and MERGE logic yourself, the Xero to BigQuery integration runs all of it as a managed pipeline on a schedule you set.

Xero object	BigQuery table	What it feeds
Invoices (ACCREC)	stg_invoice	Accounts receivable, revenue recognition, aging
Bills (ACCPAY invoices)	stg_bill	Accounts payable, cost and spend analysis
Payments	stg_payment	Cash in and out, collections timing
Contacts	dim_contact	Customer and supplier dimension
Accounts (chart of accounts)	dim_account	GL structure, account grouping for reports
BankTransactions	stg_bank_transaction	Bank feed detail, reconciliation
CreditNotes	stg_credit_note	Refunds and adjustments against revenue
Journals	stg_journal	Full double-entry ledger for reconciliation

Journals deserve a note. They give the complete double-entry view and are append-only in practice, which makes them the cleanest source for a trial balance that ties out. Invoices and bills are friendlier to read, but they mutate as their status changes, so you need the incremental approach below to keep them honest.

How do I load Xero data incrementally into BigQuery?

You load Xero incrementally by storing the maximum UpdatedDateUTC you have already seen for each object, then asking Xero for only the records changed since that timestamp. Xero supports this two ways: the If-Modified-Since request header, and filtering on the UpdatedDateUTC field. Both let you pull a small delta each run instead of the entire ledger.

This is not a nice-to-have. A Xero invoice does not sit still: it moves from DRAFT to AUTHORISED to PAID, it can be VOIDED, and it can be reduced by a credit note weeks after it was raised. A naive nightly full dump that only inserts new ids will miss every one of those after-the-fact edits, and your revenue and receivables numbers slowly drift from what Xero shows. The watermark pattern catches them because any change bumps UpdatedDateUTC, so the record comes back in the next delta.

Once the delta lands in a staging table, you upsert it into the target with a MERGE keyed on the Xero GUID. MERGE is what makes the pipeline safe to retry: if a run fails halfway or two runs overlap, the same InvoiceID updates the existing row rather than inserting a second copy. Store the new high-water mark only after the MERGE commits, so a crash mid-load re-reads the same window rather than skipping it.

MERGE INTO fct_invoice T
USING stg_invoice S
ON T.invoice_id = S.invoice_id
WHEN MATCHED AND S.updated_utc > T.updated_utc THEN
  UPDATE SET status = S.status, total = S.total, amount_paid = S.amount_paid, updated_utc = S.updated_utc
WHEN NOT MATCHED THEN
  INSERT (invoice_id, contact_id, status, total, amount_paid, updated_utc, org_id)
  VALUES (S.invoice_id, S.contact_id, S.status, S.total, S.amount_paid, S.updated_utc, S.org_id)

The S.updated_utc > T.updated_utc guard means an out-of-order or replayed page never overwrites a newer state with an older one. That combination, watermark plus MERGE plus a freshness guard, is the whole trick to a Xero pipeline that stays correct without babysitting.

How do I map Xero API fields to BigQuery data types?

Map each Xero field to the narrowest BigQuery type that holds it without losing precision. The three that matter most: decimal money amounts become NUMERIC (never FLOAT64, which rounds cents), the UpdatedDateUTC timestamp becomes TIMESTAMP, and the GUID ids stay STRING. Everything else follows from there.

A common shortcut is to land the raw JSON payload in a STRING or JSON column first, then parse typed columns from it. That gives you a replayable copy of exactly what Xero returned, which is handy when a mapping is wrong and you need to re-derive a column without re-pulling from the API. Whether you land raw or typed, the target types are the same.

Xero field	Xero type	BigQuery type	Why
InvoiceID, ContactID	GUID string	STRING	Stable key for MERGE and joins
InvoiceNumber, Reference	String	STRING	Human-facing labels, no arithmetic
Total, AmountDue, AmountPaid	Decimal	NUMERIC	Exact cents, no floating point drift
CurrencyCode	String (ISO 4217)	STRING	Carry currency next to every amount
Date, DueDate	Date	DATE	Partition and period grouping
UpdatedDateUTC	DateTime (UTC)	TIMESTAMP	Incremental watermark, ordering
Status	Enum string	STRING	DRAFT, AUTHORISED, PAID, VOIDED
IsReconciled, HasAttachments	Boolean	BOOL	Direct flag mapping

Partition the fact tables by a real date column (invoice Date, payment Date) and cluster by the entity id you filter on most. On a few years of SMB accounting data the partition pruning keeps scan costs low, and clustering on contact_id or account_id makes the customer and GL rollups quick. Keep UpdatedDateUTC in UTC as Xero sends it and convert to a local reporting zone in the model layer, not on ingest.

How do I consolidate multiple Xero organizations in BigQuery?

You consolidate multiple Xero organizations by loading each one through its own tenant id, stamping every row with an org_id column, and writing them all into shared tables. Because Xero is strictly per organization, there is no cross-entity report inside Xero itself. The warehouse is where group reporting actually happens.

The hard part is rarely the load, it is the chart of accounts. Two entities can both have an account called "Sales" sitting at different account codes, or the same code meaning different things. If you stack the raw ledgers without reconciling that, a consolidated P and L is wrong in ways that are hard to spot. The fix is a mapping layer: a crosswalk from each org's local account code to a shared group account, applied in dim_account so every downstream model reads one consistent hierarchy. Keep org_id on the fact rows so you can still drill from the group number back to a single entity.

Handle currency at the same layer. If entities report in different currencies, store the original amount and CurrencyCode as loaded, then apply a rate table in the model to produce a group-currency column. The same warehouse-first pattern shows up whenever you centralize per-account systems, which is why the QuickBooks to BigQuery path solves consolidation the same way for teams on that ledger.

How do I keep Xero and BigQuery in sync automatically?

You keep them in sync by running the incremental load on a schedule, commonly every few hours or nightly, with each run advancing the UpdatedDateUTC watermark and MERGE-ing its delta. Automatic sync is just that loop plus the operational plumbing: retries with backoff, rate-limit handling, and alerting when a run fails or comes back empty when it should not.

Xero's rate limits shape the schedule. With a per-minute cap, a daily app cap, and a concurrent-call limit, a well-behaved job paces its requests and backs off on a 429 rather than hammering the API, which is where a lot of hand-rolled scripts fall over. The incremental design helps here too: smaller deltas mean fewer calls, so you stay comfortably under the caps even as history grows.

Downstream, trigger the transformation models after each successful load so the fact and dimension tables, and the revenue, cash, and margin models on top, refresh on the same cadence as ingest. This is the part teams underestimate: the extract is a few days of work, but keeping it correct through schema quirks, rate limits, and status churn is ongoing. A managed data integration platform handles the schedule, retries, and watermarking so you spend your time on the models rather than the pipes. The same reliability concerns apply to warehouse-to-warehouse moves, which the Postgres to BigQuery guide walks through if you also replicate an operational database.

Concern	What to do
Schedule	Run incremental every few hours or nightly; advance the watermark only after MERGE commits
Rate limits	Pace requests under the per-minute, daily, and concurrent caps; back off on HTTP 429
Idempotency	MERGE on the Xero GUID so overlapping or retried runs never duplicate rows
Late edits	Watermark on UpdatedDateUTC so status changes and voids re-flow automatically
Monitoring	Alert on failed runs, token refresh errors, and unexpected empty deltas
Modeling	Rebuild fct and dim tables and reporting models after each successful load

Can I load Xero to BigQuery without code?

Yes. A managed connector loads Xero into BigQuery with no code: you authorize the Xero organization, pick BigQuery as the destination, choose a schedule, and the connector handles OAuth refresh, paging, incremental watermarking, type mapping, and idempotent MERGE writes for you. That removes the part most teams find fiddly to build and, worse, to keep running.

Writing it yourself is a reasonable choice when you have unusual requirements or already run an extraction framework, and the pattern in this guide is exactly what you would implement. For most finance-ops and analytics teams, though, the accounting data is a means to an end: they want clean revenue, cash, and margin numbers, not a pipeline to maintain. The Xero to BigQuery connector gets the ledger into the warehouse on a schedule, and once it lands you can join it to product, billing, and ad data, then turn the ledger into board-ready financial statements without exporting spreadsheets by hand. If you want to see it move your own data first, book a demo and watch a live sync run end to end.