Step Functions with call to Redshift Data API:ExecuteStatement does not wait for Redshift Function/Stored Procedure completion.

Question

I am using Redshift Data API : Execute Statement (https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html)  within a step function.
There are 2 Step Function states/tasks sequentially to call 2 individual Redshift functions - 1) to Load data in intermediate stage table and 2) to load data in final table from stage table with upsert (update/insert) logic.

Upon execution of step function, I observed that Step function simply fires ExecuteStatement but doesn't wait for confirmation for StoredProcedure / Function to get completed in Redshift and simply runs through remaining tasks with success result.

How do I enforce step to ensure Redshift execution of stored procedure/Function is completed? Currently there is no option in Step Function similar to Glue Task which has Option to Wait for Child step to complete.

Accepted Answer

The ExecuteStatement is an asynchronous API, that means that you get back a statement ID that you then need to call DescribeStatement to check the status of the statement. When it finishes you can continue.

In your state machine you will add the ExecuteStatement step ->  Wait state for a few seconds -> DescribeExecution step -> Choice state to check if it completed, and based on the result, either go back to the Wait state or go to the next step.

For some of the optimized integrations we have the .sync integration type that does it for you. RedShift is not one of them so you will need to do it yourself.

Step Functions with call to Redshift Data API:ExecuteStatement does not wait for Redshift Function/Stored Procedure completion.

Relevant content