I haven't used it much, but from what I've tested I think it's great for denoising. I'm starting to use it more for masking/object selection - It can still be hit and miss but I'll try it first and if it can't do it right I do it myself.
Content aware fill is just witchcraft now. It blows my mind. Ethically - I'm fine with it in situations where I'd normally use a heal/clone tool anyway - bits of trash in the scene, pimples on faces etc. My "line in the sand" has always been if it's something temporary like trash or a pimple, I'll remove it, but if it's something permanent like a lightpost or a scar, I'd leave it.
Like most new tools, it's easy to overuse it. Just because a slider is there doesn't mean it needs to be used. There is a certain look to photos that have had the AI denoise, sharpen, and upscale treatment, and i'm starting to see it more and more in birding/wildlife circles specifically. It's subtle, but the more you use it, the more you learn to see it, much like the AI "writing style".
Ultimately, it's just another tool. People asked the same question of selective colour or HDR, or even just digital in general - People will overdo it, then it will calm down and become just another tool to subtly shift your images towards your vision.