Cómo auditar cargas en Data Warehouse: paso a paso

Este tutorial muestra cómo implementar una tabla de auditoría de cargas (load auditing) en un Data Warehouse para registrar éxitos, fallos y tiempos. Saber qué salió bien o mal en las cargas es útil para la detección de problemas, reejecución y mejora continua.

Prerequisitos

• Conocimientos básicos de SQL (SELECT, INSERT, UPDATE).
• Un entorno Data Warehouse con acceso para crear tablas y ejecutar jobs (por ejemplo SQL Server, Azure Synapse, PostgreSQL).
• Una pipeline ETL/ELT sencilla o proceso de carga que pueda invocar sentencias SQL antes y después de la ejecución.

Paso 1: Concepto y columnas esenciales

Antes de crear la tabla, definimos qué queremos registrar: identificador de la carga, fecha/hora de inicio y fin, estado (SUCESSO/ERRO), contajes leídos/grabados, duración y mensaje de error. Esto permite análisis de frecuencia de fallos y tiempos medios de carga.

Paso 2: Crear la tabla de auditoría

Creamos una tabla simple para guardar los registros de cada ejecución de carga. Mantenemos campos suficientes para diagnóstico y agregación.

CREATE TABLE load_audit (
  load_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_name     VARCHAR(200) NOT NULL,
  start_time   TIMESTAMP NOT NULL,
  end_time     TIMESTAMP NULL,
  status       VARCHAR(20) NOT NULL,
  rows_read    BIGINT NULL,
  rows_written BIGINT NULL,
  error_msg    VARCHAR(2000) NULL
);

Paso 3: Insertar registro al inicio de la carga

Al inicio del job ETL/ELT insertamos un registro con start_time y status = 'RUNNING'. Esto crea la referencia para actualizar al final. En pipelines que soportan variables, guarde el load_id retornado.

INSERT INTO load_audit (job_name, start_time, status)
VALUES ('daily_customer_load', CURRENT_TIMESTAMP, 'RUNNING')
RETURNING load_id;

Paso 4: Actualizar al finalizar con éxito

Si la carga termina sin errores, actualice el registro con end_time, status = 'SUCCESS' y contajes. Use el load_id obtenido en el paso anterior para apuntar al registro correcto.

UPDATE load_audit
SET end_time = CURRENT_TIMESTAMP,
    status = 'SUCCESS',
    rows_read = 12345,
    rows_written = 12000
WHERE load_id = 42;

Paso 5: Registrar errores y mensajes de fallo

Si ocurre una falla, capture el mensaje de error y actualice el registro con status = 'FAILED'. Incluya stack trace reducido o código del error para investigación.

UPDATE load_audit
SET end_time = CURRENT_TIMESTAMP,
    status = 'FAILED',
    error_msg = 'Timeout na leitura de origem: connection reset'
WHERE load_id = 42;

Paso 6: Automatizar en pipelines (ejemplo conceptual)

Integre la lógica de insert/updates en su orquestador (Azure Data Factory, Azure Synapse Pipelines, Airflow). Use transacciones o bloques try/catch para garantizar que el registro se actualice incluso en caso de error.

# Pseudocódigo conceptual
load_id = INSERT ... RETURNING load_id
try:
  run_etl_process()
  UPDATE load_audit SET ... WHERE load_id = load_id
except Exception as e:
  UPDATE load_audit SET status='FAILED', error_msg=substr(e.message,1,2000) WHERE load_id = load_id
  raise

Paso 7: Indicadores e informes

Con la tabla en uso, cree queries para métricas como duración media, tasa de éxito y principales errores. Estos informes ayudan a priorizar correcciones y monitorizar SLAs.

-- Duración media de las cargas por job
SELECT job_name,
       AVG(EXTRACT(EPOCH FROM (end_time - start_time))) AS avg_seconds,
       SUM(CASE WHEN status='FAILED' THEN 1 ELSE 0 END) AS failures
FROM load_audit
GROUP BY job_name;

Verificar el resultado

Confirme que para cada ejecución del job existe un registro en la tabla load_audit con start_time y end_time completados y status adecuado. Verifique que los fallos aparecen con error_msg y que las consultas de métricas retornan valores coherentes.

Conclusión

Implementar auditoría de cargas en Data Warehouse es un paso sencillo con gran retorno en diagnóstico y fiabilidad. Siguiente paso: añadir alertas automáticas para fallos recurrentes o tiempos por encima del SLA. Consejo: registre también la versión del job o commit del código para facilitar la identificación de la causa raíz.