Epicor Kinetic and Databricks integration

What moves between them

The main flow is from Epicor Kinetic into Databricks. After each sync interval, ml-connector reads all changed vendors, purchase orders, AP invoices, GL accounts, customers, and parts from Epicor using OData date filters, normalizes the records into clean dimension and fact table schemas, and writes them into corresponding Databricks tables partitioned by entity type and sync timestamp. GL accounts are written once to a dimension table and reused across all fact tables. Vendor and customer dimensions are updated in place, and invoice and PO facts are appended with lineage tracking. The flow is read-only in Databricks; Epicor remains the system of record for all transactions.

How ml-connector handles it

ml-connector stores both credential sets encrypted and manages two separate OAuth2 flows: Epicor client credentials flow with a required API Key header on every request, and Databricks workspace or account-level client credentials flow with token refresh every 3600 seconds. On the Epicor side it accepts the full tenant URL and company segment since both are required in the OData path, validates that the company exists by querying a small dimension table first, and polls all key entities using $filter on UpdatedOn (for records) or status fields to find changes since the last sync. OData offset pagination is applied to handle Epicor result sets that exceed 100 records. ML-connector maps Epicor field names to Databricks column names following a consistent naming convention (e.g., EPIVendorID -> vendor_id, APInvoiceNum -> invoice_number), tracks the Epicor Company and instance URL as lineage metadata on every row so tables can be filtered by source, and deduplicates vendor and customer records by their Epicor-assigned IDs. Write operations are idempotent: dimension tables use Upsert logic (insert new, update existing by ID), and fact tables append new records. Databricks 3600-second token expiry is monitored so token renewal happens before a call fails. Every record in Databricks carries sync timestamp, source Epicor instance, and a link back to the source record so failed downstream transformations can be traced and replayed.

A real-world example

A mid-sized discrete manufacturer uses Epicor Kinetic for procurement, manufacturing costing, and accounts payable, and Databricks as their enterprise data warehouse. Before the integration, the finance team exported vendor master data, purchase orders, and invoice details from Epicor monthly and rebuilt the tables manually in Databricks, or wrote SQL queries that joined multiple Epicor REST calls inside Databricks Spark jobs with no retry logic or audit trail. When an invoice status changed or a PO was cancelled in Epicor, the Databricks table was stale until the next manual export cycle. After connecting Epicor to Databricks via ml-connector, every new vendor, PO, and invoice flows into Databricks on your chosen schedule (daily, hourly, or on-demand), so procurement analytics, cash flow forecasts, and supplier spend reports are always current. The team no longer rebuilds tables by hand, and they can trace any discrepancy back to the exact Epicor record and sync timestamp.

What you can do

• •Poll vendors, purchase orders, invoices, payments, GL accounts, customers, and parts from Epicor Kinetic on a schedule you set, and write them as clean dimension and fact tables in Databricks.
• •Map Epicor field names to standardized Databricks column names and partition tables by entity type and sync timestamp so your analytics queries are fast and reproducible.
• •Track lineage metadata (source Epicor instance, company, sync timestamp, source record ID) on every row so failed transformations can be traced and replayed.
• •Authenticate Epicor with OAuth2 and API Key, and Databricks with workspace or account-level client credentials, and refresh both token types automatically.
• •Monitor Databricks token expiry and refresh before a call fails, and handle OData pagination and Epicor date filtering so no records are skipped and none are duplicated.

Questions

Which direction does data move between Epicor Kinetic and Databricks?

Data flows from Epicor Kinetic into Databricks only. Vendors, purchase orders, invoices, GL accounts, and other business objects are read from Epicor and written as dimension and fact tables in Databricks. Databricks is the warehouse; Epicor remains the system of record for all transactions.

How does ml-connector handle Epicor lack of webhooks and the requirement to poll?

ml-connector polls Epicor on a schedule you configure (daily, hourly, or on-demand). It uses OData $filter on date fields like UpdatedOn to fetch only records changed since the last sync, and handles offset pagination with $skip and $top to retrieve large result sets without missing records. This approach works for both cloud SaaS and on-premises Epicor deployments.

What happens if a sync fails partway through or a Databricks write times out?

Every record in Databricks carries a sync timestamp and a link back to the source Epicor record, so you can identify exactly which records were and were not written. Failed syncs can be retried without duplication because dimension tables use upsert logic (insert if new, update if exists by ID) and fact tables track the source record ID for dedup.