Metric Definition Auditor

What this skill does

Walks a dbt project or LookML repo and surfaces metrics that are defined in more than one place, or whose SQL formulas disagree. Output is a conflict report so analytics engineers can pick a single source of truth.

Inputs

• repo_dir: dbt project root (dbt_project.yml) or LookML root.
• tool: dbt or lookml.
• Optional min_similarity: Jaccard threshold for grouping near-duplicates by name (default 0.7).

Steps

1. For dbt: find metric YAML blocks via rg -lN 'metrics:' models/. For LookML: parse *.view.lkml files and extract measure: blocks with their type: and sql: fields.
2. Normalize each metric: lowercase name, stripped formula (collapse whitespace, lowercase column refs).
3. Group by exact name; any group with > 1 entry is an exact-duplicate conflict.
4. Group by similar name (Jaccard on token-bigrams); near-duplicates flagged for review.
5. Within each group, compare normalized formulas. If formulas differ, flag as formula-divergence.
6. Detect the "type" field (sum, count, count_distinct, average): if the same name uses different aggregation types, flag as aggregation-divergence — this is usually the worst kind.
7. Emit a markdown table per conflict class: columns Name, Variant Count, file:line list, Suggested Canonical (the variant with most upstream references).
8. Determine "most upstream references" by grepping the codebase for metrics.<name> (dbt) or by parsing dashboard usage exports (Looker).
9. Write the report.

Output

metric-audit.md containing three sections: Exact Duplicates, Near Duplicates, Aggregation Divergence. Each row cites file paths and line numbers. Stdout prints total conflict count.

Verification

For each "suggested canonical" pick, run the metric and one variant against a small fixture dataset (dbt run --select <model> then dbt run-operation for the metric) and compare results — disagreement confirms the conflict is material. Re-grep the codebase to confirm reference counts; if the canonical pick has zero usage downstream, switch to the most-referenced variant. Linting tool agreement: run dbt parse and confirm no errors after the report (lint failure can mask a real conflict).

Edge cases

• Metrics defined in code (Python custom metrics) that are excluded from the YAML scan: list them in an "out-of-scope" appendix.
• Renamed metrics with backward-compat aliases: detect via meta: aliases; treat alias as same-as-canonical.
• Project with custom metric framework (not dbt or LookML): refuse to run; ask for one of the supported tools.
• All metrics unique: emit a brief "0 conflicts" report, useful as a CI signal.