Integrating ERP, CRM, and Warehouse Data for Stock Availability
A retailer's ODC commerce app integrates with SAP ERP for orders, Salesforce CRM for customer cases, and an Oracle warehouse database for stock. Today a nightly CSV batch refreshes stock; availability is up to 24 hours stale, and promotions oversell by about 4%. The business needs stock freshness within 15 minutes for 2 million SKUs and peak order intake of 3,000 orders per hour. SAP exposes a REST API limited to 10 requests per second. Security forbids direct queries against the Oracle warehouse database. The CRM is the master for customer cases but not for stock. The ODC app currently calls SAP synchronously on the product page, causing timeouts during peaks. The integration team proposes a nightly delta plus a real-time call for every SKU view.
1. Choose integration patterns for stock availability and order creation. Compare synchronous API calls, event-driven updates, and batch, and justify your choice.
Read the worked answer
The current design fails because it couples a user-facing page to a rate-limited ERP API and uses batch for a freshness requirement it cannot meet. For stock availability, I would use an event-driven or incremental-delta pipeline from the warehouse/ERP into an ODC-owned availability projection, with a 15-minute freshness SLA and a nightly full reconciliation. Product pages read the projection or a cache, never SAP. Events are preferable where the source can publish changes; if it cannot, a scheduled delta every 5-10 minutes with watermarking is acceptable.
For order creation, use an asynchronous command: ODC validates locally, writes an outbox record, returns an order-accepted status, and a worker submits to SAP with idempotency keys. Synchronous calls should be reserved for low-volume, user-blocking checks, not 3,000 orders/hour. Batch remains for reconciliation and non-urgent CRM sync. This mix meets freshness, protects the 10 req/s limit, and avoids overselling by making the availability projection the read model while SAP remains the order master.
2. The SAP API allows 10 requests per second. Design a rate-limit and failure strategy for 2 million SKUs and 3,000 orders/hour.
Read the worked answer
Three thousand orders/hour is under one request per second on average, but bursts and retries can exceed the limit; 2 million SKUs cannot be polled per user or per order. I would separate lanes: order submission gets a reserved token budget (for example, 6 req/s), stock delta sync gets 3 req/s, and interactive diagnostics get 1 req/s. Use a token bucket at the integration layer, not per app instance, so autoscaling does not multiply the limit.
For stock, use paginated deltas with a watermark, not full scans; cache the projection and serve reads locally. For orders, queue with idempotency keys, exponential backoff with jitter, circuit breaker, and a dead-letter queue with replay. Cap concurrency and use bulk endpoints if SAP offers them. Monitor queue depth, 429 rate, and oldest pending age; alert when the 15-minute freshness SLA is at risk. On SAP outage, keep accepting orders into the outbox with a clear pending status, but stop promising immediate confirmation. This protects the ERP and preserves user experience.
3. Define the canonical data model and ownership rules you would enforce across SAP, CRM, Oracle, and ODC.
Read the worked answer
Start with ownership: SAP owns order commercial state and pricing; the warehouse owns physical stock; CRM owns customer cases and interactions; ODC owns the availability projection, cart, and order-acceptance state until SAP confirms. The canonical model should be small and event-oriented: Product/SKU, StockLevel, Order, OrderLine, CustomerReference, and CaseReference. Use stable source IDs and a cross-reference table; do not let ODC generate a second product identity.
Define versioned contracts for StockChanged, OrderSubmitted, and OrderConfirmed, with schema validation and backward-compatible evolution. ODC must not become master for stock just because it serves the read model; label projections as derived and rebuildable. Customer data should be referenced by CRM ID, with only the minimum attributes copied for checkout. Enforce rules in integration tests: no direct Oracle access, no synchronous SAP call from a screen, and every order command idempotent. Document freshness and reconciliation SLAs per entity so business users know what 'available' means.