Hey @playnicekids , I dig some digging and have come up with some helpful hints/tips to get you past your issue:
This behavior is due to how metric view joins are defined and executed.
Diagnosis
The join in your metric view is a many-to-many temporal join (calendar month → multiple open contact rows). Metric view joins are intended to be many-to-one; when they encounter many-to-many, the engine selects only the first matching row from the joined table for each source row. That collapses your month-to-contacts result to a single match per month, so COUNT(DISTINCT contact_id) returns 1 for every month.
This difference explains why your raw SQL works (it produces the cartesian month–contact set) while the metric view does not (it reduces the join to one match per source row).
Two additional notes that are relevant to your setup: * Metric views can use a SQL query as the source, which is the recommended way to handle many-to-many expansions like calendar scaffolds, because the expansion happens inside the source rather than in the metric view join layer.
- Metric views don’t support adding new joins at query time; the join semantics must be modeled in the view definition or the view’s source SQL. Using a pre-expanded SQL source avoids this limitation while preserving correct DISTINCT semantics.
Fix Options
Pick one of the following approaches to get correct counts.
Option A:
Push the temporal join into the source SQL Define the metric view over a SQL query that enumerates month–contact pairs (the same logic you used in plain SQL). This keeps the many-to-many expansion out of the metric view’s join semantics and preserves correct COUNT(DISTINCT) results.
-- Metric view definition (YAML in SQL)
ALTER VIEW my_catalog.mv_bug_demo.dim_calendar_month_metric_view AS
version: 1.1
source: |
SELECT
m.month_end AS month,
c.contact_id
FROM my_catalog.mv_bug_demo.dim_calendar_month m
LEFT JOIN my_catalog.mv_bug_demo.dim_contact c
ON c.from_date <= m.month_end
AND (c.to_date > m.month_end OR c.to_date IS NULL)
AND lower(c.status) = 'open'
AND c.contact_type = 'client'
dimensions:
- name: month
expr: month
display_name: Month
- name: contact_id
expr: contact_id
display_name: Contact ID
measures:
- name: eligible_contacts
expr: COUNT(DISTINCT contact_id)
display_name: Eligible Contacts
Query: sql
SELECT
month,
MEASURE(eligible_contacts) AS eligible_contacts
FROM my_catalog.mv_bug_demo.dim_calendar_month_metric_view
GROUP BY month
ORDER BY month;
This reproduces your working SQL result (2, 2, 2) because the source is already at the month–contact grain, and COUNT(DISTINCT contact_id) is evaluated correctly at the month grouping.
Option B:
Materialize a helper view and use it as the source If you prefer to keep the metric view YAML simpler, create a view that precomputes the scaffolding and then point the metric view at it.
CREATE OR REPLACE VIEW my_catalog.mv_bug_demo.v_month_contact_open AS
SELECT
m.month_end AS month,
c.contact_id
FROM my_catalog.mv_bug_demo.dim_calendar_month m
LEFT JOIN my_catalog.mv_bug_demo.dim_contact c
ON c.from_date <= m.month_end
AND (c.to_date > m.month_end OR c.to_date IS NULL)
AND lower(c.status) = 'open'
AND c.contact_type = 'client';
Metric view (YAML): yaml
version: 1.1
source: my_catalog.mv_bug_demo.v_month_contact_open
dimensions:
- name: month
expr: month
display_name: Month
- name: contact_id
expr: contact_id
display_name: Contact ID
measures:
- name: eligible_contacts
expr: COUNT(DISTINCT contact_id)
display_name: Eligible Contacts
Query: sql
SELECT month, MEASURE(eligible_contacts)
FROM my_catalog.mv_bug_demo.dim_calendar_month_metric_view
GROUP BY month
ORDER BY month;
Same correct result, with the expansion done in the view rather than in the metric view join.
Why the original metric view returns 1
Metric view joins are designed for star/snowflake schema where the source fact joins to dimensions in a many-to-one relationship. In many-to-many cases (like calendar scaffolds), metric views pick the first matching row from the joined table for each source row; that’s why you see a single joined contact per month and a constant COUNT(DISTINCT contact_id) = 1.
Small correctness tips
- You already quoted the “on” key in YAML; that’s important because YAML 1.1 parsers can misinterpret unquoted “on” as a boolean and break joins.
- When consuming metric views, make sure the attached compute meets the supported runtime requirement (Databricks Runtime 16.4+ for querying metric views), even though your simple query may still run on earlier versions.
Hope this helps, Louis.
