[Bug] v0.209.0 generates incorrect SQL for measures

[grigorjevas]

Is this a new bug in metricflow?

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

Current Behavior

When querying metrics in MetricFlow v0.209.0, generated SQL references measure name as a column instead of applying the aggregation function to the expr field. This causes "Unrecognized name" errors in BigQuery.

Example generated SQL (v0.209.0)

SELECT SUM(session_count) AS session_count
FROM `product`.`product_stats__sessions`

This treats session_count as an existing column in the table, which doesn't exist.

Expected Behavior

MetricFlow should apply the aggregation function to the expr field defined in the measure configuration.

Expected SQL (as in v0.208.0)

SELECT SUM(CASE WHEN session_id IS NOT NULL THEN 1 ELSE 0 END) AS session_count
FROM .`product`.`product_stats__sessions`

Steps To Reproduce

1. Define a measure in semantic model:

semantic_models:
  - name: sessions
    model: ref('product_stats__sessions')
    measures:
      - name: session_count
        agg: count
        expr: session_id

2. Query the metric:

mf query --metrics session_count --explain

3. Observe the incorrect SQL generation

Relevant log output

google.api_core.exceptions.BadRequest: 400 Unrecognized name: session_count at [9:7]

Location: EU
Job ID: xxx

dbt_common.exceptions.base.DbtDatabaseError: Database Error
  Unrecognized name: session_count at [9:7]

Environment

- OS: Debian 12.12
- Python: 3.12.12
- dbt: 1.10.15
- dbt-metricflow: 0.11.0
- Metricflow: 0.209.0

Which database are you using?

No response

Additional Context

This seems related to the measures to simple metrics migration introduced in PRs #1889, #1892, #1893
Downgrading to MetricFlow 0.208.0 resolves the issue

Is a fix expected in an upcoming release?

[linear]

DI-4802

[plypaul]

This might be an issue related to the recent spec change to depreciate measures. Let me take a look.

[plypaul]

@grigorjevas Are you using the create_metric flag for the metric? If so, there was a bug in an older release of dbt-semantic-interfaces that did not correctly create the metric with that shortcut. The current main branch formetricflow has updated dependencies that address this, so we could just push a new release of metricflow.

[grigorjevas]

Thank you for looking into it @plypaul

Spot on, we are using metrics like this:

    measures:
      - name: session_count
        expr: session_id
        agg: count
        description: "The total number of website visit sessions. Use for volume analysis and baseline counting."
        create_metric: true

Any ETA for the release? In the meantime we will try the main branch.

Thanks

[plypaul]

It might be a little tricky to try out main as the dbt-metricflow package depends on a published version of metricflow (https://github.com/dbt-labs/metricflow/blob/main/dbt-metricflow/requirements-files/requirements-metricflow.txt), but if you're able to change it and make it work, let us know.

We were planning to do a release in a couple of weeks that will include significant changes to improve the performance of the generated SQL by reducing the number of full outer joins. However, we might do one earlier to address this issue.