Sage 300 and Snowflake integration

What moves between them

Records move from Sage 300 into Snowflake. AP invoices, AP payment batches, AR invoices, AR receipt batches, purchase orders, GL journal batches, and vendor and customer master data are polled from Sage 300 on a cadence you define, then inserted or upserted into Snowflake tables. Each batch of records is tagged with a sync timestamp and an audit record so you know when data was pulled and can replay failed inserts if needed. Snowflake tables are user-defined; ml-connector creates or appends to them based on the mapping you configure.

How ml-connector handles it

ml-connector stores the Sage 300 HTTP Basic Auth credentials (uppercase username and password) encrypted, and includes them in every API call to the customer-hosted Sage 300 IIS server. It polls using OData filters on document date and sequence numbers to pull only new or changed records since the last sync, paginating with $skip and $top to handle large batches. For Snowflake, ml-connector uses Key Pair Authentication with your RSA private key to generate a JWT and authenticate to Snowflake's SQL API. It transforms Sage 300 entities into Snowflake table rows, handling the accounting structure: GL accounts map to account numbers and descriptions, cost codes and segments map to dimensions, and invoice batches map to transactional rows with vendor, amount, and GL posting detail. If a Snowflake insert fails due to a network error, the job is queued for retry with exponential backoff. Sage 300's self-hosted API means no shared endpoint; ml-connector accepts your full Sage 300 URL per customer so the integration works whether you run the API on a public HTTPS server or behind a VPN.

A real-world example

A mid-sized manufacturer runs Sage 300 for accounts payable, procurement, and general ledger on Windows. The finance controller needs daily AP aging reports, procurement analytics, and GL reconciliation in a business intelligence tool that runs on Snowflake. Before the integration, the AP team exported vendor invoices from Sage 300 each week by hand, then uploaded the CSV to Snowflake and manually refreshed the BI dashboards. With Sage 300 and Snowflake connected, AP invoices flow automatically into Snowflake each night, tagged with their GL posting detail so the GL account and cost center are always visible. The BI tool refreshes on the same schedule, so the controller's aging report and AP analysis are current without manual steps.

What you can do

• •Poll AP invoices, AP payments, AR invoices, and AR receipts from Sage 300 and load them into Snowflake tables with full GL posting detail and audit context.
• •Authenticate Sage 300 with HTTP Basic Auth to your customer-hosted IIS server and Snowflake with Key Pair Authentication, storing credentials encrypted.
• •Map Sage 300 GL accounts, cost segments, and vendor master data to Snowflake dimensions so finance records are complete and queryable.
• •Use OData filters and pagination to poll only new and changed Sage 300 records on a schedule you set, minimizing data transfer and API load.
• •Queue and retry failed Snowflake inserts with exponential backoff, so transient network errors do not block the sync.

Questions

Does ml-connector need access to the Sage 300 server directly, or can it go through a VPN?

ml-connector needs HTTPS access to the Sage 300 API endpoint. If your Sage 300 IIS server is behind a VPN or firewall, you can configure a VPN gateway or firewall rule to allow ml-connector's IP range inbound, or use a direct tunnel. The full Sage 300 base URL is provided per customer, so the integration works with any network setup as long as the API is reachable over HTTPS.

What happens if a Sage 300 API call or Snowflake insert fails during a sync?

ml-connector logs the failure with full context and queues the job for automatic retry with exponential backoff. If the failure is transient (network timeout, temporary rate limit), the retry succeeds. If the failure is permanent (invalid credential, malformed data), the job moves to a failed queue where you can investigate the error, fix the underlying issue, and replay the batch.

Can ml-connector sync GL journal batches and post them into Snowflake as line items?

Yes. Sage 300 exposes GLJournalBatches with embedded line-item detail. ml-connector reads the batch and its lines, denormalizes them into a Snowflake table with one row per line (batch id, line number, account, segment, amount, description), so you can analyze journal postings by account and segment in your BI tool.