Xero to BigQuery: How to Move Your Data

Moving Xero into BigQuery gets accounting data out of a system built for bookkeeping and into a warehouse where it can be joined to everything else. Xero knows what was invoiced and paid. It does not know which customers those invoices belong to in your product, or what they cost to serve, and margin questions need both.

This guide covers the managed path with Airbyte. The structural thing to know first is that a source connects to one Xero organisation, which matters because businesses using Xero very often have several.

Xero to BigQuery at a glance:

Why move data from Xero to BigQuery?

Two situations account for most of these pipelines.

The first is joining finance to commercial data. Invoices next to product usage, support cost and CRM records is how you answer questions about margin per customer or which segments are actually profitable. Xero cannot answer those because it has never seen the other side.

The second is consolidation across entities. Groups running several Xero organisations, one per country or trading company, have no single view of the whole. A warehouse is where that consolidation happens, and it is often the reason this pipeline gets funded.

What do you need before you start?

Two of these are about which Xero application you already have, and one is about how many you need:

An application in the Xero developer centre. Created with the appropriate authentication method and the required scopes configured. The Xero source documentation lists what to enable.

An authentication method the connector supports. Bearer access token, or an OAuth custom connection using a client identifier and secret. If your organisation already runs a Xero application built on a different OAuth flow, check compatibility before assuming you can reuse it, since this has caught people out.

A tenant ID for each organisation. The tenant ID identifies which Xero organisation the source connects to, and it is a required field. Multi-entity groups therefore need one source per organisation, and gathering the IDs up front turns the build into repetition rather than investigation.

A start date in UTC. Supplied with a date and time. Set it to cover the earliest period anyone will report on, remembering that finance questions tend to reach further back than product ones.

On the BigQuery side you need a project, a dataset and a service account with the BigQuery User and BigQuery Data Editor roles. Use a dataset dedicated to finance data, since it will end up with its own access controls.

How do you build a Xero to BigQuery pipeline in Airbyte?

Step 1: List your organisations and their tenant IDs

Work out how many Xero organisations you need and collect a tenant ID for each. This is the step that determines the size of the project: one entity is an afternoon, eight entities is eight sources, eight connections and a consolidation model on top. Better sized now than discovered halfway through.

Step 2: Configure the Xero source

Click Sources in the left navigation, then New Source, and select Xero, following adding a source. Supply your authentication details, the tenant ID and the start date. Name each source after the organisation rather than accepting a default, because a workspace with several near-identical Xero sources is easy to create and painful to maintain.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key. If you are syncing several organisations, decide now whether they share a dataset with the entity distinguished by a column, or get their own, because retrofitting that across eight connections is tedious.

Step 4: Use incremental sync throughout

Click Connections, then New connection, select your streams and an incremental sync mode. Every stream supports it, using an updated timestamp as the cursor, which is unusually complete coverage and means there is no reason to run full refresh on anything. Daily suits accounting data, which changes at the pace of bookkeeping rather than continuously.

Compare a period's totals against Xero after the first sync. This is finance data that somebody reconciles, so a twenty-minute check now is worth considerably more than the same check after a quarterly report has been built on it.

Why does one source mean one organisation?

Because Xero's API scopes every request to a tenant, and the connector takes that tenant as a single required configuration value. There is no field for a list, so the organisation is fixed when you create the source.

For a single-entity business that is invisible. For a group, or an accountancy practice managing client books, it is the defining characteristic of the project. Ten organisations means ten sources, ten connections, ten sets of credentials to keep working, and a consolidation layer that unions them together with an entity column.

Two habits make that manageable. Name sources and connections after the organisation so failures are attributable at a glance, and decide the dataset layout before you build the second one. Both are trivial at the start and awkward once several exist.

What happens to Xero's unusual date formats?

They are handled for you, which is worth knowing because otherwise they would be a genuine nuisance. Xero is built on .NET, and some date fields in its records arrive in the .NET JSON date format, which looks like a wrapper around a millisecond timestamp and an offset rather than anything a warehouse would recognise.

The connector detects those and transforms them into ISO 8601 automatically, and also normalises the fields that already arrive as ISO 8601 so every datetime is consistent and in UTC. That means you get usable timestamp columns rather than strings you would otherwise be parsing yourself in every downstream model.

The practical note is about timezones rather than formats. Everything lands in UTC, and your finance team reports in local time, so a transaction late on the last day of a month can fall into a different period depending on which you use. Agree the rule and apply it in one modelling layer rather than letting each report decide.

Frequently asked questions

Can one source sync several Xero organisations?

No. The tenant ID selects a single organisation, so create one source per entity and union them in a consolidation model with an entity column.

Where do I find my tenant ID?

Xero's own documentation covers retrieving tenant IDs for the organisations your application is connected to. Collect them all before you start building.

Which authentication methods are supported?

A bearer access token or an OAuth custom connection using a client identifier and secret. Check compatibility before assuming an existing Xero application will work unchanged.

Do I need to parse Xero's .NET date format myself?

No. The connector detects and converts those to ISO 8601, and normalises other datetime fields to a consistent UTC format.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want BigQuery views unioning entities and applying your reporting timezone rule, which is short SQL.

Get your Xero data into BigQuery

Count your organisations first, because that sizes the whole project. Collect the tenant IDs, name every source after its entity, use incremental sync everywhere since all streams support it, agree the timezone rule with finance, and reconcile a period against Xero before anyone builds on it.

Airbyte's connector catalog includes 600+ pre-built connectors, so accounting data can sit beside product and CRM sources in one warehouse. For a larger ERP in the same position, see NetSuite to Snowflake, and for the currency reference data finance will want alongside it, Exchange Rates API to Snowflake.

Start syncing now →