What type of foam to fill this pipe hole

I would be more concerned that your wire looks to be at the very bottom of the concrete, making it useless. Concrete is very strong under compression but very weak under flex. The steel is there to take the flex and should be about center of the concrete. Definitely use a concrete bonding agent and Ditra. The Ditra will provide enough of a barrier that the cracks should not come through. Personally I would not put a huge pile of money into tile considering it might end up cracking

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.)