Improve dependency management

### What would you like to be added?

The current dependency management process involves a lot of manual intervention. There have been some attempts at improving it, including updating the `update_deps.sh` script, trying out different dependabot configurations, and evaluating other tools besides dependabot.

I want to formalize the work and have a central place to discuss alternatives, pros, and cons.

### Possible approaches

1. Do nothing / keep dependency management as it is right now. As stated before, the current process requires a lot of manual intervention, and the toil is high. The time to create the weekly PR and merge it keeps sliding into later in the week. There are times in which by human error, the commits are not properly bumping the dependencies.
2. Improve dependabot configuration (so far, I've tested the following options):
1. Use the [_new_ `directories` property](https://github.blog/changelog/2024-06-25-simplified-dependabot-yml-configuration-with-multi-directory-key-directories-and-wildcard-glob-support/): The result of this is multiple pull requests for the same dependency across all of the submodules where it is present. Although we (@jmhbnz and I) thought [this](https://github.com/dependabot/dependabot-core/issues/7547) was going to be the silver bullet, in the end, it doesn't work as we expected. Enabling this will just add noise to our repository, as we still need to create a PR to bump dependencies manually. See the following example of `go.opentelemetry.io/otel/trace` in my fork: https://github.com/ivanvc/etcd/pull/410 https://github.com/ivanvc/etcd/pull/406 https://github.com/ivanvc/etcd/pull/390 https://github.com/ivanvc/etcd/pull/386.
2. Use the `directories` property and [`groups`](https://github.blog/changelog/2023-08-24-grouped-version-updates-for-dependabot-are-generally-available/). Although this looked promising, as it creates a single pull request with all the weekly dependencies, the issues that I find are: 1. it creates a single commit for the whole group (rather than one per dependency bumped), 2. it doesn't run `go mod tidy`, and it breaks our CI. See an example in my fork: https://github.com/ivanvc/etcd/pull/265 (in the files changed and any `go.sum` file, it's clear that it is not tidying them).
3. Replace dependabot with renovatebot. I noticed that several projects on board for dependabot's `directories` beta feature moved to renovate. The immediate issue is that we would need a new GitHub application installed for our GitHub organization. This is not the silver bullet but may help decrease the effort. I tested some configurations in my fork:
1. One pull request per dependency bump: This would be similar to what we have right now, but it bumps the dependency across all submodules (and tides them). This will be a minimal improvement, as we can merge and close these PRs without the manual step to generate a grouped PR. Refer to an example in my fork: https://github.com/ivanvc/etcd/pull/435
2. Grouped pull request: This doesn't work as expected. It's similar to dependabot's approach (single commit for all bumps), although the community seems interested in having multiple commits (https://github.com/renovatebot/renovate/issues/7288). An example of this configuration in my fork: https://github.com/ivanvc/etcd/pull/442
4. A wild idea would be to write our own automation, but I think it would be less than ideal as we'd need to maintain another project.

I lean towards 3i (renovate + one pull request per dependency), which would be one small step to improve the process. But I want to open the topic for discussion.

cc. @ArkaSaha30

### Why is this needed?

To decrease the toil of keeping dependencies up to date.

Start by reading the update_deps.sh script and the current Dependabot configuration, then compare the Renovate configurations and dependency-update examples linked from the issue. Done means selecting and documenting a dependency-management approach that reduces manual toil while preserving the required Go module updates and CI behavior.

Written by the indexing model from the issue text.