This is a question of both implementation and design. Fountains use water in the following ways:
Based on their surface area and the climate that they are installed in, fountains have evaporation rates comparable to any other body of water like a lake or pond so they lose water based on their environment including wind, temperature and humidity.
If a fountain has a waterfall, nozzles or other aerated display, this exposes more surface area to evaporation and can increase water use. The design of these elements within the fountain basin have to be well executed to contain overspray and splash without spraying or splashing outside of the basin and additional loss of water.
When fountain filters are cleaned, the water in the filter tank is either drained to access the filter elements (in the case of cartridge filters and bag filters) or in the case of sand and DE filters the water is flushed through the filter tank at a high flow rate and sent to sewer. On large fountains with equally large filter systems, this can amount to significant amounts of water.
Note: While not always necessary, a backwashing system often provides benefits to the overall operation of the fountain. As water evaporates from the pools, the Total Dissolved Solids (TDS) increases because the minerals stay while the water leaves. Backwashing the filter removes some of that water which is replenished by the fill system, helping dilute the buildup of minerals in the water.
Whether fountains are a waste of water is a more subjective issue as for many years fountains were used as points of social