We have a requirement in Looker to refresh derived table on Periodic basis.Around 80 derived tables which needs to be refreshed at different schedules.When users are execting the Looks, they should get the data from the cache and it shouldn’t hit warehouse.
To achieve this we have used below code to refresh a single DT:
when (((extract(day from current_date))=1) and (extract(hour from current_timestamp(0))=16 and (extract(minute from current_timestamp(0))=00 )) then current_timestamp
when (((SELECT day_of_week from sys_calendar.calendar where
calendar_date = current_date) = 1)
AND (extract(hour from current_timestamp(0)) = 20)
AND (extract(minute from current_timestamp(0))=00) ) then
Problems we are facing with the above code:
- After each successful refresh an unnecessary refresh will occur.(The reason for this is Case statement will return null value when it does not meet any condition .Null is not equal to Sql trigger value of Looker hence unnecessary refresh will occur).
- These Unnecessary built are causing utilization issues in data base end.
Our dialect is Teradata dialect.
Can anyone please suggest a way to meet this requirement.