This is a very general question so the answer can be unavoidably "it depends".
As an example, JPA (Hibernate) and Entity Framework are attempts to create abstraction over (mostly relational) databases - so that your app is agnostic of the actual SQL database engine it is running on.
That's great, but it's not free. What you get is more flexibility (you can theoretically switch the underlying DB engine) and better abstraction, but you lose the possibility to use more advanced features of the DB for e.g. better performance. Also, these abstractions are often quite "leaky". Replaced DB (and app) will still probably behave a bit differently than the old one. You still need to be aware of what SQL does, basics of relational model. So it can actually be more difficult for newcomers - they need to know both SQL and framework of your choice.
But this is still not totally agnostic. JPA/EF are again just a piece of technology which you want to be agnostic about. So you create your own layer on top of JPA/EF which hides away all the implementation details (entitymanagers, dbcontexts etc.)
People typically employ existing patterns (dao, repository) for this purpose. New employee still needs to know SQL, JPA/EF and now your shiny new layer.
Situation described above is actually quite common, but it's far from "as much agnostic as possible". You could also stop assuming that you use db with relational model (NoSQL), that your db supports transactions and ACID, then you end up with having programming model of essentially persistent key-value storage. You need to think about what you lose and what you gain.