Knowledge Drop

How does the SQL Trigger Value Process work for nested PDTs

Userlevel 3

Last tested: Jan 16, 2019

Chat tip: consider using datagroups for dependency PDTs that should always rebuild on the same interval! Looker will take care of the rest.

If the PDTs have the same sql_trigger_value, then we do a topological sort to make sure that the upstream PDT is built before the downstream PDT (and this works in general with more complex dependencies). BUT we only look for those dependencies for tables with the same sql_trigger_value! This is also beneficial because we cache the trigger result.

If a user tries to take the rebuild order into their own hands by say setting an earlier time for the upstream table, they may actually get the opposite of what they were trying to accomplish.

Example of unexpected behavior:

upstream trigger time is 12:00am, downstream trigger time 12:05.
Upstream checked at 11:59 (not triggered)
… some other table checked and triggered takes more than 6 minutes to build…
downstream checked and triggered at 12:06 will be built with yesterday’s upstream and not trigger again until tomorrow.
then upstream will trigger and build on the next regen/trigger check pass

Example makes sense, but how does this get prevented if they have the same trigger value?

because we cache the value for a given sql_trigger_value for the duration of a trigger check pass

This is more obvious and bulletproof with datagroups.


This content is subject to limited support.                




0 replies

Be the first to reply!