> the place you want to be looking is the Great Lakes area (and, probably, specifically within the drainage basin of the Great Lakes so you don't run afoul of the interstate water compact

Actually, not so much. There's a lot of water in the lakes, but it's not being replaced quickly. Lake superior actually has a mean retention time of 191 years. If a lot of people start drawing water out, the lake will shrink.

Also, the water resources compact is international, not just between states, and even being in the states' area doesn't mean you can source water from the lakes.