[SEMANTIC-239] [Bug] Derived metrics that depend on more than 2 other metrics fail

[siljamardla]

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

Here is a definition of a derived metric my_metric that depends on two other metrics: my_sub_metric_1 and my_sub_metric_2:

  - name: my_metric
    calculation_method: derived
    expression: "{{ metric('my_sub_metric_1') }} + {{ metric('my_sub_metric_2') }}" # + {{ metric('my_sub_metric_3') }}
    timestamp: created
    time_grains: [hour, day, week, month, quarter, year]

I can use this metric in a model and it works.
If I make my_metric depend on three other models by uncommenting the third part:

  - name: my_metric
    calculation_method: derived
    expression: "{{ metric('my_sub_metric_1') }} + {{ metric('my_sub_metric_2') }} + {{ metric('my_sub_metric_3') }}"
    timestamp: created
    time_grains: [hour, day, week, month, quarter, year]

I cannot use my_metric in a model.

The behaviour and core problem is very similar to [(https://github.com//issues/233)]

Expected Behavior

I can use metrics that depend on more than two other metrics.

Steps To Reproduce

See above

Relevant log output

No response

Environment

dbt --version
Core:
  - installed: 1.4.5
  - latest:    1.4.5 - Up to date!

Plugins:
  - databricks: 1.4.2 - Up to date!
  - spark:      1.4.1 - Up to date!

Packages:
  - package: dbt-labs/metrics
    version: 1.4.1

Which database adapter are you using with dbt?

databricks, spark

Additional Context

No response

[siljamardla]

Also, while Issue 233 has a workaround (build separate models that contain 2 metrics each and then a model on top of that joining the tables together) the same trick does not work with derived metrics.

- name: my_metric1
   calculation_method: derived
   expression: "{{ metric('my_sub_metric_1') }} + {{ metric('my_sub_metric_2') }}"
   timestamp: created
   time_grains: [hour, day, week, month, quarter, year]

 - name: my_metric2
   calculation_method: derived
   expression: "{{ metric('my_sub_metric_3') }}"
   timestamp: created
   time_grains: [hour, day, week, month, quarter, year]

 - name: my_metric
   calculation_method: derived
   expression: "{{ metric('my_metric_1') }} + {{ metric('my_metric_2') }}"
   timestamp: created
   time_grains: [hour, day, week, month, quarter, year]

runs into the same problem.

[callum-mcdata]

Hey @siljamardla . I believe this might be the same issue as #227 . It is most likely stemming from the metric_tree variable which we use for defining the metric hierarchy. We don't have time allocated for dealing with this bug before the 1.5 release but if you're interested in submitting a PR to debug it we're more than happy to help review it!

[github-actions]

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 remove the stale label or comment on the issue, or it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest; add a comment to notify the maintainers.