[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

[github-actions[bot]]

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[bot]]

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.