DBT: Construcción del modelo GOLD / Datamart / Modelo en estrella para BI

Introducción

Una vez que la capa Silver está transformada, limpia y consolidada, podemos construir nuestro modelo GOLD, listo para análisis y consumo BI.
El modelo GOLD representa el modelo en estrella, compuesto por dimensiones y tabla de hechos.
En esta capa se implementan técnicas como cargas incrementales, snapshots, y dimensiones lentamente cambiantes (SCD).

Dimensión cliente SCD2 (lentamente cambiante)

Esta dimensión se crea como tabla persistida.
Configuración: incremental, con política de borrar e insertar para mantener histórico de cambios.
Documentación DBT sobre incremental delete + insert: https://docs.paradime.io/app-help/concepts/dbt-fundamentals/model-materializations/incremental-materialization/using-delete+insert-for-incremental-models

Funcionamiento interno:

DBT compara los registros según el campo customer_id.
Si existen coincidencias, DBT elimina esos registros antes de volver a insertarlos.
La query del modelo debe devolver:
- Registros a actualizar (viejos que se reemplazan).
- Registros nuevos que se insertan.
- Histórico de registros desactivados que se mantienen.
Alternativa: crear una macro con un UPDATE previo, pero no es recomendado por la comunidad DBT.

El campo utilizado para identificar los registros a eliminar es customer_id. Internamente, DBT, antes de actualizar un modelo, verifica que todos los registros de la query existan; si existen, los elimina para luego volver a insertarlos.

El objetivo de esta query para la actualización de datos es asegurar que se incluyan:

Los registros que deben actualizarse (registros antiguos en la dimensión).
Los registros nuevos que se van a insertar.
El histórico de registros desactivados que se deben mantener.

En resumen, DBT elimina todos los registros que devuelve la query del modelo antes de insertar los nuevos, ya que así funciona su mecanismo de actualización.

Como alternativa, es posible crear una macro que ejecute un UPDATE previo a la ejecución del modelo; sin embargo, esta práctica no es recomendada por la comunidad DBT.

El modelo completo es más extenso; sin embargo, en esta imagen se puede observar cómo se obtiene la clave subrogada mediante el hash generado a partir de la combinación de los campos utilizados para la comparación.

Se realizará una prueba utilizando customer_id = 1, modificando el nombre en la capa Bronze (origen), para posteriormente ejecutar nuevamente el modelo y verificar que el proceso funciona correctamente.

Nombre y email actualizado en el origen

El modelo se ejecuta utilizando el siguiente comando:
- dbt run -s dim_customer

Se ha generado un nuevo registro y se ha actualizado el registro anterior.
- Nota: DBT realiza este proceso eliminando el registro anterior para luego volver a insertarlo.

A continuación, se realizará una nueva prueba modificando nuevamente el mismo registro en la capa Bronze (origen).

El modelo ha eliminado los dos registros con customer_id = 1 de la dimensión para insertar tres registros: el registro desactivado, el registro que se acaba de desactivar y el nuevo registro.

Si se ejecuta el modelo sin realizar cambios en el origen, no ocurre ninguna modificación: como DBT no detecta diferencias entre los datos de origen y la dimensión, no se eliminan ni se insertan registros.

El linaje del modelo se representa de la siguiente manera:

Dimensión tiempo: Incremental

Introducción

La dimensión tiempo es fundamental en cualquier modelo en estrella.
Permite analizar métricas a lo largo del tiempo: días, semanas, meses, trimestres y años.
En nuestro proyecto, esta dimensión se construirá de manera incremental, agregando únicamente los nuevos días que no existan en la tabla.

Construcción de la dimensión

El proceso es sencillo, ya que los días y fechas se pueden generar mediante rangos de fechas en SQL o utilizando funciones de generación de series.
Ejemplo de modelo incremental en DBT

Para ejecutar el modelo, se utiliza el siguiente comando:
- dbt run -m dim_time

Se procede a eliminar el registro correspondiente a 20251222.

A continuación, se vuelve a ejecutar el modelo utilizando el siguiente comando:
- dbt run -m dim_time

Dimensión Track: Incremental y reemplazo de registros

Introducción

La dimensión Track representa información sobre canciones o pistas.
A diferencia de una dimensión SCD2, no se mantiene histórico.
Objetivo: cuando un registro cambie en la capa Bronze, se reemplaza el registro antiguo por el nuevo, y se insertan los registros nuevos que no existían previamente.
Este enfoque permite que la dimensión siempre refleje el estado actualizado y vigente.

Construcción incremental en DBT

Se utiliza la materialización incremental de DBT, pero con lógica de replace/update implícito.
Ejemplo de configuración en el modelo DBT

Los datos han sido insertados en la tabla correspondiente.

Se actualizará el registro estableciendo media_type = 2. Como consecuencia, el valor de la clave subrogada cambiará. DBT detectará esta modificación, eliminará el registro existente en la dimensión e insertará el nuevo registro actualizado.

Para actualizar las tablas de Silver que son dependencias de dim_track y poder depurar en VSCode, se ejecuta el siguiente comando:
- dbt run -s +dim_track –exclude dim_track

A continuación, en el visor de VSCode es posible depurar el modelo antes de ejecutar el comando dbt run

En el visor de VSCode se observa que el registro que ha cambiado corresponde a track_id = 1. Por lo tanto, al ejecutar dbt run, DBT eliminará este registro de la dimensión antes de volver a insertarlo.

Para facilitar la depuración, se ha añadido un WHERE fuera de la función is_incremental(). Es importante retirar este WHERE posteriormente, ya que, de lo contrario, en una primera ejecución fallará al no existir aún la tabla dim_track. El WHERE solo es válido una vez que la dimensión ha sido creada, es decir, después de la primera ejecución.

En las imágenes siguientes se puede observar cómo se ha actualizado el valor de la dimensión y cómo se ha modificado también el valor de la clave subrogada sk_track.

Dimensión Empleado: SCD2 (lentamente cambiante)

Introducción

La dimensión Empleado sigue la misma lógica que la dimensión Cliente.
Se construye como SCD2, manteniendo histórico de cambios y permitiendo análisis temporales precisos sobre los empleados.
Cada cambio en los atributos relevantes genera un nuevo registro, mientras que los registros antiguos se marcan como desactivados.

Configuración y materialización

Modelo DBT incremental con delete + insert, utilizando la clave única employee_id para identificar registros a actualizar.
Configuración similar a la dimensión cliente

Funcionamiento

DBT compara los registros en la capa Silver con los existentes en la dimensión.
Si detecta cambios en un registro:
- Elimina el registro antiguo correspondiente.
- Inserta el registro actualizado con una nueva clave subrogada (hash de los campos relevantes).
- Mantiene los registros históricos que no han sido modificados.
Si no hay cambios, DBT no hace modificaciones, garantizando eficiencia en la ejecución.

Ejecución del modelo

Ejecutar la dimensión empleado:
dbt run -s dim_employee
Verificar que:
- Se crean nuevos registros para cambios detectados.
- Se preservan registros históricos.
- Los registros sin cambios permanecen intactos.

Para probar la actualización de los registros de la dimensión employee en la capa Bronze, se modificará la información de manera que el empleado 4 reporte directamente al CEO de la empresa.

Al ejecutar el comando:
- dbt run -m dim_employee
El modelo se actualiza, desactivando el registro anterior e insertando el nuevo registro con la información actualizada.

Tabla de hechos de ventas: Carga incremental

Introducción

La tabla de hechos de ventas representa el núcleo del modelo estrella, integrando información de transacciones, clientes, empleados, tracks y tiempo.
Se construye como modelo incremental, optimizando la actualización y evitando la reconstrucción completa de la tabla.

Clave única y carga incremental

La clave única de la tabla se define como la combinación de invoice_id + invoice_line_id.
DBT compara esta clave para detectar registros existentes:
- Si el registro ya existe, DBT elimina la fila anterior antes de insertarla nuevamente.
- Si es un registro nuevo, simplemente lo inserta.

Relación con la capa SILVER

La tabla de hechos se alimenta de los modelos intermedios Silver:
- clientes_silver
- empleados_silver
- tracks_silver
- dim_tiempo
Durante la primera carga, DBT genera automáticamente una clave subrogada para cada registro, asegurando la integridad y unicidad en la tabla de hechos.

Ejemplo de modelo DBT

Durante la primera ejecución del modelo, se crea la tabla y se insertan todos los registros iniciales en la tabla correspondiente.

El modelo de datos combina las facturas con sus respectivas líneas de factura.
- Un valor de invoice_line_id = -1 indica que se están visualizando los datos de la cabecera de la factura.
- El resto de valores representan los datos de cada línea de la factura.
- Además, para cada línea se incluye el campo total_invoice, que permite calcular métricas de la línea en relación con el total de la factura.

En ejecuciones posteriores, el modelo de DBT obtendrá la fecha de la última factura registrada (o a partir de 7 días atrás) para determinar el listado de registros que deben insertarse o actualizarse. Este comportamiento es similar al de las dimensiones SCD2, donde se gestionan los registros históricos y las actualizaciones de manera incremental.

Ejemplo de funcionamiento del modelo con cargas incrementales:

Si se ejecuta el modelo, todas las facturas posteriores al 22 de septiembre se actualizarán de manera incremental, insertando nuevos registros y actualizando aquellos que hayan sufrido modificaciones.