Lookml Fields

Instructions

1. Field Types Overview

LookML fields are the building blocks of your data model. Each type serves a specific purpose in generating SQL.

2. The Role of sql Parameter

The sql parameter behaves differently strictly based on the field type.

Dimensions: The "What"

• Role: Defines the raw transformation of the column before any aggregation.
• SQL Context: The expression is placed directly into the GROUP BY clause.
• Input: Can reference table columns (${TABLE}.col), other dimensions (${dim}), or raw SQL functions.
• Example:

  dimension: full_name {
    sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
  }
  -- Generates: CONCAT(table.first_name, ' ', table.last_name)
  

Measures: The "How Much"

• Role: Defines the value to be aggregated or the calculation involving other aggregates.
• SQL Context: Puts the expression inside the aggregation function (e.g., SUM(sql)), or as a standalone calculation for type: number.
• Input:
  • For type: sum/avg/min/max: References dimensions or columns.
  • For type: number: References other measures.
  • For type: count: sql is ignored (always COUNT(*) or COUNT(primary_key)).
• Example:

  measure: total_profit {
    type: sum
    sql: ${sale_price} - ${cost} ;; 
  }
  -- Generates: SUM(sale_price - cost)
  

Filters: The "Which"

• Role: Defines the condition logic, usually for Templated Filters used in Derived Tables or sql_always_where.
• SQL Context: The sql parameter in a filter field is rarely used directly in modern LookML. Instead, the input to the filter is used in {% condition %} tags.
• Best Practice: Identify if you need a filter field or just a parameter + dimension.
• Example (Templated Filter):

  filter: date_filter { type: date }
  -- Usage in Derived Table SQL:
  -- WHERE {% condition date_filter %} created_at {% endcondition %}
  

Parameters: The "User Input"

• Role: Does NOT generate SQL itself. It holds a user-selected value to be injected into other fields.
• SQL Context: Accessed via Liquid variables ({% parameter name %}) inside Dimensions, Measures, or Derived Tables.
• Input: User selects from a UI list or types a value.
• Example:

  parameter: timeframe_selector {
    type: unquoted
    allowed_value: { value: "month" }
    allowed_value: { value: "year" }
  }
  dimension: dynamic_date {
    sql: DATE_TRUNC({% parameter timeframe_selector %}, ${created_raw}) ;;
  }
  

Dimension Groups: The "Time Generator"

• Role: Defines the source timestamp or date column. Looker then generates multiple dimension fields based on the timeframes list.
• SQL Context: Casts and truncates the source column for each timeframe.
• Input: Must be a standardized timestamp or date expression.
• Example:

  dimension_group: created {
    type: time
    timeframes: [date, month]
    sql: ${TABLE}.created_at ;;
  }
  -- Generates:
  -- created_date -> CAST(table.created_at AS DATE)
  -- created_month -> DATE_TRUNC(table.created_at, MONTH)
  

3. Summary of Differences

Reference Skills

For detailed standards on specific field types, refer to:

• Dimensions: Naming, labels, and type-specific rules.
• Measures: Aggregation types, filters, and formats.
• Filters & Parameters: Templated filters and user input.
• Dimension Groups: Timeframes and intervals.
• Value Formats: Named and custom currency/number formats.