Barrel and pump is a good idea.
Just need one barrel, fill about 1/2-3/4 full, pump the water up hill, let it run down a nice mountain looking landscape like a stream, over a small water fall and the wheel making the power, back into the stream and funnel it back to the same barrel under the table. You can turn it on and forget it, it will run as long as your pump has power and it looks nice also.
I had forgot about that, but years ago a friend that was really into model trains and landscape scenery had a real system going!! This would win a science fair most likely but alot of work and some money. What he had was a mountain landscape and HO train sets and buildings bridges ect.. He made a real river running down the mountain. Used rain gutter for the river bed and some pee gravel and pretty rocks. On that river he made a grist mill I think, some type of building with a water wheel anyway. Inside that building was a motor connected to the wheel. His train was permanantly set to one speed. When he turned on the water pump the water flowed down the river and drove the wheel, the motor made the electric to run the train. Stop the wheel the train stopped.
I remember the train setup he had but not sure exactly how it was built. He had a 55gal barrel under the table and a submersable pump in the barrel and used something like 1" black utility tubbing for the water line (flexable pipe) PCV pipe should work for a straight run. I have no idea the volume of water he moved, it was alot for his setup.
In scale it kinda looked like lake Erie sitting on top a mountain and Niagra Falls at the water wheel compared to the HO train running around it I geuss. It did look great, but maybe the wheel was a bit large. He also had a carnival setup in the town scenery and his water wheel was about the same size as the HO scaled Ferris wheel.
I think for the lake on the mountain top he used a 5gal bucket or maybe cut it half hieght for 2 1/2 gal.
The first time he started it up it made like a small fountain in the center of the lake the water came in to hard straight up, so he had to make changes there. I forget if he simply put in a baffle to spread the water to the sides or if he changed the inlet to the side of the bucket.
Hmm, keeping the idea of the train. Maybe do a Water wheel above the grand canyon for power to a motorcycle race track and do a replica of Evil Knevil jumping snake river
.