Explain why two reports show different numbers for the same metric. — Claude Skill

name: metric-reconciliation description: Trace and resolve discrepancies when the same metric shows different values in two or more sources. Use before reporting, after pipeline changes, or when stakeholders question a number.

Metric Reconciliation

When to use

• Two dashboards or reports show different values for the same KPI
• A metric changed unexpectedly after a data pipeline update
• Stakeholders question a number and need an authoritative explanation
• Preparing to merge or deprecate a legacy reporting source
• Onboarding analysts to a new data model and validating it against the old one

Process

1. Define the metric and scope — confirm the exact definition (numerator, denominator, filters, time zone) and the period under investigation. Mismatched definitions are the most common cause of discrepancy.
2. Pull values from both sources — extract the metric values for the same period from each source. Record absolute values, row counts, and the query or calculation path used.
3. Compute the gap — calculate the absolute difference and percentage gap. If the gap is within an agreed tolerance (e.g., ±0.1%), document it as accepted and close. See references/reconciliation_patterns.md for tolerance guidelines.
4. Trace the computation path — walk each source's query or pipeline step by step. Common divergence points: different join types, filter order, null handling, date truncation, or deduplication logic.
5. Identify the root cause — classify the cause using references/metric_discrepancy_guide.md (definition mismatch, data freshness, aggregation grain, calculation bug). Document the divergence point with a code snippet or query excerpt.
6. Resolve and document — fix the calculation or accept a canonical source, then complete assets/reconciliation_report_template.md and share with stakeholders.

Inputs the skill needs

• The metric name and business definition (numerator, denominator, any known variants)
• Access to both sources (queries, dashboard SQL, or raw data)
• The time period showing the discrepancy
• Row counts or record-level data to enable line-by-line comparison if needed
• Any known recent changes to pipelines, schema, or business rules

Output

• assets/reconciliation_report_template.md — completed report showing source A vs. source B, the gap, root cause, and resolution status
• A corrected query or pipeline change (if a bug was found)
• A documented tolerance agreement (if the gap is acceptable)

Metric Reconciliation Patterns

Why metrics diverge

1. Filter differences

The most common cause. Two queries pulling from the same table but with different WHERE clauses produce different numbers. Check: status filters, date truncation (>= vs BETWEEN), NULL handling, deleted record exclusion.

2. Join type mismatches

LEFT JOIN vs INNER JOIN silently adds or drops rows. A revenue figure joined LEFT to a customer table will count orders with no matching customer; INNER will drop them.

3. Grain mismatch

One query aggregates at order level; another at order-line level. Duplicate rows in one source inflates the total.

4. Time zone handling

UTC vs local time causes rows to land in different periods depending on the system. Common in global dashboards.

5. Definition drift

The business definition changed (e.g., "active user" now requires two sessions, not one) but not all pipelines were updated simultaneously.

6. Refresh lag

One source reflects yesterday's snapshot; another is near-real-time. Comparing them during an intra-day reconciliation produces a spurious gap.

Reconciliation investigation sequence

1. Confirm the numbers — pull both values from authoritative sources, not cached dashboards.
2. Check the grain — what does one row represent in each source?
3. Align the period — are both using the same start/end boundary and time zone?
4. Compare filters — list all WHERE conditions side by side.
5. Compare joins — trace every JOIN and its type.
6. Sample-level check — pull 20 rows present in A but not B, and vice versa.
7. Document the root cause — record in assets/reconciliation_report_template.md.

Tolerance thresholds by metric type

Documentation checklist after resolution

• Root cause identified (one sentence)
• Which source is designated as the single source of truth
• Whether a pipeline fix is needed, or a definition clarification
• Date the discrepancy was observed and resolved
• Whether downstream consumers need recalculation

Metric Reconciliation Report

Metric: [metric name] Period: [date or date range] Analyst: [name] Date: [YYYY-MM-DD]

Values compared

Absolute difference: [Source A − Source B] Percentage difference: [(A − B) / A × 100]% Within tolerance: Yes / No (tolerance: [threshold])

Root cause

Status: Confirmed / Under investigation / No issue

Cause category: Filter difference / Join type / Grain mismatch / Definition drift / Refresh lag / Other

Description: [One paragraph: what specific difference in the queries or pipelines explains the gap]

Evidence

Steps taken to identify the cause:

1. [Step 1 — e.g., compared WHERE clauses]
2. [Step 2 — e.g., checked JOIN types]
3. [Step 3 — e.g., pulled row-level sample]

Key finding: [The specific line of query or pipeline behaviour that explains the discrepancy]

Resolution

Designated source of truth: [Source A / Source B / Neither — needs alignment]

Action required:

• Pipeline fix needed (owner: [name], by: [date])
• Query correction needed (owner: [name], by: [date])
• Definition clarification needed (owner: [name], by: [date])
• Downstream consumers need recalculation (list: [])
• No action — gap is within tolerance

Preventive measure

[How will we catch this earlier in future? e.g., automated reconciliation check, CI test, data quality alert]

Template: reconciliation_report_template.md

""" Metric Reconciliation Helper

Compare a metric value from two sources for the same period and identify where the computation paths diverge.

Usage: python reconcile_metrics.py --source-a source_a.csv --source-b source_b.csv
--metric revenue --date 2025-01-01 python reconcile_metrics.py --values 125000 118500 --metric revenue --tolerance 0.02 """

import argparse import csv import io import sys

def compare_values(value_a: float, value_b: float, tolerance: float = 0.001) -> dict: """ Compare two metric values and return reconciliation stats.

Args:
    value_a: Value from source A
    value_b: Value from source B
    tolerance: Fractional tolerance below which gap is accepted (default 0.1%)

Returns:
    dict with absolute_diff, pct_diff, within_tolerance, status
"""
absolute_diff = value_a - value_b
pct_diff = absolute_diff / value_a if value_a != 0 else float("inf")
within_tolerance = abs(pct_diff) <= tolerance

return {
    "value_a": value_a,
    "value_b": value_b,
    "absolute_diff": absolute_diff,
    "pct_diff": pct_diff,
    "within_tolerance": within_tolerance,
    "status": "ACCEPTED" if within_tolerance else "INVESTIGATE",
}

def load_metric_from_csv(filepath: str, metric_col: str, date_col: str = None, date: str = None) -> float: """ Load a single metric value from a CSV file.

Sums metric_col for all rows matching date (if provided).
"""
total = 0.0
matched = 0

with open(filepath, newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    for row in reader:
        if date_col and date and row.get(date_col) != date:
            continue
        total += float(row[metric_col])
        matched += 1

if matched == 0:
    raise ValueError(f"No rows found in {filepath}" + (f" for date {date}" if date else ""))

return total

def reconciliation_report(result: dict, source_a_label: str, source_b_label: str) -> str: """Produce a human-readable reconciliation summary.""" lines = [ "=" * 60, "METRIC RECONCILIATION REPORT", "=" * 60, f" {source_a_label}: {result['value_a']:,.2f}", f" {source_b_label}: {result['value_b']:,.2f}", f" Absolute difference: {result['absolute_diff']:+,.2f}", f" Percentage difference: {result['pct_diff']:+.4%}", "", f" STATUS: {result['status']}", ]

if result["within_tolerance"]:
    lines.append("  Gap is within tolerance — no further action required.")
else:
    lines += [
        "",
        "  NEXT STEPS:",
        "  1. Compare query/pipeline definition for each source side-by-side.",
        "  2. Check: filter conditions, join types, null handling, date truncation.",
        "  3. Pull row-level data to find the first divergence point.",
        "  4. Document the root cause in assets/reconciliation_report_template.md.",
    ]

lines.append("=" * 60)
return "\n".join(lines)

def main(): parser = argparse.ArgumentParser(description="Reconcile a metric between two sources.") parser.add_argument("--source-a", help="Path to CSV for source A") parser.add_argument("--source-b", help="Path to CSV for source B") parser.add_argument("--values", nargs=2, type=float, metavar=("A", "B"), help="Direct values: --values 125000 118500") parser.add_argument("--metric", required=False, default="value", help="Column name for the metric") parser.add_argument("--date-col", default=None, help="Date column name for filtering") parser.add_argument("--date", default=None, help="Date to filter on (e.g. 2025-01-01)") parser.add_argument("--tolerance", type=float, default=0.001, help="Accepted gap fraction (default 0.001 = 0.1%%)") parser.add_argument("--label-a", default="Source A") parser.add_argument("--label-b", default="Source B") args = parser.parse_args()

if args.values:
    value_a, value_b = args.values
elif args.source_a and args.source_b:
    value_a = load_metric_from_csv(args.source_a, args.metric, args.date_col, args.date)
    value_b = load_metric_from_csv(args.source_b, args.metric, args.date_col, args.date)
else:
    parser.error("Provide --values or both --source-a and --source-b")

result = compare_values(value_a, value_b, args.tolerance)
print(reconciliation_report(result, args.label_a, args.label_b))

sys.exit(0 if result["within_tolerance"] else 1)

if name == "main": # Demo: two values with a 5.5% gap — should trigger INVESTIGATE demo_a = 125_000.0 demo_b = 118_120.0 result = compare_values(demo_a, demo_b, tolerance=0.001) print(reconciliation_report(result, "Dashboard (Source A)", "Finance Report (Source B)")) print() print("Demo exited with status:", "ACCEPTED" if result["within_tolerance"] else "INVESTIGATE")