Fact: As shows go on, they become more expensive to produce (especially one as SFX-heavy as SNW).
It's cheaper to create a new show than it is to renew an old show indefinitely.
There's always the option of making streaming film tie-ins.
But that isn't the question. The question is at what point does Trek no longer make viable financial sense to renew. Not that its cost over the long term will grow to the point where a new show will be cheaper. Expense is just one half of the equation. The biggest factor is revenue.
Using Yellowstone as an example, Even if they didn't mess with its cast o where it was airing. That show generated enough revenue to keep producing for many more years (unless of course it just fell apart, it can happen). And still generate more revenue then most any other show they produce.
Now due to issue with Costner's scheduling, Sheridan moved to end the show. Cut its streaming contract with NBC (which Paramount has wanted from day one, just needed legal coverage to do so). rename, and get new lead. The show is going to continue (again unless its numbers just completely fall off. That show is expensive, even if they could have kept Costner and included cast and producers (key one has made got significant raises), and moved it to Paramount + that show would still have easily several years left to. Easily.
Nothing from Trek is near that level. Nor do the cost reach that level. But Trek is still expensive. And SNW is at least domestically the the third highest rated show that has aired on Paramount +. And does do better then Discovery to date or Picard to date.
So the financial reason that lead to Discovery being cancelled, doesn't mean that SNW will hit that threshold in the same number of years.
As for new show development while over the long term (if they survive, thats always an if), their initial cost will be more expensive then what a current season of Trek will costs. So there is always a gamble.