Shipment tracking view: pagination, key mismatch and API quotas
A logistics company builds a shipment-tracking view that joins carrier events to a shipment master. Events come from a carrier REST API returning JSON, 500 records per page, cursor-based pagination, OAuth 2.0 client credentials, and a hard limit of 10 requests per minute per client. The shipment master holds 5 million rows in Oracle, keyed by shipment_id stored in uppercase with trailing spaces; the API returns lowercase ids with no padding. Analysts report that the combined view returns exactly 500 rows regardless of the filter; a one-month extract takes 20 minutes; the Oracle host shows one long-running statement whose time is dominated by SQL*Net transfer with unremarkable CPU per second; and the API client secret expires every 90 days, after which every dashboard fails until someone edits the view definition.
1. Explain the exact 500-row result and the Oracle symptom, and say which part of the base view definition is wrong.
Read the worked answer
Exactly 500 rows is the signature of a base view materialising a single API response. The REST wrapper issues one HTTP call and extracts the tuple array from the JSON body; pagination is a separate configuration (page size, the cursor or page parameter, and the path in the response where the next cursor lives). If that is left unset, Denodo faithfully returns the first page - 500 records - and stops. It is not truncation, it is a complete result set of one page.
The Oracle symptom confirms where the join executes. Time dominated by SQL*Net transfer with low CPU per second means Oracle is not computing anything expensive; it is shipping rows. The join runs in memory on the Denodo server because the keys are not comparable - ABC123 versus abc123 - and the two sides are on different sources, so no delegation is possible. Denodo cannot push a predicate to Oracle that requires a value only the API can supply, and it cannot push the lowercase conversion to Oracle unless the wrapper declares support for that function.
The underlying definition error is that a base view was treated as a finished data product. A base view should expose the source as faithfully as possible; normalisation, key alignment and filtering belong in derived views where the optimiser can still reason about them.
2. Propose a redesign that keeps the join from transferring 5 million rows, and state what you would verify in the execution plan.
Read the worked answer
Normalise on both sides before joining, and shrink the Oracle side with a pushed filter.
On the API side, create a derived view that trims, upper-cases and casts the id. On the Oracle side, do not wrap the column in an expression inside the WHERE clause - that makes the index unusable and defeats pushdown. Instead, add a persisted or function-based normalised key column in Oracle, or use a source-side view exposing UPPER(TRIM(shipment_id)) as a column, and join on that column so the predicate is a plain equality Oracle can push.
Then force the shape of the join. Preferred: process events in batches (for example the ids for one day) and query the shipment master with an IN list so Oracle returns 5,000 rows instead of 5 million. Alternative: load the API events into a landing table with a Scheduler job and join inside Oracle, which makes the join a single-source operation.
In the execution plan, verify three things: that the Oracle node carries a filter rather than a full scan feeding an in-memory join; that the estimated rows leaving the Oracle node is in the thousands, not millions; and that the API node shows one call per batch, not one call per row - a per-row call will hit the 10-request-per-minute limit almost immediately.
3. Design for the 10-requests-per-minute quota and the 90-day secret rotation, assuming 300 analysts and hourly dashboards.
Read the worked answer
The quota is the binding constraint, not the API's response time: at 10 requests per minute you have 600 calls per hour for the entire company. 300 analysts refreshing hourly cannot each cause a call.
Design: a Scheduler job fetches events on a fixed cadence - say every 10 minutes, using 6 of the 10 permitted calls for the current and previous day windows - into a cache or a landing table. Analysts then query the cached view. Full cache is wrong here because each analyst filter would be a distinct cached entry, multiplying calls; an explicit load (Scheduler writes into cache or into a table) makes the call count independent of user count. Set the TTL above the load interval so a slow loader does not cause users to trigger direct API calls that consume the remaining quota.
Secret rotation: the credential should not live in the view definition. Store the client secret in the credential store and reference it from the data source, so rotation is a single change on the data source rather than 40 edits to views that embed it. Automate rotation with a reminder 14 days before expiry and a post-rotation smoke test, and add a monitoring alert on authentication failures so the failure is detected by the platform team rather than by 300 analysts at 08:00.
Finally, check whether the carrier offers a cursor that supports incremental reads; re-reading the full window on every cycle wastes quota that batching cannot recover.