These fountains hold many secrets about how the universe works. We can start teasing out these secrets by asking basic questions such as; How does the water go up? Well the water is pushed through a piping system, the push that gives the water velocity through the pipes is caused by a difference in pressure. Why does a difference in pressure cause a force? This phenomenon is a consequence of Newton's classical laws of physics. Pressure is simply a force exerted upon a unit area, when there is a difference in pressure across a surface e.g. the circular disc of air which lies between the atmosphere and the pipe entrance. Then there is a difference in force. This implies that the resultant force is non-zero as the difference in pressure gave rise to a difference in magnitudes of forces. According to Newton's second law, when a resultant force exists a system is moved from equilibrium... basically free molecules or objects will accelerate in a direction. In this case the water from the higher pressure tank (greater force) will move towards the area of lower pressure (the atmosphere) at an acceleration proportional to the difference in pressure. Hence we see jets of water.
We can ask another basic but more interesting question... why does the water stop going up and then why does the water fray/ spray when it does? As soon as the water escapes the pipe i.e. pierces through the surface, the force due to the difference in pressure stops acting. The water coming out of the pipe will experience gravity as a resultant force (it always will experience gravity in the pipes but gravity dominates once it exits them) which acts towards the center of the earth. The water jetting up will decelerate and at one point it will stop in order for it to go back down again. Let's imagine we are now water droplets on the top of the jet of water from the fountain. We were moving upwards but gravity has decelerated us until we have stopped. Now for us to go down it would be simple to just go down the same way we came up... however below are other droplets moving upwards that are in fact catching up with us. As the other droplets hit us... they cause us to go left right... or in a direction other than straight down (which is in fact nearly infinite amount of directions) we cannot calculate what small molecules exactly do ... so we assign probabilities to which way the droplets (and us if we stick to our imaginary scenario) will go. So zooming out we see the water fraying because many droplets go in many directions which comes across as a spray. The wind will also affect the likelihood of the different paths the droplets will choose to go back down to earth.
There are yet deeper and harder questions, for example, why does the water flow the way it does? This question is the quest of thermodynamics and fluid dynamics and is an extremely hard question to answer, because the many molecules moving through pipes etc. is extremely complicated. Maybe every time you push water through your tap... it will never flow the exact same way, some molecules will be out place. We can then ask why molecules... atoms.... matter itself behaves....
This is just a preview to how the real world and the basics of everyday life are doorways to the governing dynamics of the universe. What I mean by the governing dynamics are the fundamental mathematical laws to which the universe or simply certain systems, may it be a neuronal circuit, population dynamics or the workings of the atom obey. Looking for these dynamics... these rules and understanding them is the object of desire of someone who wants to know how the world works. Some may say it is sort of religious, looking for the divine nature of the universe.... but to me it's just fun.
Why should we look for the 'governing dynamics'? Firstly, it's useful. Understanding and consequently predicting events in the world can help us in furthering life expectancy and increasing a healthy and happy life. Secondly, some of us are curious. We want to know and we will know (hinting at 'We must know. We will know.' spoken by the great mathematician David Hilbert) how the universe works, like some bodybuilders get a 'pump' from lifting weights... those with curiosity get an intellectual 'pump' from uncovering a beautiful mechanism of the world.
