Requirements & scope:- Measure mentorship activity and impact on promotions, retention, PR quality, and satisfaction while protecting PII and avoiding misleading signals from small samples.Events to collect (immutable event stream, timestamped):- mentor_meeting: - event_id, ts, mentor_id_hash, mentee_id_hash, meeting_type (one-on-one/pair/program), duration_minutes, channel (zoom/in-person), topics_tags- pr_mentor_comment: - event_id, ts, pr_id, repo, file_path, line_no, commenter_id_hash, author_id_hash, comment_type (suggestion/approval/style), sentiment_score- paired_session: - event_id, ts, session_id, mentor_id_hash, mentee_ids_hash[], duration, tooling (IDE/live), artifacts_link- promotion, exit, hire: - event_id, ts, person_id_hash, event_type (promotion/exit/hire), new_level, reason_tags- satisfaction_survey: - survey_id, ts, respondent_id_hash, score (1-10), free_text (encrypted), cohort_tagsData model (star schema):- dim_person (person_id_hash, hire_date, team_id, role, hashed_email_salt_version)- dim_time- dim_team- fact_mentorship_event (event fields above keyed to dim_person, dim_time)- fact_pr_metrics (pr_id, ts, author_hash, reviewer_hashes, lines_changed, complexity_score, time_to_merge, post_comment_count)- fact_promotion_retention (person_hash, ts, outcome)ETL pipeline:1. Ingest: events -> Kafka topics. Producers send minimal PII (only hashed identifiers) with deterministic salt version. Store raw avro in data lake.2. Stream enrichment: Flink/Beam jobs enrich with team membership, repo ownership snapshots from HR/SCM APIs (join on hashed ids), compute derived fields (meeting_hours_week, mentor_comment_count_30d).3. Batch aggregate (daily via Airflow): normalize schemas, dedupe, populate OLAP (Snowflake/BigQuery), compute rolling windows (30/90/365d).4. Modeling step: compute metrics: - Mentorship exposure = weighted sum(mentoring_hours + paired_sessions + mentor_comments * weight) - PR quality delta = (author's PR metrics before vs after mentorship exposure controlling for PR complexity) - Promotion lift = survival analysis / Cox proportional hazards or logistic regression using mentorship exposure as covariate - Retention impact = Kaplan-Meier curves stratified by exposure - Satisfaction correlation = mixed-effects model (person random effects) to control confounders5. Store final aggregates in reporting tables and materialized views.Example SQL (aggregate mentorship exposure per person 90d):sql
SELECT person_hash,
SUM(duration_minutes)/60 AS mentorship_hours_90d,
COUNT(DISTINCT session_id) AS paired_sessions_90d,
SUM(CASE WHEN comment_type='suggestion' THEN 1 ELSE 0 END) AS mentor_comments_90d
FROM fact_mentorship_event
WHERE ts >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY person_hash;
Dashboards & KPIs:- Promotions dashboard: - Cohort waterfall: mentorship-exposed vs not by hire-cohort; promotion rate, time-to-promotion (median), funnel conversion - Model-adjusted lift (odds ratio with CIs)- Retention dashboard: - Survival curves by exposure buckets; monthly churn rate; cohort comparisons- PR quality dashboard: - PR cycle time, revert rate, post-merge defects, reviewer satisfaction; delta before/after exposure with control for complexity- Engineer satisfaction: - Avg NPS/score by exposure quartile; free-text theme wordcloud (encrypted storage + access)- Interactive filters: team, role, tenure, time window; confidence intervals and sample sizes shown.Handling PII & privacy:- Never store raw emails or names in analytics DBs. Use salted hashes with rotation/versioning; store mapping in a secure secrets vault accessed only by HR-approved services when needed.- Encrypt free-text responses at rest; access via audited decryption service.- Role-based access control and column-level masking in warehouse.- Differential privacy / noise addition for public reports or exports: apply Laplace noise calibrated to epsilon for counts; or only show aggregates above a minimum k (k-anonymity).- Logging and periodic privacy audits; data retention policy (e.g., delete raw event payloads after 1 year unless required).Small-sample noise & statistical validity:- Minimum n thresholds: only show rates when N >= 20 (configurable). Display sample size and 95% CI.- Use Bayesian shrinkage (empirical Bayes) to pull noisy small-group estimates toward global mean: - posterior = (alpha + successes) / (alpha+beta + trials)- Use hierarchical/mixed models to borrow strength across teams and control confounders.- Report effect sizes with CIs and p-values, but emphasize practical significance.- Present uncertainty visually (error bars, shaded survival bands) and add warnings for unstable estimates.Operational concerns & trade-offs:- Real-time vs batch: stream for near-real-time alerts (e.g., mentoring inactivity), batch for heavy modeling.- Hashing deterministic enables joins but increases reidentification risk — mitigate with salts & strict access controls.- Causality: mentorship correlation != causation. Use quasi-experimental methods where possible (propensity score matching, difference-in-differences) and document assumptions.This design provides reproducible, privacy-preserving metrics, conveys uncertainty, and supports both operational monitoring and deeper causal analysis.