spectacles

spectacles copied to clipboard

If a column is given the wrong type, it is possible it will still run in a query, but fail when you try to filter on it. i.e. if you have a column id and a lookml dimension defined as:

dimension: id {
    type: string
}

The query will actually run fine but when you try to filter on the column in some databases (I was testing against BigQuery) it will give you an error. I think we ideally would want to catch these types of errors.

One option is to filter on every column as part of the query. Another is to do some database introspection, but that requires doing things directly on the database, which I'm keen to avoid.

Looker dimensions can take on a lot of different types, see Looker reference for dimension types.

High priority

string
number
time
yesno
duration
date
date_time
date_* (week, quarter, raw, etc.)

Low priority

bin
zipcode
unquoted
tier
location
distance

For each type, we'd need to define a filter criteria to check with. We could probably test these criteria to see which is fastest. For example, for a string in Looker, we can filter on starts with, contains, is equal to, ends with, is blank, or really any option for that type in Advanced Filters. It's not clear if one of those would be consistently faster than the others.

string

WHERE column = ' '

number

WHERE column = 0

datetime

WHERE column < '1970-01-01 00:00:00'

Then, for dimension-level queries (and maybe even Explore-level queries?), we'd add these filters to the API request when we create the queries. I imagine this might slow down query compilation a bit, thought it's hard to tell how much.

We would also need to store the dimension types in the LookML tree when we build the project.