Ir al contenido

Optimization and Analytical Storage (The 'How to Improve')

Advanced data optimization strategies, volume reduction, and relational database design for SHAP at Portelcore.

0views

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.

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 ]
  1. 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.
  2. 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.
  3. 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).

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.

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 Pipeline
CREATE 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 SHAP
CREATE 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ítico
CREATE 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);

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_value
FROM model_predictions p
JOIN shap_attributions a ON p.prediction_id = a.prediction_id
WHERE p.actual_outcome IS NOT NULL
AND (p.output_probability > 0.5) != p.actual_outcome -- Predicción fallida
ORDER BY ABS(a.shap_value) DESC
LIMIT 10;

Verify that the analytical system meets the tenant optimization criteria:

  • I have discarded local persistence in .json files 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.com corporate dashboards. ✓