How To Parameterize SQL Queries in Looker

Galaxy Glossary

How do I parameterize SQL queries in Looker?

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.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Description

Overview of Parameterization in Looker

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:

  • Liquid templating — using {{ }} syntax for variables and {% %} blocks for logic.
  • parameter fields — special LookML fields users control in the Explore UI.
  • User attributes — session-level variables defined by admins and referenced in LookML.

Why Parameterization Matters

Parameterizing SQL in Looker:

  • Improves reusability — One LookML view can serve many reports.
  • Reduces maintenance — You change logic once instead of in every dashboard tile.
  • Protects performance — Parameters leverage bind variables, enabling statement caching on the database and limiting SQL injection vectors.
  • Enhances governance — Centralized definitions ensure that filters, privacy rules, and access controls are enforced consistently.

Core Concepts and Syntax

1. Liquid Templating Basics

Looker embeds Liquid, a lightweight templating language. You can:

  • Interpolate variables: {{view_name.field_name}}
  • Run logic: {% if condition %}...{% endif %}
  • Access built-in helpers like {{ _user_attributes['region'] }} or {{ _filters['orders.created_date'] }}

Example:

SELECT *
FROM orders
WHERE orders.status = {% parameter order_status %}

2. parameter Fields in LookML

A 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'

3. User Attributes

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.

4. Templated Filters (_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.

Step-by-Step Example

  1. Create a parameter field in your orders.view LookML.
  2. Reference the parameter in your measure or dimension SQL using Liquid.
  3. Expose the field in an Explore so users can pick the value.
  4. Run the Explore; Looker substitutes the user’s choice into the generated SQL.

Full Working Example

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.

Best Practices

  • Always quote strings with sql_quote or single quotes to mitigate injection.
  • Whitelist allowed values via allowed_value lists so users cannot submit arbitrary SQL.
  • Prefer user attributes for security filters—parameters can be changed by any user with Explore access.
  • Use {% bind %} for DB-level bind parameters in dialects that support prepared statements (e.g., Postgres, Snowflake).
  • Avoid logic creep: keep complex Liquid in derived tables or data tests, not everywhere.

How Galaxy Fits In

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.

Common Mistakes & Fixes

Mistake 1: Forgetting to Quote String Parameters

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

Mistake 2: Using Parameters for Security-Critical Filters

Why it’s wrong: Any user can change them, bypassing row-level rules.

Fix: Implement security via access_filter + user attributes instead.

Mistake 3: Ignoring Database Bind Support

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.

Conclusion

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.

Why How To Parameterize SQL Queries in Looker is important

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.

How To Parameterize SQL Queries in Looker Example Usage


Use a <code>date_grain</code> parameter to switch between daily and monthly order counts without rewriting your SQL.

How To Parameterize SQL Queries in Looker Syntax



Common Mistakes

Frequently Asked Questions (FAQs)

What is a LookML 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.

Can I reference normal Explore filters inside a derived table?

Yes. Use the _filters Liquid map, e.g., {{ _filters['orders.created_date'] }}, to inject the user’s filter condition into your SQL.

How do user attributes differ from parameters?

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.

Does Galaxy support the same parameter syntax?

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.

Want to learn about other SQL terms?