Hi I have a use case and i'm not sure if elementary can do it or not 🙂 we want to implement rule based alerts using SQL query example i want to run the following query once an hour and it returns any results - send alert on slack:

SELECT country, SUM(revenue) as total_revenue
FROM countries_orders 
WHERE DATE(created_at) = CURRENT_DATE()
GROUP BY 1 
HAVING total_revenue < 100

so if in the last hour one or more countries had total_revenue < 100 - send a list of those countries

Do you use a scheduler like Airflow for your pipelines? You could, for instance, run this query as a model, add a test for

accepted_values

of nothing, tag the test with something, generate the report, send the report using the tag you’ve assigned for this test. Alternatively, you could just make this a test, run the test, generate the report, send the report using the tag

@Euan Blackledge Thanks!! We do use airflow. sounds like the

accepted_values

solution might work. about the other solution, wdya mean by "make this a test"? like creating a test with custom SQL ?

Exactly, dbt allows the creation of custom tests which might be the better approach for this scenario since you would not be adding a table to your warehouse that served no purpose except for monitoring. I’ve never made a custom test but many of our analysts have

nice! i implemented sql tests on

/tests

folder and DBT runs them properly can i somehow look at the failures on elementary dashboard? i didn't find it as this test is not related to a specific table but more of a singular custom test that envolves few tables

You should be able to find the table that the test is applied to and then the test should be there. You can then see the results of the test, in this case it should be the list of countries that you have deemed to be in error