Author(s): allglenn

Originally published on Towards AI.

Before we talk about where to store your registry, let’s address the elephant in the room: What about LookML?

If you’re already using Looker, you might be wondering whether you need to build this YAML-based semantic layer at all — and that’s a fair question.

What is LookML?

LookML is Looker’s modeling language for defining your semantic layer. It’s what makes Looker powerful: instead of writing SQL for every chart, you define your business logic once in LookML, and Looker generates SQL automatically.

Here’s what the same net_revenue metric looks like in LookML:

view: orders {
 sql_table_name: analytics.fact_orders ;;
 
 dimension: order_id {
 primary_key: yes
 type: string
 sql: ${TABLE}.order_id ;;
 }
 
 dimension_group: order {
 type: time
 timeframes: [date, week, month, year]
 sql: ${TABLE}.order_date ;;
 }
 
 dimension: country {
 type: string
 sql: ${TABLE}.country ;;
 }
 
 dimension: net_amount {
 type: number
 sql: ${TABLE}.net_amount ;;
 hidden: yes # Don't expose raw dimension
 }
 
 measure: net_revenue {
 type: sum
 sql: ${net_amount} ;;
 description: "Revenue excluding tax, refunds, and cancelled orders"
 value_format_name: usd
 }
 
 measure: order_count {
 type: count_distinct
 sql: ${order_id} ;;
 description: "Total number of completed orders"
 }
 
 measure: average_order_value {
 type: number
 sql: ${net_revenue} / NULLIF(${order_count}, 0) ;;
 description: "Average net revenue per order"
 value_format_name: usd
 }
}

# Explores define which views can be joined together
explore: orders {
 description: "Order analytics"
 
 join: customers {
 type: left_outer
 sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
 relationship: many_to_one
 }
}

LookML gives you:

• ✅ Dimension and measure definitions (just like our YAML registry)
• ✅ Type safety (dates, numbers, strings with validation)
• ✅ Reusable logic (derived measures reference other measures)
• ✅ Join relationships (Explores define how views connect)
• ✅ Access controls (field-level permissions)
• ✅ Version control (LookML files live in Git)

The Integration: LookML + ADK

Here’s the good news: LookML and ADK work beautifully together.

Google opened up Looker’s semantic layer in 2024 with the Open SQL Interface, which means you can programmatically access LookML-defined metrics from anywhere — including your ADK agents.

Architecture with LookML:

Natural language question
 ↓
[ADK Agent] ← Reasoning & orchestration
 ↓
[Looker API / Open SQL Interface] ← Query LookML metrics
 ↓
[LookML Models] ← Semantic layer (metrics, dimensions, joins)
 ↓
[BigQuery] ← Raw storage & computation

Implementation with Looker API:

from google.adk import Agent, Tool
import requests
import os

class LookerSemanticAgent:
 def __init__(self):
 self.looker_api_url = os.getenv("LOOKER_API_URL")
 self.looker_client_id = os.getenv("LOOKER_CLIENT_ID")
 self.looker_client_secret = os.getenv("LOOKER_CLIENT_SECRET")
 self.access_token = self._authenticate()
 
 def _authenticate(self):
 """Authenticate with Looker API"""
 auth_response = requests.post(
 f"{self.looker_api_url}/login",
 data={
 "client_id": self.looker_client_id,
 "client_secret": self.looker_client_secret
 }
 )
 return auth_response.json()["access_token"]
 
 @Tool
 def query_looker_metric(
 self,
 model: str,
 explore: str,
 measures: list[str],
 dimensions: list[str] = None,
 filters: dict = None,
 limit: int = 500
 ) -> dict:
 """
 Query metrics defined in LookML through Looker API.
 
 Args:
 model: LookML model name (e.g., 'analytics')
 explore: Explore name (e.g., 'orders')
 measures: List of measures to query (e.g., ['net_revenue'])
 dimensions: List of dimensions to group by (e.g., ['country'])
 filters: Dimension filters (e.g., {'order_date': '30 days'})
 limit: Maximum number of rows to return
 """
 
 # Build Looker query
 query_body = {
 "model": model,
 "view": explore,
 "fields": measures + (dimensions or []),
 "filters": filters or {},
 "limit": limit
 }
 
 # Create and run query
 headers = {"Authorization": f"Bearer {self.access_token}"}
 
 # Create query
 create_response = requests.post(
 f"{self.looker_api_url}/queries",
 json=query_body,
 headers=headers
 )
 query_id = create_response.json()["id"]
 
 # Run query
 run_response = requests.get(
 f"{self.looker_api_url}/queries/{query_id}/run/json",
 headers=headers
 )
 
 results = run_response.json()
 
 # Get field definitions for context
 explore_response = requests.get(
 f"{self.looker_api_url}/lookml_models/{model}/explores/{explore}",
 headers=headers
 )
 explore_metadata = explore_response.json()
 
 return {
 "results": results,
 "metadata": {
 "model": model,
 "explore": explore,
 "measures": measures,
 "dimensions": dimensions,
 "filters": filters,
 "field_definitions": self._extract_field_metadata(
 explore_metadata, 
 measures + (dimensions or [])
 )
 }
 }
 
 def _extract_field_metadata(self, explore_metadata, fields):
 """Extract descriptions and types for requested fields"""
 metadata = {}
 
 for field in fields:
 # Search in measures
 for measure in explore_metadata.get("fields", {}).get("measures", []):
 if measure["name"] == field:
 metadata[field] = {
 "description": measure.get("description"),
 "type": measure.get("type"),
 "sql": measure.get("sql")
 }
 
 # Search in dimensions
 for dimension in explore_metadata.get("fields", {}).get("dimensions", []):
 if dimension["name"] == field:
 metadata[field] = {
 "description": dimension.get("description"),
 "type": dimension.get("type")
 }
 
 return metadata


# Create ADK agent with Looker integration
looker_agent = LookerSemanticAgent()

agent = Agent(
 name="LookerAnalyticsAgent",
 model="gemini-2.0-flash-exp",
 instruction="""You are an analytics expert with access to Looker's semantic layer.
 
 When users ask about metrics:
 1. Identify which LookML model and explore to use
 2. Map their question to specific measures and dimensions
 3. Extract any filters from their question
 4. Call query_looker_metric with the correct parameters
 5. Present results clearly with metric definitions
 
 Always include the metric description in your response so users understand
 exactly what they're seeing.
 
 Available models: 'analytics'
 Available explores: 'orders', 'customers', 'products'
 """,
 tools=[looker_agent.query_looker_metric]
)

When to Use LookML vs. Custom YAML Registry

Here’s a decision framework:

Use LookML When:

✅ You’re already using Looker — Don’t rebuild what you have
✅ You have complex join logic — LookML’s Explore layer handles this elegantly
✅ You need a BI tool anyway — Get semantic layer + visualization in one
✅ You have non-technical users — Looker’s UI makes metric discovery easy
✅ You want enterprise features — Row-level security, caching, alerting built-in

Use Custom YAML Registry When:

✅ You don’t need Looker — Why pay for software you won’t use?
✅ You want full control — No vendor lock-in, complete flexibility
✅ You have simple aggregations — Most metrics are SUM/COUNT/AVG on one table
✅ You’re building a specialized tool — Embedded analytics, CLI tools, etc.
✅ You want minimal infrastructure — Just files in Git, no services to maintain

The Hybrid Approach (Best of Both Worlds):

Many teams use both:

• LookML for BI users — Analysts build dashboards in Looker
• YAML registry for agents — Subset of metrics exposed to AI agents
• Shared definitions — Both point to the same BigQuery views

# metrics.yaml - Mirrors subset of LookML
metrics:
 net_revenue:
 description: "Revenue excluding tax, refunds, and cancelled orders"
 looker_reference:
 model: analytics
 explore: orders
 measure: net_revenue
 fallback_sql: "SUM(net_amount)" # If Looker unavailable
 source: analytics.fact_orders

This gives you:

• BI tool for human users (Looker)
• Lightweight agent access (direct BigQuery via YAML)
• Single source of truth (LookML is authoritative)
• Resilience (agents can fallback to direct SQL)

Using Looker’s Open SQL Interface

In August 2024, Google released the Open SQL Interface for Looker, which provides JDBC/SQL access to LookML metrics. This is huge for agent integrations.

import jaydebeapi

# Connect to Looker via JDBC
conn = jaydebeapi.connect(
 "com.looker.jdbc.LookerDriver",
 "jdbc:looker://your-instance.looker.com:19999",
 {"user": "your-user", "password": "your-password"}
)

cursor = conn.cursor()

# Query LookML metrics using SQL
cursor.execute("""
 SELECT 
 orders.country,
 orders.net_revenue,
 orders.order_count
 FROM analytics.orders
 WHERE orders.order_date >= '2025-01-01'
 GROUP BY orders.country
""")

results = cursor.fetchall()

The agent can now treat LookML like a database — but it’s a semantic database where column names are metric definitions.

The Best of Both: LookML + ADK + BigQuery

Here’s the architecture I recommend for enterprises already using Looker:

User Question
 ↓
ADK Agent decides:
 ├─→ Complex/exploratory? → Looker API (leverages joins, caching)
 ├─→ Simple/real-time? → Direct BigQuery (lower latency)
 └─→ New metric? → Suggest LookML addition
 ↓
Both paths use same BigQuery views

Implementation:

class HybridSemanticAgent:
 def __init__(self):
 self.looker = LookerSemanticAgent()
 self.bigquery = SemanticLayerAgent(registry_path="./semantic-layer")
 
 @Tool
 def query_metric(self, metric_name: str, filters: dict, **kwargs):
 """Route queries to optimal backend"""
 
 # Check if metric requires joins (use Looker)
 if self._requires_joins(metric_name):
 return self.looker.query_looker_metric(
 model="analytics",
 explore=self._get_explore(metric_name),
 measures=[metric_name],
 filters=filters
 )
 
 # Simple metric, use direct BigQuery
 return self.bigquery.query_metric(
 metric_name=metric_name,
 filters=filters,
 **kwargs
 )
 
 def _requires_joins(self, metric_name: str) -> bool:
 """Check if metric needs data from multiple tables"""
 complex_metrics = {
 'customer_lifetime_value', # Needs orders + customers
 'product_revenue_share', # Needs orders + products
 'repeat_customer_rate' # Needs customer history
 }
 return metric_name in complex_metrics

Real-World Example: Multi-Table Metrics

Here’s where LookML shines — metrics that span multiple tables:

# This is painful to manage in YAML, elegant in LookML
explore: orders {
 join: customers {
 sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
 relationship: many_to_one
 }
 
 join: products {
 sql_on: ${orders.product_id} = ${products.product_id} ;;
 relationship: many_to_one
 }
}

view: orders {
 measure: revenue_by_customer_tier {
 type: sum
 sql: ${net_amount} ;;
 filters: [customers.tier: "enterprise"] # Cross-view filter
 }
 
 measure: high_margin_product_revenue {
 type: sum
 sql: ${net_amount} ;;
 filters: [products.margin_percentage: ">30"] # Another join
 }
}

Your agent can query these complex metrics without worrying about join logic:

result = looker_agent.query_looker_metric(
 model="analytics",
 explore="orders",
 measures=["revenue_by_customer_tier"],
 dimensions=["order_month"],
 filters={"order_date": "90 days"}
)

Looker handles the joins, caching, and SQL generation.

Migration Path: YAML → LookML

If you start with YAML and grow into needing Looker:

Phase 1: YAML only (Week 1–2)

• Quick prototype with minimal infrastructure
• Prove value with core metrics

Phase 2: Hybrid (Month 1–3)

• Introduce Looker for BI users
• Migrate complex metrics to LookML
• Keep simple metrics in YAML for agents

Phase 3: LookML primary (Month 3+)

• LookML is source of truth
• Agents query via Looker API or Open SQL Interface
• YAML becomes a cache/fallback layer

The investment scales with your needs.

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI