[Feature] metadata-based freshness checks for external tables

[dataders]

Is this your first time submitting a feature request?

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

Describe the feature

spin out of https://github.com/dbt-labs/dbt-snowflake/issues/1054

dbt-snowflake should support metadata freshness of Snowflake external tables. The implementation would involve parsing the external tables's stage and location fields to SELECTing the corresponding last_modified metadata from the DIRECTORY({ stage })

Describe alternatives you've considered

• we don't support metadata freshness for external tables
• https://github.com/dbt-labs/dbt-core/issues/8797 to allow users to specify custom metadata freshness queries

Who will this benefit?

users of external tables in dbt-snowflake projects

Are you interested in contributing this feature?

No response

Anything else?

No response