Conditionally Protecting PII
Recently I was asked how to prevent users from running queries that combine a certain dimension with some specific other dimensions - in this case, customer PII fields alongside some potentially sensitive segmentation data, the idea being that staff shouldn’t be able to identify which individuals fall into which segments, even if the staff members already have access to PII.
While there are a few approaches you could take here, we wanted to make sure that anyone trying to run queries with a disallowed combination of dimensions would see some kind of useful messaging, rather than no data or even a SQL error.
My solution is to use Liquid to determine if any PII fields are being used in the query, and if so hide the segment value and show a message to the user instead.
Looker’s built-in Liquid variables give you a lot of contextual information about the current query, including the model/Explore, and whether fields are used in the query at all, or specifically if they’re selected (in the results table) or used in the filters. In this case I want to know if the dimensions are in the query at all, since even if they’re not selected, using them for filters would let a user filter for a single customer and see their segment value. Using view_name.field_name._in_query for all relevant PII fields, combined with a logical or in a Liquid if/else lets us change the generated SQL accordingly. See the full Liquid variable reference for more information.
As an extra touch, you can use the same Liquid if/else logic in the html: parameter to include a clickable link out to further information. We could have done the whole thing only in the html: parameter, but that would mean users could access the underlying value by downloading results without formatting applied.
In reality you’d probably have some kind of PII protection set up already, and if so it might be acceptable for users with access to this information to also see the segments, or conversely for users who only see masked or encoded versions of the PII to see the segments regardless. Unfortunately Liquid is a bit limited - you can’t use brackets in logical expressions so you couldn’t build a complex condition with and, or and brackets. You could, however, use multiple if/elsif/else conditions to deal with each case separately.
Using the built-in Liquid variables and some clever logic you can also block users from accessing PII but include it in aggregations/totals - but I’ll save that for another tip!
