lookml-liquid

name	lookml-liquid
description	Use this skill to use Liquid variables in LookML for dynamic SQL, HTML, and Links, including advanced patterns for query optimization.
license	Apache-2.0
metadata	{"publisher":"google","version":"v1"}

Instructions

1. Syntax:
   • {{ value }}: Output syntax (inserts text).
   • {% if condition %}: Tag syntax (logic).
2. 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.
3. 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

Variable	Definition	Critical Difference (Totals)
_in_query	Returns true if the field is in the SELECT clause, Filters, or required_fields.	Remains true during totals calculation if the field contributed to the query.
_is_selected	Returns true if the field is in the SELECT clause or required_fields.	Returns false during totals calculation (Row/Column/Grand Totals) for dimensions, because dimensions are removed from the query to calculate totals.

[!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)

Variable	Definition	Usage
value	The raw value of the field.	A, H, LI
rendered_value	The formatted value of the field.	A, H, LI
filterable_value	The value formatted for URL filtering.	A, H, LI
link	The default drill link URL.	A, H, LI, S
linked_value	The value with default formatting and linking.	A, H, LI
_filters['view.field']	User filters applied to the field.	A, DE, H, LA, LI
{% date_start filter %}	Start date of a date filter.	S
{% date_end filter %}	End date of a date filter.	S
{% condition filter %} sql {% endcondition %}	Applies filter logic to SQL.	S
{% parameter name %}	Value of a parameter.	DE, LA, S
name._parameter_value	Injects parameter value (safe for logic).	DE, H, LA, LI, S
_user_attributes['name']	User attribute value.	A, DE, H, LA, LI, S, DV, F
_model._name	Model name.	A, DE, H, LA, LI, S
_view._name	View name.	A, DE, H, LA, LI, S
_explore._name	Explore name.	A, DE, H, LA, LI, S
_field._name	Field name.	A, DE, H, LA, LI, S
view._in_query	true if any field from view is queried.	DE, LA, LI, S
view.field._in_query	true if field is in query/filter.	DE, LA, LI, S
view.field._is_selected	true if field is in SELECT.	DE, LA, LI, S
view.field._is_filtered	true if field is filtered.	DE, LA, LI, S

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