[Bug]: metadata freshness checks do not work properly

[KingLommel]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

When I run dbt source freshness without using the loaded_at_field keyword (see https://docs.getdbt.com/reference/resource-properties/freshness), then according to the documentation dbt should calculate freshness via warehouse metadata tables for supported adapters.

Although I checked the metadata-tables , the freshness checks failed.

Using the debug flag of dbt I saw the following relevant lines

...
dremio adapter: On source.<path>.<to>.<mytable>: select committed_at as last_modified,
                (SELECT CURRENT_TIMESTAMP()) as snapshotted_at
          from TABLE( table_snapshot(<path>.<to>.<mytable>) )
...

The main point is, that the select committed_at does not use the max value but all of the commited_at values. Therefore the freshness checks fail.

Suggested Solution:

Having a look at this page dbt-labs/dbt-core#8307 I was able to find out what macro is responsible for that behaviour: dremio__get_relation_last_modified.

At this point https://github.com/dremio/dbt-dremio/blob/main/dbt/include/dremio/macros/adapters/metadata.sql we can see the macro

{% macro dremio__get_relation_last_modified(information_schema, relations) -%}
  {% set relation = relations[0] %}
  {%- if relation.type != 'view' -%}

    {%- call statement('last_modified', fetch_result=True) -%}
          select committed_at as last_modified,
                {{ current_timestamp() }} as snapshotted_at
          from TABLE( table_snapshot('{{relation}}') )
    {%- endcall -%}
  {%- else -%}

  {%- endif -%}

  {{ return(load_result('last_modified')) }}

{% endmacro %}

Changing the select statement to use max solved all my problems:

{% macro dremio__get_relation_last_modified(information_schema, relations) -%}
  {% set relation = relations[0] %}
  {%- if relation.type != 'view' -%}

    {%- call statement('last_modified', fetch_result=True) -%}
          select max(committed_at) as last_modified,
                {{ current_timestamp() }} as snapshotted_at
          from TABLE( table_snapshot('{{relation}}') )
    {%- endcall -%}
  {%- else -%}

  {%- endif -%}

  {{ return(load_result('last_modified')) }}

{% endmacro %}

Expected Behavior

No response

Steps To Reproduce

No response

Environment

- OS: Ubuntu 22.04
- dbt-dremio: 1.7.0
- Dremio Software: 25.0.0
- Dremio Cloud: N/A

Relevant log output

No response

[ravjotbrar]

Thanks for bringing this to our attention @KingLommel. I'll take a look at implementing your fix.