Multi-region topology with an EU data boundary
A global manufacturer with 8,000 employees runs the Denodo Platform for 75 data sources and 320 views. Three offices need access: Frankfurt (primary, 4,000 users), Singapore (1,500) and Virginia (2,500). Month-end close peaks at 45 queries per second; normal load is 4 queries per second. Requirements: Kerberos/AD single sign-on from managed Windows desktops; 99.9% availability between 07:00 and 20:00 local time; recovery of metadata within 30 minutes after a server loss; every query attributable to a named user in audits; the EU works council agreement forbids EU personal data being processed outside the EU; there are three administrators and no dedicated database administrator. Today there is one Virtual DataPort server in Frankfurt, the cache is stored in the internal database, there are no Scheduler jobs, and Singapore users report 900 ms response times on simple lookups that are fast in Frankfurt. The Singapore complaints began after the team replaced regional extracts with a single global layer.
1. Design the regional topology. State which components are deployed per region, which are centralised, and where the cache and audit data live.
Read the worked answer
Per region: at least one Virtual DataPort server. Frankfurt and Virginia carry the load that justifies two nodes behind a load balancer; Singapore can run a single node initially, accepting that it is a single point of failure for 1,500 users. Each region gets its own cache database on an external RDBMS rather than the internal database, because the internal database competes for the same server resources and is not shared across nodes of a cluster - a two-node Frankfurt cluster with a local internal cache would produce two disjoint caches and inconsistent results.
Centralised: Solution Manager, used to promote revisions (export from the development server, import into each production server) and to keep the 320 views identical in definition across regions. Design Studio is used against the development server only, never against production.
Per region: a Scheduler instance, because cache pre-load jobs must run where the cache physically is. The Frankfurt and Virginia Schedulers load EU views into EU caches only, which is how the works council constraint is actually enforced - not by a policy document.
Per region: Diagnostic and Monitoring, writing audit records to a local database. Central aggregation of audit is fine; central storage of query text containing EU personal data is not.
Metadata recovery in 30 minutes comes from having exportable revisions in Solution Manager plus a scheduled export, not from hoping the server disk survives.
2. Diagnose the 900 ms Singapore lookups and give three remedies with their trade-offs.
Read the worked answer
The symptom pattern - simple lookups slow from Singapore, fast from Frankfurt - points to work being executed in Frankfurt. Two likely causes: the small dimension views are being fetched in full over the WAN on every request because the join cannot be delegated to a single source, or the request is a round trip per source and Singapore pays the latency several times over. The migration to one global layer removed the local extract that previously made these lookups local.
Remedies:
Full cache the small, slow-changing dimension views on the Singapore server. A 60,000-row country and product hierarchy cache costs a few MB and removes the WAN hop for the majority of lookups. Trade-off: staleness equal to the TTL, and a Scheduler dependency.
Push the join down. If both tables live in the same source, restructure the derived view so Denodo delegates the join instead of pulling both sides; if they do not, consider creating a consolidated source-side object. Trade-off: an object in the source is now part of your governance scope and must be maintained.
Deploy a small Denodo Embedded MPP instance in the Singapore region for the handful of views that genuinely need cross-source joins at volume. Trade-off: data is materialised into MPP memory in-region, so it must not include EU personal data.
Measure the execution plan before and after each change - a fix that halves rows transferred but doubles round trips can make latency worse.
3. The audit team found 40,000 queries attributed to svc_bi rather than to individuals. Explain how this happens and what you would change, including the risk of each alternative.
Read the worked answer
Attribution collapses when the BI platform authenticates to Denodo with a shared technical account and pools connections. Denodo sees the service account as the session user for every request multiplexed over that pool, so thousands of queries belong to one name. Row-level filters keyed on the user also silently stop working, because every user now resolves to the same identity - a security defect disguised as an audit defect.
Options:
- Per-user authentication from the BI tool (Kerberos constrained delegation, or OAuth/OIDC with the end user's token passed through to Denodo). This is the only option that restores both attribution and per-user row filters. Risk: delegation must be configured correctly, and a misconfiguration is a privilege escalation path.
- Denodo users mapped from the IdP, with the BI tool prompting per user. Simple, but only viable for a small population; 8,000 users means 8,000 sessions to manage.
- Keep the service account but send the end-user identity in a signed assertion (JWT or trusted header) that Denodo validates. Works well, but the assertion must be signed and validated, otherwise any client can claim to be anyone.
- Accept the service account and log identity upstream only. This satisfies nobody: the Denodo audit trail, which is the record of what data left the platform, remains anonymous.
Whatever is chosen, verify with a session-variable test view that returns the resolved user, and confirm the Diagnostic and Monitoring audit shows distinct users for distinct people.