Situation: You observe a Sample Ratio Mismatch (SRM) in a live A/B test and suspect instrumentation error. Below is a forensic checklist (ordered), concrete SQL checks and log checks, and remediation options.Forensic checklist & concrete checks1) Verify raw assignment counts (by day, platform, region)sql
-- assignments table: user_id, experiment_id, variant, assigned_at, platform, country
SELECT variant, platform, COUNT(DISTINCT user_id) AS users, DATE(assigned_at) AS day
FROM assignments
WHERE experiment_id = 'exp_123'
GROUP BY 1,2,3
ORDER BY day, platform, variant;
2) Compare assignment vs exposure/event payloadssql
-- exposures/events table: user_id, experiment_id, event_type, event_time
SELECT a.variant, COUNT(DISTINCT e.user_id) AS exposures
FROM assignments a
LEFT JOIN exposures e
ON a.user_id = e.user_id AND a.experiment_id = e.experiment_id
WHERE a.experiment_id='exp_123'
GROUP BY a.variant;
3) Hashing / bucketing salt changes (detect sudden reassignments)sql
-- check last assignment per user to see flips
SELECT user_id, MIN(assigned_at) AS first_assigned, MAX(assigned_at) AS last_assigned,
COUNT(DISTINCT variant) AS distinct_variants
FROM assignments
WHERE experiment_id='exp_123'
GROUP BY user_id
HAVING COUNT(DISTINCT variant) > 1
LIMIT 100;
4) Duplicate events / double-countingsql
-- same event emitted multiple times
SELECT user_id, event_type, DATE(event_time) AS day, COUNT(*) AS cnt
FROM exposures
WHERE experiment_id='exp_123'
GROUP BY 1,2,3
HAVING cnt > 1
ORDER BY cnt DESC
LIMIT 50;
5) Timezone and timestamp skewsql
-- check distribution of assigned_at hours
SELECT DATE(assigned_at) AS day, EXTRACT(HOUR FROM assigned_at AT TIME ZONE 'UTC') AS hour_utc, COUNT(*)
FROM assignments
WHERE experiment_id='exp_123'
GROUP BY 1,2
ORDER BY day, hour_utc;
6) Platform rollout / SDK versionssql
-- platform and SDK version distribution
SELECT platform, sdk_version, COUNT(DISTINCT user_id) AS users
FROM assignments
WHERE experiment_id='exp_123'
GROUP BY platform, sdk_version;
7) Assignment-service logs (examples to search)- Search for salt/seed changes: "hash_salt updated" or "experiment exp_123 seed"- Search for deployment timestamps around SRM start: "deploy", "release", "assignment-service started"- Search for error patterns: "failed to compute bucket", "null user_id", "exception"- Trace a sample user id to see assignment decisions: - grep logs for user_id=abc123 → confirm consistent variant and salt used8) Cross-source reconciliation- Compare streaming ingestion counts (Kafka topic) vs DB loads- Check lag/consumer errors: look for offsets, consumer group rebalances.Remediation strategies- If instrumentation bug identified and reversible: - Reprocess events from raw source (Kafka/S3) with corrected mapping; re-run analysis on reprocessed dataset. Communicate expected timeline and reproducibility.- If portion of data corrupted and reprocessing impossible: - Exclude affected time windows/platforms/SDK versions from analysis (document criteria). Recompute metrics and rerun SRM test.- If assignment salt changed mid-test and caused reassignment: - Prefer re-run experiment (new randomization) unless reprocessing can deterministically map old→new buckets.- Short-term mitigation: - Pause experiment or stop decisioning on affected cohorts to prevent bad exposures. - Notify stakeholders with clear impact assessment: affected segments, time window, expected bias direction.- Postmortem: root cause, checklist updates (monitoring + alerting for SRM, logging of seed changes, automated assignment vs exposures parity checks).Key communication: provide reproducible SQL queries, list of affected user_ids and time windows, remediation plan and ETA, and follow-up verification queries to confirm fix.