RedShift Scheduled Query Failure Alerting Issues

Here is the workaround can use : Amazon EventBridge

1. go to Amazon EventBridge
2. create Rules
3. set Rule type as Rule with an event pattern instead of Schedule
4. in Creation method choose Custom pattern (JSON editor)
5. in Event pattern with JSON below

{
  "source": ["aws.redshift-data"],
  "detail-type": ["Redshift Data Statement Status Change"],
  "detail": {
    "state": ["FAILED"]
  }
}

6. in Target type choose AWS service and select (new or use existing) SNS topic
7. test the redshift schedule query (when failed)

hope it helps!

Amazon SNS topics attached to a scheduled query is currently designed to only send out notifications every time the query gets triggered, but do not come with the additional details (like query failure or success).

To verify if a schedule query is a success or failure, we have the option to check (using CLI) as of now using redshift-data API 'describe-statement'. (https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_DescribeStatement.html)

I did exactly what @JackH suggested, but for state: 'FINISHED' instead, for testing purposes, and it doesn't trigger when the query runs. Even though the event pattern matches the event when clicking Test pattern. Any suggestions?