When should I use Redshift non-Serverless over Redshift Serverless?

In general, below two principles can be applied to any serverless vs provisioned deployment

Go for serverless, when

1. Applications that do not expect steady state and constant traffic, for Example, dev or test or adhoc project
2. Application that expects steep spike or unpredictable demand

If application expects to be steady state with predictable demand then go for Redshift Cluster with RI's

As per Redshift Serverless documentation:

Amazon Redshift Serverless is ideal when it is difficult to predict compute needs such as variable workloads, periodic workloads with idle time, and steady-state workloads with spikes. This approach is also a good fit for ad-hoc analytics needs that need to get started quickly and for test and development environments.

Provisioned Redshift on the other hand may be suited for steady predictable loads.

It something hard to know in advance, especially when building a new solution. Thus starting with serverless is a reasonable choice. And at some point, practice on a similar scale that there would be in production. To have a good estimation the amount of hours your work load would consume per month in production application.

Then comes the simple math, assuming that running in us-east-1 region with minimum base RPU is 8, an hour of using redshift will cost: (0.36 * 8) = $2.88 at minimum. The smallest cluster with 1 node, single zone will cost: $182.50 / month. Which is about 64 hours of usage in the serverless case. Probably for production having an availability zone is a must, then the minimum cluster will cost 3,171.12 a month which is the amount you would pay for ~1101 hours of usage in serverless case. Since on demand, could be 60% or more cheaper if purchasing reserved instances, repeating the math, will give about ~440 of similar serverless hours per month.

Based on application needs, or Redshift behaviour, like: query speed, RPU that Redshift decides to use for a load, defend from some unexpected cost spikes due to suddenly bad query, etc. Translating those into the expected hours used, the framework above can help to decide which option to choose.