Create a custom aggregation type for measures

[callum-mcdata]

Describe the Feature A community member suggested that we add a custom aggregation method for aggregation types that we don't support. I have to imagine there are some concerns with testing & validation but I figured I'd create a FR here for folks to discuss!

Here is the proposed design:

- name: std_dev_transaction_amount
  description: "The Standard Deviation of the transaction total price"
  agg: custom
  agg_params:
     - sql: stddev( expr )
  expr: transaction_total

Would you like to contribute? We shall see. Tom and Paul will probably have the best knowledge on whether this is possible or something we want to consider!

[QMalcolm]

We should probably move this issue to dbt-semantic-interfaces

[ackoroa]

I'm interested in this because our use-case requires us to compute not only the metric value but also its variance and its covariance with another metric.

Would it be possible to define such a custom function or otherwise add variance/covariance as a supported standard aggregation function?

[supplemarty]

This would be very valuable for scenarios where a custom aggregate calculation must be done. I believe Sigma supports this with pass through functions that could call a custom value function in the DW. This is a major requirement for 2 of my larger financial customers in the Private Equity space. Please consider this feature!!