[CT-449] Make it easy to run integration tests in an adapter plugin against a dbt-core PR

I dream of the ability to:
- Tag a PR in this repo with `test redshift`, `test snowflake`, `test bigquery`, `test spark`
- Have that trigger GHA to run adapter integration tests, _in those respective repos_, with links to each running test

This makes it easy to ensure that a PR changing adapter-facing functionality, such as #4986, does not include breaking changes for our adapter plugins. The alternative is to find out a day later, when the nightly integration tests have failed for that plugin.

### challenge

This would require the GHA for adapter integration tests to take an optional configuration, for the branch of `dbt-core` that they install in `dev_requirements.txt`. The configuration of that branch is already a problem for us, which requires manual intervention whenever we cut a minor version `.latest` branch: https://github.com/dbt-labs/dbt-snowflake/pull/128, https://github.com/dbt-labs/dbt-redshift/pull/89

Would it be possible to set this up so that:
- **In local dev**, the version of `dbt-core` installed by `dev_requirements.txt` is always `main` (i.e. local development). If you're making local changes to `dbt-core` as well, this shouldn't even take effect, since you already have your working local version of `dbt-core` installed in the same virtualenv
- **In CI**, the version installed can be set by supplied configuration (arbitrary branch/commit of `dbt-core`, as above). If not set, it should use the "target" PR branch (`main`, `1.0.latest`, `1.1.latest`, ...) — just requires us to be consistent in our branch naming across `dbt-core` + plugins