If the guy goes missing, the US Coast Guard is obligated to find him, spending many hours of manpower and possibly hundreds of thousands or millions of dollars.
There's calculated risk and there's stupid risk...this one falls into the latter.
There's an outstanding conflict between statutory law (which codifies a duty to rescue on all mariners) and case law (which has generally found that neither private nor public actors have a duty to assist), but once USCG has essentially taken notice of an issue at sea, they have a legal obligation to deal with the situation lest they end up on the wrong side of a FTCA suit. In this case, there isn't any way they could ignore a crazy guy taking a Habitrail to sea; just by realizing a manifestly unseaworthy vessel was being taken into dangerous conditions by an impaired pilot, they (arguably) created a duty of care. There are a lot of cases where the fact pattern wouldn't obviously lead to this -- a drunk guy sailing a J/22 across the Atlantic on a bet is a bad idea, but not an obvious immediate danger to life and limb in the same way -- but I think any jury would look at this and say, "Yeah, no reasonable person would think this guy isn't gonna die out there."
(Leaving obligations aside, USCG takes its mission very seriously, and 14 USC §521 gives them incredibly expansive powers to do whatever they deem necessary to protect lives and property on the high seas and coastal waters. Playing stupid games on deep waters is not something that the Coasties like to see.)
Seems quite odd that the coast guard would have a higher duty of care than a school or a sheriff's office. Has there been any successful lawsuits where the coast guard failed to save someone and were sued?
I found this[0] but it appears to be ongoing. The state appears to again argue they have no duty to rescue anyone.
This case is a good example of the limits -- it's been held that once USCG begins an operation, they fall under "good Samaritan" requirements, which means (roughly) that they can't make things worse; and, if things get worse, a FTCA suit could force them to demonstrate they followed through without negligent acts or omission based on their existing practices, with due deference to the right USCG has to make reasonable discretionary decisions. (I don't have any case cites at hand, but IIRC USCG has lost a couple after calling off searches early or issuing confusing notices to private vessels also engaged in searches.)
Whether this would apply to the instant case is arguable, but turns mostly on the definition of when the duty of care kicks in (I think there's a decently-strong case to be made that once they stopped the "vessel," they pretty much didn't have an option to let it go). Interestingly, while SCOTUS has steadily limited government duty of care, this hasn't extended as much to conditions of public hazards where the police or government has specific knowledge of the risks: a cop doesn't have to charge into a school shooting situation to save your child, but there are conditions where, if they fail to correct a traffic hazard they had knowledge of and control over, you would have a case if you were injured. Hazards at sea are probably more similar to the latter case than the former.
It's worth noting that USCG doesn't have any statutory mandate to rescue anyone (their duties enumerated in 14 USC §102 say nothing about saving distressed mariners), so your fundamental point is correct: for example, if someone had called in "crazy guy in hamster wheel out at sea," USCG could likely decide "not my sea monkey, not my circus" and let it go; it's just that once they're on-site it's a more legally-fraught situation. (This is on top of the fact that the Coast Guard treats rescue as a core mandate, so no chance they weren't going to stop this guy from ending up Gulf Stream flotsam.)
There's calculated risk and there's stupid risk...this one falls into the latter.