Intraday credit exposure: what to virtualize and what to persist
A retail bank runs a nightly ETL batch that loads a 4 TB warehouse from core banking (Oracle, 2.1 TB), a card processor (SQL Server) and a payments REST API. The batch takes six hours, so risk analysts work with yesterday's numbers. The CRO now wants intraday credit exposure for 250 analysts, data no older than 10 minutes, plus 40 automated limit checks per minute. Constraints: the Oracle DBA team allows at most 30 concurrent sessions and kills any statement running longer than 60 seconds; card data may not leave the EU region; compliance requires an auditable record of who saw which figure; there is no budget for a new ingestion pipeline this year, although the existing warehouse can store curated aggregates. Analysts use SQL through Tableau and Excel. The steering committee is split between extending the ETL window and virtualizing the exposure calculation.
1. Produce a target design that states, item by item, which data stays virtual, which is cached, and which is physically persisted. Justify each choice against the 10-minute freshness target, the 30-session and 60-second limits, and the EU residency rule.
Read the worked answer
Split by freshness requirement and by source cost tolerance, not by data volume.
Exposure facts stay virtual, but aggregated. Expose a base view that delegates the date filter and
GROUP BYto Oracle, so a request returns hundreds of rows rather than millions. A 60-second statement that scans and aggregates a partition is survivable; a statement that ships detail rows is not, and it is what the DBA team will kill.Card data virtual with an EU cache. The card source is read-only and EU-resident, so keep the view virtual and enable a full cache on a cache database that physically sits in the EU data centre with a 5-minute TTL. This meets the 10-minute freshness target and keeps regulated data in-region; a cache in Virginia would be a data transfer, not a cache.
The 40 automated limit checks use a partial cache or an explicit Scheduler load. They are the same 40 statements repeated every minute; serving them from cache removes two thirds of the session pressure from Oracle.
Curated daily aggregates are persisted into the existing warehouse. They are reused heavily, change once a day, and their freshness requirement is not intraday. A copy here is cheaper than recomputing them 250 times.
The governing principle: virtualization moves computation to the source, so anything with high concurrency and heavy scans must either be aggregated at the source or served from cache; anything slow-changing and heavily reused can be copied.
2. The Oracle DBA team says the new views already pushed their session count to 28 of 30. List the specific Denodo-level controls you would apply and explain the trade-off each one introduces.
Read the worked answer
Controls to apply, in order of effect:
- Cap the connection pool per data source (for example 8 sessions for core banking) instead of letting each query open its own connection. Trade-off: queries queue at Denodo rather than at Oracle; queue time shows up as latency, so you must monitor wait time, not just execution time.
- Rewrite base views so filters are delegated. If a view wraps the table in a function the wrapper cannot push, Denodo pulls rows and filters in memory, which converts one cheap query into a large transfer. Trade-off: adding computed columns can break delegation unless the source declares support for the function, so test the plan.
- Move repetitive traffic to cache using a Scheduler job that pre-loads the exposure and limit-check views every 5 minutes. Trade-off: numbers can be up to 5 minutes stale, which must be negotiated with the CRO.
- Set a query timeout and a maximum result size at the database level. Trade-off: a legitimate month-end query may fail; give the month-end service account its own database with a longer timeout.
- Use the Denodo execution plan as a review gate. Any view whose plan shows a full scan feeding an in-memory join needs a documented justification before it is promoted.
None of this is free: every control shifts cost to either latency or freshness. The right combination is the one where the two are quantified and accepted in writing.
3. A board member asks: if we virtualize, why do we still need the warehouse? Answer in terms of logical data management versus ETL, and name one thing virtualization makes harder.
Read the worked answer
Logical data management gives you a single governed access layer that resolves a business question - what is my exposure to counterparty X right now - at query time, by federating the systems of record. Nothing is copied, so there is no six-hour window and no second copy of regulated data to classify, certify and destroy. The warehouse stays because it does something different: it holds curated, historically consistent, heavily reused aggregates that would be wasteful to recompute on every request, and it is where restatements and slowly changing dimensions are managed.
The correct framing is not 'virtualization replaces ETL' but 'virtualization shortens the distance between a system of record and a decision, and ETL keeps the durable, agreed history'. In this design the warehouse still holds the daily aggregates and the audit-friendly snapshots; Denodo adds the intraday layer.
The honest cost: virtualization makes performance a shared responsibility with source owners. If the Oracle DBA team throttles sessions or a source cannot push down an aggregation, the query degrades and Denodo cannot fix it by itself. With ETL you pay the cost once at load time; with virtualization you pay it per query, so governance of source capacity becomes a standing obligation rather than a nightly job.