There's nothing quite like a good, dense blizzard to create that ominous, oppressive atmosphere. There's a fair number of games that do it well, but in my mind nothing comes close to the snowy wizardry that is
Tom Clancy's The Division. The Survival DLC in particular is just amazing, with snow and fog so thick and soupy that it's hard to see more than a few feet ahead of you. It's integrated into the gameplay too; when you're dropped in, the first order of business is to find clothes that protect you from the freezing weather. If you can't manage it fast enough, your character will freeze to death.
Once you do manage to find gear that will keep you alive, you really start to appreciate the minute details the developers managed to cram into the game's weather systems. The way that snow builds up realistically on your character and objects in the environment, how it manipulates light bouncing around the environment, how it affects visibility when scouting for targets at a distance.. even the satisfyingly crunchy sound of fresh snow under your boots, it's all there and it's made all the more convincing by the way your character seems to be permanently hunched over, trying in vain to shield him- or herself from the storm. I loved playing the Survival DLC far more than I ever did playing regular old
Division and the setting has everything to do with that.
Snow is great in any game, though. I love the frozen hellscapes of
Horizon: Zero Dawn, the opening to
Red Dead Redemption 2 and the distant mountain peaks of
Skyrim.
In general, I think bad weather is almost always to a game's benefit. Particularly now, because we live in a time where graphics engines have become so advanced and so well optimised that developers can really go all out with these effects and make it more convincing than ever before. Volumetric fog, thunderstorms, low-hanging clouds, rainbows, any dynamic weather effect I can possibly think of adds to the experience in some way and helps sell the fantasy.