How do access_filter, sql_always_where, full_suggestions, and bypass_suggest_restrictions work with filter suggestions? (Community)

View the original community article here

Last tested: Sep 1, 2020
 

To understand how these parameters interact with filter suggestions, it's important to understand the full_suggestions parameter first. Here is the principle to remember:

• full_suggestions: no means the filter suggestion query will run against the view.
• full_suggestions: yes means the filter suggestion query will run against the explore.

For a basic explore, the default value for full_suggestions is no. This is intended to make filter suggestions more performant, since the query can run against a single table without any joins, without using any of the logic defined in the explore. However, you can manually set full_suggestions to yes instead, and the query will use the logic of the explore that you are in, along with any of the joins that are required to query that dimension in the explore.

Sql_always_where, ALWAYS_JOIN, and access_filter

When the explore has the sql_always_where, always_join, and/or access_filter parameters, the default value of full_suggestions switches to yes. This will allow the query to run using the explore logic, which means that the access_filter or sql_always_where will be applied to narrow the suggestions that come back. The assumption is that since access_filter and sql_always_where will always be added to the WHERE clause, and the user cannot remove them, they are typically used for data access, which means admins will want the suggestions to be filtered in the same way as the explore results.

If you manually set full_suggestions to no, the filter suggestion query will not run. Again, because these parameters are used for data access, the assumption is that running the query against just the view (which would not use the sql_always_where or access_filter parameters) would be a data security risk, so we do not allow the query to work at all.

bypass_suggest_restrictions

If you also have the bypass_suggest_restrictions parameter in addition to the sql_always_where, always_join, and/or access_filter parameter, the default value of full_suggestions switches back to no. Since the express purpose of the bypass_suggest_restrictions parameter is to override the restrictions from sql_always_where and access_filter, the query is allowed to run against just the view. If you manually set full_suggestions to yes, the query will use the logic of the explore—the same as if you had set only sql_always_where and/or access_filter without setting bypass_suggest_restrictions or full_suggestions.

other cases

The sql_always_where, always_join and access_filter parameters are the ONLY parameters that change how full_suggestions behaves. In all other cases, the default value of full_suggestions will still be no. This includes the following cases:

• sql_where: Even if sql_where is used on the join where the dimension you're filtering on comes from, the filter suggestions will still pull from the view and ignore everything defined in the explore. Therefore, it will not use the sql_where.
• {{_user_attributes['attribute_name']}}: If you use this liquid variable in any parameters at the explore level (other than sql_always_where) the user attribute will not be applied to the suggestion query. This includes always_filter, sql_on of a join, etc. However, if you use the liquid variable at the view level, such as in a derived table, the filter suggestion query WILL use it.
• Nested fields in BigQuery: The UNNESTs are defined in the sql parameters for the join, so the filter suggestions will not take them into account.

Bottom line: if you want the filter suggestion query to use the logic defined in the explore, you must manually set full_suggestions to yes.