mohitagw15856/metric-semantic-layer

Metric Semantic Layer Skill

"Active users" means three different things in three dashboards — that's the problem a semantic layer solves: define each metric once, precisely, and every tool reads the same definition. This skill writes that definition — the exact formula, base measure, allowed dimensions, default filters, and the edge cases that usually cause drift — in a tool-ready form (dbt MetricFlow / Cube / LookML).

Required Inputs

Ask for these only if they aren't already provided:

• The metric — its name and the business question it answers.
• The base data — the model/table and the column(s) it's computed from.
• The aggregation — sum, count, count distinct, average, ratio.
• Dimensions & filters — how it can be sliced, and any default filters (exclude test accounts, internal users, refunds).
• Tool — dbt MetricFlow, Cube, LookML, or tool-agnostic.

Output Format

Metric: [metric_name]

1. Definition (plain English) — one sentence a non-analyst understands, and the precise version ("count of distinct user_ids with ≥1 qualifying event in the period, excluding internal/test accounts").

2. Formula — the exact calculation: base measure · aggregation · numerator/denominator (for ratios).

3. Grain & time — the time grain it's reported at, the date column it's anchored to, and how partial periods are handled.

4. Dimensions — the dimensions it can be sliced by (and any it must not be — non-additive metrics break when summed across the wrong dimension).

5. Default filters — what's always excluded (test/internal/refunds) so every consumer gets the same number.

6. Edge cases — null handling, late-arriving data, deduplication, currency/timezone, and additivity (can it be summed across days? across segments?). This section is where metric drift is prevented.

7. Tool-ready spec — the YAML/LookML for the chosen tool (MetricFlow metrics: / Cube measures: / LookML measure:), ready to commit.

Quality Checks

• [ ] Has both a plain-English and an exact definition
• [ ] States the base measure, aggregation, and (for ratios) numerator/denominator
• [ ] Default filters are explicit, so every tool returns the same number
• [ ] Additivity is addressed (which dimensions it can/can't be summed across)
• [ ] Edge cases (nulls, dedup, timezone, late data) are handled
• [ ] A tool-ready spec is provided, not just prose

Anti-Patterns

• [ ] Do not leave the definition fuzzy — "active users" without the exact rule is how three dashboards disagree
• [ ] Do not omit default filters — if one tool counts test accounts and another doesn't, the metric is broken
• [ ] Do not ignore additivity — summing a non-additive metric (like a distinct count) across days gives a wrong number
• [ ] Do not define metrics in BI tools instead of the semantic layer — that's how definitions fork
• [ ] Do not skip timezone/null/dedup edge cases — they cause the subtle, hard-to-find discrepancies

Based On

Semantic-layer / metrics-layer practice (dbt MetricFlow, Cube, LookML) — single-source metric definitions with explicit grain, filters, and additivity.