How can I have a PDT only trigger a rebuild when two (or more) tables in my database have been updated?

Knowledge Drop

Last tested: Dec 27, 2018
 

Good question! There are at least two ways that we could get a PDT trigger that only triggers after two different tables in the DB have been updated.

1. If last_load_time (or load times in general) are available, we should be able to use that query in your last comment or a query like below to trigger a rebuild only after both tables have been updated:

-- Note: this min(timestamp) will only change when the most recent upload times for both Table A and Table B have changed

select min(timestamp) from

(select max(timestamp) as timestamp from Table_A union select max(timestamp) from Table_B)

Alternatively, we could a setup like below if Table load times are not available to us:

# sql_trigger_value will trigger a rebuild every day

view: pdt_one {

derived_table:

sql: .... ;;

sql_trigger_value: SELECT current_date;;

}



# sql_trigger_value will only force a rebuild when the master table updates

view: pdt_one_prime {

derived_table:

sql: SELECT count(*) as num FROM ${pdt_one.SQL_TABLE_NAME};;

sql_trigger_value: SELECT count(*) from ${master_table.SQL_TABLE_NAME}

}



# sql_trigger_value will trigger a rebuild every day

view: pdt_two {

derived_table:

sql: .... ;;

sql_trigger_value: SELECT current_date;;

}



# sql_trigger_value will only force a rebuild when the master table updates

view: pdt_two_prime {

derived_table:

sql: SELECT count(*) as num FROM ${pdt_one.SQL_TABLE_NAME};;

sql_trigger_value: SELECT count(*) from ${master_table.SQL_TABLE_NAME}

}



# sql_trigger_if will rebuild when query returns "True" (i.e. when pdt_one is larger than the pdt_one_prime snapshot table AND when pdt_two is larger than the pdt_two_prime snapshot table)

view: master_table {

derived_table:

sql: ... ;;

sql_trigger_if: SELECT CASE WHEN pdt_one.num > pdt_one_prime.num AND pdt_two.num > pdt_prime.num THEN TRUE ELSE FALSE FROM (SELECT count(*) as num FROM ${pdt_one.SQL_TABLE_NAME}) as pdt_one, (SELECT count(*) as num FROM ${pdt_two.SQL_TABLE_NAME}) as pdt_two, ${pdt_one_prime.SQL_TABLE_NAME} as pdt_one_prime, ${pdt_two_prime.SQL_TABLE_NAME} as pdt_two_prime

}

To break this down, the master_table represents your main PDT (i.e. the PDT you'd like to trigger only after two subordinate tables have been updated). The idea is that if pdt_one and pdt_two both become larger than prime_one_prime and pdt_two_prime, which are something like "snapshot" tables in this case, then we force a rebuild of the master_table. Note that pdt_one and pdt_two will rebuild at the rate you specify and in this example, they're rebuilt everyday using `SELECT current_date`. The master table being rebuilt will then trigger to the prime tables being rebuilt, which then gets our loop started back up.

This content is subject to limited support.                

Version history
Last update:
‎05-07-2021 09:07 AM
Updated by: