Some megastructures do have a practical use for the masses (the arena can host events for tens of thousands of spectators, for instance), but some others serve only the need of the few (or not at all: it served Gods).
If the sole purpose of the Pyramid was to ensure the successful reincarnation of the Pharaoh (correct me for errors), did any less "pious" people (however few) at that time consider it more useful to divert the resources spent on the Pyramids on other things (aqueducts, perhaps)?
The answer by u/mikedash is excellent, and sums up the historical evidence that relates to your question; I have a few things I'd like to add. I completely agree that no Egyptian at that time (or at least no Egyptian that would be likely to leave any evidence) would have complained about the pyramids, though it is worth noting that the kings who built the great pyramids (principally Snefru and Khufu) developed a weird reputation in Egyptian history. In Papyrus Westcar, written about 800-1000 years after them, Snefru is portrayed as a slightly comedic, very ‘nice’ king, while Khufu is also jovial but very interested in pursuing hidden, mystical knowledge. In Herodotus, 2000 years after his reign, Khufu is presented as an evil and unjust ruler who blocks up all the temples to build his pyramid (2.124-7), and this story is perhaps why everyone these days has such a negative view of the building of the pyramids. The usual qualifications about Herodotus’ trustworthiness regarding Egyptian history apply, and his chronology is wildly off. Certainly some of the ever-utilitarian Romans also shared this distaste for the pyramids: Frontinus, de aquis 1.16 writes that ‘you could compare the useless pyramids with the indispensable structures for so much water [the Roman aqueducts]’; Pliny, naturalis historia 36.8, describes the pyramids as ‘a useless and stupid display of the wealth of kings’; but Gaius Cestius, an elite Roman of the early Empire, built a monumental pyramid for himself that is still present today, and gives the Piramide station its name. Orientalist tropes are evidently present here – contrast the practical, intelligent Romans with the ostentatious and profligate Egyptians – and these have persisted to the present day, unfortunately.
I’d also like to call attention to two other flaws of your argument, which betray the modern, liberal (in the philosophical sense), enlightened context in which we live. The first of these relates to practicality. You claim that some megastructures have a ‘practical use’, citing (I assume) the Colosseum, but others serve ‘the need of the few (or not at all: it served Gods)’. This is a highly ahistoric argument, which belittles the experiences and thoughts of religious people: as u/mikedash points out, the deceased king in the Egyptian view of the world had a crucial role to play in life continuing, and required tombs constructed for him that would enable him to reach the land of the dead and exercise his influence there. Now, I don’t know about you, but I can hardly think of anything more practical than ensuring that the universe continues to exist! Someone might be so secure in their atheism (or whatever belief they subscribe to) that they can assert that the pyramids had no practical purpose, or any Greco-Roman temple, or any of the great cathedrals, but people cannot and should not approach history in this way – and, as I noted above, criticism of the pyramids in particular is the result of an Orientalist and racist Greco-Roman tradition. And what practical benefits did the Colosseum, or any modern stadium, provide? People can go and watch shows there (though they could already do this beforehand), but they often balloon in cost, result in people being kicked out of their homes and businesses, and as the 2004 and 2016 Olympics at least have shown, can very rapidly become disused and disruptive to the fabric of the city. They hardly provide anything actually essential to people, like food or water, or the preservation of cosmic order.
As for resources, this is the wrong way of thinking about it. Firstly, aqueducts in Egypt are entirely pointless: ancient Egypt as a ‘country’ was essentially just the Nile valley and a few oases, so 99% of the population lived within about an hour or two (at most) from a constant source of flowing, fresh water; irrigation (which did not even need to be particularly developed) could take care of the rest. Ancient Egypt’s climate was also wonky: for one of Egypt’s three seasons (akhet, the inundation), the Nile was flooded and so the entirely agrarian society had very, very little to do. And Egypt wasn’t some modern capitalist society: Khunanupe, your everyday Egyptian, couldn’t just make a start-up and get investors to sell some kind of niche tech product; he couldn’t just open up a bar or a café or some other hospitality business; Egypt had no need for an ever-increasing army of accountants and actuaries and investment bankers (it did, of course, have plenty of scribes, but it was not growth-obsessed like the modern world – there simply didn’t exist the constant pursuit of maximising future riches to make more and more money that is characteristic of our capitalist societies).
Employment was a duty of the state, delegated to the local governors (nomarchs) and temple complexes. So if you’re an Egyptian king, and because everything is going very well for your country you have a population boom and lots of resources, you employ these people for big infrastructure projects, like sending them off on mining expeditions to obtain rare minerals, or digging a canal to improve travel through the country, or (in later periods) forming an army to obtain tribute from foreign lands – or, indeed, building huge temples and tombs so that the gods and the deified deceased will keep the world alive. The state, rather than private business, ran the economy and provided for its inhabitants. So in many ways the pyramids, and the elaborate tombs of later dynasties, were incredibly practical and efficient projects: they made use of Egypt’s capital to provide Egyptians with state-supported employment based on state-supported resources in order to guarantee the preservation of the state and, indeed, the entire world. The inhabitants of the state-planned, state-funded, state of the art towns and villages that were built around the burial sites of the kings would certainly have disagreed that what they were working on was a ‘huge waste of resources’.
While there is always more to say, I answered a very similar question here a while ago. You might like to review that response while you wait for fresh replies to your query: