Secure Agent Placement When Pushdown Stops Working
Nordwind Insurance runs Informatica Intelligent Data Management Cloud (IDMC) Cloud Data Integration to load an on-premises Oracle general ledger (2.4 TB across 61 schemas) into a Databricks workspace in Azure West Europe. The corporate firewall allows only outbound TCP 443 from the database subnet to Informatica's cloud endpoints; no inbound rules are permitted. Legal has ruled that general-ledger records may not be processed outside the EU. One Secure Agent runs on an 8 vCPU / 32 GB VM beside the Oracle host. Since the team switched the Databricks connection to push down processing, the nightly load that once finished in 50 minutes now takes about four hours, and the agent VM sits at 93-97% CPU for most of that window. Two mappings also fail intermittently with connection resets around 02:00, when the Oracle RMAN backup runs. The team wants to add a second agent in the Frankfurt data centre and claims that will "fix performance and give us DR". The load's recovery time objective is four hours.
1. The team blames the missing second agent for the slowdown. Explain where the transformations are actually executing and what evidence would confirm it before any new agent is purchased.
Read the worked answer
Where the work runs. A Secure Agent is a polling runtime: it opens outbound HTTPS to the IDMC cloud service, receives the task, and executes it locally unless the mapping is genuinely pushed to Databricks as Spark. The execution setting lives on the connection and runtime environment, and pushdown is decided per transformation, not per mapping. Any transformation without a Spark equivalent, and any lookup to an on-premises table, falls back to the agent's Data Integration Service JVM.
Why that explains the symptoms. A four-hour runtime with the agent pinned at 93-97% CPU is the signature of agent-side execution: one JVM is doing the row-level work while the Databricks cluster sits mostly idle. Real pushdown shows the opposite - low agent CPU and populated Spark stages.
Evidence to collect, in order:
- The mapping run detail or execution plan for a single run, showing per-stage execution location.
- The connection's runtime environment - confirm it points at the Databricks cluster and not an Informatica-hosted runtime.
- The Databricks Spark UI for the same window: if no stages exist, nothing was pushed.
- Agent logs and a thread dump during the run to identify which transformation is hot.
- The 02:00 failures: correlate with the RMAN window. Resets during backup point to source contention or timeouts, not to pushdown.
A second agent would split load but would double down on an architecture executing 2.4 TB of work in a single JVM. Diagnose first.
2. Recommend an agent topology and network rules for adding the Frankfurt site without breaching the residency ruling. Address the DR claim directly.
Read the worked answer
Topology. Register both agents into one Secure Agent group bound to the same runtime environment. The group provides high availability and load balancing: if one node fails the other continues serving, and concurrent jobs are distributed across live members. Both nodes should sit close to an Oracle read path.
Network rules (all outbound, no inbound):
- TCP 443 to the Informatica cloud endpoints, including any regional endpoint the org is pinned to.
- TCP 443 to the Databricks workspace and to the cloud storage endpoint used for staging.
- TCP 1521 to Oracle, ideally through a read-only service or an Active Data Guard standby in each data centre.
- DNS and NTP to corporate resolvers - the agent is time-sensitive for token and certificate validation.
Residency. The ruling concerns where records are processed. Keep agents in the EU and the workspace in Azure West Europe, and both the agent-side path and the pushdown path stay in-region. Check the IDMC org's hosting region, any egress proxy that could route traffic outside the EU, and that temporary staging buckets are EU-region. The agent retains little at rest beyond logs and temp files, but disk encryption and log retention remain in scope.
The DR claim. A second agent gives process failover, not throughput, unless both nodes can reach Oracle. If only the primary data centre has a working Oracle route, Frankfurt is a cold standby. Prove it with a forced failover test before anyone signs off on a four-hour RTO.
3. Decide which parts of the nightly pipeline should be pushed down to Databricks and which must remain on the agent. Justify each choice.
Read the worked answer
Push down the set-based work. The 2.4 TB scan, joins against Databricks-resident dimensions, aggregations and the final write are what Spark does well and what a single agent JVM does badly. Pushdown moves compute next to the data and reduces what crosses the wire to filtered, projected rows.
Keep on the agent:
- Extraction from Oracle, especially where you depend on Oracle-specific SQL, partitioning hints or CDC views.
- Lookups to systems Databricks cannot reach. The firewall is one-way; the cluster cannot open a connection back to an on-premises table without a private link or reverse tunnel. Those lookups run agent-side, or the reference data is snapshotted into Delta first.
- Any masking or tokenisation that policy requires before data leaves the source zone.
- Transformations with no Spark equivalent. Identify them from the execution plan, not from a feature list.
Practical pattern. Split the mapping: an agent-side extraction that lands filtered, typed data into a staging Delta table, followed by a Databricks-side mapping for joins, aggregation and the final MERGE. That keeps 61 schemas from being dragged through one JVM.
The 02:00 resets. Treat them as a source-availability problem, not a performance problem. Correlate with RMAN, reschedule, raise source timeouts, or read from a standby. No amount of agent capacity fixes a source that is unavailable.