Problem Solving in Ambiguous Situations Questions

Evaluates structured approaches to diagnosing and resolving complex or ill defined problems when data is limited or constraints conflict. Key skills include decomposing complexity, root cause analysis, hypothesis formation and testing, rapid prototyping and experimentation, iterative delivery, prioritizing under constraints, managing stakeholder dynamics, and documenting lessons learned. Interviewers look for examples that show bias to action when appropriate, risk aware iteration, escalation discipline, measurement of outcomes, and the ability to coordinate cross functional work to close gaps in ambiguous contexts. Senior assessments emphasize strategic trade offs, scenario planning, and the ability to orchestrate multi team solutions.

HardTechnical

21 practiced

As lead data analyst, outline a strategic plan to migrate hundreds of bespoke Excel reports into a governed BI platform. Include phases, resource roles, training, governance (access, definitions), risk mitigation for business continuity, and measures to ensure adoption.

Sample Answer

Situation & objective: Migrate ~hundreds of bespoke Excel reports into a governed BI platform (Power BI/Tableau) to improve trust, scalability, automation, and reduce manual effort while preserving business continuity.

Phased plan:1. Discovery (4–6 weeks)- Inventory all Excel reports, owners, data sources, refresh cadence, usage, and business criticality.- Score by impact/complexity to create a migration backlog (High/Medium/Low).2. Design & Foundation (6–8 weeks)- Build target data model (single source-of-truth layers: raw, curated, semantic).- Select tech stack, ETL/ELT patterns, storage, and CI/CD approach.- Develop governance policies (definitions, access levels, naming, versioning).3. Pilot (6 weeks)- Migrate 5–10 high-impact reports from different departments end-to-end.- Validate data lineage, performance, and UX with power users.4. Rollout (incremental sprints)- Migrate by priority waves (e.g., finance, sales, ops). Each wave: requirements, build, QA, UAT, cutover, deprecation.5. Operate & Optimize (ongoing)- Monitoring, performance tuning, incremental enhancements.

Resource roles:- Lead Data Analyst (me): requirements, QA, stakeholder liaison.- Data Engineer: ETL/ELT, data model, performance.- BI Developer: dashboards, UX, accessibility.- Data Steward/Governance Owner: business definitions, SLA.- Project Manager: schedule, risk.- Security/Infra: access controls, deployment.- Change Champions (per department): UAT, adoption.

Training & enablement:- Role-based workshops: end-users (consumption), report authors (self-service), stewards (governance).- “How-to” playbooks, video walkthroughs, and office hours.- Certification badge program for internal BI authors.

Governance:- Centralized glossary and semantic layer; required canonical definitions before migration.- RBAC: viewer/author/admin roles enforced via platform and AD groups.- Data lineage and automated testing for ETL jobs.- Release process: dev → staging → prod with sign-off.- Audit logs and usage metrics.

Risk mitigation & continuity:- Parallel run: keep Excel reports read-only during migration; automate exports where needed.- Backout plan per wave: revert to Excel or previous dataset within SLA window.- Data validation: automated reconciliation tests and sampling before cutover.- SLA & escalation playbook for broken reports.- Phased sunset with communication cadence.

Measures to ensure adoption:- Involve users early (co-design) and use pilot success stories.- Track KPIs: report usage, report build time reduction, manual hours saved, data quality incidents.- Incentivize usage: retire legacy reports progressively; require business sign-off to keep Excel versions.- Ongoing support: dedicated migration inbox, fast-response “tiger team” for first 90 days post-cutover.

Expected outcomes: Reduced report maintenance, single source of truth, faster insights, measurable time savings, and higher trust in analytics.

EasyTechnical

22 practiced

You have 48 hours to build a one-page Tableau dashboard for executives, but the data definitions are incomplete. Describe what you would include on the page, which visualizations you would prioritize, and exactly which assumptions and caveats you would display to keep the dashboard credible.

Sample Answer

Situation: I have 48 hours and incomplete data definitions to deliver a one-page executive Tableau dashboard that’s decision-ready yet transparent.

What I’d include (layout & priority)- Top row — 3 KPI cards (current period value, % change vs prior period, target): Revenue, Profit (or Margin %), and Active Customers/MAU. These are what execs scan first.- Left column — Trend chart (time series) for the primary KPI with trend line and YoY/period-over-period selector.- Center — Bar chart: top 5 contributors (products/regions/channels) to the KPI with contribution % and sparkline.- Right — High-level segmentation: small table showing conversion funnel or cohort retention (if relevant).- Bottom — Data quality & assumptions panel + small recent anomalies table (top exceptions by magnitude).- Interactivity: single global date filter, one dimension filter (region or product), hover tooltips with exact values and links to source queries.

Why these visuals- KPI cards + trend provide status and trajectory.- Top contributors surface where impact is coming from.- Cohort/funnel quickly shows health of customer behavior.- Anomalies and data-quality callouts maintain credibility.

Exact assumptions & caveats to display (visible on the page)- Definitions: “Revenue = recognized sales gross of refunds; excludes tax” (or placeholder: “Revenue definition pending — currently using invoice_amount”)- Time window: “Reporting period uses transaction_date; timezone UTC.”- Data freshness: “Data as of 2025-11-20 04:00 UTC.”- Missing/imputed data: “Nulls in customer_id treated as ‘Unknown’ (X% of rows).”- Aggregation/dedup rules: “De-duplicated by transaction_id; multiple payments per order aggregated.”- Sampling: “If dataset >10M rows, dashboard uses daily aggregates — full detail available on request.”- Confidence: “Primary KPI uses source A; where source A null, fallback to source B (affects ~Y% rows).”- Action note: “Definitions flagged for validation — contact data owner: name@example.com.”

Quick validation steps (for handoff)- Provide a short README with SQL snippets used, dataset counts, and rows flagged as uncertain.- Tag any tiles built from fallback logic with a red asterisk linking to the caveat.

This delivers executive-focused insight while being explicit about limitations so stakeholders can trust and act on the results or prioritize definition fixes.

HardTechnical

22 practiced

Case: Post major launch, NPS is mixed, support tickets rose, but sales signals look healthy. You have limited telemetry. Produce a strategic analysis plan: what data to collect, short experiments to run, scenario planning for product changes, and criteria for go/no-go decisions.

Sample Answer

Situation: After a major launch we see mixed NPS, rising support tickets, but healthy sales conversion. With limited telemetry, as a Data Analyst I’d deliver a focused strategic analysis plan to quickly diagnose issues, run short experiments, and support informed product decisions.

Data to collect (prioritize quick wins):- Product telemetry: page/flow events, feature flags, error logs, load times (instrument via lightweight event collection).- Support data: ticket counts, categories, timestamps, severity, transcripts.- Customer feedback: NPS verbatim, CSAT, reviews, app-store comments.- Sales signals: conversion funnels, A/B cohort labels, churn/renewal rates.- User segmentation: device, region, plan, first-time vs returning.How: SQL pulls from product DB, export ticket CSVs, connect to BI (Tableau/Looker) for dashboards.

Short experiments (2–4 week cycles):- Funnel diagnostics: add event markers to suspicious steps; run cohort retention comparison (new vs pre-launch users).- Triage A/B rollback: for a risky feature, run canary (10% vs 90%) and measure errors, support rate, conversion lift.- In-product survey: quick micro-survey on failure points for users who submit tickets.- Support workflow test: auto-suggest KB articles vs manual agent for a subset to measure ticket volume and resolution time.

Scenario planning (three scenarios):1) UX/regression bug: NPS drop localized to new flows; tickets show repeatable errors → action: rollback feature, patch, re-run QA.2) Expectations/messaging mismatch: high conversions but negative sentiment about complexity → action: improve onboarding, tooltips, adjust copy.3) Mixed/acceptance by segment: some segments love it, others struggle → action: targeted enablement, feature flag per cohort.

Go / No-Go criteria (quantitative + qualitative):- Safety (no-go triggers): error rate > X% (e.g., 2% of key API calls), support ticket volume increase > 30% vs baseline, critical bugs affecting payments or data loss.- Performance/benefit (go triggers): conversion uplift > Y% (stat sig, p<0.05) AND neutral/positive NPS trend in affected cohorts, support tickets returning to baseline within 2 weeks of fix.- Conditional continue: if conversion positive but NPS negative, continue with mitigations (onboarding, docs) and monitor 2-week window; prepare rollback plan.

Deliverables and timeline:- 48–72 hrs: dashboards with ticket trends, top error counts, NPS verbatim themes.- 1–2 weeks: run canary A/Bs and quick surveys, present hypothesis-backed recommendation.- Ongoing: automated monitoring alerts for error/support thresholds, weekly stakeholder brief.

This plan balances rapid diagnostics with controlled experiments, uses measurable thresholds for decisions, and prioritizes customer safety while preserving business momentum.

EasyTechnical

41 practiced

You find two business reports that should be showing the same monthly revenue number, but one shows +5% month-over-month while finance reports -2% for the same set of customers. Describe a stepwise reconciliation plan to locate the discrepancy, including which SQL checks, data sources, and owner conversations you would run first.

Sample Answer

Start with a hypothesis-driven, reproducible reconciliation plan: verify sources, isolate scope, and iterate until root cause is found.

1) Clarify scope & stakeholders- Confirm "same set of customers", date ranges, timezone, revenue definition (net vs. gross, recognized vs. billed). Talk to report owners (BI owner and Finance owner) and capture their SQL/data sources.

2) Source comparison: identify upstream tables- Ask each owner: which table/view and ETL job produce the metric (e.g., analytics.events, finance.invoices, reporting.monthly_revenue_view).

3) Basic row-level check (SQL)- Compare counts and totals at customer-month grain.

sql

SELECT customer_id, month,
  SUM(amount) as total_amount,
  COUNT(*) as rows
FROM source_table
WHERE month BETWEEN '2025-01' AND '2025-03'
GROUP BY customer_id, month
ORDER BY customer_id;

Run for both systems and diff.

4) Key mismatch checks- Customer list differences: left-join customer ids to find missing/excluded customers.

sql

SELECT a.customer_id
FROM analytics_customers a
LEFT JOIN finance_customers f USING(customer_id)
WHERE f.customer_id IS NULL;

- Date alignment: check transaction timestamps vs. revenue recognition date (timezone/truncation).

5) Aggregation & filter checks- Confirm filters (product lines, test accounts, refunds, currency conversions). Re-run aggregation adding/removing each filter to see impact.

6) ETL and timing- Check job timestamps: is one report using updated data vs. a lagged snapshot? Review ETL logs and talk to data engineering.

7) Adjustment & policy differences- Verify if Finance includes accruals, write-offs, or manual journal adjustments. Ask Finance for adjustment journal entries and reconcile them.

8) Sampling & row-level audit- For a few customers with largest differences, pull detailed transaction lists and source documents (invoices, payments).

9) Automate and document- Create a reproducible notebook or SQL view showing differences per customer-month and propose a fix (ETL change, filter harmonization, or documentation).

Result: present findings to both owners with evidence (diffs, SQL, sample transactions), recommend one canonical definition and process to prevent recurrence (shared SQL view or agreed ETL contract).

MediumTechnical

28 practiced

You find multiple conflicting definitions for 'active user' across dashboards. Draft a practical plan to standardize definitions, gain cross-functional buy-in, implement the canonical definition, and migrate dashboards without breaking downstream processes.

Sample Answer

Situation: Multiple dashboards define "active user" differently (7-day MAU, any event in 30 days, last login within 14 days), causing inconsistent KPIs and stakeholder confusion.

Plan (overview):1. Discovery & inventory (1 week)- Catalog all dashboards, reports, data sources, and owners.- Capture each definition and where it’s implemented (SQL, semantic layer, LookML/Tableau calc).

2. Align stakeholders & agree canonical definition (1–2 weeks)- Convene cross-functional working group: Product, Growth, Finance, Analytics, Engineering.- Present impacts of inconsistency with examples (reconciled numbers).- Propose a canonical definition with rationale (e.g., "Active user = user_id with >=1 session event in trailing 30 days"), include variants for specific use cases.- Vote and record decision; get executive sponsorship.

3. Formalize & document (ongoing)- Create a single-source-of-truth doc in Data Catalog with: definition, SQL/metric implementation, owners, version, use-cases, measurement window.- Add semantic-layer artifact (e.g., metric in dbt/semantic layer/LookML/Power BI dataset).

Example canonical SQL (implementation):

sql

-- canonical: trailing 30-day active users
select count(distinct user_id) as active_users_30d
from events
where event_date >= current_date - interval '30' day
  and event_type = 'session_start';

4. Implementation & migration (2–6 weeks)- Implement canonical metric in semantic layer and expose as fixed metric.- Run dashboards in “dual-run” mode: keep legacy metric but add canonical metric side-by-side.- Update dashboards iteratively by priority (high-impact first). Communicate expected deltas.

5. Testing & monitoring- Build automated tests: compare legacy vs canonical on sample windows; alert large diffs.- Add dataset lineage and dependency checks to avoid breaking downstream ETL.- Schedule rollback plan per dashboard (tagging and owner contact).

6. Rollout & governance- Train users via short docs + office hours.- Require any future metric change to go through metric-change RFC and owner approval.- Quarterly review of metric usage.

Result: Single, documented canonical metric; fewer misaligned decisions; reproducible dashboards; faster onboarding for new analysts.

Unlock Full Question Bank

Get access to hundreds of Problem Solving in Ambiguous Situations interview questions and detailed answers.

Join thousands of developers preparing for their dream job.