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

[jtcohen6]

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: dbt-labs/dbt-snowflake#128, dbt-labs/dbt-redshift#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

[jtcohen6]

Probably a cross-repo GHA workflow, which dynamically updates dev-requirements.txt.

Halfway step here: Trigger on merge to main. (Then we skip the need for dynamic updates to dev-requirements.txt.)

@colin-rogers-dbt's idea: Always run against dbt-postgres, and then one of the "fun" ones chosen at random, rather than all adapters every time. test random?

Honestly would just be very helpful to have this as a manually triggered GHA, too! Rather than the manual PR process today...

[emmyoop]

The adapters are already going to need a script to automate updating dev-requirements.txt for cutting the *.latest branches per dbt-labs/actions#76. That's a lot of the lift to achieve this honestly. Being able to kick off an adapter test before merging to main by adding a label to a core PR would be amazing.

[colin-rogers-dbt]

Possible hacky solution to running integ tests off a core branch: dbt-labs/dbt-redshift#329

[colin-rogers-dbt]

If we propagate this change to the other adapters we can run integration tests against a core branch.

The remaining problem is how to have core invoke the adapter test workflows:

1. workflow-call: run the workflows in the dbt-core repository scope. The upside of this is that it's what GHA security model expects you to do so it's much cleaner. The downside is we would need to make each adapters configuration available in dbt-core. This would require either a shared secret store outside of github, duplicating configuration or storing credentials at the github org level.
2. repository-dispatch: run the workflows in the adapter repository. No need to manage credentials across repos but it's much more difficult to monitor and manage. It also could open a potential security issue. Here's an example of what we would need to add per adapter: Add repository dispatch for integ test workflow dbt-redshift#334

I'd recommend we go with option 1 and just duplicate configuration until we can use a shared secrets manager.