lookml-liquid

Instructions

1. Syntax:

• {{ value }}: Output syntax (inserts text).
• {% if condition %}: Tag syntax (logic).

1. Common Variables:

• value: Raw value from DB (best for comparisons).
• rendered_value: Formatted value (best for display).
• _filters['view.field']: User-selected filter values.
• parameter_name._parameter_value: Selected parameter value.

1. Best Practices:

• SQL Injection: Always use | sql_quote when inserting user input (like _filters) into SQL that generates string literals.
• Booleans: If your dialect requires literal TRUE/FALSE (like BigQuery), append | sql_boolean to _in_query or _is_selected variables (e.g., {{ view.field._in_query | sql_boolean }}).
• Dependency Awareness: Remember that _in_query checks for usage in SELECT, Filters, and `required_fields`. It is NOT limited to just the visible columns.
• Performance: Avoid referencing {{ field._value }} in link parameters if the field isn't already in the query, as this forces Looker to add the field to the GROUP BY clause, potentially fan-outing the result set. Use row['view.field'] instead if you only need the value from the browser result row.

Advanced Variable Usage

_in_query vs _is_selected

[!WARNING] If you use _is_selected to conditionally render logic for a dimension, that logic will fail (return false) in the Totals row. Use _in_query if you need the logic to persist in totals, or explicitly handle the false state for totals if that is the desired behavior.

Liquid Variable Definitions

The following table describes the Liquid variables that you can use with LookML and where they can be used.

Usage Key:

• A: action
• DV: default_value (dashboards)
• DE: description
• F: filters (dashboard elements)
• H: html
• LA: Label parameters (label, view_label, group_label, group_item_label)
• LI: link
• S: SQL parameters (sql, sql_on, sql_table_name)

Advanced Use Cases

1. Aggregate Awareness (Dynamic Table Selection)

Use _in_query to route queries to smaller, pre-aggregated tables when the user doesn't request granular details. This significantly improves query performance.

view: orders {
  sql_table_name:
    {% if orders.created_date._in_query or orders.created_hour._in_query %}
      orders_daily_summary  -- Fallback to daily partition if granular date used
    {% elsif orders.created_month._in_query %}
      orders_monthly_summary -- Use monthly summary for high-level queries
    {% else %}
      orders_all_transactions -- Default/Detail table
    {% endif %} ;;
}

2. Dynamic Joins (sql_on)

Use _in_query in joins to avoid joining heavy tables unless they are actually required by the user's selection.

explore: order_items {
  join: users {
    type: left_outer
    sql_on: ${order_items.user_id} = ${users.id} ;;
    relationship: many_to_one
  }

  join: user_facts {
    type: left_outer
    sql_on: ${users.id} = ${user_facts.user_id} ;;
    relationship: one_to_one
    # Only join user_facts if a field from it is actually selected/filtered
    sql_where: {% if user_facts._in_query %} 1=1 {% else %} 1=0 {% endif %} ;;
  }
}

Note: The `sql_where` trick is one way to force a join drop in some dialects, but standard `sql_on` logic with `{% if %}` is cleaner if supported.

3. Column-Specific Logic (Dynamic Denominator)

Change a calculation based on what other fields are present in the query.

measure: dynamic_rate {
  type: number
  sql:
    {% if users.traffic_source._is_selected %}
      ${total_revenue} / NULLIF(${traffic_source_count}, 0)
    {% else %}
      ${total_revenue} / NULLIF(${total_users}, 0)
    {% endif %} ;;
}

Examples

Dynamic HTML (Conditional Formatting)

dimension: status {
  html:
    {% if value == 'complete' %}
      <span style="color: green">{{ rendered_value }}</span>
    {% else %}
      <span style="color: red">{{ rendered_value }}</span>
    {% endif %} ;;
}

Templated Filters (Derived Table)

view: customer_facts {
  derived_table: {
    sql:
      SELECT customer_id, SUM(amount)
      FROM orders
      WHERE {% condition order_date %} created_at {% endcondition %}
      GROUP BY 1 ;;
  }
}

Complex Logic (Loops & Split)

LookML Liquid can handle string manipulation and loops, which is useful for parsing complex filter parameters or unnesting values. This is used infrequently, but very handy in complex modeling tasks.

view: brand_category_item {
  parameter: filter { type: unquoted }
}

explore: complex_filter_parsing {
  # Example: Parsing a string like "Brand1..Category1__Brand2..Category2"
  # This logic splits the string by '__' then '..' to generate OR conditions
  sql_where:
    {% assign items = brand_category_item.filter._parameter_value | split: '__' %}
    {% for item in items %}
      {% assign parts = item | split: '..' %}
      {% if forloop.first %} ( {% else %} OR ( {% endif %}
        ${products.brand} = '{{ parts[0] }}' AND ${products.category} = '{{ parts[1] }}'
      )
    {% endfor %}
    {% if items.size == 0 %} 1=1 {% endif %}
  ;;
}