I wonder how accurate the USDA soil maps are?
IMO, they are surprisingly accurate! Although they are certainly not perfect.
Do you own a backhoe or mini-excavator? I frequently rent a mini-excavator for work at our property. They are very cheap to rent over the weekend, and you can learn the controls very quickly.
Dangerous rookie mistakes (that I am aware of):
1.) Must call your underground locator services and abide by their notifications exactly. Electric lines and product pipelines will kill you!
2.) Must be an expert to traverse steep slopes.
3.) Must be an expert to operate on rocky slopes.
On flat ground, you can easily dig multiple test trenches in the areas you are considering for your dam. You can also test for clay content. (If you can make a "snowball" from a moist sample - good. If you can roll a "snake" between your palms - better!) You can also determine depth to bedrock. Finally, you can also assess if you have abundant groundwater. (Your trenches should show water within a few minutes to hours. But this will then stabilize at the water level of your local aquifer.)
P.S. You can also do the rest of the things on your chore list. Like dig out some stumps, install that culvert under your driveway that your wife wanted, etc.
P.P.S. There is a chance your NRCS office or a state agency may have a service available to drive a soil probe rig into your property and get you some samples. (The rigs I have seen for samples down to 25' are more difficult to move through your forest than a mini-excavator.) There is even a chance a governmental agency will provide planning or even financial services to help you build a pond. Definitely do a search on your state agencies.
You also need to contact your conservation agent at your local NRCS. There is one for every county in the U.S. My guy is very knowledgeable, and a good one can guide you to all of the available resources.