[Bug] Incorrect dependency causes older version of dbt-core to be installed

Is this a new bug?

[X] I believe this is a new bug
[X] I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

pip install dbt-snowflake==1.8.4 causes dbt-core 1.8.7 to be installed.

Expected Behavior

pip install dbt-snowflake==1.8.4 should install dbt-core 1.8.8 as this is the latest version available.

Steps To Reproduce

Install Python 3.10.12 with venv support.
python3 -m venv --clear --upgrade-deps env
source env/bin/activate
pip install dbt-snowflake==1.8.4 -vvv --no-cache-dir

Relevant log output

...
...
Collecting dbt-core>=1.8.0 (from dbt-snowflake)
  Obtaining dependency information for dbt-core>=1.8.0 from https://files.pythonhosted.org/packages/ac/97/f5fe35037be344888abe713b71dfd57f76abeb240c299e530e06001e27de/dbt_core-1.8.8-py3-none-any.whl.metadata
  Created temporary directory: /tmp/pip-unpack-96j22jvy
  https://files.pythonhosted.org:443 "GET /packages/ac/97/f5fe35037be344888abe713b71dfd57f76abeb240c299e530e06001e27de/dbt_core-1.8.8-py3-none-any.whl.metadata HTTP/1.1" 200 3879
  Downloading dbt_core-1.8.8-py3-none-any.whl.metadata (3.9 kB)
...
...
INFO: pip is looking at multiple versions of dbt-core to determine which version is compatible with other requirements. This could take a while.
Will try a different candidate, due to conflict:
    dbt-snowflake 1.8.4 depends on dbt-common<2.0 and >=1.0.4
    dbt-adapters 1.7.2 depends on dbt-common<2.0 and >=1.11
    dbt-core 1.8.8 depends on dbt-common<1.11.0 and >=1.0.4
Collecting dbt-core>=1.8.0 (from dbt-snowflake)
  Obtaining dependency information for dbt-core>=1.8.0 from https://files.pythonhosted.org/packages/81/01/67e9acf51e5b5326649066bc3ffc39dfe520dc5683bf980ab3abb7151a71/dbt_core-1.8.7-py3-none-any.whl.metadata
  Created temporary directory: /tmp/pip-unpack-h8gvyud1
  https://files.pythonhosted.org:443 "GET /packages/81/01/67e9acf51e5b5326649066bc3ffc39dfe520dc5683bf980ab3abb7151a71/dbt_core-1.8.7-py3-none-any.whl.metadata HTTP/1.1" 200 3924
  Downloading dbt_core-1.8.7-py3-none-any.whl.metadata (3.9 kB)

Environment

- OS: Ubuntu 22.04.5 LTS
- Python:3.10.12
- dbt-adapters: 1.7.2

Additional Context

I believe the issue is that dbt-adapters specifies >=1.11 in its requirements. It's difficult for me to determine whether the problem originates from dbt-snowflake, dbt-adapters, or dbt-core, but since dbt-core and dbt-adapters are supposed to have a relationship with all adapters, I concluded that dbt-adapters is likely the cause of the direct dependency conflict.

dbt-labs / dbt-adapters