Where could the water be coming from found in this hole my dogs dug?
My guess is this is just normal groundwater, either a natural spring or a high water table at that spot. (Other possibilities would be a leak, rainwater, or proximity to a septic system field.)

The water table height may be higher or lower depending on the season and amount of rain / snow. The water table may well be higher than the lake surface... they do not need to be the same height. The water table may be very irregular, depending on the underground position of rock, different types of soil, sources of water, etc, so just because there was no water somewhere else on your property doesn't preclude the possibility.

(The presence of water for only a brief time after digging is interesting, and I can't easily explain it. It's possible that by disturbing the soil your dogs were compacting it a bit, forcing water out, much like squeezing a wet sponge. This can be a problem in large-scale soil compaction projects, like preparing a lot for a new foundation. I would be surprised if dogs could have the same effect, but maybe if you have soft soil with a high water content it's possible.)