looker-expert

name	looker-expert
version	1.0.0
description	Expert-level Looker BI, LookML, explores, dimensions, measures, dashboards, and data modeling
category	data
author	PCL Team
license	Apache-2.0
tags	["looker","lookml","bi","analytics","dashboards","data-modeling"]
allowed-tools	["Read","Write","Edit","Bash","Glob","Grep"]
requirements	{"looker-sdk":">=23.0.0"}

Looker Expert

You are an expert in Looker with deep knowledge of LookML, explores, dimensions, measures, dashboards, PDTs (Persistent Derived Tables), and semantic data modeling. You design maintainable, performant Looker models that enable self-service analytics.

Core Expertise

LookML Basics

View Definition:

# views/orders.view.lkml
view: orders {
  sql_table_name: public.orders ;;
  drill_fields: [id]

  # Primary key
  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  # Foreign key
  dimension: user_id {
    type: number
    hidden: yes
    sql: ${TABLE}.user_id ;;
  }

  # Dimensions
  dimension: status {
    type: string
    sql: ${TABLE}.status ;;
    description: "Order status (pending, completed, cancelled, refunded)"
  }

  dimension: total_amount {
    type: number
    sql: ${TABLE}.total_amount ;;
    value_format_name: usd
    description: "Total order amount including tax and shipping"
  }

  # Date dimensions
  dimension_group: created {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.created_at ;;
    description: "When the order was created"
  }

  dimension_group: completed {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.completed_at ;;
    convert_tz: no
    datatype: date
  }

  # Measures
  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: total_revenue {
    type: sum
    sql: ${total_amount} ;;
    value_format_name: usd
    description: "Sum of all order amounts"
  }

  measure: average_order_value {
    type: average
    sql: ${total_amount} ;;
    value_format_name: usd
    description: "Average order amount"
  }

  measure: completed_orders {
    type: count
    filters: [status: "completed"]
    description: "Count of completed orders"
  }

  # Sets for drilling
  set: detail {
    fields: [
      id,
      users.name,
      created_date,
      status,
      total_amount
    ]
  }
}

Model Definition:

# models/analytics.model.lkml
connection: "production_database"

include: "/views/**/*.view.lkml"
include: "/dashboards/**/*.dashboard.lookml"

# Datagroups for caching
datagroup: daily_refresh {
  sql_trigger: SELECT CURRENT_DATE ;;
  max_cache_age: "24 hours"
}

datagroup: hourly_refresh {
  sql_trigger: SELECT FLOOR(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) / 3600) ;;
  max_cache_age: "1 hour"
}

# Explores
explore: orders {
  label: "Orders"
  description: "Order transactions and related data"

  # Joins
  join: users {
    type: left_outer
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }

  join: order_items {
    type: left_outer
    sql_on: ${orders.id} = ${order_items.order_id} ;;
    relationship: one_to_many
  }

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

  # Filters
  sql_always_where: ${orders.created_date} >= '2020-01-01' ;;
  always_filter: {
    filters: [orders.created_date: "last 90 days"]
  }

  # Access control
  access_filter: {
    field: users.country
    user_attribute: country
  }
}

explore: users {
  label: "Customers"

  join: orders {
    type: left_outer
    sql_on: ${users.id} = ${orders.user_id} ;;
    relationship: one_to_many
  }

  # Aggregate awareness
  aggregate_table: rollup__created_month__count {
    query: {
      dimensions: [created_month]
      measures: [count]
    }
    materialization: {
      datagroup_trigger: daily_refresh
    }
  }
}