How To: Smart Partition Key Filtering with Liquid

[jamesnestler]

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:

• Liquid stops at the first true in an if elif else block
• Each table needs to be a DISTINCT view file. The Liquid view_name._in_query variable looks at the actual view name, not the join name
• This solution assumes your partition keys are in a user_attribute. If you have a different setup, adjust the code block accordingly
• Add/Remove {% if table… %} blocks as needed.

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
;;
}

[jamesnestler]

A cleaner way (with no need for any Liquid `._in_query` or `._is_filtered` stuff would be to leverage the join level `sql_where` LookML parameter. 

 

# in a view file

filter: date_fil {}

# in your model file

explore: my_explore {

view_name: table1

sql_always_where: {% condition view_name.date_fil %} date_field_table1 {% endcondition %} ;;

join: table2 {

view_name: table2

sql_where: {% condition view_name.date_fil %} date_field_table2 {% endcondition %} ;;