Substitution and sql_table_name

A change that I often request on LookML pull requests is replacing ${TABLE}.column_name references with a reference to a LookML dimension ${dimension_name} or ${view_name.dimension_name}. As a rule I think there should be exactly one ${TABLE}.column_name for a given column in a LookML view, and anywhere else that field is used should reference that one dimension.

There are a few reasons for this.

1. Probably most importantly, is that the column_name part is just a string that Looker inserts into queries and won’t validated. Looker will trust you to get it right. You’ll only find out there’s a problem when someone runs a query and gets an ugly SQL error.

2. The LookML validator does check LookML field references, so if someone removes something important they’ll see an error and can avoid breaking things.

3. It keeps your code DRY. If the name of the column does change, you can update it in just one place. If the definition of the dimension changes, e.g. it was a yesno field doing something like ${TABLE}.column_name > 0 but now you’re doing the comparison in your data pipeline so the column is a boolean. You only need to make this change once in that dimension and it will be reflected everywhere else automatically.

4. Once you have that one dimension, when you type you get the benefit of auto-completion/suggestions which (alongside sensible dimension names) can speed up your coding.

5) You need LookML dimensions to use in the filters: parameter, access_filters etc. If you have complex conditions you can even combine multiple yesno dimensions to make more complicated ones, which along with the other benefits means you can make long CASE/WHEN statements a lot more readable. Using filtered measures is usually a good idea too, as you can easily add drill_fields and build a nice drill path for users to dig into/through the data.

So what’s the ‘substitution operator’? It’s the $ symbol! It does what the name suggests. In the case of ${TABLE}.column_name, Looker substitutes the ${TABLE} part for the (alias of) the view’s sql_table_name. For LookML references, substitutes the SQL from the field you’re referencing, so referencing a yesno field like this gives you a boolean, not the strings ‘yes’ or ‘no’.

You can also use the substitution operator to access the value of a view’s sql_table_name parameter: ${view_name.SQL_TABLE_NAME}. This opens up a lot of interesting tricks. You can put an entire subquery inside sql_table_name or use it to store arbitrary snippets of SQL to reuse later.

Some uses of this idea include: avoiding nested WITH CTEs, setting up complex self-joined chains of subqueries (e.g. for complex SQL like churn analysis), making ‘manual’ versions of incremental derived tables, and more.