Validate Dashboard Metrics Against Source Events

MediumSQL · PostgreSQL

Problem

How do you ensure the accuracy of your dashboards when reporting to senior leadership?

Translate this question into a SQL validation check for a T-Mobile US leadership dashboard. Compare dashboard metrics with independently aggregated source events and identify discrepancies.

Output

One row per reporting date and market.
Columns: metric_date, market, source_subscribers, source_data_gb, dashboard_subscribers, dashboard_data_gb, and validation_status.
Include matching, mismatched, dashboard-missing, and source-missing records. Order by metric_date, then market.

Output details

Count distinct subscriber_id values for source_subscribers per date and market.
Sum data_gb without rounding for source_data_gb.
Preserve NULL metric values; do not treat NULL as zero or equal to NULL during comparisons.
Use exactly MATCH, MISMATCH, MISSING_DASHBOARD, and MISSING_SOURCE as status values.
Sort metric_date ascending, then market ascending.

Schema

network_usage_events

Column	Type	Description
event_idPK	INT	Unique network usage event identifier
event_date	DATE	Date on which the usage event occurred
market	VARCHAR(50)	T-Mobile US market associated with the event
subscriber_id	VARCHAR(20)	Subscriber associated with the usage event
data_gb	DECIMAL(10,2)	Data usage in gigabytes

tmobile_us_dashboard_metrics

Column	Type	Description
metric_datePK	DATE	Dashboard reporting date
marketPK	VARCHAR(50)	Dashboard market
dashboard_subscribers	INT	Subscriber count displayed on the dashboard
dashboard_data_gb	DECIMAL(10,2)	Data usage displayed on the dashboard in gigabytes

Tablesnetwork_usage_eventstmobile_us_dashboard_metrics