Detect failures in CSP inputs and meticsets

[zmoog]

Situation

2 weeks ago, a simple version bump of the core aws-sdk-go-v2 module to 1.26.1 put the AWS SDK module versions mix in an inconsistent state, causing error in multiple AWS inputs and metricsets.

We are addressing this specific error at #39454

Problem Statement

While we could consider this an edge case, the current suite did not catch the dependency problem.

We should probably:

• Understand why the test suite did not catch the dependency problem.
• Set up mechanisms to avoid similar problems in the future

[zmoog]

To improve dependency management, we're setting up Dependabot to handle SDK dependency updates for all major CSPs. See #39492 for more.

[elasticmachine]

Pinging @elastic/obs-ds-hosted-services (Team:obs-ds-hosted-services)