A fantastic game doesn't have to be visually stunning to be successful. It helps of course but visuals don't make/break an otherwise fun game. What does matter however is that the visuals need to make sense and be consistent.
You can get away with "ugly" or otherwise unremarkable graphics but inconsistent or confusing visuals will interfere with gameplay and the game will suffer.
Trying to release a game with "ugly" or "unremarkable" artwork is playing on hard mode. Be ugly in a "visually stunning" way that's original. E.g. Cruelty Squad. I know there are counterexamples. Many of them are just plain lucky, like Flappy Birds. This very article is about a game that kneecapped its sales for decades on account of being ugly.
I mean... df literally wasn't for sale until recently. They only monetized because American healthcare is bananas (its mentioned in this article and discussed more fully earlier https://www.pcgamer.com/if-dwarf-fortress-sells-millions-on-...). Otherwise it sounds like they would've been content with the ongoing patreon funding they had. It was quite popular before the graphics version.
It's true, Tarn and Zach are very committed to their art. It's really impressive and inspirational, truly, and I'm happy for their success -- both critical and financial.
Personally, I'm willing to make sacrifices to make games, but I'm not willing to go as far as them.
There's a whole genre of ugly fps right now, I don't even just mean boomer shooters, I can't recall the name but there's this one where your health bar is a little glass skull you hold, another hyper anticapitalist critique one, and I'm seeing gifs going around of some third one I haven't a clue about. Sorry this is a vague post I just think it's interesting these games are leaning heavily on non visual aspects and being rewarded with massively positive reviews.
I'd argue the opposite. They are executing their art styles very well. E.g. Cruelty Squad. Like you say, there's clearly something in the water right now. Kudos to folks making it work for them.
A fantastic game doesn't have to be visually stunning to be successful. It helps of course but visuals don't make/break an otherwise fun game. What does matter however is that the visuals need to make sense and be consistent.
You can get away with "ugly" or otherwise unremarkable graphics but inconsistent or confusing visuals will interfere with gameplay and the game will suffer.