Etiqueta: SQL

Este proyecto documenta el diseño e implementación de una plataforma de datos full-stack y de código abierto, desarrollada para reemplazar una infraestructura analítica comercial costosa y rígida. A través de la migración de la base de datos relacional de AdventureWorks desde SQL Server hacia PostgreSQL, el desarrollo de pipelines ETL modulares en Python y la integración de modelos predictivos de Machine Learning, se transformó un sistema de reportes tradicional en un motor independiente y escalable, centralizado en una aplicación web interactiva con Flask y Docker.

El Desafío del Negocio (The Problem Statement)

AdventureWorks Inc. gestionaba su inteligencia de negocio y reportería a través de una suite de analítica comercial propietaria y tradicional basada en licencias de software cerrado. Al escalar la organización, el equipo se enfrentó a tres bloqueos críticos:

1. Escala Exponencial de Costos por Licenciamiento: El modelo de cobro por usuario o por núcleo se volvió financieramente insostenible ante la creciente demanda de acceso concurrente por parte de Ventas, RRHH, Producción y Compras.
2. Limitaciones de Integración con Data Science y ML: El ecosistema cerrado dificultaba acoplar modelos avanzados de Machine Learning (como forecasting de series temporales con Prophet o clasificación con XGBoost) en los flujos de datos sin recurrir a costosos módulos de IA adicionales.
3. Dependencia del Proveedor (Vendor Lock-in): La falta de control sobre el procesamiento impedía personalizar la interfaz a nivel corporativo y flexibilizar el despliegue en infraestructura propia.

Nuestra Solución:

Arquitectura Full-Stack Data de Código Abierto. Diseñamos y desarrollamos una plataforma web propietaria, independiente y end-to-end que migró la infraestructura y transformó los reportes rígidos del pasado en un motor analítico y predictivo centralizado:

Pipeline del proyecto

Fase 1: Ingesta, Auditoría y Exploración Profunda (OLTP)

Objetivo: Comprender las reglas y la estructura exacta del negocio para mitigar sesgos en las etapas de transformación y modelado.

• Aprovisionamiento: Extracción y restauración del backup relacional nativo (.bak) de AdventureWorks 2022 en SQL Server.
• Data Quality & Perfilado Inicial: Auditoría técnica mediante scripts SQL de exploración individual para cada una de las 18 tablas relevantes, documentando rangos de fechas, valores únicos y patrones de columnas clave.
• Garantía de Integridad: Identificación exhaustiva de claves primarias y foráneas reales, consolidando las dinámicas operacionales en un Diccionario de Datos maestro y un diagrama relacional completo.

Fase 2: Diseño del Modelo Analítico Relacional y Requerimientos

Objetivo: Traducir los objetivos corporativos en una arquitectura de datos técnica limpia y estructurada.

• Definición de Granularidad: Establecimiento preciso del nivel de detalle de las entidades operativas (ej. Ventas por línea de pedido, Inventarios por snapshots de producto/almacén y Finanzas por periodo contable).
• Mapeo de KPIs: Selección de indicadores estratégicos para una visión de 360° (Sales Amount, Total Product Cost, Profit, Margen %, Tendencias YoY/MoM, Ticket Promedio y Alertas de Stock).
• Gobernanza Técnica: Generación de un Documento de Requerimientos Funcionales como base para validar formalmente las necesidades lógicas con cada área antes de iniciar el código.

Fase 3: Extracción y Preparación mediante Vistas SQL

Objetivo: Proveer una capa de datos purificada, segura y optimizada desde la base de origen.

• Desacoplamiento Estructural: Creación de vistas en SQL Server diseñadas según los requerimientos funcionales para pre-estructurar el set de datos.
• Seguridad y Anonimización: Selección rigurosa de atributos descriptivos y demográficos para segmentación, aplicando políticas de privacidad al anonimizar o remover datos sensibles de clientes.
• Cómputo en Base de Datos: Implementación de transformaciones básicas directamente en el motor de origen (como cálculo dinámico de edad y unificación de jerarquías de productos) para aligerar las fases posteriores de procesamiento en Python.

Fase 4: Migración Heterogénea de Bases de Datos

Objetivo: Romper la dependencia con el proveedor tradicional migrando el núcleo de la compañía a un motor Open-Source (PostgreSQL) sin alterar su estructura relacional original.

• Schema Mapping: Traducción de tipos de datos, restricciones e incompatibilidades entre SQL Server y PostgreSQL mediante el uso de Python.
• Pipelines de Ingesta: Diseño de scripts de migración progresiva y por lotes para mover de manera controlada el modelo relacional puro, asegurando consistencia matemática absoluta entre ambos motores.
• Stack Principal: SQL Server, PostgreSQL, SQLAlchemy, PyODBC, Psycopg2 y Pandas.

Fase 5: Pipeline ETL y Automatización con Python

Objetivo: Construir un pipeline reproducible con un solo comando que extraiga, limpie y guarde datos curados listos para consumo concurrente.

• Pipeline Modular (Jupyter/Scripts): Desarrollo de notebooks de experimentación consolidados posteriormente en un script ejecutable (etl_pipeline.py) alimentado por un archivo central de configuración (config.py).
• Feature Engineering & Limpieza: Tratamiento automatizado de valores nulos mediante reglas específicas según la columna, conversión estructurada de tipos/fechas y eliminación de duplicados mediante funciones reutilizables (CheckData).
• Persistencia Eficiente (Formatos de Alto Rendimiento): Exportación de datasets limpios y validados a disco en formato Parquet (para optimizar espacio y velocidad de lectura).
• Trazabilidad y Auditoría: Integración de logs detallados de seguimiento en consola para registrar el éxito/fallo del pipeline y mantenimiento de un registro maestro de control (datasets_control.xlsx) con métricas de filas y columnas procesadas.

Fase 6: Optimización de la Capa Analítica en PostgreSQL

Objetivo: Centralizar la lógica analítica pesada dentro de PostgreSQL para mitigar la carga computacional en el backend de la aplicación.

• Modelado Lógico: Configuración de Vistas (Views) enriquecidas y almacenamiento indexado en PostgreSQL directamente sobre las tablas migradas.
• Agregación Avanzada: Construcción de consultas complejas diseñadas para unificar la información dispersa de las áreas de Sales, HR, Product Performance, Supply Chain y Customer Analytics en estructuras consolidadas.

Fase 7: Backend Analítico y Arquitectura de Software

Objetivo: Construir la capa lógica del servidor encargada de procesar peticiones y unificar los datos analíticos de la empresa.

• Backend Engineering: Desarrollo de una aplicación web robusta en Python utilizando Flask, estructurada de forma modular mediante Blueprints para segmentar los servicios de cada departamento de la compañía.
• Persistencia Dinámica y APIs: Conexión interactiva a PostgreSQL para leer de forma nativa los archivos estructurados y las vistas optimizadas, garantizando tiempos mínimos de respuesta del servidor (latencia en milisegundos).

Fase 8: Pipeline de Machine Learning y Analítica Predictiva

Objetivo: Escalar la plataforma hacia la analítica predictiva, dotando al sistema de la capacidad de anticipar eventos críticos de negocio.

• Modelado de Inteligencia Artificial: Diseño e implementación de modelos supervisados y de análisis temporal utilizando Scikit-Learn, XGBoost y Prophet.
• Casos de Uso Operacionales:
  • Forecasting de Ventas: Modelos de series de tiempo para proyectar la demanda mensual e ingresos.
  • Clasificación de Churn: Modelos predictivos para calcular la probabilidad de abandono de clientes recurrentes.
  • Optimización de Cadena de Suministro: Predicción de quiebres de stock basándose en flujos históricos de inventario.
• Inferencia en Producción: Creación de un script automatizado para la extracción de features, reentrenamiento de modelos y serialización de artefactos (.pkl / ONNX), consumidos directamente por la API de Flask para generar predicciones en tiempo real. [1]

Fase 9: Visualización de Datos e Interfaces de Usuario (Frontend)

Objetivo: Democratizar los insights corporativos permitiendo a la mesa directiva evaluar el rendimiento histórico y forecast predictivos en una sola pantalla unificada.

• Frontend Interactivo: Creación de una interfaz web multipágina y responsive codificada con Plotly y Flask, reemplazando por completo los visualizadores de la suite comercial rígida.
• Dashboards Ejecutivos Diseñados: Executive Overview, Sales Intelligence, HR Analytics, Product Performance y Geographic Insights.
• Capa Semántica Unificada: Ocultamiento de columnas técnicas y exposición directa al usuario de medidas avanzadas de KPIs históricos calculados en base de datos junto a las proyecciones dinámicas estimadas por los modelos de Machine Learning.

Fase 10: Despliegue en Producción y DevOps (MLOps)

Objetivo: Publicar el sistema bajo una infraestructura escalable, segura y con costos de mantenimiento fijos, logrando la independencia total del negocio.

• Infraestructura Cloud: Aprovisionamiento, hardening y configuración de políticas de seguridad en un Servidor Virtual Privado (VPS) con Ubuntu Server.
• Contenerización Completa: Aislamiento de entornos y servicios (PostgreSQL, la aplicación Flask con sus pipelines de ML, las dependencias de Python y los servidores web) mediante Docker y Docker Compose.
• Arquitectura de Producción: Implementación del servidor WSGI Gunicorn acoplado a Nginx como proxy inverso, garantizando la gestión eficiente de peticiones, compresión de assets, balanceo de carga básico y cifrado SSL.

Ecosistema Tecnológico (Tech Stack)

Infografía de pipeline y arquitectura tecnológica