[Feature] use redshift_connector for metadata wherever possible

[dataders]

Is this your first time submitting a feature request?

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

Describe the feature

Wherever possible, fully rely on redshift driver for metadata and not SQL info_schema or any postgres system view like pg_*.

This will future-proof the adapter as well as unblock robust support for Datasharing (see #742)

Exhaustive list of related issues and PRs (so far)

Throughout the process, we may discover shortcomings in the driver. As @ryanwaldof says in #742:

If this isn't possible because our APIs don't support your use cases lets chat so we can figure out how to fix this. If we need to provide more info in our APIs, we need to provide more info in our APIs.

In these circumstances, migration to driver for metadata would pause while the driver adds the required information.

Additionally, it is crucially important that these changes are sufficiently tested and performance is considered.

One idea that @colin-rogers-dbt and I discussed was that these metadata-related changes might be merged into a feature branch for longer-term testing with customers before merging and releasing as a new minor version of dbt-redshift.

related issues

Tasks

Beta Give feedback

1. [CT-1122] [CT-1116] [Bug] adapter.get_relation for relations in other databases [RA3] #179
   bug help_wanted ra3_node +3
2. Override check schema exists #452
   Stale cla:yes ok to test +3
3. override get_columns_in_relation with driver api call #554
   cla:yes ok to test +2
4. [ADAP-726] [Feature] Remove references to pg catalogs in hard-coded queries #555
   enhancement +1
   
5. migrate from pg_user #571
   Stale cla:yes +2
6. [Bug] adapter.get_columns_in_relation does not work cross database #736
   bug ra3_node +2
   
7. [Feature] Add support for federated query external schemas #816
   enhancement triage +2

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response