What you will be able to do
- Write a query that uses colon-prefixed named parameter markers instead of hard-coded values
- Define a parameter in a dashboard dataset and set its keyword, display name and type
- Choose the right parameter type and widget type for a SQL editor parameter
- Configure dynamic dropdown and date range widgets, and know their limits
- Convert legacy mustache {{ }} parameters to named parameter markers
Key concept
Named parameter marker — A typed placeholder written as a colon followed by a name (for example :segment) that stands in for a value in a SQL query. Databricks shows a widget for it, and the value is supplied when the query runs rather than hard-coded in the SQL text.
1.From hard-coded values to typed placeholders
A query like WHERE fare_amount < 5 answers exactly one question. To let someone ask about a different fare, you replace the literal with a named parameter marker: delete the 5, type :fare_parameter, click the gear icon next to the widget that appears, set the type to Decimal, enter a value, and click Apply changes. Databricks names three benefits: the query is easier to reuse, it is protected against SQL injection, and it is easier to build interactive queries. The SQL reference explains the injection point: a parameter marker keeps the supplied value separate from the structure of the SQL statement.
The same colon syntax works in the SQL editor (new and legacy), notebooks, the AI/BI dashboard dataset editor, and Genie Agents. That makes it the one syntax worth learning first. You can reference the same marker more than once in a statement. If a marker has no value bound to it, the query fails with an UNBOUND_SQL_PARAMETER error.
Defining a parameter in a dashboard dataset. You need at least CAN EDIT permission on the draft dashboard to add a parameter to a dataset, and you do it in the dataset query on the Data tab. Place the cursor where the parameter belongs and click Add parameter. This inserts a parameter with the default name parameter. Replace that name in the query editor to rename it. You can also define one by simply typing the :name syntax into the query.
To configure it, open the Parameter details dialog next to the parameter name. It has these options:
- Keyword: the keyword that represents the parameter in the query. You change it only by editing the query text. - Display name: the name shown in the filter editor. It defaults to the keyword. - Type: String, Date, Date and Time, or Numeric. The default is String. Numeric lets you choose Decimal or Integer, and Decimal is the default. - Allow multiple selections: lets users choose several values at runtime. This might also require a change to your query.
To test the definition, type a default value in the text field under the parameter name and run the query. Running it applies the value so you can preview the results, and it also saves the default.
| Type | Behaviour |
|---|---|
| String | Free-form text; quotes and backslashes escaped; Databricks adds quotation marks |
| Integer | Whole number value |
| Decimal | Numeric value that supports fractional values |
| Date | Calendar picker; defaults to the current date |
| Timestamp | Calendar picker; defaults to the current date and time |
A parameter value is a value, not a piece of SQL text, so you can't drop a marker in where a column or table name belongs. To make an object name a parameter, wrap the marker in IDENTIFIER. For a table, use the full three-level namespace, or build that name from separate catalog, schema and table parameters joined with ||. Other documented patterns include DATE_TRUNC(:date_granularity, ...), which lets the user choose DAY, MONTH or YEAR, and CAST(:param AS INTERVAL MINUTE) for time arithmetic.
SELECT * FROM samples.tpch.orders
WHERE IDENTIFIER(:field_param) < 10000Checkpoint 1 of 5· Check yourself
You want users to pick which column of samples.tpch.orders is compared against 10000. Which WHERE clause does that?
A bare marker is treated as a value, so it would compare a string literal against 10000. IDENTIFIER makes Databricks read the parameter value as a column name.
“Use the IDENTIFIER function to pass a column name as a parameter.”Source: docs.databricks.com
2.Configuring the parameter widget
Each named marker gets a widget in the SQL editor. Click the gear icon beside it to set four fields: Parameter name (as it appears in the query), Widget label, Widget type (how users enter the value) and Parameter type (the data type). Click away from the dialog to save. The name and the query text are tied together, so if you rename the parameter in the dialog, you also have to rename it in the query. To remove a widget, delete the marker from the query and the widget disappears with it. To reorder widgets, drag the handle to the left of each one.
In the SQL editor, parameter type and widget type are independent: any parameter type (String, Integer, Decimal, Date, Timestamp) can use any widget type. The widget type decides how much freedom the user has.
| Widget type | What the user can do |
|---|---|
| Text input | Enter any free-form value, with no suggestions |
| Dropdown | Choose only from a predefined list you enter in the settings pane |
| Combobox | Choose from a predefined list or type a custom value |
| Multiselect | Select more than one value; the values reach the query as a collection |
| Dynamic dropdown | Choose from values returned by a saved query |
| Date and Timestamp range | Set a start and end, exposed as .min and .max |
For multiselect in the SQL editor, the documented example uses a String parameter. SPLIT breaks the comma-separated string into an array, TRANSFORM trims each element, and ARRAY_CONTAINS checks whether the row's value is in that array. The example works for string values. For other data types, wrap the TRANSFORM in a CAST.
Checkpoint 2 of 5· Match them up
Match each requirement to the SQL editor widget type that fits it
Tap a term, then the definition that fits it.
Dropdown allows only listed values and Combobox also accepts typed values. Multiselect passes a collection of values, and Dynamic dropdown reads its choices from a saved query.
“Presents a predefined list of suggested values but also allows users to type a custom value not in the list.”Source: docs.databricks.com
Sources4
3.Dynamic dropdowns and date ranges
A static dropdown goes stale when the data changes. A dynamic dropdown reads its choices from a saved query instead, so the options update automatically as the underlying data changes. It has two limits: it is available in the SQL editor but not in notebooks, and it displays at most 1,024 values. If the saved query returns more than that, the widget shows the first 1,024 and drops the rest.
Checkpoint 3 of 5· Put it in order
Put the steps for setting up a dynamic dropdown in order
- 1.Choose a default parameter value and click Apply Changes
- 2.Create and save a query that returns the values you want, such as SELECT DISTINCT c_mktsegment
- 3.Click the Query field and select the saved query
- 4.Click the gear icon beside the segment_param widget and set Widget type to Dynamic dropdown
- 5.Add a named parameter marker like :segment_param to the query you want to filter
The source query has to exist and be saved before the widget can point to it, and the default value is set last, once the choices are available.
“Populates the list of choices from a saved query instead of a static list.”Source: docs.databricks.com
Date and Timestamp parameters support a Range widget type. When you select it, one marker becomes two values, accessed with the .min and .max suffixes. The blue lightning bolt icon lets users choose dynamic values such as today, last week or last month, which update automatically. Don't use those dynamic values in a query you plan to schedule: they are not compatible with scheduled queries.
SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN :date_range.min AND :date_range.maxSources4
4.Legacy mustache syntax and how to convert it
Older queries wrap parameters in double curly braces, such as {{ date_param }}. This mustache syntax works only in the legacy SQL editor, and Databricks recommends named markers for new queries. If you copy a mustache query into a notebook, an AI/BI dashboard dataset or a Genie Agent, you have to convert it before it will run. Two differences catch people out. First, mustache date values are string literals, so you wrap them in single quotes yourself. Second, a mustache date range uses .start and .end suffixes, not .min and .max. Mustache multi-value dropdowns use IN ( {{ status_param }} ) with a Quotation setting. Named-marker multiselect uses ARRAY_CONTAINS instead.
| Use case | Mustache syntax | Named parameter syntax |
|---|---|---|
| Filter by date | WHERE date_field < '{{date_param}}' | WHERE date_field < :date_param |
| Filter by number | WHERE price < {{max_price}} | WHERE price < :max_price |
| Compare strings | WHERE region = '{{region_param}}' | WHERE region = :region_param |
| Specify a table | SELECT * FROM {{table_name}} | SELECT * FROM IDENTIFIER(:table) |
Checkpoint 4 of 5· Check yourself
A colleague pastes SELECT * FROM orders WHERE price < {{max_price}} into an AI/BI dashboard dataset. What do you tell them?
Mustache syntax is supported only in the legacy SQL editor. Queries moved to a dashboard dataset have to use named parameter markers.
“Mustache parameter syntax is supported in the legacy SQL editor only.”Source: docs.databricks.com
Checkpoint 5 of 5· Exam question
An analyst is editing a dataset query for an AI/BI dashboard and wants to filter orders by a region that a viewer will choose later. Which syntax correctly defines a named parameter inside the SQL query for this purpose? SELECT * FROM sales.orders WHERE region = ___
Correct answer: A — :region
- A. The named colon marker is the syntax that AI/BI dashboard datasets recognize for substituting a runtime value into a query, and the query editor exposes an `Add parameter` action tied to this exact form. Selecting it lets the analyst configure a display name, type, and default value for the region filter.
- B. Mustache-style double-brace substitution is a legacy Databricks SQL query syntax and is explicitly not supported for dashboard dataset parameters, so a query written this way will not surface a configurable parameter widget on the dashboard.
- C. An at-sign prefix is reserved for referencing an already-defined parameter's current value inside dashboard text widgets, not for declaring a new parameter inside a SQL WHERE clause.
- D. A bare dollar-sign prefix is not a recognized marker for AI/BI dashboard dataset parameters and would be treated as invalid or literal text rather than creating a substitutable value.
Sources5
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Every Databricks date range parameter is read with .min and .max.Why is that wrong?
Named markers use .min and .max, but legacy mustache ranges use .start and .end, wrapped in single quotes.
Covered in Legacy mustache syntax and how to convert it
2.A dynamic date value like 'last week' is the right way to keep a scheduled query current.Why is that wrong?
Dynamic date values update automatically, but they are not compatible with scheduled queries.
Covered in Dynamic dropdowns and date ranges
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“This improves query reuse, prevents SQL injection, and makes it easier to build flexible, interactive queries.”
↩︎ From hard-coded values to typed placeholders“Insert a parameter by typing a colon followed by a parameter name, such as :parameter_name.”
↩︎ Key concept“Backslash, single, and double quotation marks are escaped automatically. Databricks adds quotation marks around the value.”
↩︎ Prediction“Use the IDENTIFIER function to pass a column name as a parameter.”
↩︎ Checkpoint - 2.
“If no value has been bound to the parameter marker an UNBOUND_SQL_PARAMETER error is raised.”
↩︎ From hard-coded values to typed placeholders - 3.
“You must have at least CAN EDIT permissions on the draft dashboard to add a parameter to a dashboard dataset.”
↩︎ From hard-coded values to typed placeholders“This creates a new parameter with the default name parameter.”
↩︎ From hard-coded values to typed placeholders“The keyword that represents the parameter in the query.”
↩︎ From hard-coded values to typed placeholders - 4.
“In the SQL editor, any parameter type (String, Integer, Decimal, Date, Timestamp) can use any widget type.”
↩︎ Configuring the parameter widget“If you change the parameter name, in the widget dialog, you must also change it in the query.”
↩︎ Configuring the parameter widget“A dynamic dropdown displays a maximum of 1,024 values.”
↩︎ Dynamic dropdowns and date ranges“Dynamic dropdown widgets are available in the SQL editor only, not in notebooks.”
↩︎ Dynamic dropdowns and date ranges“Dynamic date values are not compatible with scheduled queries.”
↩︎ Exam trap 2“Presents a predefined list of suggested values but also allows users to type a custom value not in the list.”
↩︎ Checkpoint“Populates the list of choices from a saved query instead of a static list.”
↩︎ Checkpoint - 5.
“you must convert it to named parameter markers before it will run.”
↩︎ Legacy mustache syntax and how to convert it“When you select a Range option, Databricks creates two parameters using .start and .end suffixes:”
↩︎ Exam trap 1“Mustache parameter syntax is supported in the legacy SQL editor only.”
↩︎ Checkpoint