[Bug] renamed_relations not providing overriding hook to adapters

[VersusFacit]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Surfaced from debugging this transaction error in this Redshift issue.

In short, pre-dbt-adapters renamed_relations is defined here in core.

It is used to know when a rename instead of a get_drop_sql macro can be called. This was implemented without mechanisms to support for adapter overrides leading to obscure behavior in Redshift adapters and presumably Bigquery, Databricks (they've been notified via Slack), Snowflake, and so on.

For verbose details, see this lovely comment from @mikealfare .

Expected Behavior

renamed_relations should not return null, but that's what it's doing. So boolean checks using it return "falsey" results.

Steps To Reproduce

This test in Redshift or other relevant repos.

from dbt.adapters.redshift.relation import RedshiftRelation
from dbt.contracts.relation import RelationType # dbt.adapters.contracts.relation in dbt ~=1.8


def test_renameable_relation():
    relation = RedshiftRelation.create(
        database="my_db",
        schema="my_schema",
        identifier="my_table",
        type=RelationType.Table,
    )
    assert relation.renameable_relations == frozenset({
        RelationType.View,
        RelationType.Table
    })

Relevant log output

No response

Environment

- OS: 13.2.1
- Python: 3.11.0
- dbt: 1.7.3

Which database adapter are you using with dbt?

redshift

Additional Context

Per Mike

The fix needs to be implemented in dbt-core>=1.6 and dbt-adapters>=1.8. The tests need to be implemented in dbt-snowflake>=1.6, dbt-redshift>=1.6, and dbt-bigquery>=1.7.