Almost never. Tap water smells are usually annoying, not apocalyptic. But here’s the rule: if the smell is chemical (like paint thinner or gasoline), do not drink it. Call your water company immediately. That’s a real problem.
Also, if everyone in your house gets sick, or the water is suddenly brown, stop drinking it. Brown water is usually just sediment from a fire hydrant being used, but better safe than sorry.
For 99% of us, a bad smell is just a sign that your pipes need some love or your water heater is being dramatic. Run the tap. Flush the heater. Clean the drain. And if all else fails, buy a Brita filter and never look back.
Now go hydrate. You earned it. And maybe light a candle first.