Microsoft Dynamics GP and Snowflake integration

What moves between them

The flow is unidirectional from Microsoft Dynamics GP into Snowflake. ml-connector polls GP on a schedule you define (e.g., hourly or daily), reading vendors, payables invoices, purchase orders, GL accounts, GL journal entries, customers, receivables invoices, and inventory items. Each record carries a ModifiedDate timestamp so only changed records are fetched on subsequent polls. Records are written into Snowflake tables as new rows (append) or as updates to existing rows (upsert), depending on your mapping. GL data is particularly important for reporting and reconciliation, and ml-connector ensures that only posted GL entries are synced so your warehouse reflects financial reality. Because GP has no webhooks, the cadence is polling-based and can be tuned to match your business cycle: daily for reconciliation-critical data, hourly for operational dashboards.

How ml-connector handles it

ml-connector stores your Windows domain account credentials encrypted and presents them to the GP endpoint using Windows Authentication (Negotiate, Kerberos, or NTLM) on every request. It accepts the server hostname and port, tenant name, and company database name as configuration per customer, since GP installations are on-premises and vary by deployment. On the Snowflake side, ml-connector stores the RSA private key or bearer token encrypted and uses it to generate JWTs or attach the token to every API call. When querying Snowflake's SQL API, ml-connector polls async query handles with exponential backoff until the result is ready, then upserts the fetched GP records into the target tables. It tracks the ModifiedDate watermark from each poll cycle and uses it as a filter on the next poll (e.g., $filter=ModifiedDate ge 2024-06-12) so unchanged records are never re-fetched. If a record is posted or in a closed fiscal period in GP, it is treated as read-only and will not be updated even if the source record changes. Snowflake's network policies require the connector egress IP to be whitelisted, and ml-connector respects Snowflake's lack of rate limits by backing off on HTTP 429 and logging all polling cycles in the audit trail so failures can be replayed. Every record retains its source identifiers and timestamps so you can trace the data path from GP to Snowflake and validate reconciliation.

A real-world example

A regional beverage distributor runs Microsoft Dynamics GP on-premises for payables, purchasing, and inventory across three regional hubs. The finance team wants to analyze payables aging and purchase orders by region and product category, but exports require manual work and lag by a day or two. The warehouse team maintains Snowflake as their business intelligence platform. By connecting GP to Snowflake, payables invoices, purchase orders, and GL accounts flow into the warehouse hourly. Finance analysts can query current state without asking the AP team for an export, and the BI dashboards update automatically so aging reports and spend analysis always reflect the current state. Month-end reconciliation is faster because the warehouse has the full audit trail of when records were posted in GP.

What you can do

• •Poll Microsoft Dynamics GP vendors, invoices, purchase orders, GL accounts, and inventory items on a schedule you define, with ModifiedDate filtering to avoid re-fetching unchanged data.
• •Authenticate to on-premises GP with your Windows domain account and to Snowflake with key pair or bearer token, with encrypted credential storage.
• •Write records into Snowflake tables as appends or upserts, with automatic schema mapping and case-sensitivity handling.
• •Handle Snowflake's async SQL API with polling, exponential backoff, and failure replay so data always reaches the warehouse.
• •Respect GL posting state and fiscal period open status so only truly valid financial records are synced and your warehouse reflects GP's authoritative state.

Questions

Does ml-connector work with all versions of Microsoft Dynamics GP?

ml-connector supports GP 2015 and later via REST Service Based Architecture (SBA), and GP 2010 and later via SOAP Web Services. Verify with your administrator which API surface is installed at your site, since many on-premises installs run SOAP-only. GP support ends September 30, 2029, so migration to Dynamics 365 Business Central may be worth planning.

What happens if a GL entry is posted in GP after we have already synced it to Snowflake?

ml-connector syncs only unposted (Work) records and records already in Open or Historical state. Once a record transitions from Work to Open (posted), it becomes read-only in GP and will not be updated further. Your Snowflake table will retain the historical record so you have a complete audit trail.

Does Snowflake require network setup to allow ml-connector to connect?

Yes. Snowflake uses network policies to restrict which IP addresses can reach it. Before connecting, work with your Snowflake administrator to whitelist the egress IP that ml-connector uses, and ensure your warehouse has AUTO_RESUME enabled so queries do not time out.