BigQuery clustering in non-case sensitive models

  • 27 October 2021
  • 0 replies

I am working on a model connected to BigQuery where our users also require non-case sensitive filters. In order to optimize queries I want to implement clustering on a few of the filter fields. However, I’m running into an issue because of how non-case sensitivity is enforced in Looker.

BigQuery clustering does not work if you perform complex filter expressions that operate on the clustered columns (documentation here). If I turn off case sensitivity in Looker it does that by applying an UPPER() function to both sides of the filter expression. This counts as complex filter expression and prevents BigQuery from optimizing on that cluster.

For example, if I had a non-case sensitive filter “keyword = ‘chocolate’” Looker would include the filter

UPPER(keyword) = UPPER('chocolate')

Has anyone dealt with this issue before? Do you have any workarounds for this case?

One option I do have for this is that I can assign uniform capitalization for the saved values in BigQuery. So if I’m able to change the customer-input filter value without performing complex expressions on the filter field that would solve my use case. In other words if I could create a filter that generates the sql

keyword = UPPER('chocolate')

that would solve my use case.

This topic has been closed for comments