Reference Architecture: Enterprise AI Agents

from google.cloud import bigquery
from vertexai.generative_models import GenerativeModel
import looker_sdk

class GroundedBIAgent:
    """
    AI Agent grounded in LookML semantic layer
    """
    def __init__(self):
        self.model = GenerativeModel("gemini-1.5-pro")
        self.looker = looker_sdk.init40()
        self.bq_client = bigquery.Client()

    def process_query(self, user_question: str) -> dict:
        """
        Process natural language query with semantic grounding
        """
        # Step 1: Get available semantic context from Looker
        explores = self.looker.all_lookml_models()
        context = self._build_semantic_context(explores)

        # Step 2: Use Gemini to interpret query with semantic grounding
        grounded_prompt = f"""
        You are a BI analyst assistant. Answer ONLY using the
        semantic definitions provided. If asked about metrics not
        defined here, say you cannot answer.

        AVAILABLE SEMANTIC DEFINITIONS:
        {context}

        USER QUESTION: {user_question}

        Generate a Looker API query specification (NOT raw SQL).
        Include: model, view, fields, filters, sorts.
        """

        response = self.model.generate_content(grounded_prompt)
        query_spec = self._parse_query_spec(response.text)

        # Step 3: Execute via Looker (inherits all governance)
        result = self.looker.run_inline_query(
            result_format="json",
            body=query_spec
        )

        # Step 4: Generate natural language explanation
        explanation = self._generate_explanation(
            question=user_question,
            data=result,
            query_spec=query_spec
        )

        return {
            "answer": explanation,
            "data": result,
            "query_used": query_spec,
            "governance": {
                "grounded": True,
                "semantic_model": query_spec.get("model"),
                "access_validated": True
            }
        }

    def _build_semantic_context(self, explores):
        """
        Build semantic context from LookML definitions
        """
        context = []
        for explore in explores:
            context.append(f"""
            Explore: {explore.name}
            Description: {explore.description}
            Available Dimensions: {[d.name for d in explore.dimensions]}
            Available Measures: {[m.name for m in explore.measures]}
            """)
        return "\n".join(context)

# Model file with agent-friendly documentation
explore: orders {
  label: "Customer Orders"
  description: "All customer orders including status, revenue, and fulfillment.
                Use this explore to answer questions about sales, revenue,
                order volume, and customer purchasing patterns."

  # Define valid joins for the agent
  join: customers {
    type: left_outer
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }

  join: products {
    type: left_outer
    sql_on: ${order_items.product_id} = ${products.id} ;;
    relationship: many_to_one
  }
}

view: orders {
  sql_table_name: analytics.fct_orders ;;

  # Agent needs clear descriptions for every field
  dimension: order_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.order_id ;;
    description: "Unique identifier for each order"
  }

  dimension_group: order {
    type: time
    timeframes: [raw, date, week, month, quarter, year]
    sql: ${TABLE}.order_timestamp ;;
    description: "When the order was placed. Use for time-based analysis."
  }

  dimension: order_status {
    type: string
    sql: ${TABLE}.status ;;
    description: "Current status: pending, processing, shipped, delivered, cancelled"
    suggestions: ["pending", "processing", "shipped", "delivered", "cancelled"]
  }

  # Measures with business context
  measure: total_revenue {
    type: sum
    sql: ${TABLE}.order_total ;;
    value_format_name: usd
    description: "Total order value in USD. Excludes cancelled and returned orders."
    filters: [order_status: "-cancelled, -returned"]

    # Tags help agents understand when to use this metric
    tags: ["revenue", "sales", "financial", "primary_metric"]
  }

  measure: order_count {
    type: count_distinct
    sql: ${TABLE}.order_id ;;
    description: "Number of unique orders placed"
    filters: [order_status: "-cancelled"]
    tags: ["volume", "count", "orders"]
  }

  measure: average_order_value {
    type: number
    sql: ${total_revenue} / NULLIF(${order_count}, 0) ;;
    value_format_name: usd
    description: "Average revenue per order (AOV). Key e-commerce metric."
    tags: ["aov", "average", "efficiency"]
  }
}