Metric Definition and Implementation Questions

Direct answer: Match the optimization to WHERE the cost actually comes from: partition pruning and pre-aggregation reduce the volume scanned per run, incremental updates avoid recomputing unchanged history, materialized views trade storage for read speed, and change data capture (CDC) avoids full-table rescans by processing only what actually changed upstream.

Structured elaboration, per option:

• Partition pruning: cheapest fix if the current job scans more data than it needs (e.g., a full-table scan instead of filtering to the relevant date range); low complexity, but only helps if the query genuinely doesn't need the unpruned data.
• Pre-aggregation: compute a coarser-grain summary once (e.g., daily totals instead of raw events) and have downstream queries read the summary; moderate complexity (a new pipeline stage to maintain), significant cost reduction when many downstream queries would otherwise redundantly aggregate the same raw data repeatedly.
• Incremental updates: only recompute the portion of the table affected by NEW data since the last run, rather than the full history every night; higher complexity (you need a reliable way to identify what changed, and correct handling of late-arriving/updated historical rows), but the biggest cost win for a large, mostly-static history with a small daily delta.
• Materialized views: the database maintains a pre-computed result incrementally (in engines that support it) or on a refresh schedule; low application-level complexity (the database does the incremental work), but real storage cost and a dependency on the specific engine's materialized-view capabilities.
• Change data capture (CDC): stream changes (inserts/updates/deletes) from the source system directly, avoiding periodic full-table diffs entirely; highest infrastructure complexity (a CDC pipeline to build and operate), but the most scalable long-term solution when the source system changes frequently and a nightly full-recompute is fundamentally the wrong shape for the problem.

Worked example: a metric derived table recomputing 3 years of daily history every night because the job doesn't distinguish "changed" from "unchanged" partitions is the classic case for incremental updates: switching to only reprocess the last few days (where late-arriving data realistically still lands) plus any dates explicitly flagged for backfill cuts the nightly compute from "3 years" to "a few days," typically the single largest cost win available before reaching for CDC.

Trade-offs & pitfalls: Reaching for CDC or a full materialized-view rebuild before first trying the cheaper options (partition pruning, incremental updates) is a common overengineering mistake; start with the simplest fix that addresses the ACTUAL cause of the cost (usually "the job rescans more history than it needs to"), and only invest in CDC when the source system's change frequency and downstream freshness requirements genuinely demand it.

Direct answer: Bring both teams together to agree on ONE canonical "activations" definition (documenting why the other was rejected, not just declaring a winner), implement it once in the shared metrics layer, migrate both pipelines to read from that single definition, and deprecate the legacy variants on a documented timeline rather than an abrupt cutover.

Structured elaboration, the process:

1. Surface the disagreement concretely: show both teams the ACTUAL numeric gap between "first 7-day key event" and "first login + profile completion" on the same historical data, and what decisions each number has been driving, so the conversation is about impact, not abstract preference.
2. Reconcile to one canonical definition: this usually isn't picking one side wholesale; it may be a genuinely new definition informed by both (e.g., "first 7-day key event, where profile completion IS one of the qualifying key events" if that's what both teams' underlying intents actually converge on), decided by whoever has ultimate accountability for the business outcome activations is meant to represent.
3. Implement once: register the canonical definition in the metrics layer (S58/S59's registry/dbt pattern) with a single metric_id, so both product's and growth's pipelines read from the SAME underlying model rather than maintaining two independently-evolving copies.
4. Migrate consumers: update dashboards, alerts, and any downstream models (e.g., an activation-driven onboarding experiment analysis) to point at the new canonical metric, running the OLD and NEW definitions side by side for a transition window so any consumer relying on a specific legacy number can see exactly how and when it will change.
5. Deprecate legacy variants: mark the old definitions deprecated with a superseded_by pointer (S38's registry schema) rather than deleting them outright, so historical reports referencing the old name remain interpretable, and set a firm sunset date communicated well in advance.

Worked example: if "product's" and "growth's" definitions of activation disagree by 15% on a sample month, and investigation shows the gap is almost entirely users who complete profile setup without an early key event (counted by growth, not by product), the canonical definition decision is really a business judgment call about whether profile completion alone should count as "activated," not a technical dispute at all; framing it as a business decision (with the data laid out clearly) rather than a data-quality argument is what actually resolves it, since neither original definition was technically WRONG.

Trade-offs & pitfalls: Migrating all downstream consumers to a new canonical definition simultaneously is risky (any consumer with an undiscovered hard dependency on the OLD number's specific behavior breaks all at once); running both in parallel for a documented transition window, with active outreach to known consumers, minimizes the disruption this question specifically asks to minimize, at the cost of a period where two numbers legitimately coexist and need to be clearly labeled to avoid re-introducing the exact confusion this whole process exists to resolve.

Direct answer: Define DAU as the count of distinct canonical users (coalescing signed-in user_id with an anonymous anon_id where no login exists) with at least one qualifying activity event in a UTC calendar day, after excluding bots and internal/test accounts and after deduplicating a user's activity across web and mobile into one canonical identity.

Structured elaboration, as the documentation would read:

1. User identifier: use user_id when signed in; when anonymous, use a durable anon_id (cookie/device ID), and if the same person later signs in, that is a KNOWN identity-merge event, not a new user, so historical anonymous activity should be attributable to the merged identity for retention analyses (though for a same-day DAU count, this rarely changes the number, since it's about distinctness within one day).
2. Qualifying events: name the specific event types that count (e.g., page_view, search, add_to_cart, purchase) rather than "any event," because some events (heartbeat pings, background sync) are not genuine activity and would inflate the count if included by accident.
3. Time window: a UTC calendar day, explicitly, with a documented note that this differs from "the user's local day" (see the timezone trade-off below).
4. Cross-device dedup: if a signed-in user is active on both web and mobile the same day, they count once; this requires the canonical-identity join described above, keyed on user_id, not on device or session.
5. Bots/test accounts: exclude via an explicit is_bot flag or a maintained internal-account allowlist, reviewed periodically since bot patterns change.
6. Documentation for reproducibility: publish the exact event-type list, the identity-merge rule, and one worked day's number with a manual audit trail so a stakeholder can re-derive it by hand on a small sample.

Worked example: a user signed in on mobile at 9am (a search event) and, later that same UTC day, browses anonymously on a shared work computer (a page_view event under a different anon_id, no login) would, under a naive identifier-per-row count, appear as two distinct "users" that day. The canonical definition above only merges them if there's an actual identity-linking signal (e.g., the anon_id was later associated with that account via a login on that device); absent such a signal, they are correctly counted as two different activity instances because the system genuinely cannot tell they're the same person from data alone; documenting that limitation explicitly (rather than silently overcounting or undercounting) is part of an auditable definition.

Trade-offs & pitfalls: UTC day boundaries are simpler to implement and keep partitioning simple, but they split a user's late-evening local activity across two UTC calendar dates for large parts of the world, understating single-day engagement for those users; user-local-day boundaries are more accurate to lived experience but require a timezone dimension on every event and complicate partition pruning. Pick one, document the choice, and don't silently switch mid-series (a switch changes the historical trend line's meaning without warning).

Direct answer: Compute on demand from raw events for low-traffic, ad hoc, or rarely-viewed metrics where freshness and flexibility matter more than cost; use scheduled batch aggregates for well-known, high-traffic metrics viewed on a predictable cadence; use materialized views (incrementally maintained) when the metric is both high-traffic AND needs near-real-time freshness.

Structured elaboration, the factors:

• Query pattern: a metric queried rarely, with ad hoc slicing, doesn't justify pre-computation; a metric hit by thousands of dashboard loads per day does, because recomputing from raw events on every page load wastes compute repeatedly for the same answer.
• Freshness requirement: an executive weekly-summary dashboard tolerates hours of staleness; a live operational dashboard does not. On-demand queries are freshest by construction (no lag beyond the raw data itself); scheduled batch introduces a lag equal to the batch interval; a well-built incremental materialized view can approach near-real-time without recomputing everything from scratch each time.
• Cost, compute vs storage: on-demand shifts cost to compute (paid every time someone looks); pre-aggregation shifts cost to storage and pipeline maintenance (paid once, read many times cheaply). For a metric read far more often than the underlying data changes, pre-aggregation is a clear win; for the reverse, on-demand wins.
• Cardinality: a high-cardinality dimension (e.g., per-user metrics for millions of users) makes a fully pre-aggregated table itself become large and slow to maintain; sometimes the right answer is pre-aggregating to a coarser grain (per-day, per-segment) and computing the finest-grain detail on demand only when someone drills in.
• Ease of maintenance: an on-demand query has one source of truth (the raw event logic) and nothing to keep in sync; a materialized view or batch table adds a second artifact that can drift from the raw logic if not tested and refreshed consistently (this connects directly to the unit-testing and reconciliation practices discussed elsewhere in this topic).

Worked example: an executive-facing "weekly active users trend" viewed by a handful of people once a week is a good candidate for on-demand or a simple daily batch job; the same DAU metric powering a live in-app "X people online now" counter for millions of daily dashboard loads needs an incrementally maintained materialized view, because recomputing a full distinct-count over raw events on every page load at that traffic level would be prohibitively expensive.

Trade-offs & pitfalls: Defaulting to materialized views everywhere "to be safe" adds real maintenance burden (schema migrations, incremental-refresh bugs, staleness monitoring) for metrics that never needed the speed; defaulting to on-demand everywhere "to keep it simple" creates a compute bill and latency problem the first time a metric goes from niche to widely used. Revisit the choice as usage patterns change, rather than treating it as a one-time decision.

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.