Requiring Field Usage
Recently I was helping a LookML developer who was building an Explore for a quite specific purpose, and she wanted to make sure that a particular field was always used in any queries a user might create.
Looker’s always_filter and conditionally_filter are great for avoiding expensive, long-running queries, and give some control beyond that, but there’s no direct way (that I know of) to require that a given field is always included in Explore queries.
My first suggestion was to use a Quick Start query so that any user opening the empty Explore would have a direct way to start building their query. This is pretty easy to do in the Explore definition LookML, and you can even use the ‘Get LookML’ option in the Explore and copy the query part of the Aggregate Table LookML to get the code from an existing Explore query.
I also suggested using sql_always_where and some Liquid to detect if the relevant field is being used, and if not, include a FALSE in the WHERE clause so that users wouldn’t be able to get any misleading data if they didn’t use the field. The Liquid variable view_name.field_name._is_selected tells you if a field is selected i.e. in the results table. We did discuss using Liquid to mask the values of other fields using the same idea/Liquid variable, but that depends on that field being in the results, so we’re basically back to square one unless we add Liquid to every field in the Explore, and that would make a horrible mess of the code.
While shoving a FALSE into the WHERE clause would avoid incorrect queries, it’s still not great, as users might end up asking why they’re getting no data, and in a big busy organisation having to deal with users’ problems can be a big time-suck.
In the end the developer came up with a really clever solution - force a SQL error! This will depend on your SQL dialect, but with Snowflake, string values are single quoted, and double quotes are used for database, table, schema and column identifiers. So, by inserting a message for users as if it were a Snowflake identifier we can create a customised error message, in a roundabout way.
Even if users got the error and went to enquire about it, hopefully by the time they’d got as far as copying the error message they’d have read it and understood the problem for themselves… I don’t want to speak too soon, but we haven’t had any complaints yet!
Also note that Liquid lacks a logical not operator, but it does have an unless construct which means we can still write a concise expression - even though Looker doesn’t recognise it for syntax highlighting.
Finally, credit to Giulia Gandolfo for the clever trick and inspiring this tip!
