So I ask if there is a sort of Push Notification (as CometD library).
There is no push notificiations in CARTO but you can use SQL API to check if your data has changed and then refresh the layer from your frontend code. This is actually a very common pattern.
In order to know if your data has changed you need to implement your own logic using maybe a trigger and a sort of
updated_at column or associated metadata table.
Disclaimer: I'm a solutions engineer at CARTO