Main Article: Looker Semantic Layer + FinOps in BigQuery

-- Example: Partitioned events table
CREATE TABLE analytics.events_partitioned
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
AS SELECT * FROM analytics.events_raw;

-- Optimized query (scans only 1 day)
SELECT
  event_type,
  COUNT(*) as total_events
FROM analytics.events_partitioned
WHERE DATE(event_timestamp) = CURRENT_DATE()
GROUP BY 1;

-- Savings: From ~$50 per query to $0.02 ✓

# Define datagroup for intelligent refresh
datagroup: daily_revenue_datagroup {
  sql_trigger: SELECT MAX(order_date) FROM orders ;;
  max_cache_age: "4 hours"
}

# PDT for aggregated metrics
view: daily_revenue_summary {
  derived_table: {
    datagroup_trigger: daily_revenue_datagroup
    partition_keys: ["order_date"]
    cluster_keys: ["customer_segment"]
    sql:
      SELECT
        DATE(order_timestamp) as order_date,
        customer_segment,
        SUM(order_total) as total_revenue,
        COUNT(DISTINCT order_id) as order_count,
        COUNT(DISTINCT customer_id) as customer_count
      FROM orders
      WHERE DATE(order_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
      GROUP BY 1, 2
    ;;
  }

  dimension: order_date {
    type: date
    sql: ${TABLE}.order_date ;;
  }

  measure: revenue {
    type: sum
    sql: ${TABLE}.total_revenue ;;
    value_format_name: usd
  }
}

# Cloud Function that monitors BigQuery costs
import functions_framework
from google.cloud import bigquery

@functions_framework.cloud_event
def monitor_bq_costs(cloud_event):
    """
    Monitors expensive BigQuery queries and sends alerts
    """
    client = bigquery.Client()

    # Query to identify expensive queries (> $10)
    query = """
    SELECT
      user_email,
      query,
      total_bytes_processed,
      total_bytes_billed,
      (total_bytes_billed / POW(10, 12)) * 5 as estimated_cost_usd,
      creation_time
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
      AND (total_bytes_billed / POW(10, 12)) * 5 > 10
    ORDER BY estimated_cost_usd DESC
    LIMIT 10
    """

    results = client.query(query).result()

    for row in results:
        # Send alert to Slack/Email
        send_alert({
            'user': row.user_email,
            'cost': round(row.estimated_cost_usd, 2),
            'query_preview': row.query[:200],
            'recommendation': suggest_optimization(row.query)
        })

def suggest_optimization(query):
    """
    AI agent that suggests optimizations
    """
    if 'SELECT *' in query:
        return "⚠️ Avoid SELECT *. Specify only necessary columns."
    elif 'PARTITION' not in query and 'WHERE' in query:
        return "💡 Consider adding partition filter to reduce scanning."
    else:
        return "✓ Query appears optimized."