Beyond the Query: 5 Scenarios Laying the Foundation for the Agentic Era

Accessing enterprise data is shifting from static reports to dynamic use by autonomous systems. To keep up, organizations must route fragmented data from SaaS, IoT, and legacy sources into secure, scalable endpoints.

However, moving to AI-driven exposure requires more than just connecting an LLM to a database, it requires a fundamental architectural shift to manage security, costs, and semantic accuracy.

What we’ll cover

This article explores the technical evolution of data exposure through five architectural patterns: from manual SQL development to autonomous workflows standardized by the Model Context Protocol (MCP).

While the examples use BigQuery and mocked CRM data, the patterns apply to most enterprise data assets transitioning into an agentic workflow.

The 5 Scenarios of Data Evolution

The transition from static reports to agentic insights is defined by two factors: Trust and complexity.

Trust dictates autonomy: Low-trust environments (like external client-facing apps) require deterministic, hard-coded logic to prevent errors. High-trust environments (like internal tools for power users) allow for probabilistic LLM reasoning, where there is more tolerance for non-deterministic outputs.

Complexity defines utility: Simple lookups need fast, cached responses. In contrast, complex, cross-functional problems require an agent to orchestrate multiple tools and data sources.

To navigate this shift, we will examine five technical scenarios, starting with the baseline of the static API.

Scenario 1: The Static API Contract

Focus: Maximum stability and deterministic execution

Scenario 1 represents the traditional model of data exposure. A developer acts as the intermediary, translating specific business requirements—such as “Show me top-selling products by category”—into optimized, hard-coded SQL queries.

Isolation and Predictability

This approach provides the highest level of security and performance:

• Low logic risk: Because the SQL is pre-written and vetted, there is no risk of a user (or an agent) crafting a query that accesses unauthorized data.

• Secure by design: Using parameterized queries instead of string concatenation provides a hard barrier against SQL injection.

• Reliability: The output is deterministic. If the development lifecycle is robust, the user is guaranteed to receive exactly what they requested, with predictable execution costs and performance.

Implementation example

This snippet demonstrates the baseline for data exposure: a direct, static API contract. It offers maximum predictability by using parameterized queries to prevent SQL injection and ensure consistent performance.

A note on the code examples: To prioritize architectural clarity, these examples are provided as conceptual blueprints rather than production-ready code. They are designed for pedagogical purposes and intentionally omit “industrial” requirements such as persistent session state, IAM/Auth protocols, and comprehensive exception handling. Use these only as a logic guide before implementing your own hardened and production-ready solution.

Analysis

When to use Scenario 1?

This approach is the benchmark for external-facing applications, customer portals, and high-traffic production dashboards. It is the best choice when your requirements include:

• Strict auditability: You need a version-controlled (Git-based) history of every query executed against your data warehouse.

• Performance at scale: You require sub-second latency, leveraging BigQuery’s result caching for high-concurrency workloads.

• Deterministic logic: You must guarantee that specific inputs always produce the exact same output, with no room for AI-driven variability.

• External multi-tenancy: You are exposing data to third parties and need absolute assurance against data cross-contamination.

Scenario 2: Custom Agent with SQL Generation

Focus: User flexibility and managed autonomy.

To resolve the development bottleneck of manual SQL authoring, Scenario 2 introduces an LLM agent (via the Agent Platform SDK) to act as a dynamic translator. In this model, the developer stops writing individual queries and starts focusing on metadata documentation.

From Query Writing to Metadata Curation

Using the Agent Platform SDK (for Python, for example), developers implement a reasoning engine that maps natural language to schema metadata. Rather than “guessing” the SQL, the agent follows a structured reasoning loop:

1. Analyze: It parses the natural language intent (e.g., “Which region had the highest growth?”).

2. Retrieve: It looks up the relevant schema metadata provided in the system context.

3. Construct: It generates a syntactically correct, BigQuery-compatible statement.

For the LLM to generate accurate queries, it must “see” the data structures. You provide this through system instructions that include table names, column types, and—crucially—semantic descriptions (e.g., “created_at: The timestamp when the user first registered”). By curating this metadata space, you define the boundaries of what the agent can explore and execute.

Access control relies entirely on underlying database permissions (like RLS). Because the agent passes generated SQL dynamically, data boundaries must be enforced at the database level.

Implementation example

This marks the first step into agentic workflows, where an LLM acts as a translator between natural language and structured schema.

Analysis

When to use Scenario 2?

Scenario 2 is best suited for internal data discovery and analyst-led exploration. It bridges the gap between raw data and business users when you require:

• High-variability querying: When the range of potential business questions is too broad (the “infinite question space”) to be efficiently covered by pre-built, static APIs.

• Rapid prototyping: When analysts need to quickly explore datasets and validate hypotheses before committing to the development of formal, production-grade dashboards.

• Semantic interpretation: When you need an agent to resolve natural language ambiguities—such as mapping “last quarter” or “active users”—into specific, technical filter criteria automatically.

Scenario 3: Conversational Analytics

Focus: Managed reasoning and verified logic.

Scenario 3 shifts the responsibility from a self-managed custom agent to a specialized, platform-native reasoning engine. By leveraging the Conversational Analytics API (currently in Pre-GA), you can deploy Data Agents – intelligent, governed layers that use enterprise-specific metadata and verified SQL to keep the LLM within strictly defined guardrails. This API translates natural language into precise queries across BigQuery, Looker, and Data Studio, while extending support to Google Cloud’s primary database solutions. We’ll consider BigQuery as our primary example for exploring these conversational insights.

The Power of Verified Queries

Unlike generic LLM prompts that guess the SQL structure, these agents are grounded in your organization’s source of truth:

• Verified queries: You provide a library of verified queries (vetted, high-quality SQL examples) that the agent uses as a reference for complex joins and business logic. This ensures the agent follows your established coding patterns.

• Managed context: The platform handles the retrieval of schema information and documentation, reducing the prompt bloat that often leads to hallucinations in custom-built agents.

• Aligned outputs: By grounding the model in existing production SQL, the system ensures that AI-generated insights remain consistent with your official reporting metrics.

This solution inherits existing BigQuery IAM permissions and provides a view of the reasoning and SQL behind every answer.

Can all of this be done with enough work on a fully customized agent? Yes. Is the custom approach practical, and time/cost efficient? Maybe not.

Implementation example

This approach leverages a specialized reasoning engine to handle intent discovery and data grounding. The developer no longer manages the translation logic: they simply call the managed agent.

Analysis

When to use Scenario 3?

Scenario 3 is the ideal path for BigQuery-centric analysis where accuracy is non-negotiable. Choose this when you require:

• Governed trust: Business logic (e.g., “Revenue”) must follow pre-vetted verified queries every time.

• Native intelligence: Users need to perform complex tasks like forecasting or anomaly detection via BigQuery AI using natural language.

• Auditability: Stakeholders require a transparent reasoning path to see exactly how the AI arrived at its numbers.

While Scenario 2 requires building a custom reasoning engine from scratch, Scenario 3 provides a platform-native experience that prioritizes verified logic over raw LLM generation.

The limitation: This data companion is ultimately confined to the BigQuery or Google Cloud ecosystem. To scale an agentic workforce across heterogeneous platforms and tools, we must look toward vendor-agnostic standards like the Model Context Protocol (MCP).

Scenario 4: Managed MCP Tools

Focus: Standardized connectivity and decoupled architecture.

Scenario 4 introduces the Model Context Protocol (MCP)—an open-source standard designed to normalize how AI applications interact with data and tools. While previous scenarios rely on custom SDKs or platform-specific APIs, MCP provides a universal interface that separates the reasoning layer from the tool execution layer.

Standardized Abstraction

MCP enables tool discovery by exposing a manifest of capabilities that any compliant agent can ingest. This allows for a modular system where the data logic is “externalized” from the agent itself.

• The MCP client: The reasoning engine (the LLM) that identifies the user’s intent. Because it uses a standardized protocol, the client can connect to any MCP server and instantly discover what it can do without needing new integration code.

• The MCP server: The domain-specific service that exposes data and logic. The managed BigQuery MCP server doesn’t just pass queries: it encapsulates the logic required to navigate Google Cloud’s infrastructure safely. It exposes tools such as:

• list_dataset_ids: Context-aware discovery of the data environment.

• get_dataset_info: Metadata retrieval for semantic grounding.

• execute_sql: Controlled execution of data retrieval.

(see https://docs.cloud.google.com/bigquery/docs/reference/mcp for the updated toolset).

Access control is managed via standard IAM service accounts and lacks programmatic logic-checks.

This decoupling future-proofs your AI stack. You can swap your LLM provider or upgrade your agent’s reasoning model without rewriting the data access logic, because the interface between them remains consistent and governed.

Implementation example

In an MCP-based architecture, connecting an AI agent to a data source is reduced to a simple configuration handshake. Instead of writing custom integration logic, you provide an MCP-compliant client (such as the Gemini CLI or a modern IDE) with a manifest defining the server’s location and security requirements.

The following manifest allows the client to connect to Google’s managed BigQuery MCP server, enabling it to dynamically discover and execute data tools without a single line of custom code:

Analysis

When to use Scenario 4?

Scenario 4 is the architectural choice for multi-agent environments that require standardized data connectivity with minimal maintenance overhead. It is the ideal path when you require:

• Managed infrastructure: You want to offload the security, execution, and maintenance of your toolset by consuming a managed BigQuery MCP server rather than building and patching custom data-retrieval code.

• LLM portability: You need an open-standard interface, allowing you to use the same tools across different LLMs or agent frameworks without rewriting proprietary function calls.

• Autonomous discovery: Your agents must navigate and inspect complex datasets dynamically. MCP’s standardized endpoints allow agents to crawl metadata and schema information autonomously to determine the best path for a query.

Scenario 5: Custom Hosted MCP Servers

Focus: Architectural extensibility and custom tool definition.

Scenario 5 takes the standardized connectivity of Scenario 4 and adds complete control by replacing the managed service with a custom-built MCP server. Typically hosted on scalable infrastructure like Cloud Run, you can rely on open source solutions such as MCP toolbox. This approach removes the guardrails of managed offerings, granting engineering teams full freedom to define specialized tools, integrate disparate third-party APIs, and implement proprietary execution logic within the protocol.

Architectural Advantages of Custom MCP

Shifting to a custom-hosted MCP server moves operational complexity from the LLM prompt to the server-side logic, unlocking three critical capabilities:

• Deterministic tool tailoring: Instead of forcing an agent to navigate raw, sprawling schemas, developers define high-level functions with specific data shapes. This replaces probabilistic SQL generation with deterministic execution, virtually eliminating schema-based hallucinations.

• Unified source orchestration: A custom MCP server acts as a consolidated gateway. Within a single tool execution, the server can orchestrate calls across BigQuery, external SaaS APIs, and legacy on-premises systems. The agent receives a pre-processed, unified response, abstracting away the multi-source complexity.

• Programmable governance: This scenario enables code-level security difficult to implement in managed environments. You can implement granular controls directly within the protocol layer, such as:

• Dynamic PII masking: Automatically redacting sensitive data before it reaches the LLM.

• Custom authentication: Injecting enterprise-specific middleware.

• Contextual rate limiting: Throttling tool usage based on the end-user’s identity or cost center.

Implementation example

In this scenario, when using MCP toolbox, you use a declarative tools.yaml file to define the interface of your custom MCP server. This file acts as the absolute boundary for your agent—it defines the BigQuery connection, enables safe discovery for schema inspection, and wraps complex, multi-table joins into a single, parameterized tool.

Analysis

When to use Scenario 5?

This architecture is the power user choice, essential for highly regulated environments and hybrid infrastructures where managed services fall short. Implement this approach when your design requires:

• Secure hybrid orchestration: You must bridge BigQuery with private on-premises systems or restricted APIs, returning a pre-processed, consolidated payload that the agent can use immediately without navigating the raw network gap.

• Hardened business logic: You need to move complex, non-negotiable calculations off the LLM and into a controlled code environment, exposing only high-level “expert” tools to guarantee absolute accuracy.

• Centralized enterprise tooling: You want to maintain a single, governed source of truth for your proprietary tools that can be served uniformly across different LLM providers or internal frameworks without vendor lock-in.

Conclusion: The Foundation of the Agentic Era

The journey from Scenario 1 to Scenario 5 traces a clear technical evolution: we are moving away from rigid, hard-coded data silos and toward a world of autonomous discovery and standardized connectivity. By adopting frameworks like the Model Context Protocol (MCP), organizations can decouple their data logic from their AI models, ensuring that as LLMs evolve, their access to the enterprise “brain” remains seamless, scalable, and vendor-agnostic.

However, increased autonomy does not mean decreased oversight. While we haven’t touched on these points in depth in this article, we must adhere to a fundamental truth: data access must be governed and controlled using governance and security tools. Regardless of the access scenario—more or less agentic depending on the use case—security, credentials, quality management, and standardized governance are absolutely essential.

On a more lighthearted note, it’s worth remembering that the golden rule of computing still applies: “Garbage In, Garbage Out”. You can build the most sophisticated, autonomous agentic layer in the world, but if you feed it messy, uncurated data, you’ll simply get “garbage” answers at a much faster and more confident pace. Sophisticated AI doesn’t fix bad data: it just makes it more visible. Maintaining high data quality is not just a legacy requirement—it is the fuel that makes the agentic engine actually work.