Business Intelligence Analyst (Staff Level) - FAANG Interview Preparation Guide

Requirements (clarify):

• Detect: refresh failures, slow queries, sudden metric anomalies (spikes/drops), dashboards without owners
• Actions: alerting (email/Slack/PagerDuty), auto-ticket creation (Jira), remediation playbooks, owner routing & escalation
• Scale: ~500 dashboards, multiple data sources (databases, cloud warehouses, APIs)
• SLOs: alert within 5–15 min of failure; priority routing for exec dashboards

High-level architecture:

• Instrumentation agents (lightweight collectors) per BI platform + query proxy for timing
• Central Monitoring Service (ingest, rules, anomaly engine)
• Time-series/metadata store (Prometheus/Influx for metrics; PostgreSQL for metadata)
• Alerting & Orchestration (Alertmanager/PagerDuty + automation runner)
• Ticketing integration (Jira API)
• Owner directory (source-of-truth: HR/Confluence + fallback escalation matrix)
• Dashboard Health UI (for BI team)

Components & responsibilities:

1. Collectors:
   • Pull dashboard metadata (owner, last refresh, schedule) via BI tool APIs.
   • Subscribe to refresh/webhook events; capture query execution time and errors.
2. Monitoring Service:
   • Normalization, enrichment (map queries → data source), stores time-series.
   • Rules engine: static thresholds (failure count, latency), and anomaly detection (EWMA or STL seasonal model per metric).
3. Storage:
   • TSDB for time-series metrics; relational DB for dashboard/catalog metadata and incidents.
4. Alerting/Orchestration:
   • Alerts via Alertmanager → Slack/email/PagerDuty.
   • Automation runner executes playbooks (retry refresh via BI API, restart ETL, run diagnostics).
   • Jira integration auto-creates ticket with runbook, logs, and suggested owner.
5. Ownership routing:
   • Lookup owner; if none or unassigned, route to team on-call via escalation matrix. After N minutes escalate to manager/pager.
6. Dashboard Health UI:
   • Shows failing dashboards, SLA violations, recent tickets, owner assignments, and audit logs.

Data flow (example: refresh failure):
Collector receives refresh failure → pushes event to Monitoring Service → increments failure counter in TSDB and writes incident draft → Rules engine triggers alert → Alertmanager notifies owner; Automation runner attempts a configured remediation (e.g., re-run refresh) → if success, system logs and resolves; if failure or no owner, auto-create Jira ticket and escalate per policy.

Anomaly detection:

• Per-metric baseline: rolling 7-28 day window with seasonality; flag deviations >3σ or relative change >X% with suppressions for low-volume metrics.
• Correlate with upstream ETL failures and datasource latency to reduce false positives.

Escalation rules (example):

• P0 (exec dashboard down or repeated failures >3 times in 15 min): immediate PagerDuty page → automation run → Jira ticket created → if unresolved 15 min escalate to director.
• P1 (business-critical metric anomaly): Slack + email to owner → auto-ticket if not acknowledged in 30 min → escalate to team lead in 2 hours.
• P2 (non-critical, missing owner): auto-ticket to BI team inbox; assign to default rotation; owner flagged for onboarding.

Remediation playbooks (examples):

• Refresh failure: validate datasource connection → check ETL job status → run adhoc query → re-trigger dashboard refresh → post results in ticket.
• Slow query: capture EXPLAIN, suggest index or materialized view, auto-create advisory ticket for data engineering with sample query and 30-day latency trend.
• Anomaly: run rollback-of-change checks (deploys, data schema changes), compare related metrics, notify stakeholders with suggested investigation steps.

Operational notes & best practices:

• Start with conservative thresholds, tune based on observed noise.
• Maintain owner registry and require dashboard creation workflow to set owner; enforce periodic owner confirmation.
• Keep playbooks idempotent and safe (dry-run option).
• Audit all automated actions; allow human override.
• Monitor the monitor (health checks, alert fatigue metrics).

This design balances automated remediation with clear escalation and owner accountability, scalable for 500 dashboards and multiple data sources while minimizing false positives through correlation and baseline-aware anomaly detection.

Direct answer
A classifier-threshold-selection dashboard should let a product manager see, for any candidate threshold, the resulting precision, recall, expected monetary cost, and predicted manual-review volume simultaneously, using a small set of linked, synchronized visuals (a precision-recall curve with a movable threshold marker, plus derived cost and volume tiles) so changing the threshold updates every downstream number at once.

Structured elaboration

1. Required data sources: the model's predicted scores and true labels on a representative validation set, plus a cost model (dollar cost of a false positive, dollar cost of a false negative, and the operational cost of a manual review).
2. Visual elements: a precision-recall (or ROC) curve with an interactive slider or draggable marker representing the candidate threshold; as the threshold moves, linked tiles recompute and display precision, recall, expected total cost, and predicted manual-review volume at that threshold.
3. Calculations: for a candidate threshold, compute confusion-matrix counts (true/false positives and negatives) on the validation set, derive precision and recall from those counts, multiply false positives and false negatives by their respective per-unit costs to get expected monetary cost, and count predictions falling into any "uncertain" band as the manual-review volume.
4. Controls: allow the user to see cost and volume plotted directly against threshold (a simple line chart of "cost vs. threshold" and "review volume vs. threshold") so a PM can visually identify the threshold that minimizes cost or meets a review-capacity constraint, not just read one static number.
5. Validating the dashboard's correctness: recompute the confusion matrix and derived metrics by hand for a couple of specific threshold values and confirm they match what the dashboard displays, and verify against a held-out or time-split validation set (not the same data the model was tuned on) to avoid overstating performance.

Worked example
Moving the threshold slider from 0.5 to 0.7 on the linked view shows precision rising, recall falling, expected cost first decreasing (fewer costly false positives) and then rising again past a certain point (missed true positives become the dominant cost), letting the PM visually identify the cost-minimizing threshold rather than picking an arbitrary round number. Concretely, at threshold 0.5 the linked tiles read precision 61%, recall 88%, expected cost $18,200, and review volume 640; moving to threshold 0.7 they read precision 79%, recall 55%, expected cost $14,900 (the minimum on this curve), and review volume 210; pushing further to threshold 0.9 they read precision 92%, recall 21%, expected cost $22,600, and review volume 40, with cost rising again because the false negatives missed at that threshold (real fraud or defects let through uncaught) now cost more than the false positives it avoids. A PM reading this curve would land close to 0.7, not 0.5 or 0.9, because that's where the expected-cost line actually bottoms out.

Trade-offs and pitfalls
A dashboard that shows precision/recall but omits the cost and volume translation leaves the actual business decision (where to set the threshold) unsupported; the whole point of this dashboard is translating a statistical trade-off into business-relevant units a PM can act on directly.

What goes on the page

A reader with five minutes and no access to you needs seven things:

1. The decision being asked and by when.
2. The recommendation in one sentence, with conditions.
3. The options compared on the same criteria.
4. The evidence behind each score and how strong it is.
5. The weak spots and risks.
6. What would change the call.
7. Next steps with an owner for each.

Excerpt (illustrative: three vendors for a customer-messaging platform)

Recommendation: choose Vendor B, conditional on its security review passing before contract signature.

Scores run from 1 (poor) to 5 (strong). Weights sum to 100. Total = sum of weight x score, divided by 5, so the maximum is 100. Cost is scored from the 3-year total: lowest cost scores 5, up to 20% above lowest scores 4, 20 to 40% above scores 3, over 40% above scores 2. For example, C ($360k) is the lowest and scores 5; B ($430k) is 19% above C, so it scores 4; A ($540k) is 50% above C, so it scores 2. Viability means whether the vendor is likely to still be around, funded and supporting the product in three years.

Showing the evidence and its weak spots

Label each score by evidence strength: tested (we ran it), corroborated (reference calls or documents), or claimed (vendor statement only). For B: fit is tested in a trial; cost is a written quote; integration is corroborated by one reference call and a demo; security rests on the vendor's own security questionnaire (a standard form of questions about how it protects data), which is claimed. Saying this on the page is what lets leadership trust the rest.

What would change my call

• B's integration score falls from 4 to 2: B drops to 70 and A (72) wins. A drop to 3 leaves B ahead, 74 to 72.
• Moving 15 weight points from fit to cost leaves B on top, 78 to C's 74.
• B failing the security review ends the recommendation outright, whatever the score.

Next steps: the security lead completes the review; procurement (the team that negotiates and signs vendor contracts) negotiates B's price; the engineering manager runs a 2-week integration spike (a short, time-boxed trial that connects B to our real systems to find out how hard the integration is).

Direct answer

Map the OKR (objective and key results) to one KPI (key performance indicator) per level of the organization so each team owns a number it can actually move: a company-level outcome metric, a product-level conversion metric that is the biggest lever on that outcome, and a feature-level metric tied to a specific launch or experiment. Each KPI needs its own cadence, owner, and an explicit pass or fail threshold, an acceptance criterion in the same spirit as an OKR's key results, so a status of "on track" or "off track" is unambiguous rather than a matter of interpretation.

Structured elaboration

Framing each KPI with an explicit acceptance threshold, rather than only a direction such as "up is good," is what turns a KPI tree into something a status report can actually score, the same acceptance-criteria discipline a support team would use to phrase a key result like "resolve 90% of tickets within SLA" rather than just "improve resolution time."

Worked example

Baseline paid subscribers: 100,000. The OKR asks for 30% growth:
$100{,}000 \times 1.30 = 130{,}000$
Net subscribers needed:
$130{,}000 - 100{,}000 = 30{,}000$

Assume paid-base churn runs at roughly 2% per month; over 12 months, as a simplified, non-compounding planning estimate rather than a forecast:
$2\% \times 100{,}000 \times 12 = 24{,}000 \text{ gross losses}$

Gross new paid subscribers needed for the year:
$30{,}000 + 24{,}000 = 54{,}000$
Average monthly pace needed:
$54{,}000 / 12 = 4{,}500 \text{ per month}$

If the current free-tier MAU (monthly active users) is 150,000 and the current free-to-paid conversion rate is 2% per month:
$150{,}000 \times 0.02 = 3{,}000 \text{ new paid per month}$
That is short of the 4,500 needed. Closing the gap at the current free-tier size requires the conversion rate to rise to:
$4{,}500 / 150{,}000 = 3\%$
a one-percentage-point lift, which is exactly the number the product-level KPI needs to hit, and which a feature-level trial-conversion improvement, the third KPI, is one lever for achieving.

Trade-offs & pitfalls

• Setting the product-level KPI target without running this kind of top-down arithmetic risks a target that's either trivially easy or quietly impossible relative to what the company number requires.
• A feature-level KPI that improves in isolation, trial conversion up, does not guarantee the company number moves if the feature only reaches a small fraction of the free base; check the feature's reach, not just its own conversion rate.
• Acceptance criteria that are too lenient at every level can all show "on track" while the company-level number still misses, since rounding and timing lags compound going up the tree; sanity-check the tree bottom-up periodically, not just top-down at target-setting time.
• Multiple teams each optimizing their own KPI in isolation can create conflicting incentives, for example a feature team pushing hard trial pressure that hurts the product-level conversion quality; a shared guardrail metric, such as support ticket volume or churn, helps catch this.

Direct answer

Pick a project that genuinely needed more than one function, and be specific about two things: what YOU owned (not what 'the team' did), and the one concrete mechanism that determined whether the collaboration worked, such as a shared definition of done, a clear handoff point, or clarity on who decided what when opinions differed. Vague answers ('we communicated well') sound rehearsed; specific answers sound lived-in.

What the story needs to show

Your specific contribution. Interviewers are listening for what you personally decided or built, distinct from what your collaborators did. If every sentence is 'we', the interviewer cannot tell what you'd do differently on the next team.

A mechanism-level explanation. Organize the story around one of three lenses:

• Shared goal: did every function agree on what 'done' looked like and how success would be measured, or was each function quietly optimizing for its own definition?
• Interface or handoff: was there a clear point where work crossed from one function to another, and was that point actually defined, or did people guess?
• Decision rights: when functions disagreed, was it clear whose call it was, or did disagreement just stall until someone got tired of arguing?

Honesty if it's a struggle story. The question explicitly allows 'succeed or struggle'. A good struggle story ends on what you changed about the collaboration, not on who was at fault.

Worked example

Situation: [your team] needed to deliver [a feature or initiative] that required real work from [Team A, for example a design or research function] and [Team B, for example a data or infra function], against a fixed external date.
Task: your role was the one connecting the three groups, for example owning the shape of the interface between design and engineering, or owning how data requirements got translated into a schema.
Action: early on, each function had a different idea of what 'done' meant for their piece, which caused rework when the pieces met. You wrote a short one-page agreement naming the shared definition of done and who would sign off on each handoff, and used it to resolve the next two disagreements without a meeting.
Result: the project shipped on the revised date, and the agreement itself became something the group reused on the next cross-functional piece of work, which is the real marker of a story about redesigning the collaboration rather than just pushing through it.

To make that skeleton concrete rather than a fill-in-the-blank: picture a checkout redesign that needed real work from the design function and the payments engineering function, against a fixed external date tied to a promotional campaign launch. The specific disagreement was about what 'done' meant for the new payment-method selector: design considered the screen done once every state (loading, error, empty) matched the approved mockups pixel-for-pixel, while payments engineering considered it done once the integration correctly handled every payment-provider response code, even ones with no mockup drawn yet. That mismatch caused two rounds of rework when a payment-provider error state shipped without a design pass. The one-page agreement that resolved it included this line: 'A screen is done when it matches an approved mockup for every state the payments API can return, and any new state discovered after mockups are drawn triggers a joint 15-minute review before either side builds it.' That single sentence is what let the two functions stop re-litigating 'done' every time a new edge case appeared, and both sides signed off on it before the next round of work began.

Trade-offs and pitfalls

• A generic 'we all communicated well' answer with no mechanism is the single most common weak version of this story, avoid it.
• Over-crediting the team at the expense of your own specific contribution leaves the interviewer unable to evaluate you.
• If you pick a struggle story, resist framing it as the other function's fault. The senior version of this answer explains what you changed about how the groups worked together, not who dropped the ball.
• The strongest answers show you redesigning a structure (a handoff, a shared definition, a decision rule), not just working harder inside a broken one.

Start by clarifying the SLA with the executive (acceptable load time, frequency, user scope) and reproduce the issue (same user, dataset, network) so you have a reliable baseline.

Triage framework (fast → deep), prioritized by impact vs effort:

1. Report design (Power BI) — quick wins first

• Tools/diagnostics: Power BI Desktop Performance Analyzer (record visual/query durations); reduce visuals and slicers; check visual-level filters.
• Quick fixes:
  • Remove or simplify high-cost visuals (maps, complex custom visuals).
  • Turn off “Auto date/time” if not needed.
  • Reduce cross-filtering/interaction between visuals.
  • Limit row/column footprint (select only needed columns).
  • Use aggregations or pre-aggregated views for executive-level pages.
  • Consider switching the page to Import mode or using hybrid tables for hot data.

2. BI connector & application

• Tools/diagnostics: Power BI Query Diagnostics (Start/Stop tracing), DAX Studio (Server Timings, Query Plan), Fiddler/Wireshark for HTTP timing, check gateway logs (if on-prem) and gateway performance metrics.
• Quick fixes:
  • Ensure gateway has enough CPU/memory and patch level; scale gateway cluster if saturated.
  • Enable query caching where appropriate.
  • Reduce frequency of auto-refresh visuals; enable bookmarks/ drill-through to avoid loading all visuals at once.
  • Verify DirectQuery timeout/retry settings and connection encryption overhead.

3. Database (source) — deep investigation

• Tools/diagnostics: SQL Server Profiler / Extended Events / Query Store / Azure SQL Query Performance Insight; capture actual SQL generated by Power BI (via DAX Studio or connector logs) and run it with SET STATISTICS IO/TIME; examine execution plans and missing index DMVs; check wait stats and resource utilization (CPU, IO, tempdb).
• Quick fixes:
  • Add targeted indexes or filtered indexes for hot queries; update statistics.
  • Create materialized views / indexed views or summary tables for heavy aggregation paths.
  • Parameterize queries or rewrite expensive predicates (avoid nested functions on indexed columns).
  • Move large lookups to smaller dimension tables or pre-join in a view.
  • If latency is network-related, colocate BI gateway or use Azure private link / ExpressRoute.

How to prioritize:

• Triage by easy wins first: report-level fixes and gateway sizing (low effort, often high impact).
• If problem persists, capture the slow SQL and run in DB to determine root cause (high impact, higher effort).
• Use an impact/effort matrix and deliver incremental fixes, communicating expected improvements and timelines to the executive.

Typical diagnostics sequence:

1. Reproduce and note end-to-end time.
2. Run Performance Analyzer → identify slow visuals/queries.
3. Use Query Diagnostics + DAX Studio to capture translated SQL and server timings.
4. Run SQL trace / Query Store and examine execution plans and wait stats.
5. Apply quick report/gateway fixes; if DB tuning required, propose indexes / summaries and measure improvement.

This approach gives fast customer-visible wins while preserving time for deeper database optimization when needed.

Direct answer

Teaching a skill you don't have natural talent for means separating what you know intuitively from what's actually teachable. You diagnose the real gap first, build an explicit, decomposed framework for the skill (even though you perform it by feel), and validate progress by watching the person apply it independently, not by how confident the coaching sessions felt.

Approach to teaching outside your natural strength

Diagnose before prescribing. "Struggles with X" is rarely one problem. Watch or review their actual attempt and separate the layers: is it a knowledge gap (they don't know the structure), a delivery gap (they know the structure but execution is shaky), or a confidence gap (they know it and can do it, but freeze under real stakes). Each needs a different intervention.

Decompose your own tacit skill into explicit steps. If you're good at something without having consciously learned it as a framework, you have to reverse-engineer your own process before you can teach it. Skipping this step and just saying "do what feels right" doesn't transfer anything.

Practice at graduated, increasing stakes. Start with low-stakes reps where mistakes are cheap and recoverable, then move toward the real, higher-stakes version. Jumping straight to the real thing conflates skill-building with performance evaluation in the person's head, which raises anxiety and slows learning.

Give feedback on the mechanism, not just the outcome. "That worked" or "that didn't work" is much less useful than pointing at which specific move in their approach caused the result.

Worked example

Situation: someone you're mentoring is excellent at the core technical work but has a real gap in a skill that doesn't come naturally to you either, say, communicating findings clearly to people outside the immediate team. Their material was always technically sound, but reviews ran long and the point often got lost.

Task: help them close that gap over a defined stretch, without pretending you have natural talent for it yourself.

Action: you watched a recording of one of their sessions together and separated content problems (no clear headline, too much detail up front) from delivery problems (pace, not anticipating pushback). You gave them a simple structure to practice against: state the conclusion first, then the supporting evidence, then the recommendation. You ran a couple of low-stakes rehearsals where you played a skeptical stakeholder, then let them run the real session solo.

Result: over a few sessions, their reviews needed fewer clarifying follow-up questions from the room, and the structure started showing up unprompted in written material too, not just live presentations. The real signal wasn't how the coaching sessions felt: it was watching them handle a session you weren't part of and hearing secondhand that it landed cleanly.

Trade-offs and pitfalls

A common junior-mentor mistake is trying to transfer your own tacit competence directly ("just do what I do") instead of decomposing it. That fails specifically because the skill you're teaching is one you never consciously learned as steps.

Another mistake: avoiding coaching on gaps you don't personally excel at, on the theory you're not qualified. You don't need to be naturally gifted at a skill to teach its structure. You need to be willing to build the explicit framework, which sometimes non-naturals do better than naturals, because they had to learn it deliberately themselves.

The real trade-off is time. Teaching a skill outside your own strength takes longer to prepare for, because you can't rely on instinct in the room. That prep time is where the actual coaching value gets built.

Direct answer

Build a single conformed customer_dim, assembled by an identity-resolution and reconciliation process that merges records from CRM, support, billing, and marketing systems into one authoritative view keyed by a conformed surrogate key, owned by a single team, so every downstream fact table joins to the same customer identity regardless of which source system originated a given fact.

Structured elaboration

• Assembly process: each source system's customer records are matched (via shared identifiers where available, or matching logic like email/phone/name similarity where not) into a single conformed identity, similar in structure to product-dimension conformance but usually with higher identity-resolution complexity, since customer matching keys (email, phone) are noisier and more prone to typos or intentional variation than product SKUs.
• Governance as new sources onboard: define an intake process for any new source system that wants to contribute customer data: it must map its records into the conformed identity space (via the same matching pipeline), not create its own parallel customer notion. A governance body (or a designated owning team) reviews and approves new attribute additions to the shared dimension.
• Communicating breaking changes: maintain a change log and a deprecation process for any modification to the conformed dimension's schema or definitions (a segment definition changing, an attribute being renamed), with advance notice to every known downstream consumer (fact tables, dashboards, and any team known to query the dimension directly), since breaking it silently corrupts every team's reporting simultaneously.
• Ongoing reconciliation: run periodic checks comparing customer counts and key attribute distributions between the conformed dimension and each source system's own counts, to catch identity-resolution drift or a source system silently diverging from the conformed model.

Worked example

A customer signs up via the marketing site (captured in the marketing system), later contacts support (captured in the support system) using a slightly different email, and eventually becomes a paying customer (captured in billing). The customer-360 pipeline's identity-resolution matches all three records (via a combination of matched device ID, name similarity, and a shared phone number) into one conformed customer_key, so a single dashboard can show this customer's full marketing-to-support-to-billing journey without three separate, inconsistent customer identities.

Trade-offs and pitfalls

Identity resolution at this scope is genuinely hard and never perfect; the practical risk is either over-merging (falsely combining two different people who share some matching signal, like a shared household email) or under-merging (missing that two records are the same person due to noisy source data). Invest in a confidence-scored matching process with human review for ambiguous cases, and make match confidence visible to downstream consumers rather than presenting every merge as equally certain.

Direct answer

In the first 60 seconds on an unfamiliar schema, scan for primary and foreign keys to understand the entity graph, check table row counts to spot which tables are the "big" transactional ones versus small reference tables, and note any obviously suspicious column names (timestamps, status/type enums, anything that looks like a business identifier) before writing a single query.

Structured elaboration

1. Keys first: list every table's primary key and foreign keys. This alone tells you the shape of the entity graph (which tables are "hubs," which are junction tables, which are lookups) faster than reading column-by-column.
2. Row counts and table sizes: a quick SELECT COUNT(*) (or checking catalog statistics if counting is expensive) per table tells you which tables are the high-volume transactional core versus small, slowly-changing reference or lookup tables; the big tables are where performance and data-quality issues concentrate.
3. Timestamps: note every created_at/updated_at/*_date column, since these tell you whether a table is append-only, mutable, or soft-deletable, and are usually the first thing you'll filter by in any real query.
4. Immediate questions to note down: which columns are nullable and what NULL means there (unknown? not-applicable? not-yet-happened?); whether any column that looks like a natural key (email, order_number) is actually enforced unique; and whether any table name or column name hints at multiple business concepts crammed into one table (a red flag for hidden complexity).
5. Prioritization: start inspecting the tables with the most foreign-key connections to others (the "hub" tables) and the largest row counts first, since those are both the most likely to contain data-quality issues and the most central to understanding the domain.

Worked example

On an unfamiliar orders/customers/order_items/payments/refunds schema, the 60-second scan would surface: orders and order_items are almost certainly the largest tables (one row per transaction, and multiple rows per order respectively) and the central hub of the graph via foreign keys; customers and payments are the next tier; and refunds referencing orders (not order_items) would immediately raise the question "does a refund apply to a whole order or can it be partial per line item," which is exactly the kind of ambiguity worth resolving before writing any analysis that assumes one or the other.

Trade-offs and pitfalls

• The temptation under time pressure is to start writing queries immediately; without the 60-second key-and-cardinality scan first, it's easy to write a join that silently multiplies rows (joining orders to order_items when you actually wanted one row per order) and never notice, because the query still "runs" and returns plausible-looking numbers.
• Row counts matter as much as the relationships themselves: a schema that looks simple on paper can have a 200-million-row order_items table sitting behind an innocuous-looking foreign key, and knowing that upfront changes how carefully you'd write any exploratory query against it.
• The single highest-value question to resolve early is usually about the grain of the biggest table (what does one row actually represent), because every subsequent aggregation's correctness depends on getting that right first.

Direct answer

Statistical power is the probability that an experiment will detect a true effect of a given size, i.e. correctly reject a false null hypothesis; it's commonly targeted at 80-90%. The minimum detectable effect (MDE) is the smallest effect size the experiment is designed to reliably detect at a chosen power and significance level. MDE should be set from the smallest effect that would actually change a business decision, not from what's convenient to detect; that choice then mechanically determines the required sample size, since a smaller MDE needs a disproportionately larger sample. Beyond sample size, the other levers for power are lowering variance, extending duration, and using a more efficient design, each with its own cost.

Structured elaboration

Choosing an MDE from business context

• Minimum actionable lift: pick the smallest effect that would be worth the engineering and rollout cost if it were real. Below that, even a confirmed effect isn't worth acting on.
• Baseline variability: high-variance metrics need a larger MDE to keep the sample size and test duration feasible; this is a real constraint, not just a preference.
• Traffic and time budget: if the team can only run the test for a fixed number of weeks at a fixed traffic volume, that budget effectively sets the smallest MDE the test can resolve. Work backward from the budget if the business's ideal MDE isn't affordable.
• Compounding effects: if small lifts stack across many surfaces or are running continuously, a smaller MDE can be worth the extra cost; a one-off feature test usually isn't.

How MDE drives sample size

For a two-proportion test, required sample size per arm scales as:

$$n \propto \frac{p(1-p)}{(\Delta)^2}$$

where $\Delta$ is the MDE (absolute difference) being targeted. Because $\Delta$ is squared in the denominator, halving the MDE roughly quadruples the required sample, and therefore roughly quadruples the calendar time needed at fixed daily traffic.

Levers to raise power without changing alpha

Worked example

Baseline conversion $p_0 = 0.10$, $\alpha=0.05$ two-sided, power $=0.80$. Required sample size per arm at two different MDEs, using the standard two-proportion formula:

$$n = \frac{\left[z_{1-\alpha/2}\sqrt{2\bar p(1-\bar p)} + z_{1-\beta}\sqrt{p_0(1-p_0)+p_1(1-p_1)}\right]^2}{(p_1-p_0)^2}$$

(computed directly from the formula above with scipy.stats.norm for the critical values)

Halving the MDE from 0.02 to 0.01 multiplies the required sample by about 3.8x, consistent with the inverse-square relationship between $\Delta$ and $n$. If daily eligible traffic is 1,000 users per arm, the 0.01 MDE needs about 15 days; the 0.02 MDE needs about 4.

Trade-offs & pitfalls

• Picking the smallest MDE that "sounds rigorous" without checking the implied sample size and duration is a common planning mistake; always translate the MDE choice into a concrete number of days before committing to it.
• An MDE chosen after looking at early results (rather than fixed up front from the business case) turns the power calculation into after-the-fact justification, not a real design constraint.
• Reducing variance or adding covariates raises power "for free," but only if the adjustment is decided and validated before the experiment starts; adjusting for whatever covariate happens to make the result significant is a form of p-hacking.
• Accepting low power to launch faster is sometimes the right call for cheap, reversible changes, but it should be a stated decision (documented as "we are only powered to detect large effects"), not a silent consequence of an unexamined MDE.