System Design · medium
Two dashboards report different numbers for active users. How do you fix it for good?
Asked in Analytics Engineer interviews, in the System Design round.
Short answer
The cause is two definitions, not a data bug. Define the metric once in a shared model or a semantic/metrics layer and have both dashboards read it, instead of each re-deriving it in its own SQL. Encode the grain and filters (which events, what window, the dedup rule) in one tested model. The fix is governance (a single source for the definition) not another patch on one dashboard.
How to answer it
First, diagnose
Resist rebuilding anything until you know which of these it is, in rough order of likelihood:
Different definitions one counts any event, another counts a login,
a third excludes internal staff
Different time grain calendar month against rolling 30 days, or
UTC against local time
Different filters one excludes bots, trials, or churned accounts
Different source one reads raw events, the other a modeled table
with deduplication applied
Different freshness one refreshed at 6am, the other at noon
Distinct count summed weekly actives computed as the sum of daily actives,
which double counts anyone returningThat last one is worth checking early, because it is common and looks like a definition problem when it is an arithmetic one.
How to find it fast
Take one day, one segment, and drill both numbers down to a list of user ids. Compare the sets rather than the totals. Whoever is in one list and not the other tells you which filter or definition differs, in minutes rather than in a meeting.
Fixing it for good
Reconciling the two numbers is not the fix; they will diverge again next quarter. The durable fix is structural:
- One definition of active user, written down, with an owner.
- Defined once in the transformation or semantic layer, not in each dashboard.
- Every dashboard reads that model. No dashboard computes it from raw events.
- A test asserting the definition holds, so a change breaks the build rather than a meeting.
- Deprecate the losing dashboard rather than leaving both up. Two dashboards is the actual bug.
Key points
- Diagnose before rebuilding: definition, grain, filters, source, freshness, or a summed distinct count.
- Compare user id sets on one day, not totals.
- The fix is one definition in one place that every consumer reads.
- Leaving both dashboards up guarantees the problem returns.
- A metric without an owner drifts by default.
FAQ
- What if both are right for their audience? Then they are different metrics and need different names. Active user cannot mean two things.
- Where should the definition live? A semantic layer if you have one, otherwise a dbt mart model. The point is one place.
- How do I stop it recurring? Ownership, tests, and refusing to let a new dashboard define its own version.
The 30-second answer
First I would diagnose rather than rebuild. The usual causes are different definitions of active, different time grains like calendar month against rolling thirty days, different filters such as excluding internal users, a different source table, or different refresh times. I would check early whether someone computed weekly actives by summing daily actives, because a distinct count does not add up, and that looks like a definition problem when it is arithmetic.
The fastest way to find it is to take one day and drill both numbers down to a list of user ids, then compare the sets instead of the totals.
Fixing it for good is structural rather than a reconciliation: one written definition with an owner, implemented once in the transformation or semantic layer, every dashboard reading that model, a test so a change breaks the build, and the losing dashboard deprecated. Leaving both up is what guarantees it comes back.
In the interview
- What they are checking: that you diagnose it as a definition problem and fix it structurally.
- Common mistake: patching one dashboard's query to match the other. The next dashboard reintroduces the gap.