Epicor Kinetic and Google BigQuery integration

What moves between them

Records move one direction, out of Epicor Kinetic and into Google BigQuery. On each scheduled run, ml-connector reads Epicor AP invoices, purchase orders, payments, vendors, and GL accounts that changed since the last sync, then writes them as rows into the matching BigQuery tables such as invoices, purchase_orders, payments, vendors, and gl_entries. Cadence follows the schedule you set, for example hourly for invoices and daily for vendor and account master data. BigQuery is read-only as far as Epicor is concerned, so the connector never writes financial entries from BigQuery back into the ERP; analysts query the warehouse, and Epicor stays the system of record.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the Epicor side it accepts the full tenant URL, company, and instance per customer, presents Basic or OAuth2 credentials plus the API key on every call, and refreshes the bearer token before its one-hour expiry. On the Google side it signs a JWT with the service account private key, preserving the literal newline characters in the PEM block that many secret stores strip, and trades it for a fresh access token each hour. Because neither system sends webhooks, the connector polls Epicor with an OData date filter and loads into BigQuery on a schedule. Each Epicor business object maps to a configured dataset and table, with field types matched to the BigQuery schema. Writes carry an insertId per row for best-effort dedup, or a stable jobReference.jobId on load jobs so resubmitting the same batch returns the existing job instead of duplicating rows. Epicor has no soft delete, so the connector filters on Inactive and Voided flags to skip closed records, and partitioning the target tables by date keeps polling queries cheap. Every record carries a full audit trail and can be replayed if a load fails.

A real-world example

A mid-sized contract manufacturer with around four hundred employees runs Epicor Kinetic for procurement, AP, and the general ledger. The finance and operations teams want spend and supplier dashboards in a BigQuery-backed BI tool, but exports from Epicor were manual and the data was always a few days stale, so buyers could not see open purchase orders against actual invoices in one place. With Epicor Kinetic connected to Google BigQuery, invoices, purchase orders, payments, and vendors load into the warehouse on an hourly schedule. Analysts build supplier spend, AP aging, and PO-to-invoice match reports directly in SQL, and the dashboards refresh on their own without anyone re-keying ERP exports.

What you can do

• •Stream Epicor AP invoices, purchase orders, payments, vendors, and GL accounts into Google BigQuery tables on a schedule.
• •Poll Epicor with OData date filters since Epicor sends no webhooks, and skip records flagged Inactive or Voided.
• •Bridge Epicor Basic or OAuth2 login plus the API key to a Google service account JWT-bearer access token.
• •Map each Epicor business object to a configurable BigQuery dataset and table with matched field types.
• •Deduplicate writes with row insertId and stable load job ids, with retries and a full audit trail on every record.

Questions

Which direction does data move between Epicor Kinetic and Google BigQuery?

Data moves one direction, out of Epicor Kinetic and into Google BigQuery. Epicor is the system of record and BigQuery is the analytics warehouse, so invoices, purchase orders, payments, vendors, and GL accounts are read from Epicor and written as rows into BigQuery tables. The connector does not write financial entries from BigQuery back into Epicor.

How does the integration detect new records when neither system has webhooks?

Both systems are pull-only, so ml-connector polls on a schedule you set. It queries Epicor with an OData $filter on a modified-date field to fetch records changed since the last run, paging through with $top and $skip. Those rows are then loaded into BigQuery, with date-partitioned target tables keeping the polling queries inexpensive.

How are the two different authentication methods handled?

ml-connector stores both credential sets encrypted. For Epicor it sends Basic Auth or an OAuth2 bearer token along with the required API key header and refreshes the token before its one-hour expiry. For BigQuery it signs a JWT with the Google service account private key, preserving the newline characters in the PEM block, and exchanges it for a one-hour access token that it refreshes automatically.