Data Analysis and Insight Generation Questions

Ability to convert raw data into clear, evidence based business insights and prioritized recommendations. Candidates should demonstrate end to end analytical thinking including data cleaning and validation, exploratory analysis, summary statistics, distributions, aggregations, pivot tables, time series and trend analysis, segmentation and cohort analysis, anomaly detection, and interpretation of relationships between metrics. This topic covers hypothesis generation and validation, basic statistical testing, controlled experiments and split testing, sensitivity and robustness checks, and sense checking results against domain knowledge. It emphasizes connecting metrics to business outcomes, defining success criteria and measurement plans, synthesizing quantitative and qualitative evidence, and prioritizing recommendations based on impact feasibility risk and dependencies. Practical communication skills are assessed including charting dashboards crafting concise narratives and tailoring findings to non technical and technical stakeholders, along with documenting next steps experiments and how outcomes will be measured.

MediumTechnical

55 practiced

You plan an A/B test and expect a baseline conversion rate of 2% and want to detect a 5% relative lift (i.e., delta = 0.1 percentage points). Using alpha = 0.05 and power = 0.8, describe how to compute required sample size per variant. Explain assumptions and how the sample size changes with higher desired power or smaller detectable effects.

HardSystem Design

66 practiced

Design an automated weekly reporting system that delivers KPI dashboards and sends a concise PDF summary to leadership. Specify pipeline components (data sources, ETL, aggregation, dashboard rendering), data tests to include, versioning, rollback procedure for bad runs, and access control considerations.

Sample Answer

Requirements:- Weekly delivery of KPI dashboards (interactive) + concise 1–2 page PDF summary emailed to leadership every Monday 07:00.- Data freshness: up-to-last-Sunday 23:59. SLAs: report ready within 2 hours.- Lineage, auditability, reproducibility, and safe rollback.

High-level architecture:Data sources (OLTP DBs, marketing APIs, CRM CSVs) → Ingestion layer → ETL/ELT → Aggregation & metrics store → BI dashboards (Tableau/Power BI) → PDF renderer & emailer → Monitoring, lineage & metadata store.

Pipeline components:1. Ingestion: scheduled connectors (Airbyte/Fivetran) for DBs/APIs, S3 landing for flat files. Raw data stored in data lake (parquet) with partitioning by date.2. ETL/ELT: dbt for transformations (models, tests, docs). Transformations run in warehouse (Snowflake/BigQuery/Redshift). Use incremental models for scale.3. Aggregation: materialized summary tables for KPIs (daily/weekly aggregates) with snapshots and change timestamps.4. Dashboarding: Tableau/Power BI connecting to the metrics layer. Use parameterized views for leader-specific filters.5. PDF Summary: automated script (Python using headless browser or BI export API) that renders a concise summary, embeds key charts and 3–5 bullets (insights), saves PDF to storage and emails via SES/SendGrid.6. Orchestration & scheduling: Airflow preferring task dependencies, retries, SLA alerts.7. Observability & lineage: Great Expectations for data tests, OpenLineage/Marquez for lineage, Prometheus+Grafana for infra metrics, Sentry for failures.

Data tests to include (implemented in dbt + Great Expectations):- Schema tests: column presence, types, nullability.- Row-count and delta tests: compare expected ingest volumes vs. historical baseline with thresholds.- Range/validity tests: KPI value ranges (e.g., conversion rate 0–1), no negative revenue.- Uniqueness & foreign-key integrity.- Freshness tests: last_updated within expected window.- Distributional tests: detect drift using KS-test or percent-change thresholds.- End-to-end reconciliation: compare source sums to transformed sums with tolerance.

Versioning & reproducibility:- All transformation code in Git with tags for weekly releases. dbt models versioned; data snapshots stored with run_id and timestamp.- Use immutable build artifacts: materialized tables named with run_id and a canonical “current” pointer view.- Catalog metadata (schema, run_id, checksum) stored in metadata DB.

Rollback procedure for bad runs:- Detection: automated test failures or business validation alerts trigger a “bad run” state.- If transient issue: re-run failing tasks after fix. If run corrupted materialized tables, promote previous good snapshot: 1. Identify last successful run_id from metadata. 2. Restore materialized tables from snapshot (or swap view pointer to run_id_{good}). 3. Re-generate PDFs from restored metrics. 4. Notify stakeholders of rollback and root cause.- Maintain backups for 30/90 days; test rollback quarterly.

Access control & security:- Principle of least privilege: RBAC in warehouse, BI tool, and storage. Analysts have read/write in dev datasets; read-only to production metrics unless approved.- Service accounts for automation with scoped keys; rotate secrets via Vault.- Encrypt data at rest and in transit; PII masked/pseudonymized at ingestion.- Audit logs enabled for queries, exports, and PDF sends.- Approval workflow for granting leadership access to raw or sensitive data.

Operational practices:- Runbook and playbooks for failures, with Slack alerts and PagerDuty for SEV incidents.- Weekly smoke tests that validate top-10 KPIs and PDF generation in staging before promoting.- Quarterly data lineage review and tabletop rollback drills.

Why this design:- dbt + warehouse minimizes duplicated ETL code and supports tests/versioning.- Materialized metrics ensure dashboard performance and reproducible PDFs.- Lineage + automated tests catch issues early and support confident rollbacks—critical for leadership-facing reports.

MediumTechnical

45 practiced

A daily scheduled ETL job failed overnight and a production revenue dashboard shows zeros. Walk through the troubleshooting steps you would take in the first 60 minutes: what logs, metrics, and checks you run, who you notify, and how you decide whether to re-run, rollback or escalate.

Sample Answer

First 0–5 minutes — quick impact assessment- Confirm scope: which dashboards/tables show zeros and which metrics impacted.- Check monitoring/alerting (PagerDuty, Slack channel, Grafana) and ETL scheduler status (Airflow/Control-M).- Post an initial incident message to stakeholders (Data Engineering on-call, Product Owner, BI manager) with: observed symptom, time, and that you’re investigating.

5–20 minutes — triage logs & metrics- Open ETL job run log and scheduler UI: look for failure step, error messages, timestamps, and retries.- Check source system availability (API/DB connection) and upstream job logs.- Query destination tables with SQL to compare yesterday’s vs. current row counts, last_updated timestamps, and sample data: - SELECT COUNT(*), MAX(updated_at) FROM fact_revenue;- Review monitoring metrics: data latency, pipeline throughput, error rates, and any schema-change alerts.

20–40 minutes — validate data quality and root cause- Run quick data quality checks: null rates for key columns, aggregates (SUM(revenue) by date), and checksum/hash comparisons vs. previous run.- If logs indicate transient failure (e.g., network timeout) and source data is intact, consider re-running the failed task for that date range.- If schema changes or corrupted input detected, DO NOT re-run; document root cause and mitigation steps.

40–60 minutes — decide & act- If safe to re-run: coordinate with Data Engineering, schedule re-run, run validation queries post-run, and verify dashboard refresh.- If rollback needed (bad data loaded): ask engineering to restore from latest clean snapshot/backfill and run validation.- If root cause unclear or systemic (multiple pipelines failing, production outage), escalate immediately to on-call engineering, SRE, and product stakeholders; provide timeline and business impact (estimated revenue metrics affected).- Update stakeholders every 15 minutes with status, ETA and next steps; after resolution, create a postmortem with corrective actions (more monitoring, retry logic, contractual SLA).

HardTechnical

58 practiced

You are asked to deliver a churn-prediction model for marketing to act upon. Describe feature generation (recency, frequency, monetary, engagement signals), model selection, evaluation criteria prioritized by business value (precision at top decile, calibration), and production considerations: latency, explainability, and retraining cadence.

Sample Answer

Situation overview (brief): We must deliver a churn-prediction model that marketing can act on — so features must be business-interpretable, evaluation must prioritize high-value intervention targets, and production must support low-latency scoring, clear explanations, and regular refresh.

Feature generation- Recency: days since last purchase/session/email open. Create binned and continuous versions and decay-weighted recency (exp(-λ * days)).- Frequency: purchases/sessions in last 7/30/90 days, repeat-purchase rate, purchase cadence variance.- Monetary: avg order value, total spend last 30/90/365 days, share-of-wallet proxies.- Engagement signals: last N email opens/clicks, time-on-site, feature usage counts, push-notification responses, complaint/return flags.- Behavioral aggregates: rolling windows, time-decay features, trends (slope of purchases), churn-risk segments (subscription status).- Practical tip: compute in SQL as materialized feature tables; include null-handling flags.

Model selection- Start with interpretable baseline: regularized logistic regression (fast, coefficients map to features).- For improved performance, try tree ensembles (LightGBM/XGBoost) with SHAP for explainability.- Handle class imbalance with class weights, focal loss, or up/down-sampling; prefer validation by business metrics rather than global AUC.

Evaluation prioritized by business value- Primary metric: precision@top_decile (or top-N) — marketing acts on the top-scoring customers, so precision of that slice drives ROI.- Secondary: calibrated probability quality (Brier score, reliability plots); probabilities must reflect true risk for cost-sensitive targeting and uplift modeling.- Other: uplift or incremental conversion (if possible), lift at k, business KPIs (retention lift, cost per retained customer).- Evaluate with time-based backtesting (train on t, test on t+window) to avoid leakage.

Calibration & explainability- Calibrate final model with isotonic or Platt scaling on a holdout.- Use SHAP values for per-customer explanations and global feature importance; provide simple rule-based explanations (top 3 drivers).

Production considerations- Latency: for real-time campaigns keep model lightweight (logistic or distilled tree); for batch marketing scoring nightly is usually sufficient.- Serving: expose model as REST endpoint for online scoring or schedule daily batch jobs writing scores to a table consumed by marketing.- Explainability: store top-K SHAP features per scored user; produce human-readable reasons in dashboards.- Monitoring & retraining: monitor prediction distribution, feature drift, calibration drift, and top-decile precision weekly. Retrain cadence: monthly or triggered by drift/seasonality; maintain rolling window training and a validation holdout. Run A/B tests for campaigns to measure actual uplift before full rollout.

Outcome focus: prioritize actions that maximize net retained revenue (precision of top targets and proper calibration), deliver explainable reasons for outreach, and maintain pipelines for reliable, timely scores.

EasyTechnical

56 practiced

You need to choose visualizations for three distinct analyses: (a) monthly revenue trend over two years, (b) revenue breakdown by product category, and (c) conversion funnel across five ordered stages. For each, recommend a chart type, justify why it fits the data and audience, and describe one way this visualization could mislead stakeholders. Also describe how to show confidence intervals for the trend.

Unlock Full Question Bank

Get access to hundreds of Data Analysis and Insight Generation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.