Problem Statement
The goal is to help data and analytics teams resolve conflicting metric definitions by producing a clear, canonical specification and describing why existing dashboards or SQL queries differ. The workflow must collect competing definitions, normalize assumptions, compare logic, and propose a governance-friendly standard. It should also include a rollout plan to align analytics tools and stakeholders.
Steps
Collect Inputs
INGESTIngest the metric name, business context, competing definitions, and target audience/use-case. Parse prose, SQL, or screenshot text into structured components.
Retrieve and Normalize Assumptions
EXECUTEExtract and normalize grains, time windows, inclusion/exclusion rules, and referenced data sources from each definition. Identify missing or ambiguous elements that require clarification.
Compare Logic Across Definitions
EXECUTEPerform a structural and semantic diff of the provided definitions. Highlight specific differences and estimate the directional impact on reported numbers.
Draft Canonical Metric Specification
EXECUTEGenerate a governance-friendly, neutral canonical metric spec including definition, grain, window, inclusion/exclusion rules, data handling rules, and source-of-truth guidance.
Validate and Propose Standardization Plan
EXECUTEPresent the reconciliation findings, recommended implementation path (semantic layer/dbt/BI), and outline rollout steps, including breaking changes. Ensure the output highlights trade-offs and identifies decision owners where policy choices are required.
Prompt Configurations
Metric Reconciliation Prompt
You are a data governance and analytics expert. Given: (1) a metric name and business context, (2) at least two competing definitions of the metric (SQL, prose, or screenshot text), and (3) target audience and intended use. Your task is to produce: A) a canonical metric specification, B) a reconciliation analysis describing why the definitions differ and the directional impact on numbers, and C) a standardization plan including recommended semantic layer/dbt/BI implementation and rollout steps. Maintain a precise, neutral, governance-friendly tone. Do not invent data. When inputs are insufficient, ask only the minimum missing questions (max 5). Highlight trade-offs and identify a decision owner instead of making policy choices. Do not pick a definition without explaining rationale and risks.
Example Inputs/Outputs
Metric: Active Users. Context: Weekly product usage review for team leads. Definition A: SQL counting distinct user_id from events where event_type='login' in the last 7 days. Definition B: Dashboard counting unique users with any event in the last 7 days. Audience: Team leads for weekly review.
Canonical Spec: Active Users are users with any qualifying product event in the last 7 days at a daily grain, excluding test accounts. Reconciliation: Definition A only counts login events, while Definition B counts all events. Definition B reports higher numbers because it includes passive activity. Standardization Plan: Implement a dbt model defining "qualifying events" and a shared semantic metric. Communicate breaking change: teams accustomed to login-only counts should expect an increase of 10–20%.