insights/.agents/skills/monitoring-ingestion-pipeline/references/investigation-playbooks.md

Investigation playbooks

Workflows for common ingestion pipeline investigations. Each playbook describes what to check and in what order — construct PromQL live using the discovery workflow from the main skill doc.

1. "Is ingestion healthy?" — quick health check

Start by pulling up the main dashboard: get_dashboard_by_uid with uid "ingestion-health".

Then verify these signals in order:

1. E2E event lag — two complementary metrics:
   • ingestion_lag_ms{groupId=~"ingestion-analytics-main"} — per-partition lag measured by the consumer itself; the primary signal driving KEDA autoscaling.
   • insights_celery_observed_ingestion_lag_seconds{scenario="ingestion_api"} — Django-measured end-to-end lag from event capture to Datastore visibility. Sustained values > 60s on either warrant investigation.
2. Consumer group lag — kminion_kafka_consumer_group_topic_lag_seconds scoped to key groups: ingestion-analytics-main, ingestion-analytics-overflow, session-recordings-blob-v2. Growing lag = consumers falling behind.
3. Pipeline results — ingestion_pipeline_results by result. Watch for rising dlq or dropped rates relative to ingested.
4. Pod restarts — kube_pod_container_status_restarts_total for namespace=~"ingestion-.*", pod=~"ingestion-.*". Any recent restarts?
5. Datastore consumer lag — kminion_kafka_consumer_group_topic_lag{group_id=~"datastore_events_json|group1|group1_recent", topic_name="datastore_events_json"}. This is the final leg: even if ingestion workers are healthy, CH lag means users see stale data. (Consumer group name differs by env: datastore_events_json in prod-us, group1/group1_recent in prod-eu.)
6. Datastore insert health — DatastoreProfileEvents_FailedInsertQuery{type="events"} rate. Non-zero = CH having trouble writing events.

2. "Events are being dropped" — pipeline result analysis

The pipeline result funnel tells you what happened to each event:

1. Check result distribution — ingestion_pipeline_results by result. Key values: ingested, filtered, dropped, dlq. Discover all values with list_prometheus_label_values on result.
2. Check drop causes — ingestion_event_dropped_total by cause. Discover live values with list_prometheus_label_values on cause.
3. Check DLQ rate — events sent to dead letter queue. Look at ingestion_outputs_errors_total by topic for DLQ topic production failures.
4. Check overflow redirect — overflow_redirect_* metrics. overflow_redirect_decision by decision shows whether events are being rerouted to overflow topics. This isn't a drop but changes the processing path.
5. Per-pipeline breakdown — scope all above by ingestion_pipeline and ingestion_lane to isolate which deployment is dropping events.

3. "Ingestion latency is high" — layered diagnosis

Diagnose layer by layer, from consumer to output:

1. Consumer batch duration — consumed_batch_duration_ms by groupId. This is the total time to consume + process a batch. High values mean slow processing. Compare across consumer groups to identify which pipeline is slow.

2. Pipeline step latency — ingestion_pipeline_step_duration_seconds by step_name (histogram; avg = rate(_sum)/rate(_count), tail via histogram_quantile on _bucket). The older events_pipeline_step_ms is legacy. Identify which step is the bottleneck. Common culprits:

   • processPersonsStep / processGroupsStep — database writes
   • hogTransformEventStep — customer-defined transformations (CPU or fetch)
   • emitEventStep — Kafka production Compare p50 vs p99: a heavy tail (p50 fine, p99 in seconds) means a minority of events is expensive — a pattern that never shows up in volume-ranked views.

3. Person store latency — person_* metrics for flush duration and DB write time. personinsights_latency_seconds for personinsights gRPC call latency. Check personinsights_requests_total{status="error"} for gRPC failures.

4. Kafka production latency — ingestion_outputs_latency_seconds by topic. Also kafka_broker_rtt_us by broker_name for broker-level RTT.

5. Postgres latency — no direct query latency metric; infer from:

   • Person flush time spikes correlating with person_* DB operation metrics
   • PgBouncer: pgbouncer_* metrics for connection pool saturation
   • CloudWatch RDS: CPU, read/write latency, connection count

4. "Consumer group lag is growing" — KMinion lag analysis

1. Identify which group — kminion_kafka_consumer_group_topic_lag_seconds by group_id. Check all ingestion consumer groups from the deployment roles table.
2. Partition-level lag — kminion_kafka_consumer_group_topic_partition_lag by partition_id. Uneven lag across partitions suggests a slow consumer pod or hot partition.
3. Batch utilization — consumed_batch_duration_ms vs configured max batch time. If batches are taking the full allowed time, the consumer is saturated.
4. Consumer assignment — kafka_consumer_assignment by groupId. Partitions should be evenly distributed. Zero = consumer not assigned.
5. Rebalance events — consumer_background_task_timeout_total for timeouts that trigger rebalances. Check Loki logs for rebalance messages: {app=~"ingestion-analytics.*"} |= "rebalance".
6. Backpressure from downstream — if Kafka production to output topics is slow (ingestion_outputs_latency_seconds high), the consumer can't commit offsets fast enough.

5. "Person processing is slow" — person store diagnosis

1. personinsights gRPC health — personinsights_requests_total by method and status. Error rate, latency percentiles via personinsights_latency_seconds. Connection state: personinsights_nodejs_grpc_connection_state.
2. Person cache — person_cache_* metrics for hit/miss rates. Low hit rate = more DB lookups = higher latency.
3. Person DB operations — person_* metrics for flush duration, write mode (optimistic vs pessimistic), version mismatch conflicts. person_update_version_mismatch rate — high values = contention.
4. Properties size — person_properties_size_bytes histogram. Large person properties = slow reads/writes.
5. Postgres health — check PgBouncer metrics and CloudWatch RDS for the persons DB (insights-cloud-persons-prod-us-east-1).
6. pganalyze — query-level view of the persons DB (the pganalyze MCP is available in agent sessions; list_servers / get_databases to find the persons DB for the environment):
   • get_query_stats sorted by avgTime and avgTimeDelta — never by pctOfTotal. A low-volume contention query (e.g. 143 calls at ~2.5s each, 0.2% of total runtime) is invisible by total-runtime share but can stall a whole team's person processing. avgTimeDelta surfaces what regressed in the incident window.
   • Watch the person write-path tables: insights_person, insights_persondistinctid, insights_personlessdistinctid, insights_featureflaghashkeyoverride.
   • Known contention signature (real incident): a team-scoped DELETE … WHERE id IN (SELECT … FOR UPDATE SKIP LOCKED) on insights_personlessdistinctid, ~2.4s of I/O per call at ~85% buffer hit (disk-bound ⇒ bloat), colliding with the hot addPersonlessDistinctIdsBatch insert path — stalls that team's person processing, and with it the Kafka partition(s) carrying that team.
   • get_query_explains for plan history; table stats for dead tuples / TOAST bloat when a query is I/O-bound.
7. Group processing — group_* metrics for similar DB operation patterns. group_update_version_mismatch for optimistic update conflicts.

6. "Kafka / MSK problems" — broker and topic health

1. Broker RTT — kafka_broker_rtt_us by broker_name and consumer_group. Identify if a single broker is slow vs all brokers.
2. Broker connectivity — check for librdkafka error logs in Loki: {app=~"ingestion-analytics.*"} |= "broker".
3. MSK JMX metrics — list_prometheus_metric_names regex "aws_msk_kafka.*". Key signals: throttle time, network processor idle %, request queue size.
4. WarpStream agent health — warpstream_agent_* metrics. Ingestion workers produce ALL Datastore-bound output to WarpStream (warpstream-ingestion-v2 VC). Key signals:
   • warpstream_agent_control_plane_operation_counter by operation — control plane errors
   • warpstream_agent_file_cache_client_fetch_local_or_remote_counter — cache hit/miss
   • Agent groups: general (consumer-side, single-AZ) and multi-az (producer-side) Dashboards: warpstream (Agent Overview), dbfj5c31spa1ogf (MSK vs WarpStream topics). Per-VC KMinion: kminion-warpstream-ingestion, kminion-warpstream-replay, kminion-warpstream-logs, kminion-warpstream-traces, etc.
5. KMinion cluster view — dashboard dbfgkwxs3gw8owd for lag across all consumer groups and topics. Note: there are separate KMinion instances per WarpStream VC (kminion-warpstream-*) and per MSK cluster (kminion-msk-*). Scope lag queries with app_kubernetes_io_instance to target the correct backing system.
6. Topic production — ingestion_outputs_message_value_bytes by topic for output volume. These go to WarpStream, not MSK. Compare with CH consumption rate to find bottlenecks.

7. "Redis problems" — diagnosing Redis dependency failures

First, identify which Redis instance is affected:

Symptom	Likely instance
overflow_redirect_redis_* errors rising	Ingestion Redis
cookieless_redis_error rising	Cookieless Redis
Overflow decisions reverting to defaults	Ingestion Redis
Person deduplication failures	Dedup Redis

Then check infrastructure health:

1. CloudWatch ElastiCache — datasource P034F075C744B399F. Key metrics: EngineCPUUtilization, DatabaseMemoryUsagePercentage, CurrConnections, Evictions, ReplicationLag. Cluster IDs: ingestion-prod-redis, insights-solo (prod-us primary), cookieless-prod-redis, ingestion-duplicates-prod-redis. prod-eu primary: insights-prod-redis-encripted (sic).
2. Loki logs — {app=~"ingestion-analytics.*"} |= "redis" |= "error".
3. Fail-open behavior — ingestion workers are generally designed to fail open for Redis failures, but overflow routing accuracy degrades.

8. "Postgres problems" — database health

1. Ingestion-side signals — postgres_error_total by operation. person_update_version_mismatch / group_update_version_mismatch for contention.
2. PgBouncer — pgbouncer_* metrics for connection pool utilization, waiting clients, query duration. Scope to the ingestion PgBouncer instances: ingestion-default-pgbouncer (main) and ingestion-events-pgbouncer (persons).
3. CloudWatch RDS — datasource P034F075C744B399F. Cluster IDs: insights-cloud-prod-us-east-1 (main), insights-cloud-persons-prod-us-east-1 (persons). Key metrics: CPUUtilization, ReadLatency, WriteLatency, DatabaseConnections.
4. Postgres exporter — list_prometheus_metric_names regex "pg_.*". prometheus-postgres-exporter and prometheus-postgres-persons-exporter.
5. Loki logs — {app=~"ingestion-analytics.*"} |= "postgres" |= "error" or {app=~"ingestion-analytics.*"} |= "ECONNREFUSED".

9. "Session replay ingestion issues" — replay pipeline

1. Replay-specific dashboard — get_dashboard_by_uid with uid "ingestion-sessionreplay".
2. Consumer lag — kminion_kafka_consumer_group_topic_lag_seconds{group_id="session-recordings-blob-v2"}.
3. Replay metrics — list_prometheus_metric_names regex "recording_blob_ingestion_v2_.*". Key: batch size, S3 upload latency, session manager operations.
4. K8s resources — scope to app="recordings-blob-ingestion-v2". Also check recordings-blob-ingestion-v2-overflow for overflow pipeline health.
5. Pyroscope — profile recordings/recordings-blob-ingestion-v2 for CPU/memory hotspots.

10. "Datastore ingestion is behind" — downstream health

Datastore lag means users see stale data even if ingestion workers are healthy.

1. Consumer lag on datastore_events_json — Datastore now reads primarily from the WarpStream ingestion VC, so scope to the correct KMinion instance: kminion_kafka_consumer_group_topic_lag{app_kubernetes_io_instance="kminion-warpstream-ingestion", group_id=~"datastore_events_json|group1|group1_recent", topic_name="datastore_events_json"}. Also check kminion_kafka_consumer_group_topic_lag_seconds for time-based lag. (Consumer group name differs by env: datastore_events_json in prod-us, group1/group1_recent in prod-eu.) Dashboards: dbfgkwxs3gw8owd (KMinion Consumer Group Lag — all backing systems), 8e93b023-a544-4a3b-8fac-123459d4eb84 (WarpStream CH Consumer Lag — US only, personal dashboard).

2. CH Kafka engine errors — DatastoreProfileEvents_KafkaRowsRejected{type="events"} rate. Also DatastoreProfileEvents_KafkaConsumerErrors and KafkaCommitFailures. Non-zero rejection rate = CH can't parse/insert some rows (schema mismatch, data issues).

3. Insert failures — DatastoreProfileEvents_FailedInsertQuery{type="events"} rate. May indicate too-many-parts errors, memory pressure, or schema issues.

4. Part count growth — DatastoreAsyncMetrics_MaxPartCountForPartition{type="events"}. Rising steadily = merge thread can't keep up with insert rate. CH hard-limits at 300 parts per partition — approaching this causes insert rejects. Dashboard: fe469e59-e10a-465a-9dac-ac8f6f82dc9a (stuck merge loop investigation).

5. Merge pressure — DatastoreMetrics_BackgroundMergesAndMutationsPoolTask{type="events"}. Compare to pool size. Also DatastoreProfileEvents_MergedRows rate.

6. Readonly replicas — DatastoreMetrics_ReadonlyReplica{type="events"}. Non-zero = a replica fell behind and can't accept writes.

7. Replication delay — DatastoreAsyncMetrics_ReplicasMaxAbsoluteDelay{type="events"}. High delay = inter-shard replication lagging.

8. Kafka Connect health (DuckLake path) — kafka_connect_connect_worker_metrics_connector_failed_task_count{namespace="kafka-connect"}. Also kafka_connect_ducklake_sink_task_metrics_ducklake_records_processed for throughput and ducklake_errant_record_count for DLQ'd records. Dashboard: cdzv7o1635n9ca (Kafka Connect).

9. CH ingestion pod resources — dashboard datastore-ingestion-overview-20260615. CPU throttling, memory pressure, disk usage on chi-ingestion-* pods in the datastore namespace.

10. CH cluster overview — dashboard vm-datastore-cluster-overview. QPS, memory tracking, disk capacity, connection count, ZooKeeper health.

11. "A single partition is lagging" — keyed-cost diagnosis

One partition lagging while the rest of the topic is healthy is a distinct failure class with its own method. Distilled from a real multi-hour incident; following the order below turns it into minutes.

1. Topology reasoning first — it eliminates whole categories. Analytics events are keyed by token:distinct_id, so a single lagging partition means the cost is tied to what hashes there: one team, or a small set of distinct_id values. Anything fleet-wide (a team-wide transformation, a deploy, a global config change, shared-infra degradation) would lag every partition and is structurally excluded — don't spend time on those. Corollary: one partition = one consumer; adding pods can never drain it. Restarts only help if a stuck connection was the cause (rare — verify before restarting; if lag growth continues unchanged after a restart, the cost is in the traffic itself).

2. Locate it. ingestion_lag_ms{app="…", partition="N"} for the lag curve and onset time. Find the owning pod and live offsets from the batch logs in Loki: {app="ingestion-analytics-main"} |= "KAFKA_BATCH_START" |= "\"partition\":N".

3. Slow or busy? Compute it, don't guess. From two batch-log samples: offset delta ÷ elapsed = events/sec; batch duration ÷ batch size = ms/event.

   • Busy (high events/sec, normal ms/event): a volume hot key — identify it with topfn ranked by volume, and the lever is overflow routing.
   • Slow (low events/sec, high ms/event): expensive per-event work — continue below. Nothing will show up in volume rankings; a "no hot key" result from volume tools is expected, not exculpatory.

4. Which stage. Per-pod step breakdown: sum by (step_name) (rate(ingestion_pipeline_step_duration_seconds_sum{pod="…"}[5m])) / sum by (step_name) (rate(…_count{pod="…"}[5m])), then rank the suspect pod against the fleet for the top step. Two traps:

   • A pod usually carries ~2 partitions — elevation on the pod can come from either one, and two different steps can implicate two different partitions. Treat pod-level attribution as a hint, not proof.
   • During backlog drain the pod saturates and all steps inflate together — measure during the lagging phase, or re-measure after drain. Also check p50 vs p99 for the suspect step (histogram_quantile on _bucket): a heavy tail means a minority of poisoned events.

5. Who. topfn ranked by cost, not volume — see the querying-topfn skill. process_persons_time summed by key['team_id'], key['distinct_id'] with an ms-per-event ratio separates hot keys from expensive actors; data written after 2026-07-06 can filter key['partition'] = 'N' directly, and merge_events_per_distinct_id identifies merge storms.

6. If processPersonsStep is implicated → playbook 5 step 6 (pganalyze, sorted by avgTime/avgTimeDelta). The root cause can be a single low-volume contention query on the persons write path that no volume-ranked view will ever surface.

7. If hogTransformEventStep is implicated → inspect the suspect team's enabled transformations (Data pipelines UI, or insights_function where type = 'transformation' AND enabled). Two traps: app_metrics2 failure counts mislead — destinations fail noisily but run in CDP, off the ingestion path; only type='transformation' runs inline and can wedge a partition. And a slow but succeeding fetch inside a transformation lands in succeeded, invisible in failure counts — judge by latency evidence, not failure evidence.

12. "Comparing prod-us vs prod-eu" — cross-environment

Most ingestion app metrics use the app / ingestion_pipeline labels but no environment label — the environment is implicit in which Grafana you're connected to.

For cross-environment comparison:

1. Same queries, different Grafana — run the same PromQL against both environments by switching the Grafana MCP connection (requires a new session).
2. Dashboard UIDs are mostly synced — boards from the shared Synced folder carry the same UID in both environments. The exceptions are boards that live in a personal or team folder: pl-ingestion-slas, 8e93b023-a544-4a3b-8fac-123459d4eb84 and ws-coarse-lag-explore are US only, fdrcd04np3hfkf is EU only.
3. Label values are identical — ingestion_pipeline, ingestion_lane, Datastore type values are the same across envs.
4. CloudWatch cluster IDs differ — use the env-specific names from the topology sections:
   • Redis: insights-solo (US) vs insights-prod-redis-encripted (EU) for primary
   • MSK: insights-prod-us-events-* vs insights-prod-eu-events-*
   • RDS: insights-cloud-prod-us-east-1 vs insights-cloud-prod-eu-central-1
5. Deployment differences — ingestion-analytics-turbo exists only in prod-us.
6. Kafka topology — both envs use a dedicated ingestion MSK cluster (prod-us: c21; prod-eu: insights-prod-eu-ingestion-*) for the consume side and WarpStream (warpstream-ingestion-v2 VC) for the Datastore output side. Topic partition counts differ (e.g., main is 1024 in US, 512 in EU; clientwarnings is 16 in US, 32 in EU). WarpStream VCs exist in both envs with the same logical names but different virtual_cluster_id values.