A properly installed tonneau will keep out 95% of the water. As others have said, the only way to be completely dry is to either get a topper or an SUV.
I've run virtually all the trifolds and hard rolling that are out there. My thoughts:
1.) The leak points of any cover will be the front corners, and around the tailgate. They will also leak if the rails are not installed properly. If you take a electric/cordless ratchet and snug down the clamps, you run the risk of overtightening which causes the rails to sag. When the rails sag it creates a gap between the rail and the bed cap and this lets water in.
2.) Different manufacturers use different methods to try and seal the front. The "driest" setup is probably the Leer HF650M, which uses double seals all the way around and doesn't require a foam seal across the front edge of the bed. The Realtruck trifold covers (Bak, Undercover, Gator, etc...) all use a foam seal across the front, and drain tubes.
3.) Realtruck has standardized the way that the cover attaches to the rail. With the exception of the Extang Endure ALX, all other Realtruck covers use a plastic clamp which slides into a channel, hooks onto the edge of the rail, and tightens down. The "Realtruck Advantage" covers sold by GM dealers as an OEM accessory still use the "bolt through the panel" installation method. Problem with these is over time, water gets past the rubber seal, into the panel, and once they become waterlogged they begin to delaminate (and weigh a ton.)
4.) Unless you are using GM Rewards points, buy your cover separately. Realtruck offers a 5 year warranty on BAK, Undercover and Extang covers. Gators get either a 2 or 3 year warranty. If you have an issue, they are much easier to deal with. If you have an issue with an OEM GM cover, you have to go through the service department and GM Warranty processes to get it taken care of.
Recommendations based on my experience:
Hard folding:
1.) Undercover Ultra/Armor Flex (identical except Ultra has carpet on the underside and a smooth finish. Armor has no carpet but has a Line-X coating on the outside.)
2.) Leer HF650M. Has 4 panels instead of 3. Double seals to keep the water out. Downside is only a 3 year warranty and warranty only honored if you purchase from a Leer dealer. If you have your dealer install it or buy off Amazon they won't warranty it.
3.) Bak MX4. They sell a ton of them. A good cover for the money when you can find one on sale. About to be redesigned to include a spring to ease opening and closing. (Supposed to be released 7/26. Bak MX4 EZ-Lift)
Hard rolling:
Bak Revolver X4S or X4TS (has t-slot rails.) Personal favorite and the one I use on my truck. Easy to roll up one handed. Doesn't block the rear window. Only costs you about 6" of the bed when open as opposed to a retractable canister which eats up about a foot of bedspace. Downside is it's either all the way open or all the way closed.
Good luck with you search.