It seems to me that you might use "in deep water" for trouble that you can, or could have, avoided. And dire straits for trouble that was unforeseen or unavoidable. Compare the the two examples from Cambridge:
These kids are in dire straits, and the schools are doing nothing to help them!
The trouble the kids are in seems not to be their fault. "Dire straits" is often used for critical financial troubles or other simple problem with no clear solution. So, without out any other context, you might think that the kind of problems that the kids had were related to poverty.
The director knew he'd be in deep water if he didn't mention his wife in his acceptance speech.
This trouble can be avoided.
Moreover "deep water" is the kind of trouble you can get out of (if he doesn't mention his wife, he might still get out of trouble by getting her some flowers). It might be the kind of complex problem, that you can solve by breaking it down into smaller problems. Whereas "dire straits" is more serious and hard to get out of (the kids might be in some kind of cycle of poverty and failure and it is not easy for them to escape)