Arquitectura de referencia: data warehouse / analítica
Las consultas analíticas (informes, agregaciones, escaneos amplios) son fundamentalmente distintas de las transacciones OLTP: pocas consultas, pero muy amplias y de larga duración. Partiendo de la decisión de separar la analítica del OLTP, este árbol recorre paso a paso componentes como el método de carga (ETL vs ELT), el almacenamiento columnar/columnstore, el modelado en esquema en estrella, el particionamiento + compresión y el precálculo con vistas materializadas, sopesando el compromiso entre concurrencia y throughput.
¿Su carga analítica se ejecuta en la misma base de datos que el OLTP?
El error más común es ejecutar informes pesados sobre la BD transaccional en vivo y degradar la latencia del OLTP. La primera decisión es la separación: ¿la analítica sigue ejecutándose en la instancia OLTP, está construyendo un entorno analítico separado, o está decidiendo cómo trasladar allí los datos?
- Los informes siguen ejecutándose sobre el OLTP en vivo; ralentizan el OLTP
- Existe un warehouse separado; ¿cómo debo cargar los datos?
- Existe un warehouse; las consultas siguen siendo lentas, ¿cómo debo modelar?
¿Dónde transformará: antes o después de la carga?
Al trasladar datos desde los orígenes al warehouse, ¿hará la limpieza/transformación en una capa de procesamiento separada antes de cargar (ETL), o cargará primero los datos en bruto en el warehouse y los transformará con SQL dentro del potente motor del warehouse (ELT)? La decisión depende de la capacidad de procesamiento del warehouse y de la necesidad de flexibilidad del esquema.
- La transformación es compleja/externa; hacerla antes de cargar
- El warehouse es potente; cargar en bruto y transformar dentro con SQL
¿El cuello de botella de las consultas está en la disposición del almacenamiento o en el modelo de datos?
Si la analítica es lenta, hay dos palancas principales. Los escaneos amplios leen la fila completa en el almacenamiento por filas; el almacenamiento columnar lee solo las columnas necesarias y comprime bien. En el lado del modelo, una normalización excesiva implica muchos joins que ralentizan las consultas; un esquema en estrella (hechos + dimensiones) simplifica las cosas para la analítica.
- Los escaneos de agregación amplios son lentos; se leen pocas columnas
- Demasiados joins, esquema normalizado complejo
- El mismo informe pesado produce repetidamente el mismo resultado
Recomendación: separe la analítica del OLTP
Las cargas transaccionales y analíticas se ahogan mutuamente en el mismo motor: los informes largos retienen bloqueos/IO y retrasan las transacciones cortas. Trasladar la analítica a un destino separado (una réplica de lectura o un warehouse analítico separado) alivia ambos lados.
Recomendación: ETL (transformar antes de cargar)
En ETL, los datos se extraen de los orígenes (Extract), se limpian y transforman en una capa de procesamiento separada (Transform) y solo después se cargan en el warehouse (Load). Solo entran en el warehouse datos limpios y conformes al modelo.
Recomendación: ELT (transformar dentro del warehouse)
En ELT, los datos en bruto se cargan primero en el warehouse y la transformación se realiza dentro del propio y potente motor SQL del warehouse. Como los warehouses columnares modernos tienen una gran capacidad de procesamiento, se mueven los datos una vez y se procesan dentro, en lugar de llevar la transformación a los datos.
Recomendación: almacenamiento columnar / columnstore + compresión
Las consultas analíticas suelen recorrer unas pocas columnas en todas las filas. El almacenamiento columnar guarda los datos columna a columna; lee solo las columnas necesarias, comprime muy bien porque los valores similares están contiguos, y la E/S cae drásticamente.
Recomendación: esquema en estrella (hechos + dimensiones)
En un modelo analítico, una tabla de hechos con las medidas se sitúa en el centro, rodeada de tablas de dimensiones descriptivas. En comparación con un esquema sobrenormalizado, el número de joins baja, las consultas se simplifican y el motor genera mejores planes.
Recomendación: precalcule con vistas materializadas
Si la misma agregación pesada (ingresos diarios, resumen mensual) se ejecuta repetidamente y produce el mismo resultado, almacene físicamente el resultado en una vista materializada y haga que los informes lean el resultado precalculado. El cálculo pesado se ejecuta una vez y las consultas responden al instante.