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

View the original community article here

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.