Metric Views

playnicekids
New Contributor III

‎10-22-2025 05:53 AM

Hi

I think I’ve found a reproducible bug / or am misunderstanding some syntax / capabilities of Metric Views when joining a calendar scaffold to an SCD2 table.
The same SQL query works perfectly, but the Metric View always returns a constant 1 per month when using COUNT(DISTINCT …) on a joined column. I've added some simple code / metric view that reproduces the behaviour I'm seeing.

Minimal Repro Code:

-- Base calendar
CREATE OR REPLACE TABLE dim_calendar_month AS
SELECT explode(sequence(DATE'2025-06-01', DATE'2025-08-01', INTERVAL 1 MONTH)) AS month_start,
last_day(month_start) AS month_end;

-- Simple SCD2 table
CREATE OR REPLACE TABLE dim_contact AS VALUES
('A','Open','client',DATE'2024-01-01',NULL),
('B','Open','client',DATE'2025-06-01',DATE'2025-07-01'),
('B','Closed','client',DATE'2025-07-01',NULL),
('D','Open','client',DATE'2025-07-15',NULL)
AS dim_contact(contact_id,status,contact_type,from_date,to_date);

-- Works fine (expected 2,2,2)
WITH calendar_cte AS (SELECT month_start, month_end FROM dim_calendar_month)
SELECT calendar_cte.month_end, COUNT(DISTINCT c.contact_id) AS open_contacts
FROM calendar_cte
LEFT JOIN dim_contact c
ON c.from_date <= calendar_cte .month_end
AND (c.to_date > calendar_cte .month_end OR c.to_date IS NULL)
AND lower(c.status)='open'
GROUP BY calendar_cte .month_end
ORDER BY calendar_cte .month_end;

Metric View:

version: 1.1
source: MY_CATALOG.mv_bug_demo.dim_calendar_month
joins:
  - name: contact
    source:mv_bug_demo.dim_contact
    "on": contact.from_date <= source.month_end AND (contact.to_date > source.month_end
      OR contact.to_date IS NULL) AND lower(contact.status) = 'open' AND contact.contact_type
      = 'client'
dimensions:
  - name: month
    expr: source.month_end
    display_name: Month
  - name: contact_id
    expr: contact.contact_id
    display_name: Contact ID
measures:
  - name: eligible_contacts
    expr: COUNT(DISTINCT contact_id)
    display_name: Eligible Contacts
  - name: rows_all
    expr: COUNT(*)
  - name: contacts_distinct
    expr: COUNT(DISTINCT contact.contact_id)
  - name: dup_rows
    expr: MEASURE(rows_all) - MEASURE(contacts_distinct)
  - name: max_age
    expr: MAX(contact.age)

SQL:

-- Returns 1, 1, 1

SELECT
  month,
  MEASURE(eligible_contacts) AS eligible_contacts
FROM dim_calendar_month_metric_view
GROUP BY month
ORDER BY month;

Environment

  • Unity Catalog enabled

  • Runtime: 16.0

  • DBSQL metric view syntax: version 1.1

 

0 Kudos