Add a macro to get the last modified time of a relation

^{Originally posted by **yu-iskw** August 7, 2024}
Hi all, how about implementing a dbt Macro to get the last modified time of a relation like `last_modified_at(ref("..."))` so that we can implement 3rd party macros to check the model freshness? dbt Source freshness enables us to check the last modified time of a dbt Source, if we don’t specify `loaded_at_field`. And dbt Adapters as dbt-bigquery has the implementation to calculate the gap between the current timestamp and the last modified time of a relation. Wa can take advantage of the functionality not only for dbt source freshness check.

https://github.com/dbt-labs/dbt-bigquery/blob/71dd5753173fac24976b0d36586fe73a47b3a8ab/dbt/adapters/bigquery/impl.py#L757C9-L775

Meanwhile, dbt-utils Package has the recency macro. However it doesn’t support to check the model freshness based on the last modified time. If we want to support the functionality to check the last modified time, we need to get the metadata of a relation. But it would be a bit hassle to implement the functionality only with dbt Macros for different warehouses. What do you think?

1. We can support a dbt Macro to get the last modified time like `last_modified_at(ref("..."))`
2. We can do refactoring `calculate_freshness_from_metadata` in dbt Adapters by extracting the part to get the last modified time of a relation in addition to a dbt Source.
3. We can extend `dbt-utils.recency` to check the last modified time of a relation by using the proposed dbt Macro.

Originally posted at https://getdbt.slack.com/archives/C2JRTGLLS/p1722847041393809