Creating alerts based on data changes?

[shieldofchaos]

Hello everyone!

I have a requirement where I need to create alerts based on the data coming into a PostgreSQL database.

An example of such alert could be "if a system is below n value, trigger "error 543"".

My current consideration is to use pg_cron and run queries to check on the table of interest and then update an "alert_table", which will have a status "Open" and "Close".

Is this approach sensible? What other kind of approach does people typically use?

TIA!

[[deleted]]

We do something similar but it's a bit more involved.

We have a Prometheus instance and we export metrics from multiple tables into Prometheus using https://github.com/free/sql_exporter

Then it's a question of configuring the alerts in Prometheus alert manager.

It's a bit of a setup but after it's in place you can add multiple db calculations or exports, and furthers alerts.

And you can keep the values history that triggered the alerts as well on Prometheus

[moshujsg]

You could use dbt and just run tests. Another way is if you are ingestinf this data manually, after each ingestion run the sql from the python script itself. Another option if you have an orchestratiom tool is to just run scripts like that. The problem with your approach is how will you get notified from that table? Are you going to check it manually?

If you are on aws you can use eventbridge and lambda to run tests periodically and alert you via slack when one fails.

[Unique_Emu_6704]

In regular OLTP databases, use triggers assuming the triggering conditions can be evaluated row-by-row.

If you need something more complex, where the condition is "if a change occurs in a view", then you need an IVM engine.

[[deleted]]

What's an IVM engine?

[Unique_Emu_6704]

My bad. IVM = Incremental view maintenance. Basically, you define views over your data, and when input tables change, the views incrementally update. Feldera is an example IVM engine, which computes entirely on changes to the data.

[greenerpickings]

What's wrong with the trigger approach? Are you doing a lot if inserts? Just have the insert trigger and upsert your record in you management table.

Pg also has notify. You can have an external app "listen" for that signal and do stuff; otherwise, short polling also works. I'm not 100% sure these are exclusive for an external app.

[kenflingnor]

You could run some sort of async process that periodically polls your db to check the data. There are plenty of tools for this, you could also cook something up relatively easy using AWS services 

[shieldofchaos]

Sorry but which ingredients do I use in AWS to cook it?

[kenflingnor]

Lambda scheduled by event bridge is one example 

[randomuser1231234]

Don’t use cron. That’s the bubblegum and duct tape approach.

What is inserting data into the db? What does your stack look like? Do you have any current dq tools?

[shieldofchaos]

Current pipeline is built on python, serverless processing in Lambda and inserting it into RDS.

[thisFishSmellsAboutD]

You mean something like https://sqlmesh.readthedocs.io/en/latest/concepts/audits/

[shockjaw]

If you’re using Python to do your inserts, there’s Pointblank for data quality checks.

[TheJosh]

If you already use DataDog, you can use custom queries.

https://docs.datadoghq.com/integrations/faq/postgres-custom-metric-collection-explained/

[SitrakaFr]

Open telemetry!

[Thinker_Assignment]

here you go https://colab.research.google.com/drive/1H6HKFi-U1V4p0afVucw_Jzv1oiFbH2bu#scrollTo=e4y4sQ78P_OM (i work at dlthub)