dbt-labs/dbt_metrics

[SEMANTIC-67] [Bug] metrics using window periods include an extra day

Closed this issue · 2 comments

lianncheung commented

Is this a new bug in dbt_metrics?

  • I believe this is a new bug in dbt_metrics
  • I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When defining Window Periods:

    window:
      count: 7
      period: day

in .yml files, I would expect this to equate to a "rolling 7 day window"; however, in practice this is what appears in the date range in the query:

            on cast(base_model.date as date) >= dateadd(day, -7, calendar_table.date_day)
            and cast(base_model.date as date) <= calendar_table.date_day

which equates to technically 8 days being included in the data.

Expected Behavior

I would expect that using a window count of 7 days would implement a 7 rolling day window, rather than including 8 days of data.
This may be as simple as changing the query to use a > instead of a >= for the filter.

Steps To Reproduce

  1. package version 1.3.1
  2. develop script looks something like this:
{% set my_metric_yml -%}
{% raw %}

metrics:
  - name: develop_metric
    model: ref('model')
    label: metric label
    timestamp: date
    time_grains: [day]
    calculation_method: count_distinct
    expression: id
    window:
      count: 7
      period: day

{% endraw %}
{%- endset %}

select * 
from {{ metrics.develop(
        develop_yml=my_metric_yml,
        metric_list=['develop_metric'],
        grain='day'
        )
    }}
order by 1

Relevant log output

No response

Environment

- dbt-adapter & version: 
- dbt_metrics version: 1.3.1

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

callum-mcdata commented

This has now been resolved and will come out with the next release at the end of the week!