Problem: We have multiple partitioned tables, and are joining them into an explore. To improve query performance, we always want to filter on the partition key of each table (to make use of all partitions). However, we won’t always be joining all tables, so an access_filter or vanilla sql_always_where won’t cut it.
Solution 1: Use Liquid + sql_always_where! Sample code & gotchas below.
The Gotchas:
sql_always_where:
-- always filter for the base table
${table_a.partition_key} = {{ _user_attributes['partition_key_value'] }} AND
-- start control flow block
{% if table_b._in_query %}
${table_b.partition_key} = {{ _user_attributes['partition_key_value'] }} AND
{% endif %}
{% if table_c._in_query %}
${table_c.partition_key} = {{ _user_attributes['partition_key_value'] }} AND
{% endif %}
{% if table_d._in_query%}
${table_d.partition_key} = {{ _user_attributes['partition_key_value'] }} AND
{% endif %}
-- gracefully exit
{% if true %}
1=1
{% endif %};;
Solution 2
What if partition keys aren’t in user attributes? We could use filters with Liquid to achieve similar logic.
# in one of our view files
filter: partition_fil {
type: date/string/number
}
# in our explore
sql_always_where:
-- always filter on the base table, but we're going to always filter
-- for something even if the user has not selected a filter value
{% if view_a.partition_fil._is_filtered %}
{% condition view_a.partition_fil %} ${view_a.partition_column} {% endcondition %}
{% else%}
${view_a.partition_column} = 'default condition'
{% endif %}
AND
-- control flow block for view_b
{% if view_b._in_query and view_a.partition_fil._is_filtered %}
{% condition view_a.partition_fil %} ${view_b.partition_column} {% endcondition %}
{% elsif view_b._in_query %}
${view_b.partition_column} = 'default condition'
{% else %}
1=1
{% endif %}
-- add more if blocks for each joined table
;;
}