Microsoft Dynamics NAV and Snowflake integration

What moves between them

The main flow is pull from Microsoft Dynamics NAV into Snowflake. ml-connector polls NAV for purchase orders, purchase invoices, sales invoices, customers, vendors, and GL entries on a configurable schedule (daily, for example). Each record is mapped to a corresponding Snowflake table (e.g., purchase_orders, sales_invoices, vendors, gl_entries). Dimensions and GL accounts are synced as reference data so transactions land on valid accounts. A timestamp watermark on each table tracks the last successful sync, so incremental pulls retrieve only new and modified records since the previous run, avoiding full rescan overhead and NAV API throttling.

How ml-connector handles it

ml-connector accepts the Microsoft Entra ID tenant ID and application credentials from your NAV Business Central subscription, validates OAuth tokens and refreshes them before expiry, and polls the OData endpoints for each entity. On the Snowflake side, it uses key pair authentication (RSA JWT) to open a connection, resumes the warehouse if it is suspended, and submits SQL INSERT and UPDATE statements via the SQL API to load or refresh records in your target tables. Because NAV purchase orders do not emit webhooks, polling is the only reliable retrieval method. ml-connector tracks the last update timestamp per table and uses that watermark in subsequent OData queries to fetch only new and changed rows, reducing API load. It handles Snowflake's asynchronous query model by polling the statement handle until the query completes (success, error, or timeout), and backs off on HTTP 429 rate-limit responses. Transaction boundaries are maintained so a partial sync failure can be replayed without data loss or duplicate records.

A real-world example

A growing wholesale distributor runs Microsoft Dynamics NAV on-premises for purchasing, inventory, and accounts payable, and uses Snowflake as their cloud analytics warehouse. Their finance team exports purchase orders and invoices from NAV monthly and loads them into Excel pivot tables to track vendor performance and cash spend. With NAV and Snowflake connected, every purchase order and vendor invoice is automatically loaded into Snowflake within hours of posting, and the finance team writes SQL queries to analyze spending trends, identify payment bottlenecks, and reconcile GL accounts to Snowflake invoice totals. Month-end close is faster because vendor reconciliation is automated.

What you can do

• •Load purchase orders, purchase invoices, and sales invoices from Microsoft Dynamics NAV into Snowflake tables on a daily or configurable schedule.
• •Authenticate NAV with OAuth 2.0 client credentials via Microsoft Entra ID and Snowflake with RSA key pair authentication.
• •Use a timestamp watermark to retrieve only new and updated records, avoiding full rescans and reducing NAV API throttling.
• •Sync dimensions and GL account reference data so every transaction lands on a valid NAV GL account in Snowflake.
• •Handle Snowflake's asynchronous query model and resume suspended warehouses before loading data.

Questions

Why use polling instead of NAV webhooks?

Microsoft Dynamics NAV webhooks expire after 3 days and must be renewed, and the standard webhook entity list does not include purchase orders, a critical procurement record. Polling with a timestamp watermark is more reliable for this integration. ml-connector tracks the last sync timestamp per table, so incremental polls retrieve only new and changed records since the previous run.

How does ml-connector handle NAV's OAuth 2.0 tokens and Snowflake's key pair authentication?

ml-connector validates and refreshes NAV OAuth tokens before they expire and uses RSA key pair authentication to generate a short-lived JWT for Snowflake. Both credential sets are stored encrypted in ml-connector's database. The connector re-authenticates on every sync cycle so token or certificate renewal never causes an outage.

What happens if a Snowflake query times out or the warehouse is suspended?

ml-connector checks warehouse status before loading data and resumes it if AUTO_RESUME is true. If a query times out or returns an error, the integration logs the failure and retries on the next scheduled sync. The timestamp watermark ensures retries pick up where the previous attempt left off without duplicating records.