Monitoring state for Redshift Scheduled Queries in Query Editor v2 using EventBridge, SNS and Lambda

Question

I'm trying to send a custom email created with Lambda and an SNS topic, when a **scheduled query in Redshift Query Editor v2** state is 'FINISHED'. In order to do that, I've created a rule in **EventBridge** with this event pattern:

```
{
  "source": ["aws.redshift-data"],
  "detail-type": ["Redshift Data Statement Status Change"],
  "detail": {
    "state": ["FINISHED"],
    "statementName": ["QS2-Refresh_materialized_views"]
  }
}
```
This pattern passes the test in EventBridge rule builder.
The rule has a Lambda function as target, input type is Matched event, and the function has 2 triggers: the EventBridge rule and the SNS Topic. I've used this approach successfuly with Glue jobs and other services. The query runs on schedule with no error, **but the rule never triggers.**

The question is: **Does the "Redshift Data Statement Status Change" event even trigger for scheduled queries?** or maybe I shouldn't susbcribe the scheduled query to the SNS topic, and let the rule handle it?

There is too little information for **aws.redshift-data** and its supported events (only that one, as far as I know), and for EventBridge in general. I wish all services, events and properties were properly documented.

re:Post Agent · Accepted Answer

Based on the information available, it appears that the "Redshift Data Statement Status Change" event should indeed trigger for scheduled queries in Amazon Redshift Query Editor v2, but there are a few things to consider:

1. The "Redshift Data Statement Status Change" event is generated when the ExecuteStatement or BatchExecuteStatement API operation is used with the WithEvent option set to true. Scheduled queries in Query Editor v2 should use these API operations internally, but you may need to ensure that the WithEvent option is enabled for your scheduled query.

2. The event pattern you've set up in EventBridge looks correct, matching the source, detail-type, and specific state and statement name. However, make sure that the statementName "QS2-Refresh_materialized_views" exactly matches the name of your scheduled query.

3. It's possible that the SNS topic subscription you mentioned might be interfering with the EventBridge rule. You should let the EventBridge rule handle the event routing to your Lambda function, rather than subscribing the scheduled query directly to the SNS topic.

4. Double-check that your Lambda function has the necessary permissions to be invoked by both EventBridge and SNS, and that it can publish to the SNS topic.

5. Verify that your scheduled query is actually completing successfully and reaching the FINISHED state.

If you've confirmed all of these points and the rule still isn't triggering, you may want to consider the following troubleshooting steps:

1. Use CloudWatch Logs to check if the events are being emitted at all.
2. Temporarily broaden your EventBridge rule pattern to capture all events from aws.redshift-data to see if you're receiving any events.
3. Contact AWS support for further assistance, as there might be an issue with the event emission for scheduled queries that isn't documented.

Regarding your observation about the lack of comprehensive documentation for aws.redshift-data events and EventBridge in general, this is a valid concern. AWS services are constantly evolving, and sometimes documentation lags behind. In such cases, it can be helpful to experiment with broader event patterns and use CloudWatch Logs to understand what events are actually being emitted.  
**Sources**  
[Monitoring events for the Amazon Redshift Data API in Amazon EventBridge - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/data-api-monitoring-events.html)  
[Monitoring the scheduled query - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-schedule-query-sns.html)

Monitoring state for Redshift Scheduled Queries in Query Editor v2 using EventBridge, SNS and Lambda

Add your answer

Relevant content