Federated Query at Petabyte Scale: A Deployment Pattern for a Governed AI-Agent Data Layer

Enterprises with data spread across ERP, CRM, cloud warehouses and observability platforms face a recurring problem: As data volume grows into the tens of petabytes, traditional ETL-and-centralize approaches produce unacceptable query latency and duplicated infrastructure cost. This case study describes an automated deployment framework for a distributed SQL query engine (federated query architecture) that queries data at its source rather than moving it, built and operated as the lead infrastructure engineer at a health care technology company. It further describes how that same data layer was extended into a governed, agent-accessible interface for enterprise GenAI, using a semantic layer and tool-exposure protocol (MCP-style) to connect a conversational AI interface to real-time business data. This architecture fully preserves the role-based access control and audit requirements appropriate for regulated (health care-adjacent) data. The contribution is a reusable deployment and governance pattern, not a specific vendor product.

Problem

A business intelligence function at a health care technology company had grown to ingest data from multiple ERP/CRM systems, multiple cloud data warehouses and observability/logging platforms, converging on roughly 30 petabytes of federated data. The broader data and analytics organization supporting this initiative numbered 30–35 people; the automated deployment framework and federated query infrastructure specifically were owned and built by this author as the team’s DevOps engineer. This growth happened faster than the data architecture could absorb: Each new source system arrived with its own connector requirements, its own access model and its own idea of what ‘current’ data meant.

The default response to this kind of sprawl is to centralize — build a pipeline that copies everything into one warehouse, then query the copy. At this scale, that approach broke down in a few specific ways:

• Query turnaround had no consistent floor. Depending on which systems a business question touched, getting an answer could mean waiting on a batch ETL cycle, requesting a manual data pull from another team or stitching together exports by hand. There was no reliable answer to how long it would take. This unpredictability was often more damaging to the business than any single slow query.
• Storage and pipeline maintenance were duplicated. Centralizing meant maintaining a second (or third) copy of data that already had an owner and a home. Every schema change upstream meant a corresponding fix downstream, multiplied across every consuming pipeline.
• Governance got flattened. Once data left its source system and landed in a shared warehouse, the fine-grained access controls that existed at the source often didn’t carry over cleanly — meaning either over-provisioning access or building a second permissions system from scratch.

The alternative — federated query, where the engine queries data in-place at the source rather than copying it — solves the duplication and staleness problems but shifts the burden onto operating the query engine itself reliably. When this work began, distributed SQL engines built for this pattern (Starburst/Trino-based architectures) were still a relatively young category, and the available documentation focused on manual, one-time cluster setup. There was very little guidance on making that deployment repeatable, automated and safe to redeploy without losing operational history, which is the gap this work addressed directly.

Approach: Automated Federated Query Deployment

The core contribution was an integrated CI/CD deployment framework for a Kubernetes-hosted, distributed SQL query engine, built from the ground up rather than adapted from an existing internal playbook, as no such playbook existed for this engine at this scale.

• Automated Cluster Provisioning and Upgrade Pipelines: Cluster setup, configuration and version upgrades were previously handled manually — a process that took 3–5 days per deployment cycle when done by hand and carried real risk of configuration drift between environments (a setting changed in staging but forgotten in production, for instance). Moving this into a versioned CI/CD pipeline meant every deployment used the same tested configuration, and the same pipeline that deployed to a test environment could promote to production with a predictable, auditable set of steps. This took deployment time down to a few hours — roughly a 10x reduction — but the more durable benefit was consistency: Every engineer on the team could trigger a deployment with the same expected outcome, rather than the process depending on one person’s institutional knowledge of the manual steps.
• Log Persistence Across Deployments: Kubernetes pods are ephemeral by design. When a pod restarts or is redeployed, anything written only to its local filesystem is gone. For a query engine handling business-critical data, losing operational logs on every redeploy meant losing the ability to diagnose what happened before an incident. The available documentation for this engine, being relatively new to the ecosystem, didn’t have a clear answer for this. The solution was to route logs to persistent, external storage independent of pod life cycle, so redeploys (routine or emergency) never cost the team its operational history.
• Connector-Based Access to Source Systems: Rather than extracting data from ERP, CRM, cloud object storage and cloud data warehouse systems into a central store, the query engine connects to each source directly and executes queries against the live data. This eliminates the staleness problem inherent to batch ETL (data is only ever as old as the source system, not as old as the last successful pipeline run) and eliminates the duplicated storage cost of keeping a second copy of everything.
• Least-Privilege IAM as Code: Access control was provisioned through Terraform rather than configured manually, so that permissions granted to the federated query layer mirrored the access controls already in place at each source system, rather than introducing a new, separately managed permission model. This mattered specifically because a portion of the underlying data is subject to health care data protection requirements (HIPAA) — access needed to remain auditable and scoped, not flattened into a convenient but overly broad tier just because the data now sat behind a different query interface.
• Outcome: Business users can now run a single SQL query joining data across previously siloed systems and get a result in 15–30 minutes — down from a process that, before, had no consistent turnaround time at all, since it depended on ad hoc manual work across teams.

Extension: From Query Layer to AI-Agent Data Layer

Once the federated query layer was stable and fast, the natural next request from the business was: Could people just ask questions in plain language instead of writing SQL? That request is where most enterprise GenAI projects get into trouble. The easy version is to point an LLM at a document store or a database and let it retrieve whatever seems relevant, and this version is exactly what breaks down when some of that data is regulated.

The pattern built here keeps the LLM several layers removed from raw data at all times:

1. Data Products Layer: Rather than exposing raw tables, the federated query engine surfaces curated, named datasets — volume, feedback/case data, subscriptions, territory data and similar business-defined products — as stable, documented interfaces. This layer is the first checkpoint: Nothing downstream ever sees a raw table, only a data product someone has deliberately defined and named.
2. Semantic/Governance Layer: A knowledge graph and catalog layer sits on top of the data products, carrying context (what does this field mean, where did it come from), lineage (what upstream sources feed it) and access governance (who is allowed to see it). This is what lets an AI agent’s access be scoped and auditable, rather than ‘the model can see whatever the underlying database permissions allow’ — which is rarely a policy anyone actually designed on purpose.
3. Tool-Exposure Layer: A protocol-based server (following the emerging MCP pattern) exposes the governed data products as discrete, callable ‘tools’ that an LLM-based orchestrator can invoke — with defined inputs and outputs — rather than granting the LLM a live database connection. The distinction matters: A tool call can be logged, rate-limited and scoped to a specific data product. A database connection generally can’t be constrained the same way once the model is composing its own queries.
4. Orchestration Layer: A commercial conversational AI product consumes the tool-exposed data to answer natural-language business questions inside a collaboration platform used company-wide. SQL generated in response to a user’s question is constrained to the governed data products defined in step 1, and governing policies are applied before an answer reaches the end user — so the system’s freedom to interpret a question doesn’t extend to freedom to reach data it wasn’t given access to.
5. Compliance Boundary: Since a portion of the underlying data is subject to HIPAA, encryption, role-based access and audit logging are enforced at the data-product and semantic layers specifically — meaning the AI orchestration layer never receives ungoverned access to raw regulated data, regardless of how a user phrases their question.

The result — now in use by an estimated 300–500 business users across North America, LATAM, EMEA and APAC — is a system where a regional sales lead can ask a plain-language question in a chat interface and get an answer grounded in live, governed data without that convenience requiring anyone to loosen the access controls that existed before the AI layer was added.

This is, in effect, a retrieval-and-tool-use pattern for enterprise AI agents that preserves existing data governance, rather than a RAG pipeline built on an unstructured document dump.

Why This Matters Beyond one Company

The pattern addressed here — automate federated query deployment first, then expose that same governed layer to AI agents via a semantic/tool layer rather than piping raw data into an LLM — is broadly applicable to any enterprise trying to make GenAI safe to use against regulated or sensitive data. The specific components (query engine choice, LLM vendor, protocol implementation) are replaceable; the sequencing and governance boundary are the reusable insight:

• Don’t let the AI orchestration layer talk directly to source systems or raw warehouses.
• Put a data-products layer and a semantic/governance layer between the two.
• Automate the deployment of the query layer itself, or it becomes the bottleneck that makes ‘real-time AI answers’ impossible at petabyte scale.

Results

• Deployment Time: Manual Starburst cluster deployment/upgrade previously took 3–5 days. The automated CI/CD pipeline reduced this to a few hours — a 10x reduction.
• Query Turnaround: Cross-source business queries that previously had no consistent turnaround time (dependent on ad hoc extracts and manual joins across systems) now typically return in 15–30 minutes through the federated query layer.
• Data Volume Under Management: It is approximately 30 petabytes, federated across ERP, CRM, cloud data warehouse and observability sources.
• Adoption: The natural-language AI interface built on top of this data layer is in use by an estimated 300–500 business users across North America, LATAM, EMEA and APAC.
• Delivery: The initial version of the automated deployment framework was designed and released over 2–3 months, supporting a broader data and analytics organization of 30–35 people, with the deployment automation and query-layer work itself owned by this author.

Conclusion

Enterprises adopting GenAI over their own data face a governance problem before they face a modeling problem. This case study describes a deployment and architecture pattern — automated federated query infrastructure plus a semantic/tool-exposure layer — that let one infrastructure engineer scale a petabyte-class data platform into a governed, AI-agent-accessible system without compromising regulated-data controls.

Key Takeaways

• • At petabyte scale, federated query (query-in-place) avoids the staleness, duplicated storage and flattened governance that centralize-then-query approaches introduce.
  • Automating the deployment of the query engine itself, not just the data pipelines, is what makes federated query operationally viable cutting a 3–5-day manual process to a few hours.
  • Ephemeral infrastructure such as Kubernetes pods requires deliberate log persistence design, or operational history is lost on every redeploy.
  • Making enterprise data safely available to an LLM agent requires a data-products and semantic/governance layer between the model and raw sources, not direct database access.
• A tool-exposure layer (MCP-style) lets an AI agent’s access to governed data be logged, rate-limited and scoped in ways a raw database connection cannot be — preserving existing compliance controls such as HIPAA even as AI is added on top.

from DevOps.com https://ift.tt/94l0cEe