There's no magic number.
Water volume x head (drop) = energy.
1000 litres of water dropped 500m yields the same energy as 500 litres dropped 1000m or 5000 litres dropped 100m.
Highest single stage drop in the world is 1883m at Bieudron power station, Switzerland.
Highest in Australia is 835m at Poatina, Tasmania.
At the other end of the scale Cluny (Tasmania) is only 16m and 4.88m of that is due to the variable water level behind the dam, so it's down around 11m head at minimum water level.
That said....
Economics and practicality of construction favours moderate heads. That requires no special materials and typically ends up a fairly low cost installation relative to its electrical output.
Higher heads mean difficult construction and costly materials. Eg Poatina was pushing the limits and was notable at the international level when built (construction 1957 - 64) and in more recent times Bieudron the same during its construction completed 1998. Noting there that Bieudron did have a significant incident, rupture of the penstock, but has since returned to service.
In the opposite direction the problem with low heads is purely economic. In practice it tends to end up with a lot of civil works relative to power output. Eg Cluny was extremely marginal at the time, being built essentially as a "now or never" project following higher head stations built nearby and before the associated construction workforce and facilities were removed from the area. Economically it's piggy backed on the rest basically, it wouldn't have worked financially as a standalone project.
Much the same internationally. Plenty of low head stations but they're mostly either in the same "now or never" category following construction of much higher head facilities nearby, or they're simply an add-on to a dam built for some other purpose eg water supply and with the civil works cost attributed to that other purpose.
The big difficulty is economic along with community acceptance (environment etc). In a purely technical sense it's just water volume x head = output and pretty much anything can be built as long as the geology's OK.
In the Australian context nobody's sensibly suggesting we try and run the whole country using hydro, that's out of the question. Rather it's a question of whether or not more hydro should be built for medium storage or deep firming rather than using gas for that purpose. To the extent there's any serious debate about what ought be done, that's what it comes down to.
Some will advocate gas on the grounds of low capital cost, quick to deploy, "off the shelf" equipment, low physical footprint, etc.
Others will advocate hydro pointing to the finite nature of gas reserves and that gas has other important uses, CO2 and climate change, financial risk of gas prices going forward, etc.
I'll argue for proper science on a case by cases basis, since the issues surrounding hydro projects vary hugely from site to site, but in practice the politics favours gas as the path of least resistance. Hence most commenting will assume gas unless they're specifically advocating otherwise.