For anyone else wondering, I was able to follow this tutorial and able to check the reason why my scheduled query was also failing. I also use Redshift serverless and this solution works for that service also: https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-schedule-query-troubleshooting-cloudshell.html