looker-expert（观察专家）

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
    }
  }
}

Advanced Dimensions and Measures

Derived Dimensions:

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:

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:

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:

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):

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
        order_id,
        COUNT(*) as item_count,
        SUM(quantity) as total_quantity
      FROM order_items
      GROUP BY order_id)
    ;;
  }
}

Explores and Joins

Advanced Join Patterns:

explore: orders {
  # Many-to-one join
  join: users {
    type: left_outer
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }

  # One-to-many join with symmetric aggregates
  join: order_items {
    type: left_outer
    sql_on: ${orders.id} = ${order_items.order_id} ;;
    relationship: one_to_many
  }

  # Many-to-one fanout prevention
  join: products {
    type: left_outer
    sql_on: ${order_items.product_id} = ${products.id} ;;
    relationship: many_to_one
    required_joins: [order_items]
  }

  # Join based on derived table
  join: customer_metrics {
    type: left_outer
    sql_on: ${users.id} = ${customer_metrics.user_id} ;;
    relationship: one_to_one
  }

  # Cross join (use sparingly)
  join: date_spine {
    type: cross
    relationship: many_to_many
  }

  # Join with additional conditions
  join: user_preferences {
    type: left_outer
    sql_on: ${users.id} = ${user_preferences.user_id}
           AND ${user_preferences.is_active} = true ;;
    relationship: one_to_one
  }
}

Refinements:

# Extend base explore
explore: +orders {
  label: "Orders Extended"

  # Add additional join
  join: promotions {
    type: left_outer
    sql_on: ${orders.promotion_id} = ${promotions.id} ;;
    relationship: many_to_one
  }

  # Override existing join
  join: users {
    fields: [users.id, users.name, users.email]  # Limit fields
  }
}

Parameters and Templated Filters

Parameters:

view: orders {
  # Parameter for dynamic measures
  parameter: metric_selector {
    type: unquoted
    allowed_value: {
      label: "Revenue"
      value: "revenue"
    }
    allowed_value: {
      label: "Order Count"
      value: "count"
    }
    allowed_value: {
      label: "Average Order Value"
      value: "aov"
    }
  }

  measure: dynamic_metric {
    label_from_parameter: metric_selector
    type: number
    sql:
      {% if metric_selector._parameter_value == 'revenue' %}
        ${total_revenue}
      {% elsif metric_selector._parameter_value == 'count' %}
        ${count}
      {% elsif metric_selector._parameter_value == 'aov' %}
        ${average_order_value}
      {% else %}
        NULL
      {% endif %}
    ;;
  }

  # Date range parameter
  parameter: timeframe_picker {
    type: unquoted
    allowed_value: {
      label: "Day"
      value: "date"
    }
    allowed_value: {
      label: "Week"
      value: "week"
    }
    allowed_value: {
      label: "Month"
      value: "month"
    }
  }

  dimension: dynamic_timeframe {
    label_from_parameter: timeframe_picker
    type: string
    sql:
      {% if timeframe_picker._parameter_value == 'date' %}
        ${created_date}
      {% elsif timeframe_picker._parameter_value == 'week' %}
        ${created_week}
      {% elsif timeframe_picker._parameter_value == 'month' %}
        ${created_month}
      {% else %}
        ${created_date}
      {% endif %}
    ;;
  }
}

Templated Filters:

view: orders {
  # Filter-only field
  filter: date_filter {
    type: date
    description: "Use this filter to define date range"
  }

  # Dimension using filter
  dimension: is_in_date_range {
    type: yesno
    sql: {% condition date_filter %} ${created_raw} {% endcondition %} ;;
  }

  # Measure using filter
  measure: orders_in_range {
    type: count
    filters: [is_in_date_range: "yes"]
  }

  # Multiple filter conditions
  filter: amount_range {
    type: number
  }

  measure: orders_in_amount_range {
    type: count
    sql: ${id} ;;
    filters: [
      status: "completed"
    ]
    sql: {% condition amount_range %} ${total_amount} {% endcondition %} ;;
  }
}

Dashboards

Dashboard Definition:

# dashboards/executive_overview.dashboard.lookml
- dashboard: executive_overview
  title: Executive Overview
  layout: newspaper
  preferred_viewer: dashboards-next

  filters:
  - name: date_range
    title: Date Range
    type: field_filter
    default_value: last 30 days
    allow_multiple_values: true
    required: false
    model: analytics
    explore: orders
    field: orders.created_date

  - name: region
    title: Region
    type: field_filter
    default_value: ""
    allow_multiple_values: true
    required: false
    model: analytics
    explore: orders
    field: users.region

  elements:
  # Revenue tile
  - name: total_revenue
    title: Total Revenue
    model: analytics
    explore: orders
    type: single_value
    fields: [orders.total_revenue]
    filters:
      orders.status: completed
    sorts: [orders.total_revenue desc]
    limit: 500
    listen:
      date_range: orders.created_date
      region: users.region
    row: 0
    col: 0
    width: 6
    height: 4

  # Order count tile
  - name: order_count
    title: Total Orders
    model: analytics
    explore: orders
    type: single_value
    fields: [orders.count]
    listen:
      date_range: orders.created_date
      region: users.region
    row: 0
    col: 6
    width: 6
    height: 4

  # Revenue trend chart
  - name: revenue_trend
    title: Revenue Trend
    model: analytics
    explore: orders
    type: looker_line
    fields: [orders.created_date, orders.total_revenue, orders.count]
    fill_fields: [orders.created_date]
    sorts: [orders.created_date desc]
    limit: 500
    x_axis_gridlines: false
    y_axis_gridlines: true
    show_view_names: false
    show_y_axis_labels: true
    show_y_axis_ticks: true
    y_axis_tick_density: default
    y_axis_tick_density_custom: 5
    show_x_axis_label: true
    show_x_axis_ticks: true
    y_axis_scale_mode: linear
    x_axis_reversed: false
    y_axis_reversed: false
    plot_size_by_field: false
    trellis: ''
    stacking: ''
    limit_displayed_rows: false
    legend_position: center
    point_style: none
    show_value_labels: false
    label_density: 25
    x_axis_scale: auto
    y_axis_combined: true
    show_null_points: true
    interpolation: linear
    y_axes: [{label: Revenue, orientation: left, series: [{axisId: orders.total_revenue,
            id: orders.total_revenue, name: Total Revenue}], showLabels: true, showValues: true,
        unpinAxis: false, tickDensity: default, tickDensityCustom: 5, type: linear}]
    series_colors:
      orders.total_revenue: "#1f77b4"
    listen:
      date_range: orders.created_date
      region: users.region
    row: 4
    col: 0
    width: 12
    height: 6

  # Top products table
  - name: top_products
    title: Top Products
    model: analytics
    explore: orders
    type: looker_grid
    fields: [products.name, order_items.total_quantity, order_items.total_revenue]
    sorts: [order_items.total_revenue desc]
    limit: 10
    show_view_names: false
    show_row_numbers: true
    transpose: false
    truncate_text: true
    hide_totals: false
    hide_row_totals: false
    size_to_fit: true
    table_theme: white
    limit_displayed_rows: false
    enable_conditional_formatting: true
    header_text_alignment: left
    header_font_size: '12'
    rows_font_size: '12'
    conditional_formatting: [{type: along a scale..., value: !!null '', background_color: !!null '',
        font_color: !!null '', color_application: {collection_id: default, palette_id: default-sequential-0},
        bold: false, italic: false, strikethrough: false, fields: [order_items.total_revenue]}]
    listen:
      date_range: orders.created_date
      region: users.region
    row: 10
    col: 0
    width: 12
    height: 6

Access Control and Security

User Attributes:

# Access filters based on user attributes
explore: orders {
  access_filter: {
    field: users.country
    user_attribute: country
  }

  access_filter: {
    field: users.region
    user_attribute: user_region
  }

  # SQL always where with user attribute
  sql_always_where:
    {% if _user_attributes['department'] == 'finance' %}
      ${orders.status} = 'completed'
    {% else %}
      1=1
    {% endif %}
  ;;
}

Field-Level Security:

view: users {
  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
    # Hide from non-admin users
    required_access_grants: [admin_only]
  }

  dimension: ssn {
    type: string
    sql: ${TABLE}.ssn ;;
    # Multiple required grants (AND logic)
    required_access_grants: [admin_only, pii_access]
  }
}

# Define access grants
access_grant: admin_only {
  user_attribute: role
  allowed_values: ["admin", "super_admin"]
}

access_grant: pii_access {
  user_attribute: can_see_pii
  allowed_values: ["yes"]
}

Best Practices

1. View Design

• Use primary keys on all views
• Create dimension groups for dates
• Add descriptions to all fields
• Use value_format_name for consistent formatting
• Hide technical fields from users
• Use drill_fields for exploration paths

2. Explore Design

• Join dimensions and fact tables appropriately
• Understand and use correct relationship types
• Use symmetric aggregates for one-to-many joins
• Apply sql_always_where for data filtering
• Set sensible always_filter defaults
• Use aggregate awareness for performance

3. Performance

• Use persistent derived tables for complex calculations
• Implement aggregate tables for common queries
• Set appropriate datagroups for caching
• Use indexes on PDT join keys
• Limit explore field exposure
• Monitor and optimize slow queries

4. Maintainability

• Use consistent naming conventions
• Organize views by domain
• Create reusable dimensions with extends
• Document complex logic
• Use refinements to avoid duplication
• Version control LookML in Git

5. Governance

• Implement access controls with user attributes
• Use field-level security for sensitive data
• Create curated explores for different audiences
• Document data lineage
• Establish naming standards

Anti-Patterns

1. Symmetric Aggregate Issues

# Bad: Incorrect fanout handling
measure: total_items {
  type: sum
  sql: ${order_items.quantity} ;;  # Will double-count with 1-to-many join
}

# Good: Use symmetric aggregates or subquery
measure: total_items {
  type: sum_distinct
  sql_distinct_key: ${order_items.id} ;;
  sql: ${order_items.quantity} ;;
}

2. Not Using Primary Keys

# Bad: No primary key
view: users {
  dimension: id { type: number }
}

# Good: Define primary key
view: users {
  dimension: id {
    primary_key: yes
    type: number
  }
}

3. Hardcoded Values

# Bad: Hardcoded logic
dimension: is_current_year {
  sql: YEAR(${created_date}) = 2024 ;;
}

# Good: Dynamic logic
dimension: is_current_year {
  sql: YEAR(${created_date}) = YEAR(CURRENT_DATE) ;;
}

4. Missing Descriptions

# Bad: No documentation
dimension: ltv { type: number sql: ${TABLE}.ltv ;; }

# Good: Clear documentation
dimension: ltv {
  type: number
  sql: ${TABLE}.ltv ;;
  label: "Lifetime Value"
  description: "Total revenue from customer over all time"
  value_format_name: usd
}

Resources

• LookML Reference
• Looker Best Practices
• Looker Community
• Looker Discourse
• LookML Validator
• Looker API Documentation
• Looker GitHub