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

The cause is two definitions in two places, not a data bug. Fixing the data reconciles them once; fixing the definition reconciles them forever.

First, find the difference precisely. Pull both queries and diff the logic: which events count as activity, what window (calendar day or trailing 24 hours), which users are excluded (internal, test, bots), and how duplicates are handled. The gap is always in one of those four.

Then:

• Write the definition down once, in the model layer, as a tested table: active_users_daily with the grain, the window, the filters, and the dedup rule encoded in SQL that both dashboards read.
• Point both dashboards at it and delete their own SQL for the metric. If one team needs a different definition, it gets a different name (engaged_users_daily), not a different implementation of the same name.
• Put the definition text on the dashboard itself, next to the number.
• Add a reconciliation check that fails if any dashboard's number diverges from the model.

Then the governance part, said briefly: someone owns the metric, changes to it go through review, and the semantic layer or metrics layer, if the stack has one, is where it lives.

Worked line: "Marketing counted a page view as activity with a trailing-24-hour window; product counted a session start on a calendar day. Both were right by their own definition. One model, active_users_daily, calendar day, session start, internal users excluded; both dashboards read it. The 12% gap is gone and the word 'active' means one thing."

• 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.

Related questions

• Design a data pipeline to ingest 1B events/day.
• Batch vs streaming: how do you decide for a given use case?
• A nightly pipeline fails halfway through. How do you design it so re-running is safe?
• Events can arrive up to 48 hours late. How do you handle that in a daily aggregation?
• When would you denormalize a warehouse table, and what do you give up?
• Design a data warehouse schema for an e-commerce company.

Practice this for real

• Check your resume against a Analytics Engineer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card