Derived tables are a common use case for applying templated filters. This is because templated filters allow us to restrict tables to certain criteria prior to building them.
This approach is particularly useful in columnar databases, as they have a single sort order. Thus, it can be more efficient to isolate a subset of data from a large table as a joined subquery, and transform the subset on the fly. With this method, we can avoid joining in the entire large table.
A great example use case of templated filters in derived tables is performing a dynamic cohort analysis, as detailed in the Help Center article [Analytic Block] Creating Custom Cohort Analysis.
Referencing Dynamic Derived Table Views Correctly
In Looker, Explores and joins must reference the view name that is defined in the underlying view file's view
parameter.
This is especially important when creating Explores or joins with derived tables that leverage templated filters.
If the correct view name is not referenced, an error message will appear, claiming that the templated filter variable {% condition filter_name %}
does not exist (even when it is defined properly in LookML):
The most common cause of this error is improper aliasing of Explores with the from
parameter. This is because from
changes not only the Explore name, but the fields in the underlying view as well. Fields are recognized as belonging to the aliased Explore, rather than the underlying view.
Why Exactly Does this Error Occur?
Although a derived table takes the name in the view
parameter, this name does not affect how the SQL definition of the table is processed.
When Looker builds a derived table, it first parses the templated filters into SQL, and then builds the table using appropriate filter values. This occurs prior to checking the name of the view.
Because of this, it's possible for the view name at the time it's checked not to match the view name at the time the SQL was parsed (for example, when the Explore/join is aliased). In such a case, Looker may believe that templated filters exist under one view name when they actually exist under another — in this example, a view name that doesn't exist in this Explore.
Let's use the following as an example:
Note: Best practice is to separate the view into a separate view file, and place the explore into a model file.
###### EXPLORE ###### explore: test { from: derived_table_test } ###### VIEW ###### view: derived_table_test { derived_table: { sql: SELECT id_ , status , created_at FROM orders WHERE {% condition status %} orders.status {% endcondition %} AND {% condition created_date %} orders.created_at {% endcondition %} ;; } filter: status { type: string suggestions: ["Complete", "Pending", "Cancelled"] } dimension_group: created { type: time timeframes: [date, week, month, year] sql: ${TABLE}.created_at ;; } }
When Looker parses the SQL for the derived table, it assumes the fields derived_table_test.status
and derived_table_test.created_at
are front-end filters in the UI. Looker assumes this because of the {% condition filter_name %}
variables referencing these fields in the WHERE
clause.
However, since the from
parameter is used to alias the Explore as test
, all fields in derived_table_test
will be referenced from test
. For example, test.status
and test.created_date
.
Thus, the conditions {% condition status %}
and {% condition created_date %}
, which Looker believes are a part of the derived_table_test
view, become floating conditions; they have no home.
Solution 1: extends
There are two scenarios where you may want to use from
in an Explore or join:
- To change the name of the underlying view when it is used as an Explore.
- To alias the name of the view when it is used in a join (most common use case is joining one table more than once in an Explore).
For changing the name of the underlying view used in an Explore, the recommended method for aliasing is to use a combination of view_name
and label
. This will allow us to completely avoid the variable not found
error:
explore: some_name {
view_name: actual_view_name
label: "Pretty Name"
}
If we absolutely must use from
to alias an Explore or a view at the join level (for example, when joining the same table more than once in an Explore), we can resolve the variable not found
error with an extends
parameter. In this case, extends
can be leveraged to copy the underlying view definitions and be renamed the aliased join name.
For example, let's say we have named our dynamic derived table view derived_table_test
and want to join it more than once in an Explore. We can create a new view file (called derived
below) and create a copy of derived_table_test
with the new view name using extends
for views:
view: derived {
extends: [derived_table_test]
}
Next, join the extended view derived
:
join: derived_table_test {
sql_on: ${derived_table_test.id} = ${test.id} ;;
}
join: derived {
sql_on: ${derived.id_2} = ${test.id} ;;
}
Here is everything together:
Note: Best practice is to separate the view and extended view into separate view files, and place the Explore into a model file.
###### EXPLORE ###### explore: test { join: derived_table_test { sql_on: ${derived_table_test.id} = ${test.id} ;; } join: derived { sql_on: ${derived.id_2} = ${test.id} ;; } } ###### EXTENDED VIEW ###### view: derived { extends: [derived_table_test] } ###### VIEW ###### view: derived_table_test { derived_table: { sql: SELECT id , status , created_at FROM orders WHERE {% condition status %} orders.status {% endcondition %} AND {% condition created_date %} orders.created_at {% endcondition %} ;; } filter: status { type: string suggestions: ["Complete", "Pending", "Cancelled"] } dimension_group: created { type: time timeframes: [date, week, month, year] sql: ${TABLE}.created_at ;; } } }
Solution 2: Scope Templated Filters with Aliased Explore Name
Although using extends
is the recommended resolution, another solution is to scope the templated filters referenced within the {% condition filter_name %}
variables with the aliased Explore name. For example:
###### EXPLORE ###### explore: derived { from: derived_table_test } ###### VIEW ###### view: derived_table_test { derived_table: { sql: SELECT id , status , created_at FROM orders WHERE {% condition derived.status %} orders.status {% endcondition %} AND {% condition derived.created_date %} created_at {% endcondition %} ;; } filter: status { type: string suggestions: ["Complete", "Pending", "Cancelled"] } dimension_group: created { type: time timeframes: [date, week, month, year] sql: ${TABLE}.created_at ;; } }
Notice the explore name, derived
, in front of the status
and created_date
conditions. This is not recommended because any Explore or join that uses derived_table_test
not aliased as derived
will break.
If the unaliased view name derived_table_test
will never be used in favor of the alias derived
, the true fix is to rename the view derived
, since that is the only name it will be referenced across the model.