Parameterizing SQL queries in Looker means replacing hard-coded values in SQL with dynamic variables—controlled by the user, filters, or user attributes—using LookML’s Liquid templating and field-level parameters.
In Looker, parameterization is the practice of making parts of a SQL statement dynamic so that the same LookML definition can answer many questions without being edited every time the input changes. Instead of hard-coding a date range, customer ID, or currency, you expose that value as a parameter that is substituted at query-run time.
Looker supports parameterization primarily through three mechanisms:
{{ }}
syntax for variables and {% %}
blocks for logic.parameter
fields — special LookML fields users control in the Explore UI.Parameterizing SQL in Looker:
Looker embeds Liquid, a lightweight templating language. You can:
{{view_name.field_name}}
{% if condition %}...{% endif %}
{{ _user_attributes['region'] }}
or {{ _filters['orders.created_date'] }}
Example:
SELECT *
FROM orders
WHERE orders.status = {% parameter order_status %}
parameter
Fields in LookMLA parameter
field lets end users pick a value in the Explore UI that your SQL references.
parameter: order_status {
type: string
allowed_value: { value: "pending" }
allowed_value: { value: "shipped" }
allowed_value: { value: "returned" }
}
measure: orders_count {
type: count
sql: ${TABLE}.id ;; # notice double semi-colon for multiline SQL
filters:
order_status: {% parameter order_status %}
}
The filters
block makes the parameter available in the generated SQL behind the scenes. When a user selects shipped, Looker renders:
... WHERE orders.status = 'shipped'
User attributes are named variables assigned per user, group, or default. They are ideal for data-level security or local preferences.
dimension: user_region {
sql: {{ _user_attributes['region'] | sql_quote }} ;;
}
| sql_quote
safely quotes the value, preventing SQL injection.
_filters
)When you need to reference the value of a normal Explore filter inside a derived table or native derived table (NDT), use the special _filters
map.
SELECT *
FROM orders
WHERE {% condition orders.created_date %}
{{ _filters['orders.created_date'] }}
{% endcondition %}
This renders the same filter conditions the user chooses in the Explore.
orders.view
LookML.view: orders {
sql_table_name: analytics.orders ;;
parameter: date_grain {
type: string
default_value: "day"
allowed_value: { value: "day" }
allowed_value: { value: "week" }
allowed_value: { value: "month" }
}
dimension: order_date_truncated {
hidden: yes
sql:
CASE
WHEN {% parameter date_grain %} = 'week' THEN DATE_TRUNC('week', ${TABLE}.created_at)
WHEN {% parameter date_grain %} = 'month' THEN DATE_TRUNC('month', ${TABLE}.created_at)
ELSE DATE_TRUNC('day', ${TABLE}.created_at)
END ;;
}
measure: orders_count {
type: count
}
}
When a user switches date_grain from “day” to “month,” the generated SQL flips to DATE_TRUNC('month', created_at)
without changing LookML.
sql_quote
or single quotes to mitigate injection.allowed_value
lists so users cannot submit arbitrary SQL.{% bind %}
for DB-level bind parameters in dialects that support prepared statements (e.g., Postgres, Snowflake).While Galaxy is not a BI tool like Looker, it's a modern SQL editor that also supports variable substitution in saved queries and offers an AI copilot to suggest safe parameterization patterns. If you prototype a complex, parameterized SQL in Galaxy first (using :$variable
syntax or templating), you can later migrate that logic into LookML with confidence, reducing iteration time and keeping your analytics stack consistent.
Why it’s wrong: Unquoted strings break SQL or let malicious input through.
Fix: Wrap the parameter in single quotes or use | sql_quote
.
WHERE orders.country = {{ parameter_value }} -- risky
WHERE orders.country = '{{ parameter_value }}' -- safe
Why it’s wrong: Any user can change them, bypassing row-level rules.
Fix: Implement security via access_filter
+ user attributes instead.
Why it’s wrong: Some dialects can’t cache statements if you inline values.
Fix: Use {% bind parameter_name %}
or the Parameterized SQL option in Connection settings so Looker sends bind variables, not literals.
Parameterizing SQL queries in Looker unlocks flexible, reusable, and secure data models. By mastering Liquid templating, parameter
fields, and user attributes, you empower analysts and end users to answer new questions without editing code. Follow the best practices and avoid the common pitfalls outlined here to keep your Looker project clean, performant, and future-proof.
Parameterization makes Looker models flexible, reusable, and secure. It lets analysts change filters and logic via the UI instead of editing LookML, reducing maintenance and error rates while enabling database statement caching and consistent governance.
parameter
field?It’s a special field type that captures a user-supplied value in the Explore UI and makes it available in your SQL via the {% parameter param_name %}
Liquid tag.
Yes. Use the _filters
Liquid map, e.g., {{ _filters['orders.created_date'] }}
, to inject the user’s filter condition into your SQL.
User attributes are assigned by admins and can’t be changed in Explore, making them ideal for security filters. Parameters are controlled by end users for ad-hoc analysis.
Galaxy supports variable substitution in the SQL editor (e.g., :$variable
) but uses standard SQL placeholders, not Looker’s Liquid. You can prototype logic in Galaxy before porting it to LookML.