Question

PDT rebuilding every hour due to 'table not in scratch schema'

  • 22 September 2022
  • 1 reply
  • 28 views

Hello,

 

I have a problem with several persisted derived tables that are set to rebuild overnight, but are actually being rebuilt hourly, with build reason ‘Table not in scratch schema’. This is happening to one with sql_trigger SELECT CURRENT_DATE and a group of others all triggered by the same datagroup trigger: 

SELECT FLOOR((EXTRACT(epoch from NOW()) - 60*60*2)/(60*60*24))

 

I can see multiple versions of each table in the looker scratch schema, and all are query-able in the SQL-runner. I’ve tried changing the max_cache_age in the datagroup, adding a persist_with to the explores and the tables are still being rebuilt hourly by the regenerator. This causes some lag on our systems as the tables are quite expensive to run, and is frustrating as the tables are designed to remain the same throughout the day. 

The tables in the datagroup reference eachother, so are cascading. The other table with its own trigger also references another derived table with the same SQL_trigger. Could it be that the dependencies are causing Looker to rebuild every hour? 

 

This is what the build schedule looks like - they rebuild overnight as per the SQL trigger, but also rebuild hourly because ‘table not in scratch schema’:

 

 

And for when they build overnight as part of a datagroup trigger, a similar pattern:

 

For reference, we’re using Postgres and our own hosted instance of Looker. 


1 reply

Bump

Reply