QuickBooks to BigQuery: how to load financial data your team can actually query

How do you connect QuickBooks to BigQuery?

You connect QuickBooks to BigQuery by authenticating to the Intuit v3 REST Accounting API with OAuth2, reading each entity for a company (its "realm"), and writing the results into BigQuery through load jobs or the Storage Write API. QuickBooks ships no native BigQuery driver, so a managed connector, a scheduled script, or the CDC endpoint has to sit in the middle and move the data.

The connection has three moving parts. First, auth: an OAuth2 app with a client ID and secret, a refresh token per company, and the realm ID that scopes every request. Second, the read side: the v3 API exposes entities like Invoice, Bill, Payment, Account, JournalEntry, Customer, Vendor, and Item, queried with a SQL-like syntax. Third, a Google Cloud service account with bigquery.dataEditor on the destination dataset, plus object write access on a staging bucket if you land files before loading.

The managed path is the QuickBooks to BigQuery integration, which handles the OAuth refresh, pagination, the watermark, the staging table, the MERGE, and the retries. The pattern is the same one you would use for a database source such as Postgres to BigQuery, just with an accounting API in place of a wire protocol.

Can you export QuickBooks Online data to BigQuery automatically?

Yes. You schedule a job that calls the Intuit v3 API on a cadence, extracts new and changed records, and loads them into BigQuery without manual exports. QuickBooks has no built-in warehouse sync, so the automation lives in a connector or a script that manages OAuth token refresh, pagination, and the load. The result is a hands-off pipeline once it is wired up.

Pagination is the part that trips people up. The v3 API uses SQL-like queries with STARTPOSITION and MAXRESULTS, and it caps each page at 1000 rows. On top of that, Intuit enforces request throttles per realm, so a naive full pull of a large company is both slow and prone to hitting limits. A production job paginates in order, backs off on throttle responses, and checkpoints its position so a mid-run failure resumes instead of restarting from zero.

Schedule the job to match how fresh the numbers need to be. A finance dashboard that refreshes each morning is fine on an hourly or nightly cadence. Pulling every entity on every run is wasteful, so the automation should ask for changes only, which is where incremental loading comes in.

How do you load QuickBooks data into BigQuery incrementally?

You load QuickBooks data incrementally by filtering on MetaData.LastUpdatedTime as a watermark, or by calling the ChangeDataCapture (CDC) endpoint, which returns every entity changed since a timestamp including deleted ones. Store the last watermark, overlap the window slightly on each run, and MERGE into BigQuery keyed on the entity Id so retries stay idempotent.

The watermark approach queries each entity with a clause like WHERE MetaData.LastUpdatedTime > :last_watermark, orders by that timestamp, and pages through the results. After a successful run you advance the stored watermark. Two habits keep it reliable: overlap the window by a few minutes so records committed slightly out of order are not skipped, and keep the MERGE idempotent so re-reading a handful of rows costs nothing.

The CDC endpoint is the better fit when deletes matter. A voided or deleted invoice does not show up in a plain LastUpdatedTime filter the way you want, but CDC reports it, so your BigQuery tables can reflect the removal instead of carrying a ghost row. Whichever source you use, the load pattern is the same: write the batch to a staging table, collapse it to one row per Id keeping the latest LastUpdatedTime, then MERGE on Id into the partitioned target. Re-running the same batch after a timeout lands the same state rather than duplicates.

How do QuickBooks fields map to BigQuery types?

Money fields map to BigQuery NUMERIC (never FLOAT64 for money), TxnDate maps to DATE, timestamps such as MetaData.LastUpdatedTime map to TIMESTAMP stored in UTC, and the entity Id stays a STRING. Currency codes are short strings and exchange rates are NUMERIC. Get these right at load time or reconciliation gets painful later.

First, map the entities to tables so the warehouse layout mirrors the accounting model.

QBO entity	BigQuery table	Notes
Invoice	invoices	Fact table. Partition by TxnDate, cluster on CustomerRef. Line items usually land in a child invoice_lines table
Bill	bills	Accounts payable side. Partition by TxnDate, cluster on VendorRef
Payment	payments	Links to invoices through applied lines. Keep the linked transaction Ids for cash application
Account	accounts	Chart of accounts dimension. Small and slow-moving, so a full reload is fine
JournalEntry	journal_entries	GL postings. Partition journal_lines by TxnDate, cluster on AccountRef for ledger queries
Customer	customers	Dimension table. MERGE on Id so name and status changes update in place

Then map the field-level types. The money and timestamp rows are the ones that cause silent problems if you get them wrong.

QuickBooks field / type	BigQuery type	Notes
TotalAmt, Line.Amount (money)	NUMERIC	Never FLOAT64 for money. NUMERIC holds 38 digits with scale 9, which covers currency to the cent without drift
TxnDate	DATE	Calendar date of the transaction, no time component. Good partition key for fact tables
MetaData.LastUpdatedTime	TIMESTAMP	Store in UTC. This is the incremental watermark, so treat it as an absolute point in time
MetaData.CreateTime	TIMESTAMP	Also UTC. Useful for auditing when a record first appeared
Id, CustomerRef.value	STRING	Intuit Ids are opaque strings. Keep them as STRING and MERGE on them; do not cast to INT64
CurrencyRef.value (currency code)	STRING	Three-letter ISO code such as USD or EUR. Store it next to every money amount
ExchangeRate	NUMERIC	Rate that converts transaction currency to home currency. Keep it so home-currency totals are reproducible

Partition the fact tables (invoices, journal lines) by transaction date and cluster on the column you filter on most, usually account or customer. That combination cuts the bytes each dashboard query scans, which is the lever that controls your BigQuery bill.

How do you handle multi-currency in a QuickBooks to BigQuery load?

Carry both amounts and the rate. QuickBooks stores a home currency and, for multi-currency companies, a transaction CurrencyRef plus an ExchangeRate. Load the transaction-currency amount, the home-currency amount, and the rate into BigQuery so general ledger totals tie back to QuickBooks exactly. Dropping any one of the three breaks reconciliation somewhere down the line.

If a company runs a single currency, this is a non-issue: every amount is already in the home currency. The moment multi-currency is on, a euro invoice posts in EUR but rolls up to the home currency (say USD) at the exchange rate on the transaction. If you only store the EUR amount, your USD GL will not match, and if you only store the converted amount, you lose the original figure the customer actually sees. Keep both, plus the rate, and derived columns stay auditable.

One more subtlety: posting period and transaction date can differ. A bill dated at the end of one month may post into the next accounting period. Keep both the TxnDate and the posting period so financial reports can group either way without a second pull. Storing the raw fields and computing period logic in BigQuery is safer than baking assumptions into the extract.

How much does it cost to run QuickBooks to BigQuery?

BigQuery bills on two axes: storage for the bytes you keep, and compute for the bytes your queries scan. Replication itself is cheap. Table design drives the bill, because an unpartitioned table forces every dashboard query to scan the full history instead of the slice it needs. Partitioning and clustering are what keep the number sane.

Partition fact tables by TxnDate and cluster on account or customer. A report asking for the current quarter then reads a few partitions rather than every invoice you have ever loaded. This matters more with financial data than most people expect, since ledger and invoice tables grow steadily and queries tend to fan out across dashboards. It is worth it to keep an eye on what the warehouse and the rest of your cloud run up before the monthly invoice is a surprise.

On the pipeline side, our pricing is flat by plan (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a busy close at month end does not produce a surprise line item. See pricing for the full breakdown. If you are still deciding where transformation should live, the tradeoffs are in ETL vs ELT. To see the entity mapping and schedule for your own company file, start a demo.