~/bigdatastack

lección 3

Día 2 -- El dashboard de SLAs

Ana necesita saber qué porcentaje de envíos llegan a tiempo. Diseñas la tabla de hechos de cumplimiento y agregas por zona, repartidor y hora.

⏱ 45 min

### Martes, 9:00 AM -- "Necesito números, no sensaciones"

En el standup, Ana toma la palabra. Normalmente no habla -- es del equipo de operaciones, no de tech. Pero hoy tiene algo que decir: "Los clientes B2B de moda reclamaron que el 12% de sus envíos llegaron tarde la semana pasada. Yo no tengo forma de verificarlo porque no tenemos un dashboard de SLAs. Necesito saber, en tiempo real, qué porcentaje de envíos cumplen el SLA por zona, por hora del día y por repartidor. Y lo necesito para el jueves porque tenemos reunión con Modalia." Marcos te mira. Tu dominio, tu problema.

El SLA de FlashRoute es contractual: el 95% de los envíos deben llegar dentro de la ventana horaria prometida al cliente. Si un e-commerce contrató entrega en "2 horas desde recogida", el paquete debe llegar en 2 horas o menos. Si el SLA baja del 95% en un mes, FlashRoute paga penalizaciones al e-commerce. Si sube del 97%, FlashRoute cobra un bonus. Los números importan -- no son vanity metrics, son dinero real.

Para esta parte trabajarás con dos datasets: envios.csv (cada envío con created_at, picked_up_at, delivered_at, window_start, window_end y el flag sla_met) y sla_historico.csv (cumplimiento ya agregado por fecha/ciudad/zona/cliente). Descárgalos del apartado de datasets del caso. Ojo: envios.csv contiene bugs intencionales que afectan al SLA — 45 envíos con window_end anterior a window_start y 340 marcados sla_met=true aunque delivered_at supera la ventana en más de 5 minutos. Tu cálculo de SLA debe recalcular el cumplimiento desde los timestamps, no fiarse del flag sla_met.

### Diseñar la tabla de hechos: fact_delivery_sla

Necesitas una tabla de hechos que registre cada entrega completada con información suficiente para calcular el cumplimiento de SLA desde cualquier dimensión que Ana necesite. El grano es una fila por entrega finalizada. Las dimensiones son: zona, repartidor, cliente B2B, fecha, hora, día de la semana. Las métricas: tiempo real de entrega, tiempo prometido, diferencia y un flag de cumplimiento.

1-- DDL: tabla de hechos de cumplimiento de SLA
2-- Grano: una fila por entrega completada (delivered o failed)
3CREATE TABLE fact_delivery_sla (
4    -- Clave primaria
5    delivery_id         VARCHAR(30) PRIMARY KEY,
6    
7    -- Dimensiones (quien, donde, para quien)
8    driver_id           VARCHAR(10) NOT NULL,
9    zone                VARCHAR(30) NOT NULL,
10    client_b2b          VARCHAR(20) NOT NULL,
11    hub_origin          VARCHAR(15) NOT NULL,
12    
13    -- Timestamps clave del ciclo de vida
14    pickup_at           TIMESTAMP NOT NULL,   -- Cuando el repartidor recogio
15    delivered_at        TIMESTAMP,            -- Cuando entrego (NULL si fallo)
16    promised_eta        TIMESTAMP NOT NULL,   -- Ventana prometida al cliente
17    
18    -- Metricas calculadas
19    delivery_minutes    FLOAT,               -- Tiempo real: delivered_at - pickup_at
20    promised_minutes    FLOAT,               -- Tiempo prometido: promised_eta - pickup_at
21    diff_minutes        FLOAT,               -- Diferencia: positivo = tarde
22    
23    -- Flags de cumplimiento
24    met_sla             BOOLEAN,             -- TRUE si entrego a tiempo
25    delivery_status     VARCHAR(15) NOT NULL, -- delivered | failed | returned
26    
27    -- Dimensiones de tiempo (pre-calculadas para agregar rápido)
28    delivery_date       DATE NOT NULL,
29    delivery_hour       INTEGER,             -- 0-23
30    day_of_week         INTEGER,             -- 1=Lun, 7=Dom
31    
32    -- Metadata
33    processed_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
34);
35

36-- Indices para las queries del dashboard (las que Ana ejecuta cada minuto)
37CREATE INDEX idx_sla_zone_date ON fact_delivery_sla(zone, delivery_date);
38CREATE INDEX idx_sla_driver_date ON fact_delivery_sla(driver_id, delivery_date);
39CREATE INDEX idx_sla_client_date ON fact_delivery_sla(client_b2b, delivery_date);
40CREATE INDEX idx_sla_status_date ON fact_delivery_sla(delivery_status, delivery_date);
41CREATE INDEX idx_sla_hour ON fact_delivery_sla(delivery_date, delivery_hour);

DDL de fact_delivery_sla — el corazón del dashboard de SLAs

En logística, la tabla de hechos de SLA es probablemente la tabla más consultada del warehouse. Cada reunión con clientes B2B empieza aquí. Cada standup de operaciones la mira. Diseñala pensando en las preguntas que Ana hará: "Qué % en zona X?", "Qué repartidores tienen peor SLA?", "A qué hora fallamos más?" Los índices deben reflejar esas queries.

### Poblar la tabla: streaming + batch

La tabla se puebla desde dos fuentes: el topic delivery.status de Kafka (cuando un envío cambia a "delivered") y la tabla de entregas con sus metadatos (pickup_at, promised_eta). Decides implementar ambos caminos: streaming para que el dashboard tenga datos en near-real-time (latencia < 1 minuto) y un batch diario que reconcilia posibles gaps o inconsistencias.

1from pyspark.sql import SparkSession
2from pyspark.sql.functions import (
3    col, from_json, to_timestamp, unix_timestamp, when,
4    hour, dayofweek, to_date, current_timestamp, lit
5)
6from pyspark.sql.types import StructType, StructField, StringType
7

8# Schema del evento delivery.status
9delivery_schema = StructType([
10    StructField("delivery_id", StringType(), False),
11    StructField("status", StringType(), False),
12    StructField("timestamp", StringType(), False),
13    StructField("driver_id", StringType(), True),
14    StructField("zone", StringType(), True),
15    StructField("client_b2b", StringType(), True),
16    StructField("hub_origin", StringType(), True),
17])
18

19spark = SparkSession.builder \
20    .appName("flashroute-sla-streaming") \
21    .getOrCreate()
22

23# Leer solo eventos "delivered" del topic (filtrar en origen)
24delivery_stream = spark.readStream \
25    .format("kafka") \
26    .option("kafka.bootstrap.servers", "kafka-1.flashroute.internal:9092") \
27    .option("subscribe", "delivery.status") \
28    .option("startingOffsets", "latest") \
29    .load() \
30    .selectExpr("CAST(value AS STRING) as json_str") \
31    .select(from_json(col("json_str"), delivery_schema).alias("d")) \
32    .select("d.*") \
33    .filter(col("status").isin("delivered", "failed"))
34

35# Lookup de metadatos de la entrega (pickup_at, promised_eta)
36deliveries_meta = spark.read \
37    .format("jdbc") \
38    .option("url", "jdbc:postgresql://db.flashroute.internal/ops") \
39    .option("dbtable", "deliveries") \
40    .load() \
41    .select("delivery_id", "pickup_at", "promised_eta")
42

43# Join y calcular métricas de SLA
44sla_facts = delivery_stream \
45    .join(deliveries_meta, "delivery_id") \
46    .withColumn("delivered_at", to_timestamp(col("timestamp"))) \
47    .withColumn("pickup_ts", to_timestamp(col("pickup_at"))) \
48    .withColumn("promised_ts", to_timestamp(col("promised_eta"))) \
49    .withColumn("delivery_minutes",
50        (unix_timestamp("delivered_at") - unix_timestamp("pickup_ts")) / 60
51    ) \
52    .withColumn("promised_minutes",
53        (unix_timestamp("promised_ts") - unix_timestamp("pickup_ts")) / 60
54    ) \
55    .withColumn("diff_minutes",
56        col("delivery_minutes") - col("promised_minutes")
57    ) \
58    .withColumn("met_sla", col("delivered_at") <= col("promised_ts")) \
59    .withColumn("delivery_date", to_date(col("delivered_at"))) \
60    .withColumn("delivery_hour", hour(col("delivered_at"))) \
61    .withColumn("day_of_week", dayofweek(col("delivered_at")))

Pipeline streaming que puebla fact_delivery_sla desde eventos Kafka

### Las queries del dashboard de Ana

Con la tabla poblada, el dashboard de Grafana ejecuta queries periódicas contra Redshift. Estas son las 4 queries principales que Ana necesita ver en su pantalla de 55 pulgadas. Cada panel se refresca cada 60 segundos:

1-- KPI 1: SLA global del día (número grande arriba del dashboard)
2SELECT
3    delivery_date,
4    COUNT(*) AS total_entregas,
5    SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) AS on_time,
6    ROUND(100.0 * SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) / COUNT(*), 2) AS sla_pct
7FROM fact_delivery_sla
8WHERE delivery_date = CURRENT_DATE
9  AND delivery_status = 'delivered'
10GROUP BY delivery_date;
11

12-- KPI 2: SLA por zona (tabla ordenada de peor a mejor)
13SELECT
14    zone,
15    COUNT(*) AS total,
16    ROUND(100.0 * SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) / COUNT(*), 2) AS sla_pct,
17    ROUND(AVG(diff_minutes), 1) AS avg_delay_min
18FROM fact_delivery_sla
19WHERE delivery_date = CURRENT_DATE AND delivery_status = 'delivered'
20GROUP BY zone
21ORDER BY sla_pct ASC;
22

23-- KPI 3: Top 10 repartidores con peor SLA (para coaching)
24SELECT
25    driver_id,
26    COUNT(*) AS total_entregas,
27    ROUND(100.0 * SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) / COUNT(*), 2) AS sla_pct,
28    ROUND(AVG(diff_minutes), 1) AS avg_delay_min
29FROM fact_delivery_sla
30WHERE delivery_date = CURRENT_DATE AND delivery_status = 'delivered'
31GROUP BY driver_id
32HAVING COUNT(*) >= 5
33ORDER BY sla_pct ASC
34LIMIT 10;
35

36-- KPI 4: SLA por cliente B2B (para reuniones comerciales)
37SELECT
38    client_b2b,
39    COUNT(*) AS total,
40    ROUND(100.0 * SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) / COUNT(*), 2) AS sla_pct,
41    ROUND(AVG(delivery_minutes), 1) AS avg_delivery_min,
42    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY delivery_minutes), 1) AS p95_min
43FROM fact_delivery_sla
44WHERE delivery_date = CURRENT_DATE AND delivery_status = 'delivered'
45GROUP BY client_b2b
46ORDER BY total DESC;

Las 4 queries principales del dashboard de SLAs

La query de "peores repartidores" es sensible. Ana la usa para coaching, no para castigar. Marcos te pide explícitamente que el dashboard no muestre nombres -- solo IDs. Y que el filtro HAVING >= 5 evite juzgar a un repartidor que solo ha hecho 2 entregas en el día (no es estadísticamente significativo con tan pocos datos).

### Vista materializada: tendencia semanal

Además del día en curso, Ana necesita la tendencia semanal para detectar si el SLA está empeorando o mejorando. Si hoy es 94.2% pero ayer era 96.1% y anteayer 95.8%, hay un patrón de degradación que requiere atención antes de que baje del 95% contractual.

1-- Vista materializada: tendencia SLA últimos 7 días
2CREATE MATERIALIZED VIEW mv_sla_trend_7d AS
3SELECT
4    delivery_date,
5    zone,
6    COUNT(*) AS total_deliveries,
7    SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) AS on_time_count,
8    ROUND(100.0 * SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) / COUNT(*), 2) AS sla_pct,
9    ROUND(AVG(delivery_minutes), 1) AS avg_delivery_min,
10    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY delivery_minutes), 1) AS p95_min
11FROM fact_delivery_sla
12WHERE delivery_date >= CURRENT_DATE - INTERVAL '7 days'
13  AND delivery_status = 'delivered'
14GROUP BY delivery_date, zone
15ORDER BY delivery_date DESC, zone;
16

17-- Se refresca cada hora via Step Function
18REFRESH MATERIALIZED VIEW mv_sla_trend_7d;

Vista materializada para tendencia semanal — se refresca cada hora

PERCENTILE_CONT(0.95) te da el tiempo de entrega del percentil 95. Si el p95 es 90 minutos, el 95% de entregas tardan menos de 90 minutos. Es más útil que el promedio porque el promedio oculta outliers. Un envío que tardó 3 horas sube el promedio pero no es representativo del servicio general. Los SLAs de logística siempre se miden en percentiles (p50, p90, p95, p99).

### El imprevisto: desglose por franja horaria

A las 16:00, Ana te escribe: "El dashboard está genial. Pero necesito una cosa más: SLA por franja horaria. Sospecho que por las tardes (15-19) el SLA baja porque hay tráfico. Si confirmo eso, puedo ajustar las promesas de entrega en esa franja." Es una petición razonable y el dato ya está en la tabla (delivery_hour). Necesitas una query que agrupe en franjas.

1-- SLA por franja horaria (confirmar hipotesis de Ana)
2SELECT
3    CASE
4        WHEN delivery_hour BETWEEN 8 AND 11 THEN '08-12 mañana'
5        WHEN delivery_hour BETWEEN 12 AND 14 THEN '12-15 mediodia'
6        WHEN delivery_hour BETWEEN 15 AND 18 THEN '15-19 tarde'
7        WHEN delivery_hour BETWEEN 19 AND 22 THEN '19-23 noche'
8        ELSE 'fuera horario'
9    END AS franja,
10    COUNT(*) AS total,
11    ROUND(100.0 * SUM(CASE WHEN met_sla THEN 1 ELSE 0 END) / COUNT(*), 2) AS sla_pct,
12    ROUND(AVG(delivery_minutes), 1) AS avg_min,
13    ROUND(AVG(diff_minutes), 1) AS avg_delay_min
14FROM fact_delivery_sla
15WHERE delivery_date >= CURRENT_DATE - INTERVAL '7 days'
16  AND delivery_status = 'delivered'
17GROUP BY franja
18ORDER BY franja;
19

20-- Resultado esperado: la franja 15-19 tiene el SLA más bajo (~92%)
21-- Ana puede usar esto para ajustar promesas en hora punta

Query por franja horaria — Ana sospecha que la tarde es problemática

El resultado confirma la hipótesis de Ana: la franja 15-19 tiene un SLA del 92.3% vs 96.8% por la mañana. El tráfico urbano en hora punta hace que los repartidores tarden más. Ana decide ajustar las ventanas de entrega prometidas en esa franja: en vez de "2 horas" promete "2.5 horas" a los clientes que piden entrega por la tarde. Problema de negocio resuelto con datos. Esto es exactamente para lo que existe tu dominio.

## ejercicios

[01]

Disenar tabla de hechos de SLA

Escribe el DDL completo de fact_delivery_sla incluyendo todas las columnas necesarias para responder las preguntas de Ana. Incluye índices optimizados para las queries más frecuentes del dashboard.

Cargando editor...
[02]

Queries del dashboard de SLAs

Escribe las queries SQL que alimentan el dashboard de Grafana de Ana. Incluye: SLA global, por zona, ranking repartidores, desglose cliente B2B y franja horaria.

Cargando editor...
[03]

Pipeline streaming para poblar fact_delivery_sla

Implementa el job de PySpark Streaming que lee eventos "delivered" de Kafka, hace join con los metadatos de la entrega y calcula todas las métricas necesarias para la tabla de hechos de SLA.

Cargando editor...

Regístrate para guardar tu progreso.

## comentarios

Reporta erratas, ayuda a otros o comparte tu opinión. Sé constructivo.

Inicia sesión para comentar y responder.

cargando comentarios...