Alcance. Este documento define la arquitectura analítica de ConTodo: el Data Warehouse (DW) dimensional bajo esquema estrella (Kimball), el pipeline de ingesta CDC (Change Data Capture) desde PostgreSQL operacional hacia el DW, la capa semántica, el dashboard ejecutivo (C-level) y el catálogo de KPIs financieros y operativos con fórmulas auditables (EBITDA, margen bruto/neto, rotación de inventario, liquidez corriente/ácida, ROE/ROA, DSO/DPO). Todo el diseño respeta el modelo multi-tenant (company_id como partición lógica obligatoria), la terminología SUNAT (PLE, SIRE, detracciones, tipo de cambio SBS) y los principios NIIF/PCGE 2019 que alimentan las métricas. Se incluyen supuestos explícitos, riesgos, alternativas y diagramas mermaid.
1. Principios de diseño y supuestos
| # | Supuesto / Principio | Justificación |
|---|
| S1 | Separación OLTP / OLAP. El ERP transaccional (PostgreSQL RDS) jamás corre consultas analíticas pesadas; estas viven en un DW columnar separado. | Proteger latencia del ERP (p95 < 300 ms). Las consultas BI escanean millones de filas; mezclarlas degrada la operación. |
| S2 | Multi-tenant físico-lógico. Cada hecho y dimensión lleva company_id. El DW se particiona por company_id + fecha; el RLS del ERP se replica como filtro obligatorio en la capa semántica. | Aislamiento de datos entre tenants es contractual. Un dashboard nunca debe filtrar cruzado entre empresas. |
| S3 | Esquema estrella (Kimball), no copo de nieve puro. Dimensiones desnormalizadas; jerarquías embebidas. | Optimiza lectura analítica y simplifica el modelo semántico. El copo de nieve añade joins que penalizan el dashboard. |
| S4 | Moneda dual. Cada hecho monetario guarda monto en moneda origen, TC SBS y monto en moneda funcional (PEN). Métricas consolidadas siempre en PEN. | NIC 21 y exigencia SUNAT. Empresas importadoras textiles facturan en USD; el BI consolida en soles. |
| S5 | Grano explícito por tabla de hechos. Cada fact_* declara su grano (1 fila = 1 línea de comprobante, 1 movimiento de kardex, 1 snapshot diario). | El grano define la verdad. Mezclar granos produce doble conteo en agregaciones — el error #1 en DW. |
| S6 | Métricas como código (semantic layer). Las fórmulas de KPI se versionan en una capa semántica (dbt metrics / Cube), no se hardcodean en cada dashboard. | "Una sola definición de EBITDA" para toda la organización. Evita que Finanzas y Ventas reporten cifras distintas. |
| S7 | Latencia por capa. Near-real-time (CDC streaming) para operativo (stock, ventas del día); batch nocturno para estados financieros y snapshots. | No todo necesita tiempo real. El cierre contable es batch; el tablero de stock sí debe ser fresco. |
| S8 | Idempotencia y reprocesamiento. Todo job ETL es re-ejecutable sin duplicar datos (upsert por clave natural + _loaded_at). | Fallos de red/Sidekiq son inevitables; el pipeline debe tolerar reintentos. |
| S9 | Slowly Changing Dimensions Tipo 2 para dimensiones con historia (cliente, producto, lista de precios, tipo de cambio). | Reportar "margen al momento de la venta" exige conservar el costo/precio histórico, no el actual. |
Justificación de capa medallón (bronze/silver/gold). Bronze conserva el dato crudo del CDC (auditoría y reprocesamiento total); Silver aplica limpieza, deduplicación y tipado; Gold materializa el esquema estrella listo para consumo. Esto permite time-travel y rehacer el modelo sin reextraer del OLTP.
3. Pipeline ETL/ELT con CDC desde PostgreSQL
3.1 Por qué CDC y no SELECT periódico
| Enfoque | Latencia | Carga sobre OLTP | Captura de deletes | Veredicto |
|---|
Batch SELECT WHERE updated_at > | Minutos–horas | Alta (full scans) | No detecta hard-deletes | Descartado para tablas calientes |
| Triggers + tabla de auditoría | Baja | Media (overhead por escritura) | Sí | Alternativa si no se puede tocar WAL |
| CDC vía WAL (Debezium) | Segundos | Mínima (lee el log, no las tablas) | Sí (eventos d) | Elegido |
ConTodo usa replicación lógica de PostgreSQL (publication/slot) consumida por Debezium, que emite eventos c (create), u (update), d (delete) a Kafka. Cada evento porta before, after, op, ts_ms y company_id. El consumidor escribe a Bronze (parquet append-only) y un job dbt incremental construye Silver/Gold.
3.2 Cadencia por dominio
| Dominio | Tablas fuente | Modo | SLA frescura |
|---|
| Ventas / facturación | sales_invoices, sales_invoice_lines | CDC streaming | < 5 min |
| Inventario / Kardex | stock_moves, stock_quants | CDC streaming | < 5 min |
| Compras | purchase_orders, bills | CDC streaming | < 15 min |
| Tesorería / cobranzas | payments, bank_statements | CDC streaming | < 15 min |
| Contabilidad (mayor) | journal_entries, journal_lines | Batch incremental | 1 h |
| Estados financieros / snapshots | derivados | Batch nocturno | Diario (cierre 02:00) |
| RRHH / Planillas | payslips, employees | Batch mensual | Tras cierre de planilla |
- Deduplicación CDC: por
(tabla, pk, ts_ms) quedarse con el último evento por ventana.
- Manejo de deletes: soft-delete lógico en Gold (
is_deleted = true) para preservar historia analítica.
- Conversión de moneda: join con
dim_tipo_cambio (SCD2) por fecha del documento → monto PEN.
- Tests dbt obligatorios:
unique y not_null sobre claves, relationships (FK a dimensiones), accepted_values (estados), y test custom de balance (Σ debe = Σ haber por asiento) heredado de la integridad contable.
4. Modelo dimensional (esquema estrella)
4.1 Diagrama del bus dimensional
4.2 Tablas de hechos (facts)
| Tabla de hechos | Grano (1 fila =) | Tipo | Métricas aditivas | Métricas semi/no aditivas |
|---|
fact_ventas | 1 línea de comprobante de venta | Transaccional | cantidad, monto_bruto, descuento, igv, monto_neto_pen, costo_venta | margen_unitario (calc) |
fact_compras | 1 línea de orden/factura de compra | Transaccional | cantidad, monto_neto_pen, igv, detraccion | — |
fact_inventario_mov | 1 movimiento de kardex | Transaccional | cantidad_in, cantidad_out, costo_unit, valor_mov_pen | — |
fact_inventario_snap | 1 producto×almacén×día | Snapshot periódico | — | stock_qty, stock_valor_pen (no aditiva en tiempo) |
fact_finanzas_gl | 1 línea de asiento contable | Transaccional | debe_pen, haber_pen | saldo (semi-aditiva por cuenta) |
fact_cobranzas | 1 documento por cobrar (snapshot) | Acumulativo (accumulating snapshot) | — | dias_vencido, saldo_pendiente |
Nota sobre snapshots. fact_inventario_snap evita recalcular stock histórico recorriendo todo el kardex; el dashboard de rotación lee el saldo del día directamente. Es semi-aditiva: se suma entre productos/almacenes pero no entre fechas (se promedia).
4.3 Tablas de dimensiones (dims)
| Dimensión | Atributos clave | SCD | Jerarquías |
|---|
dim_tiempo | fecha, dia, semana, mes, trimestre, año, es_feriado_pe, periodo_fiscal | Tipo 0 (estática) | Año→Trim→Mes→Día |
dim_empresa | company_id, razón_social, ruc, régimen_tributario, sector | Tipo 1 | — |
dim_cliente | cliente_id, ruc/dni, nombre, tipo_doc, segmento, dpto, provincia | Tipo 2 | Geografía; Segmento |
dim_producto | sku, nombre, línea, categoría, talla, color, unidad, partida_arancelaria | Tipo 2 | Categoría→Línea→SKU; (textil: color/talla) |
dim_proveedor | proveedor_id, ruc, nombre, país_origen, es_importado | Tipo 2 | País→Proveedor |
dim_almacen | almacen_id, sucursal_id, tipo, ubicación | Tipo 1 | Sucursal→Almacén |
dim_sucursal | sucursal_id, nombre, región | Tipo 1 | Región→Sucursal |
dim_vendedor | vendedor_id, nombre, equipo, cuota | Tipo 2 | Equipo→Vendedor |
dim_cuenta | código_pcge, descripción, clase, naturaleza, saldo_normal, ef_destino | Tipo 1 | Clase→Cuenta→Divisionaria |
dim_centro_costo | cc_id, nombre, tipo, responsable | Tipo 1 | — |
dim_moneda | código_iso, símbolo | Tipo 1 | — |
dim_tipo_cambio | fecha, moneda, tc_compra, tc_venta, tc_sbs | Tipo 2 | — |
5. Catálogo de KPIs con fórmulas
Todas las fórmulas se expresan en moneda funcional (PEN) y se materializan en la capa semántica. Las cuentas referidas usan clases PCGE.
5.1 KPIs financieros
| KPI | Fórmula | Componentes (origen) | Frecuencia | Benchmark/objetivo |
|---|
| Margen Bruto | (Ventas Netas − Costo de Ventas) / Ventas Netas | fact_ventas.monto_neto_pen, fact_ventas.costo_venta | Diaria | Textil: 35–50% |
| Margen Neto | Utilidad Neta / Ventas Netas | Resultado neto (clase 6/7 GL) | Mensual | ≥ 8% |
| EBITDA | Utilidad Operativa + Depreciación + Amortización | GL: result. operativo + ctas 681/682 | Mensual | ≥ 12% s/ventas |
| Margen EBITDA | EBITDA / Ventas Netas | derivado | Mensual | ≥ 12% |
| ROE | Utilidad Neta / Patrimonio Promedio | result. neto; dim_cuenta clase 5 | Trimestral | ≥ 15% |
| ROA | Utilidad Neta / Activo Total Promedio | clases 1–3 | Trimestral | ≥ 8% |
| Liquidez Corriente | Activo Corriente / Pasivo Corriente | GL ctas corrientes | Mensual | 1.5–2.0 |
| Prueba Ácida | (Activo Corriente − Existencias) / Pasivo Corriente | excluye clase 2 | Mensual | ≥ 1.0 |
| DSO (Días de Cobro) | (Cuentas por Cobrar / Ventas a crédito) × 365 | fact_cobranzas, fact_ventas | Mensual | ≤ 45 días |
| DPO (Días de Pago) | (Cuentas por Pagar / Compras a crédito) × 365 | fact_compras, GL clase 42 | Mensual | 30–60 días |
| Ciclo de Conversión de Efectivo | DSO + DIO − DPO | derivados | Mensual | Minimizar |
| Capital de Trabajo | Activo Corriente − Pasivo Corriente | GL | Mensual | > 0 |
5.2 KPIs operativos / comerciales / inventario
| KPI | Fórmula | Origen | Objetivo |
|---|
| Rotación de Inventario | Costo de Ventas / Inventario Promedio | fact_ventas.costo_venta, fact_inventario_snap | ↑ (textil: 4–8x/año) |
| DIO (Días de Inventario) | 365 / Rotación de Inventario | derivado | ≤ 90 días |
| Ticket Promedio | Ventas Netas / N.º Comprobantes | fact_ventas | Crecer MoM |
| Tasa de Conversión CRM | Oportunidades Ganadas / Total Oportunidades | módulo CRM | ≥ 25% |
| Cumplimiento de Cuota | Ventas Vendedor / Cuota | fact_ventas, dim_vendedor | ≥ 100% |
| Stock-out Rate | SKU sin stock / Total SKU activos | fact_inventario_snap | ≤ 3% |
| Margen por Línea/Color/Talla | margen agregado por jerarquía | fact_ventas × dim_producto | — |
| % Ventas Importado vs Nacional | mix por dim_proveedor.es_importado | fact_ventas | — |
5.3 Tabla de cálculo ilustrativo (EBITDA mensual)
| Concepto | Cuenta PCGE | Monto (PEN) |
|---|
| Ventas netas | 70 | 1,200,000 |
| (−) Costo de ventas | 69 | (720,000) |
| Utilidad bruta | | 480,000 |
| (−) Gastos de ventas | 95 | (180,000) |
| (−) Gastos de administración | 94 | (140,000) |
| Utilidad operativa | | 160,000 |
| (+) Depreciación | 681 | 35,000 |
| (+) Amortización | 682 | 8,000 |
| EBITDA | | 203,000 |
| Margen EBITDA | | 16.9% |
6. Dashboard ejecutivo (C-level)
6.1 Estructura propuesta (React + Recharts sobre capa semántica)
| Widget | Tipo visual | KPI | Drill-down | Refresco |
|---|
| Tarjetas de salud | KPI card + sparkline | EBITDA, Margen Neto, Liquidez, ROE | A estado financiero | Diario |
| Capital de trabajo | Gauge + tendencia | DSO, DPO, CCE | A documentos vencidos | Diario |
| Ventas vs meta | Barras + línea meta | Ventas MTD/YTD | A vendedor→cliente→factura | < 5 min (CDC) |
| Mix de margen | Treemap | Margen por línea/color | A SKU | Diario |
| Rotación inventario | Heatmap por almacén | Rotación, DIO | A SKU lento | Diario |
| Embudo CRM | Funnel | Conversión | A oportunidad | Horario |
| Alertas IA | Lista priorizada | anomalías, forecast | A causa raíz | Diario |
6.3 Seguridad del dashboard
- RLS en capa semántica: cada query inyecta
company_id del JWT del usuario; un usuario nunca ve otra empresa.
- Permisos por rol: el CFO ve finanzas completas; el jefe de tienda solo su sucursal (
dim_sucursal filtrada). Se reutiliza el modelo multirol del módulo Seguridad.
- Auditoría de consultas: log de quién consultó qué KPI (cumplimiento Big Four).
| Caso de uso | Técnica | Insumo (DW) | Salida |
|---|
| Pronóstico de ventas | Series temporales (Prophet / ARIMA) | fact_ventas + dim_tiempo (feriados PE) | Forecast 30/90 días por línea |
| Detección de anomalías | Isolation Forest / z-score | fact_finanzas_gl, fact_ventas | Alertas de gasto/venta atípica |
| Riesgo de cobranza | Clasificación (gradient boosting) | fact_cobranzas, historial cliente | Score de morosidad por cliente |
| Optimización de stock | Punto de reorden + EOQ | fact_inventario_snap, lead times | Sugerencia de reposición |
| NL2SQL / Chat BI | LLM + capa semántica (Cube) | metadatos + KPIs versionados | Respuesta a "¿cuál fue mi margen en mayo?" |
Decisión: el LLM no consulta SQL crudo; opera sobre la capa semántica (definiciones de KPI gobernadas), evitando alucinaciones de métricas y respetando RLS.
8. Riesgos y mitigaciones
| # | Riesgo | Impacto | Mitigación |
|---|
| R1 | Slot de replicación lógica crece y llena disco del RDS si el consumidor cae | Caída del OLTP | Monitoreo de lag del slot; alerta + auto-restart; max_slot_wal_keep_size |
| R2 | Doble conteo por mezcla de granos | KPIs erróneos en comité directivo | Grano documentado por fact + tests dbt de unicidad |
| R3 | Definiciones de KPI divergentes entre áreas | Pérdida de confianza en el BI | Capa semántica única (metrics-as-code), revisión de Finanzas |
| R4 | Fuga de datos entre tenants en dashboards | Brecha contractual/legal | RLS obligatorio en capa semántica + tests de aislamiento |
| R5 | TC SBS no cargado para una fecha | Métricas en PEN incorrectas | Job de carga TC con backfill y validación de cobertura diaria |
| R6 | Costo de DW columnar (Redshift/Snowflake) elevado para PYMEs | Margen del SaaS | Alternativa lakehouse DuckDB+S3/Iceberg para tenants pequeños |
| R7 | Latencia CDC degradada en picos | Stock desfasado | Particionado de topics Kafka + autoscaling de consumidores |
9. Alternativas arquitectónicas evaluadas
| Componente | Opción elegida | Alternativa | Por qué |
|---|
| CDC | Debezium + Kafka/MSK | Triggers + tabla auditoría | WAL no toca tablas calientes; captura deletes; escala mejor |
| DW | Redshift (grandes) / DuckDB+S3 Iceberg (PYMEs) | BigQuery, Snowflake | Costo-eficiencia por segmento; coherencia con AWS del stack |
| Transformación | dbt (ELT) | Spark/EMR (ETL) | dbt = SQL versionado, tests, docs; menor complejidad operativa |
| Capa semántica | Cube | dbt metrics, LookML | Cube expone API REST/SQL/GraphQL con RLS para el dashboard React |
| Visualización embebida | React + Recharts (dashboard ejecutivo) | Power BI Embedded | Control total UX y costo; Metabase/Superset para self-service |
Recomendación de fasing. Fase 1 (MVP): batch nocturno con dbt + DuckDB/S3, dashboard ejecutivo con 12 KPIs núcleo. Fase 2: CDC streaming para ventas/inventario. Fase 3: módulo IA (forecast, anomalías, NL2SQL). Esto entrega valor financiero temprano sin la complejidad operativa del streaming desde el día uno.