Good design and getting everything right up works right up until the requirements change and the design no longer meets the ask. Unsurprisingly, teams might not even know all the requirements when building something new. Having everything orderly up front is a pipe dream.
That said, there are definitely cases where sitting back and thinking through a design is a really good idea. In my experience, it's 1/10th of the job.
Good design can handle changing requirements, otherwise it isn't good design. Good design requires a lot of domain expertise and not just being a good programmer since you have to be aware of what kinds of things the system should be able to handle, but it isn't impossible.
By domain expert I mean someone who has worked in a single domain for many years. Lets say you have written healthcare backend software for 10 years, then if they tell you to design a new healthcare software system then you will be able to predict most future requirements the system will have even if they don't spell those out explicitly to you. Sometimes laws changes and you have to redesign stuff, but having to adapt code due to new laws isn't a common occurrence.
Edit: And 10 years in a single domain really isn't a lot of time. In a sane world you would be called an apprentice until you reach that level, that is the only way to make robust products. This is how almost every other field works, but in programming people seem to think that such expertise is impossible, at least at scale.
That said, there are definitely cases where sitting back and thinking through a design is a really good idea. In my experience, it's 1/10th of the job.