Yeah, the info_command_spawn intended for that kind of command, but it has two subtle extra points. The first is that it is applied when a player spawns, rather than when the map starts. The only time this makes a difference is in coop, but using it means that each player is sure to get the command when they connect. It also gets sent again if they respawn, so if you have different fog levels in different sections of the map, setting the initial fog level with info_command_spawn ensures respawning players will have the right fog for that location.

The other thing that it does is sets the "environment variable" for the client to the command in the info_command_spawn. This means that when they load a saved game on this map, the command from the environment variable will be sent again. Otherwise it's possible that they could load another map or restart the engine, which would reset the wateralpha value. There are ways to update the environment variable for different regions of the map, but that's only needed if you're doing different fog levels/wateralpha levels around the place. If you have a global setting for the whole map, put it in info_command_spawn and it's all handled for you.

Two important additions on how to format commands though:
1) Make it "r_wateralpha 0.4\n", the extra \n on the end of the command makes it execute, otherwise players can encounter a problem where the engine misses them pressing/releasing buttons.
2) If you had fog as well as wateralpha, you shouldn't add two info_command_spawn entities as only one of them will work. Instead make one entity containing both commands separated by semicolons. Like:
"fog 0.2 0.3 0.2 0.5;r_wateralpha 0.4\n"