Derived metrics without double counting

Derived metrics are where metric layers earn their keep, and also where silent mistakes multiply. A ratio that looks correct in a dashboard can be wrong if the numerator and denominator expand against the same join in different ways. This post covers how datahashi models derived metrics so composition stays honest.

What “derived” means here

In datahashi, a derived metric is defined in terms of other metrics, not raw columns alone. Classic examples: conversion rate, average order value, attach rate, and margin. You declare the formula once; callers ask for the derived name through run_semantic_query rather than rebuilding the expression in every notebook.

That single definition is only useful if the engine expands it the same way every time. Double counting usually appears when a grain change or a join multiplies rows before the aggregation that the formula assumed.

The double-counting traps

Three patterns show up again and again:

1. Join fan-out before ratio. Orders join to line items. Summing revenue on the fan-out path counts the order total once per line. If conversion uses that inflated sum, the rate is wrong even when the SQL “runs fine.”
2. Filter on one side only. A filter on the denominator’s entity that does not apply to the numerator (or the reverse) changes the meaning of the ratio without changing the metric name.
3. Re-aggregating an already aggregated input. Taking an average of daily averages is not the same as the average over the raw events. Derived metrics that nest incorrectly inherit that bias.

datahashi keeps measure definitions tied to a declared grain. When you compose metrics, the compiler checks that the inputs can share a grain, or it materializes each input at its own grain before combining. That is the main guard against silent fan-out.

Discovering and inspecting definitions

Start with the catalog, then inspect the metric you care about:

• Use list_catalog to find metric names and which models they live on.
• Use describe_metric to see the formula, grain, and any constraints on how the metric may be filtered or joined.
• Use run_semantic_query with the derived metric name so the engine expands the definition for you.

Prefer that path over hand-writing SQL that reimplements the formula. Hand SQL tends to drift from the catalog, and drift is how double counting sneaks into production reports.

Modeling tips that keep ratios honest

Define atomic metrics first (counts, sums) at a clear grain. Build derived metrics only from those atoms. Avoid encoding business logic that depends on a specific join tree inside the derived formula; put join safety in the semantic model instead.

When a ratio must respect different filters on numerator and denominator, make that explicit in the metric description and test both filtered and unfiltered calls. If describe_metric does not document the filter behavior, treat the metric as incomplete until it does.

A practical workflow

1. List candidates with list_catalog.
2. Read each input metric with describe_metric.
3. Confirm grains align, or that the derived definition states how misaligned grains are resolved.
4. Run a small run_semantic_query on a known slice and compare totals to a trusted hand check at the same grain.
5. Only then wire the derived metric into dashboards or agents.

Derived metrics without double counting are less about clever SQL and more about grain discipline. datahashi pushes that discipline into the catalog so every caller, human or agent, gets the same expansion.