Hiprup

How would you resolve conflicting definitions of the same business metric across teams?

When Finance says 40,000 active customers and Marketing says 62,000, neither is lying — they are computing different things and calling them the same name. The fix is a governance problem with a technical implementation, and interviewers use it to test whether you can drive alignment rather than just write SQL.

  • Reproduce both numbers first — never arbitrate from descriptions; rebuild each figure and diff the logic to find the precise divergence, which is usually filters, status handling, date basis or grain.

  • Name the specific difference — "Marketing counts any login, Finance counts completed non-refunded orders" turns an argument about correctness into a factual choice between two clear options.

  • Decide with the owners, not alone — the analyst surfaces the options and consequences; the business owner picks the definition, because a metric imposed by an analyst does not stick.

  • Certify it in one place — encode the agreed logic once in a shared view, semantic model or dbt metric so every report inherits it instead of each team re-implementing it.

  • Give real variants distinct names — where both definitions are legitimately needed, publish active_purchasing_customers and active_logged_in_customers; the failure mode is two things sharing one name, not two metrics existing.

Key terms: single source of truth, metric definition, semantic layer, certified dataset, data dictionary, dbt metrics, governance, metric ownership

-- "How many active customers did we have in June?"  Two teams, two numbers.

-- DEFINITION A (Marketing): anyone who logged in during the month
SELECT COUNT(DISTINCT customer_id) AS active_logged_in
FROM   app_sessions
WHERE  session_date >= DATE '2026-06-01'
  AND  session_date <  DATE '2026-07-01';
-- returns 62,000

-- DEFINITION B (Finance): anyone with a completed, non-refunded order
SELECT COUNT(DISTINCT customer_id) AS active_purchasing
FROM   fact_orders
WHERE  order_date  >= DATE '2026-06-01'
  AND  order_date  <  DATE '2026-07-01'
  AND  order_status = 'completed'
  AND  is_refunded  = FALSE;
-- returns 40,000


-- STEP 1 -- diff the logic, do not arbitrate. Quantify each filter's effect:
SELECT
    COUNT(DISTINCT customer_id)                                        AS all_orders,
    COUNT(DISTINCT CASE WHEN order_status = 'completed'
                        THEN customer_id END)                          AS completed_only,
    COUNT(DISTINCT CASE WHEN order_status = 'completed'
                         AND is_refunded = FALSE
                        THEN customer_id END)                          AS completed_not_refunded
FROM   fact_orders
WHERE  order_date >= DATE '2026-06-01' AND order_date < DATE '2026-07-01';
-- Now the gap is explained line by line rather than argued about.


-- STEP 2 -- certify BOTH, with names that cannot be confused, in one shared view.
CREATE OR REPLACE VIEW metric_active_customers AS
SELECT
    DATE_TRUNC('month', d.calendar_date) AS month,

    -- owner: Marketing | engaged with the product at least once
    COUNT(DISTINCT s.customer_id)                        AS active_logged_in_customers,

    -- owner: Finance   | generated recognised revenue
    COUNT(DISTINCT CASE WHEN o.order_status = 'completed'
                         AND o.is_refunded = FALSE
                        THEN o.customer_id END)          AS active_purchasing_customers
FROM       dim_date     d
LEFT JOIN  app_sessions s ON s.session_date = d.calendar_date
LEFT JOIN  fact_orders  o ON o.order_date   = d.calendar_date
GROUP  BY  1;
-- Every dashboard now reads from this view. Neither team may publish "active customers"
-- as a bare label again -- the name itself carries the definition.

The first two queries make the conflict concrete: both are valid SQL, both are correctly computed, and they differ by 22,000 customers purely because they answer different questions. The step-one query is the technique that actually resolves these disputes — rather than debating, it counts the same population under each filter in turn so the gap can be attributed clause by clause, showing exactly how many customers are lost to the status filter and how many to the refund filter. That converts a credibility argument into an arithmetic explanation.

The step-two view implements the governance fix: both definitions survive, but each gets an unambiguous name and a documented owner, and every downstream report consumes the certified view rather than re-implementing the logic. The closing comment states the rule that prevents recurrence — the failure was never that two metrics existed, it was that both were labelled active customers.

Lead with reproducing both numbers — candidates who start by picking a side lose the question immediately, because the interviewer is testing whether you investigate before arbitrating. The most impressive move is reframing correctness as a choice between two well-defined options, which is what unblocks the meeting. Have a real example ready about a date basis or refund-handling difference, since that level of specificity is hard to fake.

Expect the follow-up "what if the two teams refuse to agree?" — the right answer is to publish both under distinct names with documented definitions and escalate ownership, not to let one silently win. Finish on the semantic layer, which shows you know how to make the fix permanent.