Query filters

  • Article

A query filter lets you interactively reduce the amount of data shown in a visualization. Query filters are similar to query parameter but with a few key differences. A query filter limits data after the query has been executed. This makes filters ideal for smaller datasets and environments where query executions are time-consuming, rate-limited, or costly.

The following describes some benefits of Databricks SQL.

  • While previous query filters operated client-side only, these updated filters work dynamically on either client- or server-side to optimize performance.
  • Simplified UI experience: click the +Add filter button and select a column from a dropdown to add a filter. You don't need to author, permission, and refresh a separate query in order to filter on the distinct values of a column.
  • Enable "highlight relevant values" to see which selections within a filter will return results given other filter selections. For example, consider a user who has both a "State" and "City" filter. If a user chooses to highlight relevant values, selecting "California" in the state filter will highlight only the cities in California in the "City" filter. Non-highlighted options are put under a "Filtered out" menu option in the dropdown.
  • Text Input filters: filters column results based on text input searches. There are three modes the search can find matches with: exact match, contains, and starts with.
  • Quick date selectors enable you to filter on predefined date ranges such as last week, last month, last year, and more.
  • You can set default date ranges when creating date filters.
  • You can also use query filters on dashboards. By default, the filter widget appears beside each visualization where the filter has been added to the query. To link together the filter widgets into a dashboard-level query filter see Dashboard filters.

Description of functionality

After running a query, in the Results panel, click + and then select Filter.

The +Add filter button opens a popup menu where you can apply the following filters and settings.

  • Column: the column on which to apply the filter.
    • Strings, numbers, and dates are currently supported.
    • If the selected column contains dates, users can choose to specify a time binning by date, month, or year.
  • Type: the type of filter to apply
    • Single Select: filter to one field value only
    • Multi Select: filter to multiple field values
    • Text Input: enter a string to search for matching values in a particular column. Supports "Contains," "Exact Match," and "Starts With"
    • Date / time picker and ranger picker: ability to select a date or date range to filter on date data types
  • Sort Order: The order in which values are sorted for the filter dropdown. You can choose between "Ascending" or "Descending."
  • Highlight relevant values (y/n): Enable this to easily see which selections within a filter will return results given other filter selections. For example, consider a user who has both a "State" and "City" filter. If a user chooses to highlight relevant values, selecting "California" in the state filter will highlight the set of options available in the "City" filter to only show cities in California, while non-highlighted options will be put under a "Filtered out" menu option in the dropdown. Note that this requires running a query each time a filter is updated.
  • Default value: When a "date" type column is selected and a time-binned value is chosen (days, months, or years), users can also choose to set a default date range for the filter. The default filter is automatically applied whenever the query is refreshed.

Limitations

  • It is important to note that query filters are applied over the entirety of the dataset. However, the dropdown selector for query filters is limited to 64k unique values. If a user wishes to filter in situations where there are more than 64k unique filter values, it is recommended to use a Text parameter instead.
  • Filters can only be applied to columns returned by a query, not all columns of a referenced table.
  • Filters display the distinct list of options from the designated column in returned results. If the results are limited (i.e. query run with Limit 1000), then a filter will only display unique values from within those 1000 results.
  • While filters applied to a query will optimize to run on either the client or server side for better performance, filters applied to a dashboard will always run on the server side.