MediumSystem Design

74 practiced

Design a lightweight alerting and escalation strategy for critical BI dashboards using only existing tools (email, Slack, database monitoring). Define alert thresholds, owners, routing rules, and actions for the following event types: data-late, schema-change, and sudden-metric-drift. Explain how you would prevent alert fatigue.

Sample Answer

Requirements & constraints:- Use only email, Slack, and existing DB monitoring (e.g., cron jobs, DB alerting, or scheduled queries).- Lightweight, low-maintenance; separate critical executive dashboards (SLA 15m) vs operational (SLA 1h).- Goal: detect data-late, schema-change, sudden-metric-drift; route to correct owners and escalate to on-call if unresolved.

High-level design:- Three scheduled monitors per dashboard: ingestion-latency check, schema-compatibility check, metric-stability check. Runs in DB or as scheduled query from BI tool (every 5–15 min for critical dashboards).- Alerts emitted to a small orchestration layer (simple DB table + cron): records alert, owner, severity, timestamps, and escalation state. Notification channels: Slack for immediate, email for summary and paging.

Event definitions, thresholds, owners, routing, actions:1) data-late- Definition: latest source partition timestamp older than threshold (critical: >15m; operational: >60m).- Owner: data owner (ETL engineer) primary; BI analyst as secondary.- Routing: Send immediate Slack DM to data-owner on-call + channel #data-alerts; email to owner + stakeholders.- Action: owner acknowledges in Slack (reaction or link to alert row); if no ack in 10 min (critical) escalate to team lead and on-call, create issue in ticketing system (optional).2) schema-change- Definition: unexpected column add/drop/type change compared to known schema fingerprint.- Threshold: any breaking change = immediate critical.- Owner: data engineering; BI analyst notified for downstream impact.- Routing: Slack channel #schema-changes (tag @data-eng-team) + email with diff and affected dashboards list.- Action: freeze dashboard refreshes (mark “stale” banner) until resolved; owner's acknowledgment required within 5 min; escalate to manager after 20 min.3) sudden-metric-drift- Definition: metric deviates >X sigma or >Y% vs rolling baseline (e.g., >4σ in last 24h or >30% change vs 7-day median) AND absolute impact threshold (to avoid noise).- Owner: BI analyst + product/business owner.- Routing: Post to dashboard’s Slack channel with visualization snippet and link, email summary to stakeholders.- Action: quick triage checklist: confirm data freshness, check upstream anomalies, label as incident if confirmed. If unlabeled/unacknowledged in 30 min escalate to director.

Preventing alert fatigue:- Use severity tiers (info/warning/critical) and thresholds combining relative (σ) and absolute value safeguards so tiny changes don’t alert.- Silence windows and deduplication: group identical alerts per dashboard for a rolling 30–60 min window; suppress repeated notifications until state changes.- Require acknowledgment before re-alerting: only escalate after N unacknowledged repeats.- Auto-tune: track false positives; weekly review to adjust thresholds; add whitelist rules during known maintenance windows.- Provide actionable context: each alert includes root-cause pointers (last ETL logs, recent schema diffs, sample rows, links to dashboard and query) so responders can act quickly.

Operational notes & trade-offs:- Lightweight orchestration via a DB table and cron keeps system maintainable; downside: less feature-rich than PagerDuty.- Rely on Slack for immediacy and email for audit/history.- Regular post-incident reviews keep thresholds meaningful and reduce noise over time.

MediumTechnical

73 practiced

You just finished an ambiguous BI project. Outline a structured retrospective you would run with stakeholders to capture lessons learned: agenda items, data-driven artifacts to bring, techniques to surface root causes of ambiguity, and how you will convert learnings into measurable process improvements with owners and timelines.

Sample Answer

Situation: We just closed an ambiguous BI project that delivered dashboards but had scope drift, rework, and stakeholder dissatisfaction. I’d run a structured retrospective with stakeholders to capture lessons and convert them into measurable improvements.

Agenda (90 minutes)- 0–10 min: Objectives & ground rules (blameless, factual)- 10–25 min: Timeline review — major milestones, decisions, handoffs- 25–45 min: Data-driven review (metrics & artifacts)- 45–70 min: Root-cause analysis workshop (techniques below)- 70–85 min: Action planning — concrete improvements, owners, timelines- 85–90 min: Wrap-up & next steps (follow-up cadence)

Data-driven artifacts to bring- Dashboard usage logs (views, filters, session length)- Version history / change requests and approval timestamps- Requirements docs, initial wireframes, email threads / meeting notes- Data lineage & quality incident log (ETL failures, data gaps)- Delivery schedule vs. actual timeline and rework effort estimates

Techniques to surface root causes- Timeline walk: annotate where ambiguity/changes occurred- Affinity mapping: collect pain points from all stakeholders, cluster themes- 5 Whys on top 3 pain clusters to get to root causes- Fishbone (Ishikawa) for recurring issues (People, Process, Data, Tools)- Example scenario walkthroughs using actual data incidents

Convert learnings into measurable process improvements- Draft 4–6 SMART actions with owner and due date; examples: - “Introduce a 1-week discovery template & sign-off” — Owner: PM/BI lead — Due: 4 weeks — Metric: % of projects with signed discovery = 100% within quarter - “Instrument dashboards for usage and feedback” — Owner: BI engineer — Due: 3 weeks — Metric: weekly active users and NPS score - “Enforce data quality SLA & incident tracker” — Owner: DataOps — Due: 6 weeks — Metric: mean time to detect/resolve data incidents ≤ 24 hrs - “Requirement change log + impact assessment” — Owner: Product owner — Due: next sprint — Metric: % scope changes with documented impact- Assign RACI for each improvement and publish an action tracker (shared doc) with weekly status updates for 6 weeks, then monthly review.- Measure success: baseline current metrics, set target improvements, review at 30/60/90 days.

This approach ties qualitative feedback to objective artifacts, surfaces root causes systematically, and converts lessons into owned, measurable changes with timelines.

EasyTechnical

73 practiced

A stakeholder provides two conflicting definitions for 'active user' (one counts any login, another counts only users who performed a transaction). Walk through how you would reconcile these definitions: who you would involve, the tests or sample data you would produce to show differences, and how you would document the final agreed definition so future reports are consistent.

Sample Answer

Situation: Two stakeholders disagree — Product wants "active user" = any login, Finance wants only users who performed a transaction. As BI Analyst I must reconcile definitions so reports are consistent.

Approach / Who I’d involve:- Convene a short working session with Product, Finance, Data Engineering, and a PM/owner to align on the metric’s purpose (engagement vs revenue).- Ask each stakeholder: what decision will use this metric, acceptable latency, and required granularity (daily/weekly/monthly).

Tests / sample data to show differences:- Produce side-by-side counts for a recent period (e.g., last 30 days): logins, transactions, and overlap. Example SQL to get counts:

sql

WITH logins AS (
  SELECT user_id, COUNT(*) AS login_count FROM events WHERE event='login' AND ts >= CURRENT_DATE-30 GROUP BY 1
),
transactions AS (
  SELECT user_id, COUNT(*) AS tx_count FROM events WHERE event='purchase' AND ts >= CURRENT_DATE-30 GROUP BY 1
)
SELECT
  (SELECT COUNT(*) FROM logins) AS login_users,
  (SELECT COUNT(*) FROM transactions) AS transacting_users,
  (SELECT COUNT(*) FROM logins INNER JOIN transactions USING(user_id)) AS both;

- Create a small dashboard showing: MAU by login, MAU by transaction, conversion rate (login→purchase), and cohort retention differences.- Run sensitivity checks: how business KPIs (revenue per active user, conversion) change using each definition.

Decision & Reasoning:- If metric is for product engagement, choose "login-based active user." If for revenue/unit economics or billing, choose "transacting active user." Propose maintaining both as distinct, named metrics (e.g., ActiveUser_Login, ActiveUser_Purchase).

Documentation & Governance:- Add both definitions to the BI data dictionary with: - Name, exact SQL / logic, time window (daily/weekly/monthly), update cadence, owner, examples, and intended use-cases.- Version-control definitions (Git + changelog) and include tests (unit queries) that alert if counts change unexpectedly.- Communicate changes: share a one-page summary, update dashboards with metric name badges and hover-text describing definition, and schedule an annual review or change request process.

Result: Stakeholders understand trade-offs, each gets the metric they need, dashboards are unambiguous, and future reports remain consistent through documented definitions and governance.

MediumTechnical

82 practiced

Create an onboarding checklist and short cadence (initial meetings, documents, sample dashboards) for new BI stakeholders to set expectations about metrics, delivery timelines, data access, and communication channels. Explain why each checklist item is necessary and how it reduces future ambiguity.

Sample Answer

Framework: I build an onboarding checklist that sets clear expectations across metrics, timelines, access, and communication so stakeholders and BI share one source of truth from day one.

Checklist (must-have items + why it reduces ambiguity):- Stakeholder info sheet (owner, decision rights, frequency) — identifies who to engage and who approves metrics; avoids ownership debates.- Business goals & primary use cases (top 3 questions the report must answer) — aligns outputs with decisions, prevents feature creep.- Glossary of metrics & definitions (calculation, filters, time windows) — prevents semantic mismatch (e.g., “active user”).- Data sources inventory & SLAs (tables, owners, freshness) — clarifies where numbers come from and expected latency.- Data access checklist (roles, sample queries, required accounts) — speeds onboarding and avoids failed requests.- Delivery timeline & cadence agreement (MVP date, iterative releases) — sets expectations on scope and helps prioritize.- Communication plan (channel for requests, escalation path, expected response times) — reduces back-and-forth and missed messages.- Sample dashboards & templates (executive, operational, sandbox) — provides concrete examples of acceptable outputs and speeds iteration.- Success metrics & review schedule (adoption KPIs, feedback loops) — measures impact and drives continuous improvement.- Compliance & privacy notes (PII handling, masking rules) — ensures safe usage and avoids rework.

Short cadence (first 90 days):- Day 0: Welcome packet + access checklist sent.- Week 1: 60–90 min kickoff — review goals, glossary, data sources, immediate priorities.- Week 2: Data access verification + sample SQL/dashboard walkthrough.- Week 3–4: MVP delivery (1st dashboard) + feedback session.- Month 2: Weekly 30-min touchpoints to iterate.- Month 3: Business review — adoption metrics, full roadmap, handoff to BAU.

How this reduces ambiguity:- Concrete definitions and owners remove debates about meaning and responsibility.- Early MVP + templates produce tangible artifacts to validate assumptions quickly.- Regular, time-boxed touchpoints ensure continuous alignment and fast course corrections.- Documented SLAs and access steps prevent delays from hidden dependencies.

Expected outcomes: faster delivery (shorter time-to-first-dashboard), fewer rework cycles, higher stakeholder satisfaction and adoption measured by delivery SLA compliance and usage metrics.

EasyBehavioral

99 practiced

Tell me about a time you proactively owned an ambiguous reporting request that lacked specific requirements. Use the STAR method: describe the situation, the actions you took to clarify or prototype (including questions you asked), how you adapted as requirements shifted, how you communicated progress, and the measurable outcome.

Adaptability & Ownership in Ambiguous Situations Questions

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Unlock Full Question Bank