[Feature] Add support for federated query external schemas

[ronco]

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

Redshift can pull data from a remote RDS database, a process known as federated queries. However, dbt does not recognize tables in these external schemas as valid relations. Ideally, these tables would work out of the box with dbt-redshift.

I believe this is due to the code making use of information_schema.tables and pg_class, neither of which include federated query tables. Using svv_tables for these relation lookups would expose the external tables.

Describe alternatives you've considered

I've made this work by creating a materialized view of the external schema table. The downside is the additional creation step and manually refreshing the view before use.

Who will this benefit?

This will benefit any database engineer or developer who uses federated queries to pull data from RDS databases such as Postgresql.

Are you interested in contributing this feature?

No response

Anything else?

Querying data with federated queries in Amazon Redshift

[dataders]

@ronco the creation of these objects is something that's already possible for other dbt adapters via dbt-external-tables. We're actively working to bring the notion of External Tables into dbt, could you be so kind to add a comment to https://github.com/dbt-labs/dbt-adapters/discussions/92 requesting that federated queries be supported?

however, it sounds as if the package supported their creation. there's another problem.

even if you create them outside of dbt, you cannot refer to them within dbt, because dbt cannot discover that they exist, right?

I believe the solution to that will be to address #776.

[dataders]

@ronco the creation of these objects is something that's already possible for other dbt adapters via dbt-external-tables. We're actively working to bring the notion of External Tables into dbt, could you be so kind to add a comment to https://github.com/dbt-labs/dbt-adapters/discussions/92 requesting that federated queries be supported?

however, it sounds as if the package supported their creation. there's another problem.

even if you create them outside of dbt, you cannot refer to them within dbt, because dbt cannot discover that they exist, right?

I believe the solution to that problem will be to address #776.

[ronco]

Hey @dataders , thanks for following up! I've left a comment on that discussion regarding federated-queries. I also agree #776 would likely address this. Since #776 already addresses the concern I'll go ahead and close this feature request.
Thanks!