Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Using the datagroup: vs sql_trigger_value: methods

  • 25 September 2017
  • 4 replies
  • 774 views
KieranM

Hi,



This may be a really silly question, but I am wondering what is considered best practice?



My scenario is: I have several PDTs, which at a View Level contain the “sql_trigger_value: SELECT CURDATE();;” statement. These are joined at the Explore level. I am seeking to improve query performance and was reading through Looker’s discourse.



Do I need to a) state a “sql_trigger_value:” at the Explore level? Or b) do nothing as they are already PDTs?



As well as seeking performance improvements, I am wondering what would be considered as best practice?



Thanks!

4 replies

KieranM

Thanks, will do!

philip.martinelli
Rank
Userlevel 2

@ted.conbeer nice explanation!



@KieranM if you have any issues with implementing this feature, please hop on chat or send an email to help.looker.com!

KieranM

Thanks for this, I have updated our views and explores to mirror this approach now.

T
Userlevel 1

I really like datagroups, and they apply well to your application here.



First, create a new datagroup at the top of your model file where the explores are defined. Although your trigger condition is really straightforward, there is some gain to re-use this query, especially if you want to make it more nuanced to run at a different time than 00:00 UTC. I use:



datagroup: daily_datagroup {


  sql_trigger: SELECT DATE_PART(day, CONVERT_TIMEZONE('UTC', 'America/New_York', GETDATE())) ;;


}


Next, in the explore, you want to define the cache behavior. This is the amount of time that Looker will save the results of queries and serve those results to new (identical) queries, rather than hitting your database again. If the underlying data isn’t changing, you may as well keep the cache on the same timeframe, so in each explore, instead of persists_for: x hours use persists_with: daily_datagroup



Finally, in each view, replace your sql_trigger_value: with datagroup_trigger: daily_datagroup



A final note, if you do this across a lot of views, especially views that take a long time to build, keep in mind that as soon as your trigger changes values, all the tables will be rebuilt, which can create a bit of a logjam in your database if you don’t choose the time carefully. You may want to create a few different daily datagroups at different times

Reply


Terms of Service