Obviously, it can be overdone, and one needs to know "where to stop" adding layers.
My rule of thumb is, that if one layer does not need to include any significant functionality / code, I don't need it. That being said, testability is a significant thing, so if a layer buys me better testability, I will add it.
Nothing in application-design comes without a price. Here the price is increased complexibility. You ALWAYS have to looks through your options before choosing the one that fits your needs the best.
With that being said, I think your question sounds a little biased. In the real world, you might not often see a customer demanding a new kind of DB, but you might often see a new costumer who wants the same kind of data-access than the previous, but on another DB. It's not only about ease-of-change, it's also about code-reuse.
It's very difficult to design the right business aligned conception for a software. I mean with an acceptable ROI.
However,for me, The number of layers or the level of low-coupling of a software conception is depending on the context in which the software will be used !
If you are sure about what the software has to do, and there will be probably not big modifications or adds in the future, a super low level coupling application with a lot of layers is not the solution. Because it makes the architecture too complex without any reason and the time invested for the architecture isn't profitable...
On the other hand, if you know that the application will have to be customized to each customer and will be certainly modify in depth, so time spent to a low level coupling architecture is a good idea...
Low level coupling can be smart when you want to reuse part of the code for other application. In this case, time spend for design is totally profitable, of course.
Then, in summary, I will say that all depends of the evolution of the software in the future and the potential of code reuse for other applications.
But it's a best practice for me doing not systematically very complex architecture.
When evaluating these things I usually find it helpful to look at it through the lens of the DRY principle and looking at duplication in your project.
If you're using an architecture that is more complex than your project you will tend to see a lot of layers that do little besides wrap other layers and pass on calls. This is repeating yourself.
If on the other hand you're using a thin architecture for a complex system you might find that creating functionality in one part of the system does not let you reuse that functionality in another part, forcing you to write a lot of very similar code all over the place. This is repeating yourself.
In my experience, if you are vigilant in identifying duplication and constantly refactoring to remove it, structure will present itself to you. Say for example that you're starting out with a rather basic architecture but breaking things out into methods as soon as they need to be reused, and breaking methods out into classes as soon as they need to be reused by other classes. Then you may eventually realize that your 13 Helper/Manager/Handler classes you've written to remove duplication actually look quite a bit like they're taking on responsibility that could have been more clearly defined if it had gone into a Service layer.
It can be tricky to know when it's worth it, but I think the key to success in constructing a system with a lot of layers is being very clear with what responsibilities the different layers should have. Any layer you add must be helpful to you in reducing the complexity of the operations in the layer above it and making it easier to weave functionality together in an elegant way.
Take a hypothetical example project. The tests use various in-process SQLite databases for the speed, the development database on some developer's computer is PostgreSQL, the staging database is a single Oracle install, and the production database is a massively scalable, very expensive, and very hard to configure Oracle cluster.
How would you ever pull that off without separating the SQL layer from the business layer?
It's a recent trend, and less experienced leads/architects are falling prey to jumping on the bandwagon.
In the project I'm on, data access went:
Service Interface -> Service Implementation -> Delegate Interface -> Delegate Implementation -> DAO Interface -> DAO Implementation -> Ibatis XML.
This was (and is!) bad in our case, because the most code in any Delegate Implementation method was a single line; it just called the DAO. For every DAO, we had two extra classes and one extra bean config, and we weren't gaining anything at all from this.
Accept the change, refactor, and move on. Use as many layers as it takes to piece apart different concepts... and then stop. If there's no perceived gain in writing some code except that it will follow a template, either slow down and try to fully understand why the template did that, or accept that the template is wrong for your application and ignore that bit.