Reto Final: The Ghost Taxi | Data Engineering

Briefing de la Misión

El sistema de detección de fraudes ha identificado un patrón: Viajes que duran 0 minutos (Pickup = Dropoff) pero cobran tarifas altas (>$50).
El equipo de analítica intentó correr una consulta para auditarlos, pero colapsó el servidor de producción.

QUERY TÓXICA

-- Intento Fallido (Duration: 45s, Reads: 3.5M)
SELECT * FROM Trips 
WHERE DATEDIFF(MINUTE, Pickup, Dropoff) = 0 
  AND TotalAmount > 50;

Análisis Forense (Root Cause)

Por qué falló el índice.

El problema es la función DATEDIFF() en el WHERE.

Esto rompe la regla de SARGability (Search ARGument Ability). Al envolver las columnas en una función, el motor no puede mirar el índice y saltar a la fecha correcta. Tiene que calcular la resta para CADA UNA de las 3 millones de filas antes de saber si el resultado es 0.

Diagnóstico: Index Scan (Lectura Completa) + Alto uso de CPU por cálculo fila por fila.

Visualización del Problema

Scanning 3,000,000 Rows...

Ingeniería de Solución

Materializar el cálculo.

1. Columna Calculada Persistida

Creamos una columna virtual que almacena el resultado de DATEDIFF físicamente en el disco. El cálculo se hace UNA vez al insertar, no cada vez que consultas.

ALTER TABLE Trips 
ADD DurationMinutes AS 
    DATEDIFF(MINUTE, Pickup, Dropoff) 
    PERSISTED; -- < La clave

2. Indexar lo Calculado

Ahora que el dato existe físicamente, podemos indexarlo. Ordenamos por duración para que todos los "0" queden juntos.

CREATE INDEX IX_Duration_Scam 
ON Trips(DurationMinutes) 
INCLUDE (TotalAmount);

graph LR Insert[INSERT Data] --> Calc[CPU Calcula DATEDIFF] Calc --> Disk[(Guardar en Disco)] Disk --> Index[Actualizar Índice B-Tree] Query[SELECT] -->|Lee Índice| Seek[Index Seek Rápido] style Insert fill:#1e293b,stroke:#94a3b8,color:#fff style Calc fill:#b45309,stroke:#f59e0b,color:#fff style Disk fill:#1e293b,stroke:#94a3b8,color:#fff style Index fill:#064e3b,stroke:#10b981,color:#fff style Seek fill:#064e3b,stroke:#10b981,color:#fff

Despliegue y Verificación

La consulta SARGable.

PATCHED QUERY

-- Consulta Optimizada
SELECT * FROM Trips 
WHERE DurationMinutes = 0  -- Ahora busca en el índice
  AND TotalAmount > 50;

Tiempo

45s → 2ms

Lecturas

3.5M → 12

CPU Load

100% → 0%

Incidente Resuelto. Servidor Estable.

Volver al Centro de Mando