Collect QuickBooks Online data into your data warehouse

The connector has 26 endpoints, each landing in its own table.

17 entity tables. Accounts (quickbooks_account), customers, vendors and items are the lists everything else points to. Sales: invoices (quickbooks_invoice), payments, sales receipts, credit memos and refund receipts. Purchases: bills (quickbooks_bill), bill payments and purchases, the card, cash and check spend that never had a bill. Journal entries hold the adjusting postings the other tables miss; deposits and transfers move money between accounts; company info and preferences hold one row per company, home currency and fiscal-year start included.

One change-data-capture table. quickbooks_cdc_invoice reads Intuit’s change data capture endpoint with a changedSince time and returns every invoice created, updated or deleted since then, with the full invoice as JSON. Deleted invoices carry status = Deleted. This is the only place a delete shows up: a query on last-updated time never returns a deleted row, so quickbooks_invoice keeps the last live version of an invoice that no longer exists. Intuit’s endpoint looks back at most 30 days and returns at most 1,000 changes per request, so the table runs daily and a delete older than its first run is not recovered.

Eight dated report snapshots. Profit and Loss with one column per month, Balance Sheet, Cash Flow, A/R Aging Summary, A/P Aging Summary, Vendor Expenses, General Ledger Detail and Sales by Customer. Each run requests the report for its run window and stores it with start_period and end_period, rows kept as JSON.

The chart of accounts, items, company info and preferences reload in full on every run. The transaction and name-list tables read the records whose MetaData.LastUpdatedTime is after the “Load changes since” date you set, oldest edit first and at most 1,000 per run, so an edited invoice returns with a new last_updated_time and sync_token. A company with more than 1,000 changes since that date needs the date moved forward, or a second Data Mart on a later window, before the table is complete. Line items stay as one line JSON column, every row carries the company id as realm_id, and an HTTP 429 from Intuit is retried with a back-off, using the Retry-After header when Intuit sends one. Range reports run on Accrual or Cash, whichever you choose, and record it in accounting_method_param, so a cash-basis and an accrual-basis P&L never mix in one query.

Authentication is OAuth 2.0 with a refresh token. The platform exchanges the refresh token for an access token on each run. Intuit rotates refresh tokens and expires one left unused for 100 days, so the token is saved in an OWOX credential, where the platform persists the rotated token for the next run.

A custom connector, built in OWOX Data Marts

QuickBooks is not one of the open-source native data connectors. It is a custom connector, built in the connector builder of OWOX Data Marts from Intuit’s Accounting API documentation: 26 nodes, each a request plus the fields it lands. Its whole manifest opens in the builder’s Code tab, so you can see exactly what it fetches.

To set it up:

1. In OWOX Data Marts, open Connectors and choose the custom connector QuickBooks Online.
2. Create a credential with your Intuit app’s Client ID and Client Secret, a Refresh Token and the Realm ID, the QuickBooks company id.
3. Create one Data Mart per table you need and point it at a dataset in your data storage.
4. Add a Connector Trigger that runs the Data Marts daily.

On one company’s books, eight Data Marts load daily into a BigQuery dataset named quickbooks: accounts, customers, invoices, payments, bills, items, A/R aging and P&L. The P&L history from 2026-01-01 to 2026-11-05 was backfilled in 10 segments, every one Success.

Two checks from that setup:

• A/R aging rebuilt in SQL from open balances in quickbooks_invoice matches the A/R Aging Summary report to the cent.
• Over July to October, invoices explain 94% of P&L income and bills 71% of P&L expenses. The rest sits in tables the eight Data Marts do not load yet: sales receipts and refunds on the income side, direct purchases (card and cash spend without a bill) on the expense side. Invoices alone are never the revenue number.

Join these tables with orders and ad spend in a data model, and margin by channel comes from one readable query.

Question	Table	Date field
Income, expenses and net income by month, as QuickBooks reports them	quickbooks_report_profit_and_loss	start_period, end_period
Invoiced sales by customer or item	quickbooks_invoice, unnest line	txn_date
Cash received	quickbooks_payment	txn_date
Open receivables and aging	quickbooks_invoice (balance), checked against quickbooks_report_aged_receivables	due_date; end_period
Payables	quickbooks_bill, quickbooks_bill_payment	txn_date, due_date
Spend without a bill	quickbooks_purchase	txn_date
What changed or was deleted	quickbooks_cdc_invoice	last_updated_time

• Bucket money by txn_date, the business date. last_updated_time is the last edit: use it for freshness, never for revenue by month.
• Every transaction and list table’s key is realm_id + id. Join on both. Company info and preferences hold one row per realm_id.
• How the 26 tables fit together is drawn in the QuickBooks data model: quickbooks_company_info is the anchor table on realm_id, and customers, vendors and accounts are the hubs every transaction table joins to. Open it on the model canvas or read the bundle on GitHub.
• customer_id joins to quickbooks_customer.id, vendor_id to quickbooks_vendor.id, and any *_account_id to quickbooks_account.id.
• Payment to invoice: unnest quickbooks_payment.line, keep each LinkedTxn entry with TxnType = Invoice, and join its TxnId to quickbooks_invoice.id. Bill payments link to bills the same way.
• Drop deleted invoices with an anti-join to quickbooks_cdc_invoice where status = Deleted.
• In report tables, flatten report_rows; ColData.id is the account, customer or vendor id, and report_columns is index-aligned with each ColData array. Where accounting_method_param exists, filter it to one method.

A Report delivers one Data Mart to one destination, and each Data Mart can feed many destinations.

• Push mode: OWOX writes the data on a manual or scheduled run. Google Sheets, email, Slack, Microsoft Teams and Google Chat work this way.
• Pull mode: the tool asks OWOX for the data when a user refreshes it. Data Studio dashboards and Microsoft Excel work this way.
• AI assistants such as Claude and ChatGPT read the Data Mart through OWOX MCP, not the raw tables, so answers use your definitions.