Optimization and Analytical Storage (The 'How to Improve')
Advanced data optimization strategies, volume reduction, and relational database design for SHAP at Portelcore.
3.1. The Volume Challenge: The Cost of Granularity
Sección titulada «3.1. El Desafío del Volumen: Coste de la Granularidad»In an automated production environment executing continuous predictions, the volume of data generated by SHAP can scale unsustainably if not properly mitigated.
- Exponential Matrix: If a model processes $10,000$ daily predictions analyzing $80$ predictive variables, a complete SHAP breakdown will generate $800,000$ new telemetry records per day.
- Associated Problems:
- Query Degradation: Analytical dashboards (Grafana, Kibana, Power BI) suffer severe latency when processing aggregated analytical queries over billions of rows.
- Stability Redundancy: Storing the exact breakdown of highly stable predictions (where the model predicts with absolute certainty) indefinitely consumes expensive storage and provides zero learning or debugging value.
3.2. Portelcore Selective Retention Strategies
Sección titulada «3.2. Estrategias de Retención Selectiva de Portelcore»To mitigate uncontrolled growth in analytical storage, the Portelcore engineering team applies the Hybrid Telemetry Strategy:
[ Entrada de Predicciones ] | { ¿Es interesante? } / \ (Sí: Excepción o Muestra) (No: Operación Nominal) / \ [ Almacenar Desglose SHAP Completo ] [ Almacenar Solo Predicción Final ]- Error Audit (The Golden Rule):
if prediccion_modelo != resultado_real- Store the full SHAP autopsy of all false positives and false negatives. These logical deviations constitute the primary data source for retraining and debugging.
- Periodic Control Sampling:
if transaction_id % 100 == 0- Store 1 out of every 100 standard successful transactions. This provides a statistical baseline to study ordinary model behavior without saturating storage.
- Confidence Deviation Filtering (Anomalies):
if output_probability BETWEEN 0.45 AND 0.55- Detailed storage of predictions with a high level of uncertainty (close to the decision threshold).
3.3. Optimized Relational Database Design
Sección titulada «3.3. Diseño de Base de Datos Relacional Optimizada»Storing SHAP attributions in flat file formats (.csv or .json) is a bad practice that degrades search performance. We implemented an optimized three-tier relational schema to ensure efficient searching of predictive telemetry.
Entity-Relationship Diagram (ERD)
Sección titulada «Modelo Entidad-Relación (ERD)»erDiagram EJECUCION { VARCHAR run_id PK TIMESTAMP started_at VARCHAR model_version } PREDICCION { VARCHAR prediction_id PK VARCHAR run_id FK TIMESTAMP predicted_at VARCHAR portel_user_id FLOAT output_probability BOOLEAN actual_outcome } ATRIBUCION_SHAP { BIGINT attribution_id PK VARCHAR prediction_id FK VARCHAR feature_name FLOAT feature_value FLOAT shap_value } EJECUCION ||--o{ PREDICCION : "genera" PREDICCION ||--o{ ATRIBUCION_SHAP : "desglosa"SQL DDL Script (PostgreSQL & SQLite Compatible)
Sección titulada «Script DDL SQL (PostgreSQL & SQLite Compatible)»The following script establishes the analytical tables, ensuring cascading referential integrity and indexing for fast massive searches:
-- 1. Tabla de Trazabilidad de Ejecuciones del PipelineCREATE TABLE IF NOT EXISTS model_runs ( run_id VARCHAR(64) PRIMARY KEY, started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, model_version VARCHAR(32) NOT NULL);
-- 2. Tabla de Registro de Predicciones Guardadas (Híbrido)CREATE TABLE IF NOT EXISTS model_predictions ( prediction_id VARCHAR(64) PRIMARY KEY, run_id VARCHAR(64) REFERENCES model_runs(run_id) ON DELETE CASCADE, predicted_at TIMESTAMP NOT NULL, portel_user_id VARCHAR(64) NOT NULL, output_probability REAL NOT NULL, actual_outcome BOOLEAN, -- NULL si no se ha validado aún el resultado real CONSTRAINT fk_run FOREIGN KEY (run_id) REFERENCES model_runs(run_id));
-- 3. Tabla Granular de Atribuciones SHAPCREATE TABLE IF NOT EXISTS shap_attributions ( attribution_id INTEGER PRIMARY KEY AUTOINCREMENT, -- Utilizar BIGSERIAL en PostgreSQL prediction_id VARCHAR(64) REFERENCES model_predictions(prediction_id) ON DELETE CASCADE, feature_name VARCHAR(128) NOT NULL, feature_value REAL NOT NULL, shap_value REAL NOT NULL, CONSTRAINT fk_prediction FOREIGN KEY (prediction_id) REFERENCES model_predictions(prediction_id));
-- Índices de Alto Rendimiento para Consultas del Equipo AnalíticoCREATE INDEX IF NOT EXISTS idx_predictions_run ON model_predictions(run_id);CREATE INDEX IF NOT EXISTS idx_attributions_prediction ON shap_attributions(prediction_id);CREATE INDEX IF NOT EXISTS idx_attributions_feature ON shap_attributions(feature_name);3.4. Example Analytical Query
Sección titulada «3.4. Consulta Analítica de Ejemplo»With this optimized design, we can audit predictive failures in seconds through a simple structured query:
SELECT p.prediction_id, p.portel_user_id, p.output_probability, a.feature_name, a.feature_value, a.shap_valueFROM model_predictions pJOIN shap_attributions a ON p.prediction_id = a.prediction_idWHERE p.actual_outcome IS NOT NULL AND (p.output_probability > 0.5) != p.actual_outcome -- Predicción fallidaORDER BY ABS(a.shap_value) DESCLIMIT 10;3.5. Storage Validation Checklist
Sección titulada «3.5. Checklist de Validación del Almacenamiento»Verify that the analytical system meets the tenant optimization criteria:
- I have discarded local persistence in
.jsonfiles in favor of the indexed relational schema. ✓ - The selective (hybrid) retention policy reduces daily data consumption by at least 80%. ✓
- Foreign key indices are correctly deployed to guarantee sub-second response times on
portelcore.comcorporate dashboards. ✓

