QuickBooks Online and Google BigQuery integration

What moves between them

Records flow one direction: QuickBooks Online to Google BigQuery. When QuickBooks Online webhooks fire, ml-connector fetches the full account, invoice, bill, customer, or vendor record and writes it to BigQuery as a new row. For efficiency, ml-connector also polls the QuickBooks CDC endpoint every 6 hours to catch any missed webhook deliveries. Each row in BigQuery carries a load timestamp and the original QuickBooks SyncToken so QuickBooks data changes can be tracked and reconciled.

How ml-connector handles it

ml-connector caches the QuickBooks OAuth access token and automatically refreshes it 5 minutes before expiry so token rotation never interrupts a sync. When a QuickBooks webhook arrives, ml-connector validates the webhook signature, fetches the full record via GET with the current SyncToken, and streams it to BigQuery using tabledata.insertAll with a unique insertId (constructed from entity ID and sync timestamp) to prevent duplicates. BigQuery's deduplication is best-effort, so ml-connector also polls the 30-day CDC endpoint on a cron schedule as a catch-all for any out-of-order or missed webhook events. For the Google Cloud side, ml-connector fetches a short-lived access token by signing a JWT with the service account key and exchanging it at the OAuth token endpoint; tokens are cached and refreshed before expiry. Every record carries the SyncToken from QuickBooks and the load timestamp from BigQuery for audit purposes.

A real-world example

A mid-sized software-as-a-service company uses QuickBooks Online to manage invoices to customers and bills to vendors. The finance and product teams want to analyze invoice aging, churn patterns by customer cohort, and vendor spend trends without exporting quarterly data snapshots by hand. With QuickBooks Online and Google BigQuery connected, invoice and bill records stream into BigQuery automatically within minutes of being created in QuickBooks. The data team queries live invoice aging by customer segment, identifies customers with unpaid invoices over 60 days, and tracks vendor spend by category - all without a weekly export ritual or stale data.

What you can do

• •Stream QuickBooks Online invoices, bills, customers, and vendors into BigQuery tables partitioned by sync date for real-time financial analytics.
• •Automatically validate QuickBooks webhooks and fetch the full record on each event, with fallback polling from the 30-day CDC endpoint.
• •Deduplicate inserts into BigQuery using a composite key of QuickBooks entity ID and sync timestamp to prevent duplicate rows.
• •Refresh OAuth tokens automatically on both sides: QuickBooks access tokens (1-hour expiry) and Google service account tokens (1-hour expiry).
• •Maintain a full audit trail with SyncToken and load timestamp on every record so QuickBooks changes can be tracked and reconciled.

Questions

Which direction does data move between QuickBooks Online and Google BigQuery?

Data flows one direction: QuickBooks Online to Google BigQuery. Invoices, bills, customers, vendors, and GL accounts are streamed from QuickBooks into BigQuery tables where they can be queried for reporting and analysis. BigQuery is read-only in this integration; no data writes back to QuickBooks.

How does ml-connector handle QuickBooks' webhook payload limitation where the payload contains only the entity ID?

QuickBooks webhooks contain only entity ID and operation type, so ml-connector validates the webhook signature and then immediately fetches the full record via a GET request to the QuickBooks API using the SyncToken for concurrency control. This ensures the complete, current state of the entity is always written to BigQuery.

What happens if a QuickBooks webhook is delayed or lost, and how does ml-connector catch it?

ml-connector uses two mechanisms: it validates and processes every webhook that arrives, and it also polls the QuickBooks CDC (change data capture) endpoint every 6 hours to catch any out-of-order or missed events. Inserts into BigQuery are deduplicated using a composite insertId so even if a record is synced twice, only one row lands in the table.