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

[joellabes]

Discussed in #10537

^{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