Is there a library for mocking/mirroring Redshift SQL interface to run tests in an isolated dev environment ?
If not, please point to suggested best practices for such testing ?
Amazon Redshift was specifically created to run on AWS infrastructure. It is not available as a download. (Interestingly, Amazon DynamoDB does have a downloadable version for development purposes.)
The cheapest option might be to shutdown your Dev & Test instances each night and on weekends. Take a snapshot before deleting the cluster, then create a cluster the next morning based on the snapshot. This can be automated via the AWS Command-Line Interface (CLI), making it easy to schedule with cron or Scheduled Tasks.
You could also have a snapshot of Test data and restore that snapshot each morning, which means the test database doesn’t fill-up with test cases.
Another cost saving might be to reduce the number of nodes for the non-production systems. Queries will run slower and the total amount of storage will be reduced, but it could be more cost-effective. Or even use a “Dense Storage” 2TB node instead of several “Dense Compute” SSD instances — they will provide more storage on less nodes.