looker-expert

What this skill does

# 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:**
```lookml
# 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:**
```lookml
# 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
    }
  }
}
```

### Advanced Dimensions and Measures

**Derived Dimensions:**
```lookml
view: users {
  # Concatenation
  dimension: full_name {
    type: string
    sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
  }

  # Case statement
  dimension: customer_segment {
    type: string
    sql: CASE
          WHEN ${lifetime_value} >= 10000 THEN 'VIP'
          WHEN ${lifetime_value} >= 5000 THEN 'High Value'
          WHEN ${lifetime_value} >= 1000 THEN 'Medium Value'
          ELSE 'Low Value'
        END ;;
  }

  # Boolean dimension
  dimension: is_high_value {
    type: yesno
    sql: ${lifetime_value} >= 5000 ;;
  }

  # Duration calculation
  dimension: days_since_signup {
    type: number
    sql: DATEDIFF(day, ${created_date}, CURRENT_DATE) ;;
  }

  # Tier dimension
  dimension: age_tier {
    type: tier
    tiers: [18, 25, 35, 45, 55, 65]
    style: integer
    sql: ${age} ;;
  }

  # Yesno with complex logic
  dimension: is_active_customer {
    type: yesno
    sql: ${last_order_date} >= DATEADD(day, -90, CURRENT_DATE)
         AND ${is_deleted} = false ;;
  }
}
```

**Advanced Measures:**
```lookml
view: orders {
  # Conditional measures
  measure: high_value_orders {
    type: count
    filters: [total_amount: ">100"]
  }

  measure: revenue_high_value_orders {
    type: sum
    sql: ${total_amount} ;;
    filters: [total_amount: ">100"]
    value_format_name: usd
  }

  # Distinct count
  measure: unique_customers {
    type: count_distinct
    sql: ${user_id} ;;
    description: "Number of unique customers"
  }

  # Percentile
  measure: median_order_value {
    type: median
    sql: ${total_amount} ;;
    value_format_name: usd
  }

  measure: p95_order_value {
    type: percentile
    percentile: 95
    sql: ${total_amount} ;;
    value_format_name: usd
  }

  # Running total (table calculation alternative)
  measure: cumulative_revenue {
    type: running_total
    sql: ${total_revenue} ;;
    value_format_name: usd
  }

  # Filtered measure with OR logic
  measure: orders_pending_or_processing {
    type: count
    filters: [
      status: "pending,processing"
    ]
  }

  # Ratio measure
  measure: conversion_rate {
    type: number
    sql: 1.0 * ${completed_orders} / NULLIF(${count}, 0) ;;
    value_format_name: percent_2
  }

  # Average distinct (for distinct key values)
  measure: avg_daily_orders {
    type: average_distinct
    sql: ${count} ;;
    sql_distinct_key: ${created_date} ;;
  }
}
```

### Persistent Derived Tables (PDTs)

**SQL-Based PDTs:**
```lookml
view: customer_lifetime_metrics {
  derived_table: {
    sql:
      SELECT
        user_id,
        COUNT(*) as lifetime_orders,
        SUM(total_amount) as lifetime_value,
        AVG(total_amount) as avg_order_value,
        MIN(created_at) as first_order_date,
        MAX(created_at) as last_order_date
      FROM orders
      WHERE status = 'completed'
      GROUP BY user_id
    ;;

    # Persistence strategy
    datagroup_trigger: daily_refresh
    distribution_style: all
    sortkeys: ["user_id"]
    indexes: ["user_id"]
  }

  dimension: user_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension: lifetime_orders {
    type: number
    sql: ${TABLE}.lifetime_orders ;;
  }

  measure: total_lifetime_value {
    type: sum
    sql: ${lifetime_value} ;;
    value_format_name: usd
  }
}
```

**Incremental PDTs:**
```lookml
view: daily_order_summary {
  derived_table: {
    sql:
      SELECT
        DATE(created_at) as order_date,
        status,
        COUNT(*) as order_count,
        SUM(total_amount) as total_revenue
      FROM orders
      WHERE
        {% condition order_date %} DATE(created_at) {% endcondition %}
      GROUP BY 1, 2
    ;;

    # Incremental strategy
    datagroup_trigger: hourly_refresh
    increment_key: "order_date"
    increment_offset: 3

    # SQL for incremental loads
    sql_trigger_value: SELECT CURRENT_DATE ;;
    partition_keys: ["order_date"]
  }

  dimension_group: order {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.order_date ;;
  }

  filter: order_date {
    type: date
  }
}
```

**Native Derived Tables (NDTs):**
```lookml
explore: orders {
  # Inline derived table
  join: order_summary {
    type: left_outer
    sql_on: ${orders.id} = ${order_summary.order_id} ;;
    relationship: one_to_one

    sql_table_name:
      (SELECT