Microsoft Dynamics GP and Databricks integration

What moves between them

Financial records flow one direction: from Microsoft Dynamics GP into Databricks. ml-connector polls GL accounts, GL journal entries, vendors, payables invoices, and purchase orders from GP on a cadence you set (typically daily or per-posting-cycle), applies any field mappings (such as GL account numbers to cost centers), and writes rows into Databricks as Delta tables. Reference data such as vendors and GL account master records are replicated first so dimension keys exist before fact loads. Databricks tables are append-only or merge-on-key, depending on your change-data-capture strategy; updated records in GP (vendor address changes, GL account reclassifications) can be upserted into Databricks via the same job.

How ml-connector handles it

ml-connector stores GP credentials as a dedicated Windows domain account created by your IT team and scoped to a specific GP company database. On each poll cycle, it connects to the SBA or SOAP endpoint, filters records by ModifiedDate if available or by status, and transforms the response. GP's SBA requires request bodies to be wrapped in a top-level Payload key: ml-connector handles this automatically. For Databricks, ml-connector obtains an OAuth 2.0 access token once per schedule run (caching it for the 3600-second lifetime), then uses the token to call the REST catalog and SQL warehouse endpoints to append or upsert records into the target schema and tables. Because GP is pull-only and Databricks tokens expire, ml-connector implements exponential backoff for transient failures and re-queues jobs that fail midway through a table write. Vendor IDs and GL account numbers become the merge keys in Databricks, aligning with your chart of accounts. A full audit trail tracks which records arrived from which GP posting date, so month-end reconciliation can trace a Databricks balance back to the original GL entry.

A real-world example

A mid-market wholesale distributor runs Microsoft Dynamics GP for AP, AR, purchasing, and GL across three distribution centers. Their finance team needs to build a consolidated view of margin by vendor and product category, but GP reporting is limited to single-company extracts. They deploy Databricks as a data lake, connecting it to GP via ml-connector to ingest vendors, invoices, and GL postings every business day. Once the ERP data lands in Databricks, they join it with external product catalogs and pricing feeds, then build dashboards showing vendor performance and margin trends. Previously, an analyst manually exported AP registers and GL trial balances weekly and stitched them in a spreadsheet; now the data arrives automatically and updates are available within hours of posting.

What you can do

• •Replicate GL accounts, GL journal entries, vendors, and payables invoices from Microsoft Dynamics GP into Databricks Delta tables on a schedule you control.
• •Bridge Windows Authentication on the GP side to OAuth 2.0 Client Credentials on Databricks, handling token refresh and SBA Payload-wrapped requests automatically.
• •Poll GP with ModifiedDate filters to extract only changed records, reducing load on your on-premises system and cloud API quota.
• •Merge vendor and GL account updates into Databricks using natural keys, so dimension changes flow without duplicating history.
• •Maintain a full audit trail of every record's source posting date and sync timestamp, enabling month-end reconciliation and replay of failed loads.

Questions

Does ml-connector handle GP's Windows Authentication requirement?

Yes. ml-connector stores a dedicated Windows domain account (created by your IT team) and uses it to authenticate to the GP SBA or SOAP endpoint via Negotiate or NTLM. Databricks is authenticated separately via OAuth 2.0 Client Credentials, so ml-connector bridges the two auth models without exposing credentials.

What if Microsoft Dynamics GP is behind a firewall?

Your network team must expose the GP SBA or SOAP endpoint through the firewall with a valid SSL certificate, or deploy ml-connector's optional local polling agent inside your network. ml-connector then calls the endpoint remotely and pushes data to Databricks in the cloud. Confirm with your IT team that the SBA endpoint is installed (many GP instances run SOAP-only).

Can ml-connector keep Databricks in sync with real-time GL postings?

No. GP does not support webhooks or push notifications, so ml-connector polls on a schedule (daily, every few hours, or per-posting-cycle). For real-time analytics, use Databricks' scheduled SQL jobs to refresh aggregations hourly or on-demand. Audit records include the exact GP posting timestamp, so you can trace any Databricks balance back to the source GL entry.