[Feature] freshness tests on external tables should fail to parse loaded_at_field

[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 functionality, rather than a Big Idea better suited to a discussion

Describe the feature

see dbt-labs/dbt-redshift#757

metadata-generated freshness tests (ie those defined without a loaded_at_field) are not supported for External Tables. In addition to documenting this, dbt should error in this scenario and tell the user something more meaningful than current state.

Current behavior

Got an invalid result from "get_relation_last_modified" macro: []

Proposed Behavior

external table source XXX.XXX has metadata-based freshness test defined which is not supported. please add loaded_at_field or remove the freshness test

Describe alternatives you've considered

Perhaps some data platforms do support metadata-based freshness checks for external tables?

If so, perhaps this justifies an expansion of the Capability.TableLastModifiedMetadata entry of the Capability matrix (e.g. SnowflakeAdapter's
)

Who will this benefit?

dbt users with external tables defined

Are you interested in contributing this feature?

No response

Anything else?

No response

[dataders]

somewhat related to #9320?

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.