Which direction does data move between Sage 100 and Google BigQuery?

Data moves one way, from Sage 100 into Google BigQuery. ml-connector reads AP invoices, purchase orders, vendors, GL accounts, and journal entries from Sage 100 and writes them into customer-defined BigQuery tables. BigQuery is used as a read-only analytics copy, so ml-connector never writes anything back into the Sage 100 ledger.

Does Sage 100 need a local agent to reach Google BigQuery?

Yes for full finance data. Sage 100 is on-premises with no cloud API, so a lightweight Windows agent runs on the customer server and wraps the Business Object Interface, which is the only surface that exposes AP, GL, PO, and vendor objects. ml-connector calls that agent over HTTPS with an API key, and the SOAP-only path without an agent is limited to sales orders and customers.

How does the sync stay current when neither system sends webhooks?

Neither Sage 100 nor Google BigQuery pushes events, so the sync runs on a schedule. ml-connector polls Sage 100 using DateLastUpdated and date fields to pick up changed records, roughly every fifteen minutes for invoices and purchase orders and less often for slower-moving data like vendors and GL accounts. Each retry reuses a stable insertId so polling again does not duplicate rows in BigQuery.

Sage 100Google BigQuery

Sage 100 and Google BigQuery integration

Sage 100 runs the books on a Windows server inside the business. Google BigQuery stores and queries large datasets in the cloud. This connection copies Sage 100 finance records into BigQuery so the data can be reported on, blended with other sources, and queried with SQL without loading the ERP server. AP invoices, purchase orders, vendors, GL accounts, and journal entries are read on a schedule and landed in tables the customer defines. Sage 100 stays the system of record, and BigQuery becomes the analytics copy.

How Sage 100 works

Sage 100 is on-premises and has no native cloud API, so ml-connector reaches it through a local Windows agent that wraps the Business Object Interface, the only surface that exposes the full set of AP, AR, GL, PO, and inventory objects such as AP_Invoice_bus, PO_PurchaseOrder_bus, GL_Account_bus, and AP_Vendor_bus. The agent presents an HTTPS endpoint secured with an API key, and credentials for the eBusiness Web Services SOAP surface are a username, password, and a three-character company code passed on every call with no token to refresh. Sage 100 has no webhooks, so changes are detected by polling on DateLastUpdated and date fields. Every AP operation also requires the AP division number alongside the vendor number.

How Google BigQuery works

Google BigQuery exposes a REST API v2 over HTTPS, and every request carries an OAuth 2.0 bearer token. For server-to-server use the connector signs a JWT with a service account private key and exchanges it for an access token that lasts one hour. Rows are written with the streaming insert endpoint tabledata.insertAll, where an insertId gives best-effort deduplication, or batched through load jobs for larger volumes. Reads run as asynchronous query jobs that the connector submits, polls until done, and pages through with a pageToken. BigQuery has no pre-built finance objects, so the connector writes to datasets and tables the customer defines, such as invoices, purchase_orders, vendors, and gl_entries, and BigQuery sends no webhooks of its own.

What moves between them

Data moves in one direction, from Sage 100 into Google BigQuery. ml-connector polls the local agent for AP invoices, purchase orders, vendors, GL accounts, and journal entries, then streams or batch-loads each record into the matching customer-defined BigQuery table. Sage 100 invoice, vendor, and GL fields map to columns the customer has set up, with the segmented Sage 100 account key carried as a string. Invoices and purchase orders poll about every fifteen minutes, vendors hourly, and GL accounts daily, following the cadence Sage 100 documents for each entity. Nothing flows back from BigQuery into Sage 100, so the warehouse stays a clean copy.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the Sage 100 side it calls the local agent over HTTPS with the agent API key, supports a self-signed certificate on the customer server through configurable TLS verification, and passes the company code and AP division on every request. On the Google BigQuery side it signs the service account JWT, exchanges it for an access token, and refreshes that token before the one-hour expiry, taking care to preserve the literal newlines in the private key so JWT signing does not break. Because neither system pushes events, the connector polls Sage 100 on a per-entity schedule and uses DateLastUpdated and date fields to pick up only changed records. Each row is written with a stable insertId derived from the Sage 100 key, such as vendor number plus invoice number, so a retried streaming insert does not create a duplicate, and load jobs use a caller-supplied jobId for the same reason. New finance fields can be added to a BigQuery table as nullable columns without breaking the load, while renaming or removing a column is destructive and needs a new table. Sage 100 record-locking errors during a busy posting window are retried with backoff, and every record carries a full audit trail and can be replayed if a write fails.

A real-world example

A regional building-products distributor with about 120 staff runs Sage 100 on a server in its main office for AP, purchasing, and the general ledger. The finance team wanted spend and vendor dashboards that combined Sage 100 data with shipping and sales figures, but reporting straight off the ERP server slowed down order entry and the data never sat alongside the other sources. With Sage 100 and Google BigQuery connected, AP invoices, purchase orders, vendors, and GL entries land in BigQuery tables on a schedule, where the analytics team queries them with SQL and joins them to the rest of the business. The ERP server is left to do day-to-day work, and the dashboards refresh from the warehouse copy instead.

What you can do

Copy Sage 100 AP invoices, purchase orders, vendors, and GL accounts into Google BigQuery tables on a schedule.
Land Sage 100 journal entries in BigQuery for SQL reporting without querying the live ERP server.
Bridge Sage 100 per-call credentials to the BigQuery service account JWT and refresh the access token each hour.
Deduplicate writes with a stable insertId built from the Sage 100 record key so retries do not create duplicates.
Poll Sage 100 through the local agent for changed records, with backoff on record-lock errors and a full audit trail.

Questions

Which direction does data move between Sage 100 and Google BigQuery?: Data moves one way, from Sage 100 into Google BigQuery. ml-connector reads AP invoices, purchase orders, vendors, GL accounts, and journal entries from Sage 100 and writes them into customer-defined BigQuery tables. BigQuery is used as a read-only analytics copy, so ml-connector never writes anything back into the Sage 100 ledger.
Does Sage 100 need a local agent to reach Google BigQuery?: Yes for full finance data. Sage 100 is on-premises with no cloud API, so a lightweight Windows agent runs on the customer server and wraps the Business Object Interface, which is the only surface that exposes AP, GL, PO, and vendor objects. ml-connector calls that agent over HTTPS with an API key, and the SOAP-only path without an agent is limited to sales orders and customers.
How does the sync stay current when neither system sends webhooks?: Neither Sage 100 nor Google BigQuery pushes events, so the sync runs on a schedule. ml-connector polls Sage 100 using DateLastUpdated and date fields to pick up changed records, roughly every fifteen minutes for invoices and purchase orders and less often for slower-moving data like vendors and GL accounts. Each retry reuses a stable insertId so polling again does not duplicate rows in BigQuery.

Connect Sage 100 and Google BigQuery

Free to use. Add your credentials, ping your real systems, and see if we fit.

Get started