ZrobDev/modelado-de-datos

Modelado de datos

Destinos: PostgreSQL, BigQuery, Snowflake. Transformación: dbt. Creación de prototipos local: DuckDB 2.0. Para los aspectos mecánicos de dbt (configuración de modelos, YAML de pruebas, instantáneas, capa semántica, migración v2), utilice las habilidades oficiales de dbt Labs que se incorporan a través de la habilidad de-vendors de este repositorio. Esta habilidad decide qué modelar, no cómo lo escribe dbt.

Procedimiento

1. Indique la pregunta de negocio y el consumidor (panel, característica de ML, cierre financiero).
2. Escriba el grano como una sola frase antes de cualquier columna: "Una fila por línea de pedido por día." Si la frase necesita "o", son dos tablas.
3. Elija el tipo de hecho (a continuación), luego enumere las dimensiones que son verdaderas en ese grano.
4. Asigne un nombre a la clave del grano (columnas naturales o un hash de ellas). Debe tener las restricciones único + no_nulo.
5. Cree un prototipo en DuckDB sobre una muestra, ejecute las comprobaciones de desbordamiento (fan-out), luego migre.
6. Agregue las pruebas listadas en "Qué probar" antes de declarar que está terminado.

Grano

• El grano primero, las columnas después. Cada medida y cada clave foránea deben ser de un solo valor en el grano; una columna que no lo sea pertenece a otra tabla, porque multiplica filas silenciosamente.
• Mantenga el grano más bajo que admita la fuente. Los agregados se pueden derivar del detalle, el detalle no se puede derivar de los agregados. Cree resúmenes como marts separados.
• Nunca mezcle granos en una sola tabla (totales de cabecera de pedido repetidos en filas de líneas de pedido). Los montos de cabecera sumados sobre las filas de líneas se duplican; modele los hechos de cabecera y de línea por separado.

Hechos vs. dimensiones

• Hecho: un evento o medición; numérico, aditivo cuando sea posible, con clave por claves de dimensión más dimensiones degeneradas (número de pedido).
• Dimensión: contexto descriptivo utilizado para filtrar y agrupar. Que sea ancha y desnormalizada está bien.
• Clasifique cada medida: aditiva (suma en todas las dimensiones), semiaditiva (saldos: no a través del tiempo), no aditiva (razones: almacene numerador y denominador, divida después de la agregación).
• Tipos de hechos:
  • Transacción: una fila por evento. Opción predeterminada.
  • Instantánea periódica: una fila por entidad por período (saldo diario). Úselo cuando el estado importe y los eventos sean escasos o tardíos.
  • Instantánea acumulativa: una fila por instancia de proceso con una columna de fecha por hito, actualizada en el lugar. Úselo para procesos con un ciclo de vida conocido (pedido realizado, enviado, entregado). Espere actualizaciones; haga que la carga sea idempotente.
  • Hecho sin medidas: eventos o cobertura sin medida (asistencia, elegibilidad). Cuente las filas.

Forma del esquema

• Esquema estrella por defecto: un hecho, dimensiones planas. Copo de nieve (dimensiones normalizadas) solo cuando una subdimensión es compartida y grande o mantenida por otro equipo; las uniones extra cuestan legibilidad y compatibilidad con herramientas de BI.
• Dimensiones conformadas: un dim_cliente, dim_fecha compartido por cada hecho, mismas claves y significados de atributos. Constrúyalos una vez; una segunda tabla de clientes con reglas diferentes es un defecto, porque los informes entre hechos entonces discrepan.
• Dimensiones con roles múltiples (fecha_pedido, fecha_envío) reutilizan una dim_fecha a través de columnas FK separadas.
• Dimensiones basura/mini para banderas de baja cardinalidad; no deje 8 booleanos en el hecho.
• Miembro desconocido: mantenga una fila (clave -1 o hash de 'desconocido') en cada dimensión para que los hechos que llegan tarde conserven sus filas. Las uniones internas a dimensiones eliminan hechos silenciosamente; use uniones izquierdas y un coalesce a la clave desconocida.

Claves

• Clave natural: el identificador de negocio del sistema fuente. Manténgala siempre como un atributo.
• Clave sustituta: agregue una cuando las claves naturales sean compuestas, puedan cambiar, provengan de varias fuentes, o la dimensión sea SCD2 (una entidad tiene varias versiones). En almacenes de datos al estilo dbt, prefiera un hash determinista de la clave natural (más válido_desde para SCD2) sobre secuencias, para que las reejecuciones y compilaciones paralelas coincidan.
• No exponga las claves sustitutas a los usuarios de negocio como identificadores; cambian al reconstruir.

Dimensiones de cambio lento

• Tipo 1 (sobrescribir): cuando el historial no tiene valor analítico o el cambio es una corrección.
• Tipo 2 (nueva fila por versión, válido_desde / válido_hasta / es_actual): cuando los hechos deben informarse con el valor del atributo en el momento del evento (nivel de cliente en la compra).
• Las instantáneas son suficientes cuando la fuente no guarda historial y solo necesita capturar cambios hacia adelante; son el mecanismo de captura, la dimensión SCD2 sigue siendo un modelo sobre ellas. Si la fuente ya tiene historial o un registro de eventos, derive los rangos de validez a partir de ella en lugar de tomar instantáneas.
• Una los hechos con SCD2 mediante clave más ts_evento >= válido_desde and ts_evento < válido_hasta (rangos semiabiertos, sin superposición). Pruebe que no haya rangos superpuestos o con huecos por clave natural.
• Tipo 3 o tipo 2 por atributo: solo cuando una sola columna de "valor anterior" responda la pregunta; de lo contrario, omítalo.

Desbordamiento (fan-out) y doble conteo

Causa: unir tablas en granos no coincidentes, de modo que el lado "uno" sea en realidad muchos. Síntomas: totales mayores que la fuente, count(*) crece después de una unión izquierda, los promedios cambian.

• Detectar: compare count(*) y count(distinct clave_grano) antes y después de cada unión; deben ser iguales después de una unión a una dimensión. Pruebe la unicidad de la clave de unión en el lado de la dimensión (select key from dim group by 1 having count(*) > 1).
• Conciliar: la suma de la medida en el mart es igual a la suma en la fuente para el mismo filtro.
• Solucionar agregando el lado muchos al grano del hecho antes de unir, o uniendo a través de una tabla puente con pesos de asignación; nunca use distinct en el resultado para ocultarlo.
• Muchos a muchos (cliente a cuenta, producto a categoría): tabla puente con un factor de ponderación, o elija uno primario y explíquelo.
• SQL para estas comprobaciones: consulte referencias/comprobaciones-desbordamiento.md.

Estratificación

• raw/staging: un modelo de staging por tabla fuente; renombrar, convertir, deduplicar, sin uniones, sin lógica de negocio. Mantenga raw inmutable.
• intermedio: uniones, remodelación, lógica compartida reutilizada por 2+ marts. No expuesto a BI.
• marts: hechos y dimensiones, un proceso de negocio por mart, documentado, probado, con contrato. Solo los marts son públicos.
• Las dependencias fluyen en una dirección; los marts no leen raw, y staging nunca lee staging.
• Materialice staging como vistas, marts como tablas (incremental cuando son grandes y la clave del grano es estable).

Nomenclatura

• stg_<fuente>__<entidad>, int_<entidad>_<verbo>, fct_<proceso>, dim_<entidad>; snake_case; plural o singular de forma consistente (elija uno).
• Claves <entidad>_id (natural) y <entidad>_key (sustituta); marcas de tiempo <evento>_at en UTC; fechas <evento>_date; booleanos es_ / tiene_; cantidades llevan una unidad o moneda (cantidad_usd).

Qué probar

• Clave del grano: único + no_nulo, en cada modelo; para granos compuestos pruebe la combinación.
• Cada FK en un hecho: no_nulo (o miembro desconocido) y una prueba de relaciones con la dimensión.
• Valores aceptados en columnas de estado/tipo; un nuevo valor falla ruidosamente en lugar de ser mal categorizado.
• Conciliación de recuento de filas y suma de cada mart con su fuente, con una tolerancia declarada (cero para finanzas).
• SCD2: sin rangos superpuestos, exactamente una fila actual por clave natural.
• Frescura en las fuentes. Rompa cada prueba una vez sobre datos de muestra para demostrar que puede fallar.

Revisión de esquema OLTP (PostgreSQL)

• Normalice a 3NF para tablas transaccionales; desnormalice solo para una ruta de lectura medida. Cada tabla tiene una clave primaria.
• Indexe las columnas de clave foránea: Postgres no lo hace automáticamente, y las FK sin índice hacen que las eliminaciones de padres y las uniones escaneen la tabla hija.
• Use timestamptz, numeric para dinero, text en lugar de límites varchar arbitrarios; aplique reglas con restricciones NOT NULL, CHECK, UNIQUE y FK en lugar de solo el código de la aplicación.
• Prefiera bigint identidad o claves UUIDv7 sobre UUIDv4 aleatorios para tablas grandes con muchas inserciones (localidad de índice).
• Multiinquilino: ponga id_inquilino en cada tabla propiedad del inquilino, conviértalo en la columna principal de claves e índices compuestos, inclúyalo en las FK (FK compuesta) para que las referencias entre inquilinos sean imposibles, y respáldelo con seguridad a nivel de fila. Esquema compartido con tenant_id por defecto; esquema o base de datos por inquilino solo para aislamiento estricto o necesidades de restauración por inquilino.
• Eliminaciones blandas: índices únicos parciales (where deleted_at is null) para que la unicidad aún se mantenga.

Errores comunes y modos de fallo observados: referencias/errores-comunes.md