Do I need Mexican pesos or are US Dollars and a credit card fine?

Almost all places in this region accept credit cards, and many accept US Dollars but perhaps not at great rates. a few pesos are a good idea but not necessary.

How much water should I bring with us for the day-long tour?

Your guide will have bottled water for your whole group throughout the day. There are opportunities throughout the day to buy soft or alcoholic drinks, and your guide may even make some cocktail or artisanal beer recommendations.