[CI/CD Improvements] Update and simplify CD workflows

[mikealfare]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [X] I have searched the existing issues, and I could not find an existing issue for this feature
• [X] I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

As a maintainer, I want to simplify the CD jobs, so that deployment is more reliable and stable.

## Acceptance Criteria
- [ ] All workflows adhere to the style standards below
- [ ] This package is automatically published to dbt's private PyPI daily from `main`; the current workflow is cancelled in favor of the new workflow when this job is resubmitted
- [ ] This package can be published on demand to the public PyPI, GitHub, and Docker for the specified branch; the current workflow is cancelled in favor of the new workflow when this job is resubmitted for the same branch
- [ ] This package is tested against appropriate published packages prior to releasing
- [ ] I can publish to both test and production versions of publishing targets
- [ ] Publishing to PyPI is authorized via Trusted Publishers
- [ ] Integration tests are only configured in one place (e.g. environment variables, the Postgres service, etc.)
- [ ] Legacy workflows are removed (e.g. `release`, `release-internal`, `version-bump`, etc.)

Suggestions

• Create two workflows:
  • release-cloud.yml
  • Exists as release-internal
  • Run on schedule: 22:00 UTC
  • Run on workflow_dispatch
  • Test against private PyPI
  • Slack notification on failure
  • release-open-source.yml
  • Exists as release
  • Run on workflow_dispatch
  • Test against public PyPI
  • Slack notification on failure

Style standards:

• Job ids, step ids, and variables are in kebab case
• Job names, step names, and description fields follow dbt docs standards (capitalize first word only)
• Extra descriptors should be avoided unless required for disambiguation, e.g.
  • version-number -> version
  • archive-name -> archive
• Workflow files use a four space tab
• Scripts (inline or separate files) use environment variables in env instead of inline substitution like ${{ inputs.value }}

[mikealfare]

This issue is consolidated into https://github.com/dbt-labs/dbt-snowflake/issues/1004.