Metric Definition and Implementation Questions

Direct answer: Use exact COUNT(DISTINCT user_id) when correctness at exact precision genuinely matters (financial reporting, small-to-moderate data volumes) and switch to HyperLogLog (HLL) when the query needs to merge distinct counts ACROSS many partitions cheaply, or when the exact computation's memory/compute cost becomes prohibitive at scale, accepting a small, bounded error (typically under ~2%) in exchange.

Structured elaboration:

• Accuracy: exact COUNT(DISTINCT) has zero error by definition; HLL trades a small, characterizable relative error (tunable via its precision parameter, at the cost of more memory for lower error) for speed and mergeability.
• Memory/compute cost: exact distinct counting over a huge cardinality requires materializing (conceptually) every distinct value seen, which can be memory- and shuffle-heavy in a distributed engine; an HLL sketch uses a small, FIXED amount of memory (a few KB) regardless of how many distinct values it's summarizing, which is the core efficiency win.
• Mergeability: this is the decisive practical advantage of HLL: two HLL sketches (e.g., one per day) can be MERGED into a combined sketch representing the union's distinct count, without re-scanning either day's raw data; an exact COUNT(DISTINCT) cannot be merged this way at all (you cannot combine two days' exact distinct-user counts into a 2-day distinct count without re-scanning both days' raw user IDs together, since the same user could appear in both days and a naive sum would double count).
• When HLL is appropriate: high-cardinality unique-count dashboards refreshed frequently across many partitions/dimensions (e.g., "unique visitors per hour, mergeable up into daily/weekly/monthly totals" without rescanning raw data for each rollup level); NOT appropriate when the number feeds an audited financial or legal report, where the small approximation error, however characterized, is unacceptable.
• Production heuristic: a reasonable starting rule is to use exact COUNT(DISTINCT) below roughly tens of millions of distinct values per query where compute cost is still acceptable, and switch to HLL above that threshold or whenever the SAME dashboard needs the distinct count rolled up across many overlapping dimension combinations (which would otherwise require re-scanning raw data for every combination).

Worked example: a "unique visitors" metric computed hourly and rolled up to daily/weekly views is a strong HLL candidate: maintain an hourly HLL sketch per relevant dimension, and compute daily/weekly unique visitors by MERGING the relevant hourly sketches, rather than re-scanning raw events at every rollup level; a monthly financial "unique paying customers" number feeding revenue recognition should stay exact.

Trade-offs & pitfalls: The mergeability property is the real reason to reach for HLL, more than the raw compute savings on a single query; if a use case never needs to merge sketches across partitions (a one-off query on a single day's data), the memory/compute case for HLL over exact COUNT(DISTINCT) is much weaker, and the added complexity (a less-familiar data structure, an approximation error to explain to stakeholders) may not be worth it.

Direct answer: Establish a small set of naming rules (a consistent verb/noun pattern, a required grain suffix, a controlled vocabulary for common dimensions), assign an owner and a review gate for any NEW metric, and a documented deprecation policy so metric sprawl is prevented at creation time rather than cleaned up after the fact.

Structured elaboration:

• Naming rules: a consistent pattern like <grain>_<subject>_<measure> (e.g., daily_active_users, weekly_active_paying_users) makes the grain and intent legible from the name alone, rather than requiring a lookup; ban ambiguous shorthand ("active," "engagement," used bare with no qualifier) as a metric name on its own.
• Canonical examples: publish 5-10 flagship metrics (DAU, MAU, MRR, conversion_rate, churn_rate) with their full definitions as the reference pattern new metrics should follow, so a new metric author has a concrete template rather than an abstract style guide.
• Ownership: every metric has an owning team assigned at creation, not retrofitted later; ownership is what makes the review gate below actually enforceable.
• Versioning and deprecation: a metric's calculation logic changes only via a new version (S61's change-management workflow); a metric no longer maintained is marked deprecated with a pointer to its replacement (if any) rather than silently left stale and orphaned.
• Lightweight review process: before a NEW metric is added to the shared catalog, a brief check (does an existing metric already cover this? does the name follow convention? is an owner assigned?) run by a small rotating review group, deliberately kept lightweight (a same-day turnaround, not a weeks-long committee process) so it doesn't become a bottleneck people route around.

Worked example: a company with 6 product teams each independently creating an "engagement score" metric (MECE data-dictionary discipline would catch this) ends up with 6 subtly different, confusingly identically-named metrics; a lightweight review gate that simply asks "does something like this already exist?" before approval would catch most of this duplication at the point of creation, far more cheaply than a later company-wide audit and consolidation project.

Trade-offs & pitfalls: A review process that is too heavy gets bypassed (teams just build the metric locally without registering it, defeating the entire point); a naming convention enforced only by a style guide with no actual review gate drifts within a quarter, since good intentions alone don't hold under deadline pressure. For a multi-region company, the taxonomy also needs to explicitly address per-region variation (different instrumentation coverage, currency, and privacy constraints across markets) as a documented EXCEPTION-HANDLING pattern within the naming convention (e.g., a region-scoped metric variant is named and registered distinctly from the global one, not silently substituted for it).

Direct answer: Join the legacy and new implementations' output on the shared date key, compute the percent difference per date, and where the difference exceeds 1%, run a set of automated diagnostic checks (missing partitions, NULL-handling differences, and a zero-baseline check) to populate a reason_code rather than leaving the discrepancy unexplained.

Structured elaboration and executable SQL:

WITH compared AS (
  SELECT l.date, l.value AS legacy_value, n.value AS new_value,
         CASE
           WHEN l.value IS NULL OR n.value IS NULL THEN NULL
           WHEN l.value = 0 THEN NULL
           ELSE ABS(l.value - n.value) * 1.0 / ABS(l.value)
         END AS pct_diff
  FROM legacy_metric l
  FULL OUTER JOIN new_metric n ON n.date = l.date
)
SELECT date, legacy_value, new_value, pct_diff,
  CASE
    WHEN legacy_value IS NULL THEN 'missing_in_legacy'
    WHEN new_value IS NULL THEN 'missing_in_new_partition'
    WHEN legacy_value = 0 AND new_value <> 0 THEN 'value_mismatch_zero_baseline'
    WHEN legacy_value = 0 AND new_value = 0 THEN 'within_tolerance'
    WHEN pct_diff > 0.01 THEN 'value_mismatch_over_threshold'
    ELSE 'within_tolerance'
  END AS reason_code
FROM compared
WHERE legacy_value IS NULL
   OR new_value IS NULL
   OR (legacy_value = 0 AND new_value <> 0)
   OR pct_diff > 0.01;

The reason_code column is what turns a bare list of mismatched dates into an ACTIONABLE list: "missing partition" points to a pipeline scheduling problem, "value_mismatch_zero_baseline" points to a case a plain percent-difference formula cannot express, and "value mismatch" points to an actual logic difference worth diffing the two SQL definitions for.

Worked example: if legacy_value=1000 and new_value=950 for a given date, pct_diff=0.05 (5%), correctly exceeding the 1% threshold and flagged value_mismatch_over_threshold; a date present in new_metric but entirely absent from legacy_metric (perhaps the new pipeline started a day earlier) is flagged missing_in_legacy rather than silently excluded from the comparison. A date where legacy_value=0 and new_value=100 cannot be scored as a percent difference at all (dividing by zero is undefined), so a naive query that simply wraps the denominator in NULLIF(l.value, 0) turns that division into NULL and, because pct_diff > 0.01 is never true for a NULL value and neither legacy_value nor new_value is NULL, silently drops that row from the result entirely, exactly the kind of go-from-nothing-to-something regression this comparison exists to catch. The corrected query adds an explicit value_mismatch_zero_baseline branch so a former-zero metric that starts producing nonzero output (or vice versa) is always flagged, and a legitimate zero-to-zero date is still excluded as within_tolerance.

Trade-offs & pitfalls: Automate this as a nightly job comparing the trailing N days (enough to catch both immediate regressions and slower-arriving late data), storing the results in a small history table so a persistent, unresolved mismatch on the SAME dates over multiple nights escalates in priority differently than a one-off, likely-timing-related blip that resolves itself the next night once late data lands. During a migration period, this comparison should run for as long as BOTH pipelines remain live; cutting over the new pipeline before a sustained period of within_tolerance results is the most common way a genuine logic bug in the new pipeline goes undetected until well after the legacy pipeline (and the ability to easily compare) has been decommissioned. Never let a percent-difference formula's own division silently swallow the zero-baseline case; it is one of the most consequential mismatches to catch, not an edge case safe to ignore.

Direct answer: An event-level metric describes individual occurrences (one row per click, one row per page view); a user-level metric aggregates those occurrences up to one row per user (per some time window), answering "how many distinct users did X" rather than "how many times did X happen."

Structured elaboration: Compute at event-level when the question is inherently about volume or rate of occurrences (total page views, click-through rate on a specific banner). Compute at user-level when the question is about reach or behavior of people (DAU, percent of users who converted, average sessions per user). The SQL pattern to go from one to the other is a two-step aggregation: first collapse events to a per-user-per-day flag or count, then aggregate that intermediate table.

Worked example (executed): given clicks(user_id, click_time) with user 1 clicking 3 times on June 1 and once on June 2, and user 2 clicking once on June 1:

WITH daily_activity AS (
  SELECT user_id, date(click_time) AS day, COUNT(*) AS clicks_that_day
  FROM clicks
  GROUP BY user_id, date(click_time)
)
SELECT day, COUNT(DISTINCT user_id) AS daily_active_clickers
FROM daily_activity
GROUP BY day;

Run against a small SQLite table with exactly these rows, this returns (June 1, 2) and (June 2, 1): an event-level COUNT(*) on the same raw data would instead give (June 1, 4) and (June 2, 1), a materially different number for June 1 because it's answering a different question (how many clicks happened, not how many people clicked).

Trade-offs & pitfalls: The most common bug is silently mixing the two grains within one report: e.g., reporting "average clicks per user" as total_clicks / distinct_active_users_this_week when total_clicks was accumulated over a different window than the user count, producing a number that isn't a genuine per-user average for any single period. Always name the grain (event-level count, user-level count, or a ratio of the two over the SAME window) in the metric's label, not just in internal documentation, because dashboard consumers otherwise assume "clicks" and "active clickers" are interchangeable.

Direct answer: Detect every dashboard/model that reads the "purchase" event type (via lineage), decide explicitly whether historical data should be reprocessed under the new definition or left as a version boundary, version the metric definition rather than silently redefining it in place, and communicate the change with enough lead time that no stakeholder discovers it by noticing an unexplained trend break.

Structured elaboration, the plan:

1. Detect impacted dashboards: use the lineage documentation (S38/S58) to enumerate every metric/dashboard/model that reads event_type = 'purchase', rather than relying on informal knowledge of "who probably uses this," since an undocumented downstream consumer is exactly what gets broken silently.
2. Decide: reprocess historical data, or not: if the new schema is a genuine REDEFINITION of what counts as a purchase (not just a bug fix), reprocessing history under the new definition may be actively WRONG (it would misrepresent what was actually recorded/understood as a purchase at the time), whereas if it's a correction of an acknowledged error, reprocessing may be appropriate; this is a judgment call to make explicitly and document, not default silently either way.
3. Version the metric definition: the "purchase" metric gets a new semantic_version; historical dashboards spanning the change date get an explicit annotation marking exactly when the definition changed (S61's labeling practice).
4. Communicate: notify every identified downstream owner with the specifics: what changed, why, when it takes effect, and whether/how historical data is affected, with enough lead time for them to adjust any dependent alert thresholds or models before the cutover, not after.
5. Safeguards against downstream confusion: add a schema-contract test (S18) asserting the expected shape/values of event_type = 'purchase' events, so a FUTURE undocumented schema change to this same event type fails CI immediately rather than silently propagating another surprise redefinition.

Worked example: two SDK versions logging events under events_v1 and events_v2 schemas is a concrete instance of this exact problem: reconciling them requires a compatibility mapping (translating v1's "purchase" semantics to align with v2's, or vice versa) applied consistently at query time or via a unified view, rather than either silently ignoring the older schema's rows or silently treating both schemas as if they meant the same thing without verification.

Trade-offs & pitfalls: deciding NOT to reprocess history (treating the schema change as a forward-only version boundary) is often the safer default for a genuine semantic redefinition, since retroactively reinterpreting the past under a definition that didn't exist at the time it was recorded can itself introduce a new, subtle inaccuracy; but this must be a deliberate, documented decision made WITH the stakeholders who'll read the resulting discontinuous chart, not a default nobody chose on purpose.