Community Conundrum 28: News Engagement is live! Read More

Scenario triggered by an external change

Level 3
Scenario triggered by an external change

I am trying to create a scenario that is triggered by an external change to a dataset. 

Specifically, I have dataset created from a Redshift table. And I have a recipe that processes this dataset.

I want to be able to trigger the recipe every time a change is made to the Redshift table. Any change to that table will be done 'outside' of Dataiku. It's some type of reporting table and new rows are added at some random times. 

I set up a trigger that monitors the dataset changes, but it doesn't work. I remove and add rows to my table by running Redshift queries outside of Dataiku, and nothing happens.

I was thinking of using the "SQL query change" trigger and monitor the number of rows in the table. What is the best way to keep track of the number of rows, i.e. the change? Should I use an environmental variable of some sort? What is the best practice?

Thank you

0 Kudos
3 Replies
Level 3
Author

Are there any examples of "SQL query change" triggers in the documentation or anywhere else on the Dataiku website?

0 Kudos

Hi @davidmakovoz. I was able to found this link with google: https://gist.github.com/alexcombessie/64e5508d87e47685f9b5955cb18a9ee2

It has a "coded" trigger that works on SQL datasets changes. It might help.

From the documentation or academy, I didn't find any explicit examples.

0 Kudos
Neuron
Neuron

Here is an example SQL query trigger from one of our projects, this trigger looks a a table with a new date prefix, which is created every morning by other job:

SELECT 
max(cast(parse_date('%Y%m%d',
substr(table_id,13)) as timestamp)) as max_date
FROM `dataset.__TABLES__`
where table_id like '%ga_sessions_%'
and table_id not like '%intraday%'
and row_count > 50000

 Does this help?

Ben

Labels (2)
A banner prompting to get Dataiku DSS