Building a Multicloud Agentic Lakehouse Reference Architecture

Artificial intelligence has evolved past static retrieval-augmented generation chatbots. Organizations are now deploying autonomous AI Agents that can analyze requests, design plans, query data lakes, and execute downstream operations.

However, when developers connect AI agents to traditional enterprise data platforms, they encounter critical barriers. Traditional data warehouses are built for human business intelligence analysts who write predictable SQL queries. AI agents generate queries dynamically, require sub-second response times for iterative reasoning loops, and must operate under strict security boundaries to prevent data exfiltration.

To solve these challenges, organizations are adopting the Agentic Lakehouse architecture. This reference architecture describes an open, multicloud data lakehouse specifically optimized for autonomous AI agents. The stack is anchored by Apache Iceberg as the open storage standard, Apache Polaris as the cross-cloud REST catalog, and Dremio as the semantic and query acceleration layer.

1. The AI Agent Data Bottleneck

To understand why a dedicated architecture is necessary, we must examine the specific issues that occur when an AI agent interacts with standard data infrastructure.

┌────────────────────────────────────────────────────────┐
│                     AGENT WORKFLOW                     │
│               [ User: "Analyze Sales" ]                │
│                           │                            │
│                           ▼                            │
│                  [ Reason & Plan ]                     │
│                           │                            │
│              ┌────────────┴────────────┐               │
│              ▼                         ▼               │
│       [ Generate SQL ]          [ Execute Tool ]       │
│              │                         │               │
│              ▼                         ▼               │
│       [ Query Engine ]          [ Action Loop ]        │
└──────────────┬─────────────────────────┬───────────────┘
               │ (Wait for DB)           │ (Write back)
               ▼                         ▼
┌────────────────────────────────────────────────────────┐
│                TRADITIONAL DATA PLATFORM               │
│   - Low-context tables (tbl_sales_v2)                  │
│   - Slow JDBC/ODBC serialization                       │
│   - Coarse access controls (All or Nothing)            │
└────────────────────────────────────────────────────────┘

Non-Deterministic Query Generation

When a human analyst writes a SQL query, they inspect the database schema, identify the foreign keys, and write a structured join. An AI agent uses a Large Language Model (LLM) to generate SQL queries on the fly based on text descriptions of the database.

If the database schema is disorganized, uses cryptic column names (such as c_adr_id_fk), or lacks rich metadata, the agent will generate incorrect joins or hallucinated column names, causing the query to fail. Agents require a structured semantic layer that translates raw database layouts into clean, documented business concepts.

Latency Accumulation in Reason-Action Loops

Autonomous agents use cognitive architectures like the ReAct (Reasoning and Action) pattern. Instead of running a single query, the agent may execute a multi-step loop:

1. Look up user information.
2. Query purchase history.
3. Compare purchases with regional trends.
4. Calculate fraud risk scores.

If each query takes five to ten seconds to complete due to query planning or serialization delays, the end-to-end agent loop can take over thirty seconds, creating an unacceptable user experience. Agents require sub-second query response times to complete multi-step reasoning tasks.

Fine-Grained Security and Data Leakage

Traditional database security relies on granting broad permissions to service accounts. If you grant an AI agent access to a database via a general service account, the agent can potentially query any table, read sensitive columns, or scan the entire dataset.

If the agent’s prompt is manipulated (prompt injection), the agent could be instructed to dump private customer data or overwrite table configurations. Agents require strict, granular access control down to the row and column level, enforced at the query engine level, to guarantee data security.

The Multicloud Reality

Modern enterprises do not keep all their data or AI tools in a single cloud. You may run machine learning pipelines on Google Cloud Platform (GCP) Vertex AI, query transaction records stored on Amazon Web Services (AWS) S3, and deploy customer-facing agents on Microsoft Azure.

Moving hundreds of gigabytes of data between clouds to support local AI models is cost-prohibitive due to egress fees. The data must remain in place and be queried where it lies, using a federated, multicloud metadata catalog.

2. The Storage and Metadata Foundation: Apache Iceberg and Apache Polaris

The physical storage and catalog layers of the reference architecture must support multi-engine access and cross-cloud query execution without creating data silos.

┌────────────────────────────────────────────────────────┐
│                     COMPUTE ENGINES                    │
│    ┌──────────────┐ ┌───────────────┐ ┌────────────┐   │
│    │ Dremio (SQL) │ │ Apache Spark  │ │ Python/ML  │   │
│    └──────┬───────┘ └───────┬───────┘ └─────┬──────┘   │
└───────────┼─────────────────┼───────────────┼──────────┘
            │                 │               │
┌───────────▼─────────────────▼───────────────▼──────────┐
│                  REST CATALOG ROUTER                   │
│                   [ Apache Polaris ]                   │
│   - Validates engine identity and OAuth2 tokens        │
│   - Vends short-lived S3 access credentials            │
└─────────────────────────────┬──────────────────────────┘
                              │
┌─────────────────────────────▼──────────────────────────┐
│                     STORAGE LAYER                      │
│             [Cloud Object Storage (S3/ADLS)]           │
│             [Apache Iceberg Table Metadata]            │
└────────────────────────────────────────────────────────┘

Unified Open Table Format: Apache Iceberg

To prevent vendor lock-in and support diverse engines, the data lakehouse stores all files as Apache Iceberg tables. Iceberg provides:

• ACID Transactions: Ensures that data written by real-time streaming pipelines (e.g., Flink) is committed atomically, making it instantly visible to analytical engines (e.g., Dremio) without read-write conflicts.
• Hidden Partitioning: Speeds up query planning by automatically translating natural queries (like timestamp ranges) into optimized partition filters, ensuring that agent-generated queries do not trigger full table scans.
• Schema and Partition Evolution: Allows the database schema and partitioning strategies to evolve over time without requiring table rewrites.

Cross-Cloud Routing: Apache Polaris

To coordinate table state across multiple clouds, we deploy Apache Polaris as our open REST Catalog. Polaris operates as a lightweight, stateless catalog manager:

• Single Catalog Registry: Polaris manages pointers for all Iceberg tables across AWS S3, Azure Data Lake Storage, and GCP Cloud Storage. It allows query engines in any cloud to resolve table paths using a single API.
• Credential Vending for Security: When a query engine requests the location of an Iceberg table, it authenticates with Polaris using OAuth2 client credentials. Polaris validates the request and communicates with the cloud provider (e.g., AWS STS) to generate short-lived, read-only security credentials for the specific table path. The query engine never has permanent read or write access to the raw S3 bucket, preventing credentials from being leaked or abused.
• Ecosystem Interoperability: Polaris supports the open-source Iceberg REST catalog specification. This ensures that Dremio, Snowflake, Spark, Flink, and Python engines can query the same metadata registry, preventing catalog fragmentation.

Credential Vending Protocols and AWS IAM Integration

To understand how Polaris secures object storage, we can trace the credential vending handshake. When Dremio attempts to plan a query over a table, it does not use a global AWS access key. Instead, the transaction follows a strict sequence:

1. Token Exchange: The engine sends an OAuth2 token request to Polaris using client credentials configured for that engine.
2. Access Control Resolution: Polaris verifies the client credentials and checks if the mapped principal has the catalog_read privilege on the requested namespace.
3. AssumeRole Handshake: Polaris contacts the AWS Security Token Service (STS) endpoint using an IAM AssumeRole API call. Polaris passes a session policy that restricts access exclusively to the table’s S3 location (for example, s3://lakehouse-warehouse/db/user_events/).
4. Credential Injection: AWS STS returns a set of temporary, scoped security credentials (access key, secret key, and session token) that expire after a short duration (typically one hour).
5. Data Scan: Polaris sends these credentials back to Dremio along with the table’s metadata location. Dremio uses the temporary keys to stream the Parquet blocks directly from S3.

This process ensures that Dremio is never exposed to keys that could read other directories in the bucket.

3. The Semantic Layer: Dremio

The semantic layer bridges the gap between raw database storage and the AI agent’s reasoning engine. Dremio serves as the unified semantic and query acceleration layer in this reference architecture.

┌────────────────────────────────────────────────────────┐
│                     CLIENT SYSTEM                      │
│      ┌──────────────────────────────────────────┐      │
│      │  AI Agent (Python Framework/LlamaIndex)  │      │
│      └────────────────────┬─────────────────────┘      │
└───────────────────────────┼────────────────────────────┘
                            │ (Arrow Flight SQL / TCP Stream)
┌───────────────────────────▼────────────────────────────┐
│                    SEMANTIC LAYER                      │
│                  [ Dremio Platform ]                   │
│   - Semantic Mapping (Virtual Datasets)                │
│   - Dynamic SQL Reflections (Acceleration)             │
│   - Row/Column Masking Policies                        │
└────────────────────────────────────────────────────────┘

Business Context Mapping (Virtual Datasets)

Dremio allows data architects to define Virtual Datasets. These are clean logical abstractions of raw tables that do not duplicate the underlying data. Dremio’s semantic features include:

• Human-Readable Schemas: Cryptic table layouts are mapped to intuitive business hierarchies (e.g., Enterprise_Data.Customer_Success.Active_Subscribers).
• Rich Documentation Caching: Descriptions, tags, and data types are attached directly to columns in the semantic layer. When the AI agent scans the schema, it reads these descriptions as structured prompt context, ensuring it understands the meaning of each column.
• Pre-Joined Relationships: Complex joins are defined as virtual views. The agent can query a single dataset without needing to reconstruct multi-table join syntax, reducing query errors.

Arrow Flight SQL for Sub-Second Latency

Traditional database connections use Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC) protocols. These protocols serialize data into row-by-row representations, creating a network transfer bottleneck when moving large datasets.

Dremio supports Apache Arrow Flight SQL, an open-source protocol built for high-speed columnar data transfer:

• Vectorized Data Streaming: Flight SQL streams data directly from Dremio’s memory to the AI agent’s Python environment in columnar Arrow buffers. This eliminates the serialization and deserialization steps required by JDBC.
• Parallel TCP Streams: Flight SQL can distribute the data transfer across multiple network streams, allowing large result sets to be loaded into Python in milliseconds, which accelerates the agent’s internal analysis steps.

Query Acceleration: Reflections

To support real-time interactive BI and rapid agent loops, Dremio utilizes SQL Reflections:

• Autonomous Query Acceleration: Reflections are optimized materializations of data layouts (such as aggregations or sorted partitions) stored as Apache Iceberg tables in the warehouse.
• Cost-Based Plan Rewriting: When an agent submits a query, Dremio’s compiler evaluates the query and automatically rewrites the execution plan to scan the reflection instead of the raw table. The agent gets query responses in milliseconds without needing to modify its SQL syntax.
• Background Synchronization: Dremio coordinates the maintenance of reflections in the background, updating them incrementally as new data commits to the base Iceberg tables.

SQL Reflection Mechanics and Arrow Flight SQL Serialization

Dremio accelerates agent loops using SQL Reflections, which represent pre-computed physical representations of logical query paths. There are two primary types of reflections:

• Raw Reflections: These reflections store a subset of table columns, sorted or partitioned by fields commonly used in filtering or joining. They behave like materialized index layouts but are stored as Iceberg tables on S3.
• Aggregation Reflections: These reflections pre-calculate common roll-ups and grouping metrics, storing the aggregated measures along with the dimension dimensions.

During the query compilation phase, Dremio’s cost-based optimizer performs reflection matching:

[ Incoming SQL Query ]
         │
         ▼
[ Cost-Based Optimizer ]
         │
  ┌──────┴────────────────────────────────────────┐
  │ (Checks available Reflections)                │
  ▼                                               ▼
[ Option A: Scan Raw S3 Table ]     [ Option B: Match Reflection Subtree ]
Cost: High I/O, slow scan           Cost: Low I/O, pre-aggregated scan
                                                  │
                                                  ▼
                                    [ Rewrite Query Plan to Reflection ]

If the optimizer identifies that the query’s projection and filtering criteria can be satisfied by an active reflection, it automatically swaps the execution plan subtree. The physical plan reads from the reflection’s pre-computed Parquet files instead of scanning millions of raw rows, which reduces latency.

Once the compute nodes process the data, it must be returned to the client. Flight SQL maximizes this transfer speed by using a vectorized stream layout:

1. Arrow IPC Format: Unlike JDBC, which requires converting binary records to Java objects and then to client formats, Flight SQL keeps records in the Apache Arrow In-Memory format.
2. gRPC Transportation: Data is streamed in chunks over gRPC, bypassing traditional network serialization overhead. This allows the AI agent’s Python process to receive millions of records directly into memory as a PyArrow buffer, accelerating downstream pandas or polars manipulations.

4. Execution Flow: Step-by-Step Walkthrough

To see how the components interact in production, we trace a query from the initial user request to the final result delivery.

 [User Prompt]
      │
      ▼
┌───────────┐
│ AI Agent  │ ◄─── (Retrieves Virtual Schema)
└─────┬─────┘
      │ (Submits SQL via Arrow Flight)
      ▼
┌───────────┐
│  Dremio   │ ◄─── (Requests Table Pointer & S3 Credentials) ───► ┌─────────┐
└─────┬─────┘                                                     │ Polaris │
      │                                                           └─────────┘
      │ (Applies Row Filters & SSN Masking)
      ▼
┌───────────┐
│ NVMe Cache│ ◄─── (Reads Cached Parquet Blocks or S3 Streams)
└─────┬─────┘
      │
      ▼ (Returns Vectorized Arrow Stream)
 [AI Agent]

Step 1: User Request

A business manager inputs a query to the agent interface: “Identify the total revenue generated by premium tier subscribers in the Northwest region during the first quarter of 2026.”

Step 2: Semantic Analysis and Schema Discovery

The AI Agent parses the request. It uses PyIceberg or a metadata utility to query Dremio’s semantic schema. The agent retrieves the virtual dataset definition for Corporate_Sales.Subscription_Details, reading column tags and descriptions:

{
  "dataset": "Corporate_Sales.Subscription_Details",
  "columns": [
    {
      "name": "subscriber_id",
      "type": "STRING",
      "description": "Unique identifier for customer accounts"
    },
    {
      "name": "tier",
      "type": "STRING",
      "description": "Subscription tier, values include 'Basic', 'Standard', 'Premium'"
    },
    {
      "name": "region",
      "type": "STRING",
      "description": "Geographical region code"
    },
    {
      "name": "monthly_rate",
      "type": "DECIMAL",
      "description": "Billing rate per month"
    },
    {
      "name": "signup_date",
      "type": "TIMESTAMP",
      "description": "Timestamp of account creation"
    }
  ]
}

Step 3: SQL Generation

The agent uses its internal LLM reasoning block to construct a SQL query based on the virtual dataset schema:

SELECT
  region,
  SUM(monthly_rate * 3) as q1_revenue
FROM Corporate_Sales.Subscription_Details
WHERE tier = 'Premium'
  AND region = 'Northwest'
  AND signup_date BETWEEN '2026-01-01 00:00:00' AND '2026-03-31 23:59:59'
GROUP BY region;

Step 4: SQL Submission

The agent submits the generated SQL query to Dremio using Arrow Flight SQL.

Step 5: Catalog Authentication and Pointer Resolution

Dremio’s query planner receives the SQL. Before executing the plan, Dremio contacts the Apache Polaris catalog:

1. Dremio authenticates with Polaris using its OAuth2 client credentials.
2. Dremio requests the Iceberg metadata pointer for the physical tables referenced by the virtual dataset.
3. Polaris validates Dremio’s permissions, generates short-lived, read-only IAM access tokens for the specific S3 file paths, and returns the pointer to the latest Iceberg metadata JSON file.

Step 6: Plan Optimization and Security Enforcement

Dremio’s optimizer applies fine-grained access control policies and plan rewrites:

• Row Filtering: Dremio checks the agent’s execution role. If the role restricts access to specific regions, Dremio automatically injects additional filters (e.g., AND region = ‘Northwest’) into the query tree.
• Column Masking: If the query requested sensitive user fields, Dremio applies masking expressions to redact them.
• Reflection Matching: Dremio checks if a matching reflection (such as an aggregation reflection on revenue columns) is available, rewriting the plan to scan the reflection.

Step 7: Execution and Data Ingestion

Dremio’s execution worker nodes process the query:

1. The nodes check Dremio’s Columnar Cloud Cache (C3). If the required Parquet blocks are already cached on the workers’ local NVMe SSD drives, they read them instantly.
2. Any missing data blocks are streamed directly from S3 using the temporary credentials vended by Polaris.
3. The query engine performs the aggregation and filtering in memory using Arrow columnar structures.

Step 8: Vectorized Result Stream

Dremio streams the resulting dataset back to the AI Agent over Arrow Flight SQL. The agent receives the data directly into a local Python Polars dataframe without serialization delays.

Step 9: Response Generation

The agent analyzes the table data and outputs a natural-language response to the user: “Premium tier subscribers in the Northwest region generated a total of 14,250,300 dollars in revenue during Q1 2026.”

5. Security and Governance Controls

To deploy this reference architecture in enterprise environments, you must implement strict safety boundaries at each layer of the stack.

┌────────────────────────────────────────────────────────┐
│                   SECURITY BOUNDARIES                  │
│                                                        │
│   ┌──────────────────────────────────────────────┐     │
│   │  AI Agent Prompt Sanitization (LLM Guard)    │     │
│   └──────────────────────┬───────────────────────┘     │
│                          ▼                             │
│   ┌──────────────────────────────────────────────┐     │
│   │  Dremio Semantic Layer Row & Column RBAC     │     │
│   └──────────────────────┬───────────────────────┘     │
│                          ▼                             │
│   ┌──────────────────────────────────────────────┐     │
│   │  Apache Polaris REST Credential Vending      │     │
│   └──────────────────────┬───────────────────────┘     │
│                          ▼                             │
│   ┌──────────────────────────────────────────────┐     │
│   │  Cloud KMS Encryption & IAM Buckets Policies │     │
│   └──────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────┘

Restricting Catalog Permissions in Polaris

The role-based access controls in Polaris should be configured to isolate engines based on their operational duties. The AI Agent’s query interface should connect to Dremio using a dedicated, read-only credential. Dremio’s catalog client in Polaris must only hold the TABLE_READ role on specific namespaces, preventing the engine from executing DDL commands (like DROP TABLE or ALTER TABLE) even if a malicious prompt injection occurs.

Centralized Data Masking in Dremio

Enforce data masking policies inside Dremio’s semantic layer, rather than relying on application code. Masking policies must automatically replace sensitive identifiers (like credit cards, emails, or government IDs) with hashed strings or default masks unless the user role is authorized to view them. This ensures that raw personal data is never loaded into the agent’s LLM context window.

S3 Object Storage Encryption

Ensure that all Parquet data files and metadata logs are encrypted at rest using server-side encryption with customer-managed keys (SSE-KMS) inside cloud object storage. When Polaris vends credentials, it should only vend read permissions for the specific keys corresponding to the active table paths, maintaining strict file-level isolation.

Custom IAM Policies for Polaris Credential Vending

To implement credential vending securely, the IAM role assumed by Polaris must have a policy that allows it to delegate access to S3. Below is an example of an AWS IAM policy attached to the Polaris catalog execution role:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "S3BucketList",
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::lakehouse-warehouse",
      "Condition": {
        "StringLike": {
          "s3:prefix": ["db/*"]
        }
      }
    },
    {
      "Sid": "S3ObjectReadWrite",
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:PutObject", "s3:DeleteObject"],
      "Resource": "arn:aws:s3:::lakehouse-warehouse/db/*"
    },
    {
      "Sid": "KMSEncryption",
      "Effect": "Allow",
      "Action": ["kms:Decrypt", "kms:GenerateDataKey"],
      "Resource": "arn:aws:kms:us-east-1:123456789012:key/my-key-uuid"
    }
  ]
}

This policy grants Polaris the ability to list directories and read or write files within the db/ path, while also securing the data using KMS keys.

6. Real-World Implementation Guide: Setting Up the Architecture

To deploy this reference architecture, follow these implementation steps.

Step 1: Configuring Apache Polaris REST Catalog

Start Polaris and create a new catalog instance pointing to your multicloud S3 storage warehouse.

# Create a storage credential configuration in Polaris
curl -i -X POST http://polaris-service:8181/api/v1/catalog-roles \
  -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "aws-storage-role",
    "properties": {
      "role-arn": "arn:aws:iam::123456789012:role/PolarisS3Access"
    }
  }'

# Create the Iceberg catalog
curl -i -X POST http://polaris-service:8181/api/v1/catalogs \
  -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "enterprise_warehouse",
    "type": "INTERNAL",
    "properties": {
      "default-base-location": "s3a://lakehouse-warehouse/"
    }
  }'

Step 2: Registering Polaris Catalog in Dremio

To connect Dremio to your Apache Polaris REST Catalog:

1. Open the Dremio administrator console.
2. Click Add Source and select Apache Iceberg.
3. Set the Connection Type to REST.
4. Set the REST URI to http://polaris-service:8181/api/v1.
5. Set the Authentication method to OAuth2 Client Credentials.
6. Input the Client ID and Client Secret generated during your Polaris setup.

Step 3: Establishing Row-Level Security in Dremio

Create a row filter policy in Dremio to restrict database access based on user role assignments:

CREATE OR REPLACE ROW FILTER enterprise_warehouse.db.sales_data.region_filter
ON enterprise_warehouse.db.sales_data
USING (
  CASE
    WHEN IS_MEMBER('Executive') THEN TRUE
    WHEN IS_MEMBER('Regional_Sales_North') THEN region_code = 'US-NORTH'
    WHEN IS_MEMBER('Regional_Sales_South') THEN region_code = 'US-SOUTH'
    ELSE FALSE
  END
);

ALTER TABLE enterprise_warehouse.db.sales_data ADD ROW FILTER region_filter;

Step 4: Fetching Data via Arrow Flight SQL in Python

Use the pyarrow.flight client library to establish a high-speed columnar connection from the AI Agent Python framework directly to Dremio:

import pyarrow.flight as flight
from pyarrow.flight import FlightClient, Ticket

# Establish connection to Dremio coordinator node
client = FlightClient("grpc+tcp://dremio-coordinator:32010")

# Authenticate client credentials
auth_handler = flight.ClientAuthHandler()
# (Configure custom authentication handshake)

# Define query ticket representing the SQL execution command
sql_query = "SELECT * FROM enterprise_warehouse.db.sales_data"
ticket_bytes = Ticket(sql_query.encode('utf-8'))

# Stream results vectorially into PyArrow table
reader = client.do_get(ticket_bytes)
arrow_table = reader.read_all()

# Convert Arrow table directly to Polars DataFrame for agent analysis
import polars as pl
df = pl.from_arrow(arrow_table)
print(df.head())

Step 5: Implementing an Agentic Reasoning Loop

To build an agent that interacts with Dremio dynamically, you can construct a python execution class that receives natural language prompts, translates them to SQL queries, runs them over Arrow Flight, and returns a summarized answer:

import openai
import pyarrow.flight as flight
from pyarrow.flight import FlightClient, Ticket
import polars as pl

class DremioAgent:
    def __init__(self, dremio_host, dremio_port, openai_api_key):
        self.flight_client = FlightClient(f"grpc+tcp://{dremio_host}:{dremio_port}")
        self.openai_client = openai.OpenAI(api_key=openai_api_key)
        self.schema_context = self.load_schema_context()

    def load_schema_context(self):
        # Queries Dremio metadata to load table descriptions
        query = """
        SELECT table_name, column_name, data_type
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = 'enterprise_warehouse.db'
        """
        ticket = Ticket(query.encode('utf-8'))
        reader = self.flight_client.do_get(ticket)
        arrow_table = reader.read_all()
        df = pl.from_arrow(arrow_table)

        # Format the schema as prompt context
        context = "Available Tables:\n"
        for row in df.iter_rows(named=True):
            context += f"Table: {row['table_name']}, Column: {row['column_name']}, Type: {row['data_type']}\n"
        return context

    def execute_query(self, sql_query):
        try:
            ticket = Ticket(sql_query.encode('utf-8'))
            reader = self.flight_client.do_get(ticket)
            arrow_table = reader.read_all()
            return pl.from_arrow(arrow_table)
        except Exception as e:
            return f"Query Execution Error: {str(e)}"

    def run(self, user_prompt):
        # Step 1: Generate SQL query using OpenAI LLM
        prompt = f"""
        You are an AI Agent with read-only access to a data lakehouse.
        Using the following schema context, generate an ANSI SQL query to answer the user's request.
        Do not explain the query. Return only the raw SQL query.

        {self.schema_context}

        Request: {user_prompt}
        SQL Query:
        """

        response = self.openai_client.chat.completions.create(
            model="gpt-4o",
            messages=[{"role": "user", "content": prompt}],
            temperature=0.0
        )
        generated_sql = response.choices[0].message.content.strip()
        print(f"Generated SQL: {generated_sql}")

        # Step 2: Execute the query over Arrow Flight SQL
        result_df = self.execute_query(generated_sql)

        if isinstance(result_df, str):
            return f"Failed to retrieve data. {result_df}"

        # Step 3: Summarize results
        summary_prompt = f"""
        Summarize the following data to answer the user's question: '{user_prompt}'

        Data:
        {result_df.head(10).to_string()}

        Summary:
        """

        summary_response = self.openai_client.chat.completions.create(
            model="gpt-4o",
            messages=[{"role": "user", "content": summary_prompt}],
            temperature=0.2
        )
        return summary_response.choices[0].message.content.strip()

# Usage Example
# agent = DremioAgent("dremio-coordinator", 32010, "your-openai-api-key")
# print(agent.run("What are the top three customer segments by revenue?"))

This class demonstrates how agents can query a lakehouse platform dynamically while leveraging the performance benefits of Apache Arrow.

7. Comparative Architecture Analysis

To evaluate how this reference architecture performs against legacy data warehouse models and basic RAG setups, refer to the analysis below:

Conclusion

Building a governed data lakehouse optimized for AI Agents requires a modern stack. Storing data in Apache Iceberg tables on object storage ensures that multiple engines can access files concurrently. Managing table pointers via Apache Polaris REST APIs coordinates secure, cross-cloud access. Deploying Dremio as the semantic and query acceleration tier provides the necessary business metadata structure, row and column security boundaries, and Arrow Flight SQL execution speeds to support autonomous AI agent loops.

By implementing this reference architecture, enterprise organizations can deploy secure, performant, and cost-effective AI agents that query multicloud datasets without data duplication or vendor lock-in.

If you are ready to evaluate table format performance in detail, read our adjacent guide on benchmarking open table formats or learn more about Apache Iceberg Architecture.