[Feature] Relax double underscore requirement on metrics about dbt-semantic-interfaces HOT 3 OPEN

It would be really helpful to know whether this is doable and likely to be accepted or not. It has huge bearing on our migration path. @plypaul grateful for your input, thank you.

from dbt-semantic-interfaces.

Hey @adamcunnington-mlg , dunders currently do a lot of heavy lifting in Metricflow name spacing. For example, we will append the requested granularity to time dimensions columns in the result table with dunders (ordered_at__week, etc.), and we use them to denote join paths to dimensions, i.e., order__customer__name. This is fundamentally how we name columns in the result set.

We're planning features that would allow query time transformation of metrics like period over period and dynamic windows for cumulative metrics. We'd use the dunder syntax here as well to call out the metric transformation in the column name, i.e., revenue__wow, cumulative_revenue__7d_window. I do think we're overloading the dunder as it serves a bunch of different purposes, and we've chatted internally about different ways to name columns, but there is no active work going on right now to update this.

All that to say is I don't think we'll be relaxing the dunder restriction on metric names in at least the next 6 months. One option we discussed would be to allow users to specify a replacement for dunders in their project configs.

I get that this is not an ideal timeline given you're trying to migrate today. I'm looking to understand the migration effort for you more clearly to see if there is a path to unblocking you. It seems like there are a few pieces of work:

1. Create semantic models and migrate metrics to use the MetricFlow spec - there is some automated tooling [here](https://github.com/dbt-labs/dbt-converter), and happy to collaborate on this if you're getting stuck.
2. Update metrics names to remove dunders
3. Update references to metric names in downstream tools

It sounds like #3 is the most challenging because metric names are imported directly to Superset then referenced in a ton of downstream dashboards/reports. Is there any way to update these metric definitions via an API? We can help write migration tooling if this is an option.

from dbt-semantic-interfaces.

@Jstein77 thank you for the response. The project config option seems like a really trivial change that would unblock me here. I empathise with the challenge but am glad the overloading of a name is recognised here! Feels far too magical!

The migration effort for us is enormous. We're very aware of dbt-converter and we used that as part of an intensive week where 4 developers sunk a combined 120 hours manually migrating ~1600 metrics to MetricFlow. We finished points 1 and 2 and 3 remains. Preset/Superset does have an API but the metric key is the fundamental ID and can't be changed - we'd have to work with Preset to do a crazy export/import operation to fundamentally change values in their DB - it's super high risk and significant effort.

Could the config be considered as a short term workaround? I could simply use a character other than dunder to drive MetricFlow and all of the work could be avoided :(.

from dbt-semantic-interfaces.