I have date dimension, and I have a filter based on date to get data earlier than certain date. However, I noticed that looker automatically use wrapper function (TO_DATE) which prevents snowflake to leverage the index and make the query slower. I tried to use date_raw, but this won’t appear in the explorer. Anyone knows solution for this? Thanks.
QUERY generated by Looker:
WHERE (view.“BUSINESS_DATE” ) >= (TO_DATE(TO_TIMESTAMP(‘2020-11-16’)))
Dimension in the model file:
dimension_group: business {
type: time
timeframes: [
raw,
date,
day_of_week,
day_of_week_index,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.“BUSINESS_DATE” ;;
}
Hi @lookeruser8888 - You can use the the ${business_raw} dimension from the dimension group to keep the dimension group from applying any type of time or date conversion.
No - the ‘raw’ version of the dimension does not show up in the explore.
Just to be clear - in your example the todate() is not being used around the dimension business date - that’s just being used to format the filter as a date.
Because you’ve specified that the datatype is ‘date’ in your dimension definition - Looker will not do any conversions or casting on the date timepart.
See my example here - it’s not actually adding anything extra to the column name when we are calling the date dimension.
