Oracle Fusion Cloud ERP and Snowflake integration

What moves between them

The primary flow is Oracle Fusion to Snowflake. ml-connector polls Oracle Fusion for new and changed invoices, payments, GL journals, and account master data, transforming each record to match Snowflake table schemas. Records are loaded into Snowflake tables on a configurable schedule, typically every 5 to 15 minutes. GL journal lines are denormalized to include both header context and line detail. Supplier and GL account dimension tables are updated whenever master data changes. Snowflake itself is read-only from ml-connector; finance teams use SQL queries for reconciliation and reporting without loading data back into Oracle Fusion.

How ml-connector handles it

ml-connector stores both credential sets encrypted: the Oracle Fusion OAuth2 client ID and secret in one envelope, and the Snowflake key pair or PAT token in another. When polling Oracle Fusion, it constructs REST requests with the last-known LastUpdateDate and automatically refreshes the OAuth2 bearer token when a call returns 401. For Snowflake, ml-connector uses Key Pair authentication when a private key is provided (recommended), which avoids token refresh cycles and is valid for one hour per JWT. All Snowflake table identifiers are quoted and lowercased to ensure case consistency. Because Oracle Fusion is pull-only without webhooks, ml-connector polls on your schedule; Snowflake's async SQL API is used for large result sets, and ml-connector polls the statement handle until results are ready. GL account combinations and dimensions are mapped through lookup tables so every transaction line references valid Snowflake dimension keys. Network policies must include ml-connector's egress IPs. Every record is logged with source ID, transformation, and load timestamp for full traceability and replay if a downstream reconciliation needs correction.

A real-world example

A mid-sized manufacturing and distribution company runs Oracle Fusion Cloud for AP, AR, and GL, with 50,000+ transactions per month. The finance team needs to load GL entries and invoice data into Snowflake for rolling forecasts, variance analysis, and audit-trail reporting that Oracle Fusion's built-in reports cannot provide. Before the integration, the controller exported GL batches and invoice extracts to CSV every week and imported them into Snowflake tables by hand, a 4-hour manual process. With Oracle Fusion and Snowflake connected, new GL journals and invoices flow into the warehouse automatically on a daily schedule. The finance team's BI tool now reads from Snowflake's latest data, month-end closes have fresh numbers without manual export steps, and the audit log in Snowflake provides proof of every transaction's journey from Oracle Fusion.

What you can do

• •Automatically sync Oracle Fusion invoices, payments, GL accounts, and journal lines into Snowflake tables on a schedule.
• •Map Oracle Fusion GL account combinations and supplier dimensions to Snowflake lookup tables for consistent reporting.
• •Bridge OAuth2 authentication from Oracle Fusion and key pair authentication from Snowflake, with automatic token refresh and expiry tracking.
• •Handle Snowflake's async query API, case sensitivity rules, and warehouse auto-resume requirements.
• •Maintain a complete audit trail of every record's source ID, transformation, and load timestamp for reconciliation and replay.

Questions

Which direction does data flow between Oracle Fusion and Snowflake?

The primary flow is Oracle Fusion to Snowflake. New and changed invoices, payments, GL journals, and master data are polled from Oracle Fusion and loaded into Snowflake tables. Snowflake serves as your read-only analytics warehouse; ml-connector does not write data back into Oracle Fusion.

Does Oracle Fusion's lack of webhooks mean the integration is slow?

Oracle Fusion has no direct webhooks without Oracle Integration Cloud middleware, so ml-connector polls the REST API on a schedule you control, typically every 5 to 15 minutes. This ensures your Snowflake tables are fresh for daily reporting and month-end close without requiring external middleware.

How does the integration handle Snowflake's case sensitivity and async queries?

ml-connector quotes and lowercases all Snowflake table and column identifiers to ensure consistency, since Snowflake uppercases unquoted names. For large result sets, it uses Snowflake's async SQL API and polls the statement handle until results are ready, avoiding query timeouts.