SAP Business One and Snowflake integration

What moves between them

ml-connector reads invoices, purchase invoices, purchase orders, incoming and outgoing payments, journal entries, and dimensions from SAP Business One via OData polling on a schedule you define (typically daily or after each accounting close). For each record, it checks if the SAP Business One DocNum or journal entry key already exists in the Snowflake table to avoid duplicates. If the record is new or changed, ml-connector inserts or updates the Snowflake row. Chart of accounts and dimensions are synced first as reference tables so every transaction references a valid GL account. The main flow is one direction (SAP Business One to Snowflake) because Snowflake is read-only for this purpose -- ml-connector never writes financial entries back into SAP Business One.

How ml-connector handles it

ml-connector stores the SAP Business One customer instance URL and login credentials encrypted, logs in once, and reuses the session token to avoid the 5-second login penalty on every request. When the session expires (30 minutes inactivity, error -5002), ml-connector detects it and logs in again. ml-connector builds OData queries with $filter=UpdateDate gt datetime'YYYY-MM-DDThh:mm:ss' to fetch only new or changed records since the last sync. For Snowflake, ml-connector stores the RSA private key encrypted, generates a JWT on each request (with a 1-hour expiry ceiling), and submits SQL statements asynchronously, polling the statement handle until the query completes. Before inserting invoices or journal entries, ml-connector validates that the dimensions (profit center, cost center) exist in the Snowflake dimensions table; if not, it loads them first. If a dimension is missing in SAP Business One, the invoice is flagged in an error log and skipped until the dimension is added. SAP Business One OData has no published idempotency key, so ml-connector checks for the DocNum before inserting to avoid duplicates after retries. If Snowflake returns HTTP 429 (rate limit), ml-connector backs off exponentially. SAP Business One self-signed TLS certificates are trusted on a per-instance basis after the customer provides the certificate fingerprint.

A real-world example

A regional wholesale distributor runs SAP Business One on-premise for purchasing, invoicing, and general ledger, but needs to preserve transaction history and run cross-year financial analysis without querying the live ERP every month. Finance staff download trial balances and invoice registers every 90 days and archive them in Excel. With SAP Business One and Snowflake connected, each daily journal entry and invoice from SAP Business One flows into Snowflake automatically. The finance team creates SQL views that recreate the trial balance from the Snowflake archive, run variance analysis across fiscal years, and export audit-trail reports showing which transactions were posted and by whom, all without touching SAP Business One.

What you can do

• •Load SAP Business One invoices, purchase orders, and journal entries into Snowflake tables on a schedule you define.
• •Archive financial transactions and dimensions from SAP Business One for long-term storage and historical analysis.
• •Prevent duplicate records by checking the SAP Business One document number before insert.
• •Validate dimensions (profit centers, cost centers, GL accounts) from SAP Business One before loading transactions that reference them.
• •Authenticate SAP Business One with reusable session tokens and Snowflake with RSA key-pair signatures, with automatic retry on inactivity timeout.

Questions

Which direction does data flow between SAP Business One and Snowflake?

Data flows one direction: SAP Business One to Snowflake. ml-connector reads invoices, journal entries, purchase orders, dimensions, and chart of accounts from SAP Business One and inserts them into Snowflake tables for analysis and archival. Snowflake is used as a read-only replica and historical archive, so ml-connector never writes financial entries back into SAP Business One.

How does ml-connector handle SAP Business One session timeouts?

SAP Business One session tokens expire after 30 minutes of inactivity (error code -5002). ml-connector logs in once and reuses the session token to avoid the 5-second login penalty on every request. When a timeout is detected, ml-connector automatically logs in again and retries the request. Login credentials are stored encrypted.

How does ml-connector prevent duplicate records in Snowflake?

SAP Business One OData has no published idempotency key, so ml-connector checks the Snowflake table for the SAP Business One DocNum (document number) before inserting each invoice or purchase order. If the DocNum already exists, the record is skipped or updated if it has changed. Dimensions are synced first as reference tables to ensure every transaction references a valid GL account.