FreshBooks and Google BigQuery integration

What moves between them

The flow runs from FreshBooks into Google BigQuery. ml-connector reads FreshBooks invoices, payments, expenses, bills, bill vendors, and clients on a schedule and writes each record type into its own BigQuery table, mapped to typed columns. Invoice numeric status codes such as Draft, Sent, Paid, and Partial are resolved into readable values, and the FreshBooks account and business identifiers are carried as columns so multi-entity data stays separable. Soft-deleted FreshBooks records, marked with vis_state, are carried through with a flag rather than dropped, so the warehouse reflects deletions. Because BigQuery is the reporting destination, ml-connector does not push financial changes back into FreshBooks.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the FreshBooks side it runs the OAuth 2.0 Authorization Code flow and refreshes the access token before expiry, saving the newly rotated refresh token each time because the prior one is invalidated. It calls the FreshBooks /me endpoint once to capture the account ID and business ID, then uses the right identifier for each endpoint namespace. On the BigQuery side it signs a JWT with the service account private key, preserving the literal newline characters in the PEM block so signing does not break, and exchanges it for a fresh one-hour token before each token expires. Because neither system emits a reliable real-time push, ml-connector polls FreshBooks on a schedule you set, paging with page and per_page until the page count is reached, and backs off and retries on HTTP 429. Each record type maps to a configurable BigQuery dataset and table, and every row carries an insertId derived from the FreshBooks resource ID so a streamed re-read is deduplicated within the BigQuery dedup window. Where larger backfills are needed, ml-connector batches rows through a load job with a caller-supplied jobId so a retried job returns the existing job rather than duplicating it. Adding a column to a target table is safe, but renaming or removing one is destructive in BigQuery, so the connector only appends fields. Every record carries a full audit trail and can be replayed if a downstream write fails.

A real-world example

A twenty-person marketing agency bills retainer and project work through FreshBooks and wants a single revenue and expense view alongside its ad-spend and project data, which already sit in BigQuery. Before the integration, an analyst exported invoice and expense reports from FreshBooks each month and loaded them into the warehouse by hand, so the numbers were always a few weeks stale and a missed export left gaps. With FreshBooks and Google BigQuery connected, invoices, payments, and expenses stream into warehouse tables on a daily schedule, joined with the rest of the agency's data, and the finance dashboards refresh on their own. The manual export step is gone and the revenue view is current.

What you can do

• •Stream FreshBooks invoices, payments, expenses, bills, and clients into Google BigQuery tables on a schedule you set.
• •Resolve FreshBooks numeric invoice status codes into readable columns and carry account and business IDs for multi-entity data.
• •Bridge FreshBooks user-delegated OAuth refresh tokens and the BigQuery service-account JWT, refreshing each before expiry.
• •Dedupe each row with an insertId from the FreshBooks resource ID, and use a stable jobId for batch load jobs.
• •Append new columns safely as FreshBooks fields change, with retries and a full audit trail on every record.

Questions

Which direction does data move between FreshBooks and Google BigQuery?

The flow is one direction, from FreshBooks into Google BigQuery. Invoices, payments, expenses, bills, bill vendors, and clients move from FreshBooks into warehouse tables, where they can be queried and modeled. BigQuery is a reporting warehouse rather than an accounting system, so ml-connector treats FreshBooks as the source of record and does not write financial changes back into it.

Does FreshBooks push records, or does ml-connector poll for them?

ml-connector polls FreshBooks on a schedule. FreshBooks can send webhooks, but its docs state delivery latency is not guaranteed and can run from seconds to minutes, so it is not reliable as a real-time trigger. The connector reads each entity with offset pagination, paging through page and per_page until it reaches the last page, and backs off and retries when FreshBooks returns HTTP 429.

How does the integration avoid duplicate rows in BigQuery?

BigQuery does not enforce primary keys, so ml-connector handles deduplication itself. Each streamed row carries an insertId built from the FreshBooks resource ID, which gives best-effort dedup within BigQuery's roughly one-minute streaming window, and larger backfills run as load jobs with a caller-supplied jobId so a retried job returns the existing one instead of writing duplicates.