Oracle PeopleSoft and Snowflake integration

What moves between them

Vendor records, invoices, purchase orders, GL accounts, and requisitions flow one-way from Oracle PeopleSoft into Snowflake tables on a daily polling schedule. ml-connector reads these operational records from PeopleSoft's REST inquiry APIs using Basic Auth credentials, maps the results to Snowflake table schemas, and writes them via INSERT or UPSERT. GL account dimension tables sync first to ensure downstream invoices and purchase orders reference valid accounts. Data moves daily or on a custom schedule you define; the cadence is controlled entirely by ml-connector's polling interval, not by PeopleSoft pushes.

How ml-connector handles it

ml-connector stores the PeopleSoft hostname, port, node name, and Basic Auth credentials (OPRID and password) encrypted per customer. On each polling cycle, it constructs the REST service operation URLs using the stored hostname and authenticates with HTTP Basic Auth, handling PeopleSoft's requirement that each customer has a unique endpoint. Large result sets are filtered by date range to stay within performance bounds. ml-connector polls PeopleSoft on a fixed schedule because webhooks are not available and Integration Broker async messages require customer configuration. Snowflake's SQL API handles upserts via MERGE statements, matching on natural keys such as vendor ID or PO number. Case sensitivity in Snowflake requires quoted lowercase table and column names throughout the mapping. Because Snowflake is read-only for incoming data, ml-connector never writes back to PeopleSoft; the relationship is strictly one-way. JWT tokens expire within one hour with Key Pair Authentication, so ml-connector refreshes the token on each request. If Snowflake returns HTTP 429 rate limits, ml-connector backs off with exponential jitter and retries. Every record carries a full audit trail including the source timestamp and load time.

A real-world example

A mid-market financial services company runs Oracle PeopleSoft for general ledger, AP, PO, and vendor management across three regional offices. Analysts previously exported vendor and invoice records monthly from PeopleSoft via manual queries and loaded them into a separate data warehouse for reconciliation and compliance reporting. With Snowflake and PeopleSoft connected, vendor records and invoices sync automatically into Snowflake each night, eliminating the manual export step. Finance can run reconciliation queries and compliance checks against fresh data on day one of the month instead of waiting for manual uploads, and they can spot duplicate vendors or invoice anomalies weeks earlier than before.

What you can do

• •Load vendor records, invoices, purchase orders, and GL accounts from Oracle PeopleSoft into Snowflake tables on a daily schedule.
• •Authenticate PeopleSoft with Basic Auth credentials and handle self-hosted hostname, port, and node configuration unique to each customer.
• •Map PeopleSoft entities to Snowflake table schemas and upsert records by natural keys such as vendor ID, invoice number, or PO number.
• •Handle Snowflake case sensitivity and ensure JWT token refresh within the one-hour expiry window.
• •Retry on Snowflake rate limits (HTTP 429) with exponential backoff and maintain a full audit trail on every loaded record.

Questions

Since PeopleSoft has no webhooks, how does ml-connector stay current?

ml-connector polls PeopleSoft on a fixed schedule you define, typically daily or more frequently. Because PeopleSoft REST inquiry APIs support date-range filters, ml-connector queries only new or changed records since the last successful poll, reducing the data load. You control the polling interval to balance freshness and performance.

Can ml-connector write changes back to PeopleSoft from Snowflake?

No. ml-connector moves data one-way from PeopleSoft into Snowflake. Snowflake is a data warehouse, not an operational system, so reverse writes are not supported. If you need to update PeopleSoft records, those changes must be made in PeopleSoft directly or via a separate integration.

How does ml-connector handle PeopleSoft's self-hosted architecture and different authentication versions?

ml-connector stores each customer's unique PeopleSoft hostname, port, node name, and Basic Auth credentials (OPRID and password) encrypted and per-customer. It constructs service operation URLs using the stored hostname and authenticates with HTTP Basic Auth, which works across all PeopleTools versions. For PeopleTools 8.58+, OAuth2 is also supported by configuring a bearer token instead of Basic Auth.