It was mocked in its earliest days, as a dotty dream, an architect's fancy or just the "puddle in the park". But after nine years of arguments and scrabbling for money, the Yorkshire city of Bradford has pulled it off: a vast mirror pool splashed by 107 fountains now glistens beside the Victorian city hall.

The largest water feature in any UK city, the £24.4m pleasure ground's cycle starts with low-lying steam at dawn – an urban Scotch mist – then sees three shallow sunken bowls slowly fill with water until dusk when an underwater light show begins.

Camera-controlled lasers will also play tricks with visitors after dark, lassoing them with loops of light as they stroll around an area as big as three full-sized football pitches. Other features include "walking on water" – a narrow causeway across the flooded area – children's fountains that throw balls of water at one another, and a central jet with a maximum reach – reserved for special events and dubbed the Bradford Burst – of 30 metres.

"We haven't got anything like this in York," said Helen Lock, who was over from Bradford's more famous tourist neighbour with her engineer husband Nigel, to give their children a half-term outing at the National Media Museum. "It beats anything in Sheffield as well," agreed a picnicking couple from South Yorkshire on the next bench. "We've got some good fountains in our Peace Gardens, but not a patch on these ones."

Such praise will delight Bradford's cabinet member for regeneration, Labour councillor David Green, and the council's strategic director, Barra Mac Ruarí, who have overseen the plan's implementation. The lake has changed shape from Alsop Architects' original teardrop – "It virtually lapped at the walls of City Hall and we weren't sure about that," says Green – but the scale and ambition are a match for Bradford's grand 19th-century "Woolopolis" heyday.

Those historical watery achievements included Europe's largest sewage works with a three-mile disposal tunnel big enough to take an opening procession of locally made Jowett cars carrying the lord mayor and aldermen.

In the same spirit, pre-launch VIP visitors for the new water feature have been ushered into the urban lake's underground workings – six kilometres of piping and a tank like a Byzantine cistern in Istanbul. "We have got to think big and be bold because that is the way to bring in business and visitors," said Mac Ruarí.

The pool and its sinuous park, which has London plane, larch and Scots pines planted instead of dinky blossom trees, won all-party support on independent estimates that it would bring £80m a year to Bradford. Officially called the City Park, it connects the Victorian centre of Bradford with the National Media Museum, the Alhambra theatre and the university, whose buildings and parkland run up a gentle valley to the west.

Attention now turns to attracting new businesses to finance a cluster of proposed office blocks beside the pool, and the long-awaited completion of a Westfield shopping mall on the other side of city hall. The recession stalled work there when only the foundations had been laid, leaving a desolate empty space nicknamed the Hole in the Heart, marginally improved in the last two years by a temporary minipark.

Other challenges include converting the derelict Odeon cinema, which overlooks the mirror pool, into offices with Homes and Community Agency funding, and talking up the image of Bradford as more than Leeds' has-been, poor relation. Gideon Seymour, who runs an art gallery between a cafe and a restaurant by the pool, says: "Each success breeds more, and the pool and fountains are the biggest success so far."

Sceptics remain, in the local tradition that holds that Bradfordians always wear macs because if it isn't raining, it soon will be. One visitor in the sunshine, Marjorie Atkinson, 71, asked: "Are you anything to do with this? Because it's a waste of £4m."

Told the true cost, she rolled her eyes. But then, the arrival of the Jowett procession at Esholt sewage works in 1923 was greeted by the project architect's wife with the words: "Here comes the first load of shit."