I have personally watched Windows Defender fail to notice a real virus infection from a USB flash drive. The USB flash drive was plugged in and instantly Windows was infected - Windows Defender did nothing. You could then manually run a Windows Defender scan and it would find the infection but it would not prevent the infection.
I think one's need for a security product should match one's exposure to issues - it depends on your org. For a very long time I was not a fan of anti-virus, in 2022 I'm back to liking anti-virus (more like EDR these days).