[CT-3293] [Feature] Use RelationType enum values in get_catalog_relations / Catalog artifacts

[dbeatty10]

Housekeeping

• I am a maintainer of dbt-core

Short description

Include values from the RelationType enum within the following:

• get_catalog_relations [1, 2]
• get_catalog [1, 2]
• Catalog artifacts

There should be a mapping from each adapter-specific table_type to a canonical relation type. Here's a swag at what it might look like:

Postgres table_type	Canonical relation type
BASE TABLE	table
LOCAL TEMPORARY	table
VIEW	view
MATERIALIZED VIEW	materialized_view
EXTERNAL	external
FOREIGN	foreign

Acceptance criteria

• dbt has an enumeration of canonical types of relations -- not all need to be representable within a database platform (i.e., ephemeral and/or cte)
• Each dbt adapter translates from adapter-specific types into canonical types
• Optional: both canonical and adapter-specific types are surfaced in macro SQL, metadata API queries, and generated artifacts (like get_catalog_relations and catalog.json)
• Add functional tests to dbt-core that validate that each valid materialized option results in the relevant enum witin get_catalog_relations and catalog.json
• Inherit functional tests in downstream adapters

Impact to Other Teams

• Cloud Artifacts (CA)
• Potentially others

Will backports be required?

TBD 🤷

Context

Initial background context from #8864:

I did some things. Please take a look at the four PRs attached to this issue.

It's worth noting that all four adapters which support materialized views (dbt-posgres, dbt-snowflake, dbt-bigquery, and dbt-redshift) were affected. All failed the new integration test that I created and all now pass that test. It's also worth noting that the new integration test tests all supported relation types for that adapter.

👇 And here's the key portion that prompted this issue being opened:

I'd like to call out one thing in particular; it looks like we're not consistent across adapters with what table_type returns. Most of them return one of "BASE TABLE", "VIEW", (and now) "MATERIALIZED VIEW"/"DYNAMIC TABLE". However, dbt-bigquery returns "table", "view", (and now) "materialized view". I did not want to change that because I didn't want to break something downstream. My knee jerk reaction is that these should return a value in the RelationType enum. I would consider all of this as beyond the scope of this fix though.

Originally posted by @mikealfare in #8864 (comment)

Other considerations

There are multiple places that contain similar logic. This is one of them:

• dbt_utils.get_table_types_sql()

[dbeatty10]

Needs refinement

I am not certain of the consequences if we don't do this -- it might be that this is ultimately not necessary or valuable!

Also not sure if we've covered all the key considerations (like all the places that have something similar).

Labeling this as "Refinement" to continue discovery work here.