Data Governance and Metric Consistency

HardSQL · PostgreSQL

Problem

How do you handle data governance, taxonomy management, and metric consistency across large reporting outputs?

Using the provided tables, write a SQL query that identifies active reporting outputs whose metric definition differs from the approved definition or whose governance approval is missing. Include only active metrics and taxonomy terms.

Output

One row per affected metric and taxonomy term
Columns: metric_name, taxonomy_name, reporting_output_count, inconsistent_output_count, and alphabetized output_names
Include only groups with at least one inconsistent reporting output, ordered by metric_name

Schema

reporting_outputs

Column	Type	Description
report_idPK	INT	Unique reporting output identifier
report_name	VARCHAR(100)	Name of the reporting output
metric_id	INT	Referenced metric definition
taxonomy_id	INT	Referenced taxonomy term
definition_version	INT	Metric definition version used by the output
formula	TEXT	Metric calculation formula used by the output
is_approved	BOOLEAN	Whether the output passed governance approval
is_active	BOOLEAN	Whether the reporting output is currently active

metric_definitions

Column	Type	Description
metric_idPK	INT	Unique metric identifier
metric_name	VARCHAR(100)	Canonical metric name
current_version	INT	Approved metric definition version
canonical_formula	TEXT	Approved calculation formula
is_active	BOOLEAN	Whether the metric definition is currently active

taxonomy_terms

Column	Type	Description
taxonomy_idPK	INT	Unique taxonomy term identifier
taxonomy_name	VARCHAR(100)	Canonical taxonomy term
taxonomy_domain	VARCHAR(100)	Taxonomy classification domain
is_active	BOOLEAN	Whether the taxonomy term is currently active

Tablesreporting_outputsmetric_definitionstaxonomy_terms