The core idea of the Repository Pattern is that it is a method of encapsulating data access and moving it away from your code. Allowing the rest of your code to talk to the database using the domain objects that are moved around through the rest of the application.
A good repository will only implement the functions that are required by its consumers rather than adding functions that would be nice to have but may never be used. If you never need to update an item don't have a method for it.
The Repository Pattern has several advantages:
This allows the Repository to be mocked out in unit tests allowing the tests to run quickly without requiring complex database set up and tear downs.
Abstracting away the database means that complex data structures that may be used in storing the data only need to exist next to the database while the rest of the code can use meaningful data structures that model the domain.
This abstraction also allows for an required changes to how the data is stored to be limited to a single place. The example of picking a different database always seems convoluted but I have been required to add extra fields for indexing more than once.
The key difference between a Repository and a Data Access Layer or Tier is that the Repository implements interactions in domain language. These may at times cover multiple database operations. e.g.
repository.StoreUpdatedItemOrder([Items]) may update the order property on each of the persisted items.
The risk with a repository is that it can become bloated. Multiple consuming classes/functions can result in a large interface that is prone to changing or in an interface that is difficult to change for fear that the change will break one or more consumers.
To mitigate the risks of the Repository Pattern;
Keep the Repository need by adopting the YAGNI (You Aint Going to Need It) principle and only implementing the methods required for a consumer.
Keep consumers safe from changes by adopting the Interface Segregation Principle and Dependency Inversion Principle. Have each consumer own their own interface to the Repository and have the Repository implement these interfaces. That way a change required for a single consumer does not need to ripple out to other consumers that do not require the change.