Last Chrismas the family and I traveled to Texas from Arizona. For comfort I opened the midgate, layed a foam pad and thick blankets down so the kids could stretch out to sleep, watch DVD's or whatever else they wanted to do. On the way there it snowed quite heavily....yes in AZ(rare), and never had a single leak during the whole trip. I also own a pressure sprayer at home that I use on my AV and still have never had a leak. The only concern you'll have, with regards to water, is when you hose out the bed. If you don't take the rubber mat out water will get underneath it and will not dry up. I always remove the mat for washing and then let it dry after I found this out.
As far as getting an AV ver. a Tahoe, you need to examine what you use it for. With the AV, hauling bags of concrete for that odd job is just as easy in both cases, but the Tahoe has carpet and can't be hosed out. On the other hand, if hauling small dirty items such as bags of concrete around is your biggest concern, then a tray in the back of a Tahoe is how a friend of mine will accomplish it cleanly.
Here in AZ I have hauled every thing from yard waste (cactus, tree trimmings, dirt and rock), sand, concrete, furniture, building materials, etc. With yard waste you have to cover your load when taking it to the landfill. The tops made this easy. The AV is sturdy enough to haul the sand and concrete. Furniture was easy with no real height restrictions. With building materials I will close the tailgate and rest the long lumber on the it. Then I will leave the 3rd panel (the one that buts against the tailgate) unlatched and resting on the lumber itself. This way I don't have to remove the panel. After all this, all I have to do is hose out the bed. Word of caution, sand can be tricky to get completely cleaned out. Either way, doing all the type of hauling I do, a Tahoe would be a little inconvenient.
Another cool thing that I am chopping at the bit to try is camping with my AV. I'm going to buy the AV tent soon, and head for the mountains to try it out. I don't think camping in a the back of a Tahoe would be as fun or versatile, but none the less, it could be done.
Just remember, when you compare an AV to a Tahoe, the Avalanche IS a Suburban that has had it's back half modified. It's the same width, length, ride and chassis of a Suburban but costs less when fully loaded.
A leaky AV is definately the last thing to be concerned with when considering an AV. You have to consider what you would really use it for, because the AV is a truck and and SUV with some really cool ideas packed into it. Either choice you make, they're both Chevys.
On a personal note, the only thing that I don't like about the AV is all the nooks and crannys that you have to clean. The cool factor out weighs this down point though. Another thing, I put 30 beers and 3 bags of ice in one of the side storage compartments, and still had room to spare....how cool is that.