Diseño técnico - DS_cuota

Objetivo

Documentar el diseño técnico del proceso NB_Silver_DS_Cuota, describiendo los componentes, fuentes, transformaciones, enriquecimientos y estrategia de carga utilizados para construir la tabla as4_fact_vtas_cuota en la capa Silver.

Alcance

Dentro del alcance

Parametrización mediante widgets.
Lectura de la tabla fuente de cuotas.
Enriquecimiento con dimensiones migradas.
Cálculo de métricas derivadas.
Resolución de categorías y subcategorías.
Generación de la tabla de hechos final.
Escritura particionada en formato Delta.

Contenido principal

Descripción general

El proceso NB_Silver_DS_Cuota realiza la transformación de un archivo de cuotas mensuales previamente cargado en Bronze hacia una estructura analítica en Silver.

El notebook ejecuta una serie de enriquecimientos utilizando dimensiones de negocio y tablas migradas, complementando la información de canal, producto, grupo de cliente, depósito, tiempo, categoría y subcategoría.

Posteriormente calcula la métrica PLAN_PIEZAS, genera la clave de partición CVE_PART y persiste la información en la tabla as4_fact_vtas_cuota.

Componentes técnicos

Componente	Descripción
Widgets	Parametrizar la ejecución mediante `w_StartDate`, `w_EndDate`, `w_catalog`, `w_schema origin` y `w_schema destiny`.
Tabla fuente	Proveer la información de cuotas de ventas mensuales desde la tabla `27_03_cuota_abril_2026` cargada en Bronze.
Dimensiones migradas	Enriquecer la información mediante dimensiones de canal, producto, grupo de cliente, depósito, tiempo, categoría y subcategoría.
Indicadores comerciales	Incorporar la clasificación frío/seco mediante la tabla `oon_fact_indi_vb_frios_secos`.
Transformaciones SQL	Resolver claves de negocio, atributos descriptivos y cálculos derivados mediante las consultas query, query_1, query_2, query_3 y query_4.
Motor Spark SQL	Ejecutar los joins, enriquecimientos y transformaciones requeridas para la construcción de la fact.
Particionamiento	Generar la clave CVE_PART para controlar la carga y organización de la información.
Tabla de hechos	Persistir la información consolidada en `as4_fact_vtas_cuota`.
Delta Lake	Almacenar la tabla final en formato Delta mediante escritura overwrite.

Entradas y salidas

Entradas

Fuente principal

Fuente principal (Bronze — carga manual):
- cat_master_alpura.sch_bronze_layer.27_03_cuota_abril_2026

Dimensiones

Dimensiones Bronze
Dimensiones Gold

Dimensiones (Bronze — migradas Snowflake):
- snw_dim_canal → lookup canal por CANALSOPP
- snw_dim_productos → lookup producto, FCONKL, ID_SKU
- snw_dim_grupo_cliente → lookup CVE_GPOCLIENTE
- snw_deposit → lookup ID_DEPOSITO via ID_PLADIS
- snw_dim_subcategoria → lookup CVE_SUBCATEGORIA
- snw_dim_categoria → lookup CVE_CATEGORIA

Indicadores

Fact Silver (indicadores):
- oon_fact_indi_vb_frios_secos → DERIVADO_SECO (TEMP)

Salidas

cat_master_alpura.sch_silver_layer.as4_fact_vtas_cuota
- Formato: Delta Lake
- Partición: CVE_PART (YYYYMM)
- Modo escritura: overwrite replaceWhere CVE_PART = w_StartDate
- Registros aprox: 18,773 (para abril 2026)

Flujo tecnico

Limpiar caché: CLEAR CACHE
Cargar librerías del framework: %run /Workspace/Shared/alpura_DEV/00_Framework/NB_FRAMEWORK_LIBRARIES
Definir parámetros (widgets)
- Widgets definidos: w_StartDate, w_EndDate, w_catalog, w_schema origin, w_schema destiny
- Valores en ejecución:
  - w_StartDate = 202604
  - w_catalog = cat_master_alpura
  - w_schema origin = sch_bronze_layer
  - w_schema destiny = sch_silver_layer
Leer variables:
- Asignación de variables Python: v_StartDate, v_EndDate, v_catalog, v_schema_origin, v_schema_destiny.
Generar v_run_id: v_run_id = str(uuid.uuid4())
Cargar tabla fuente Bronze:
- La tabla fuente tiene campos: AÑO, MES, ID CANAL, CANAL, ID_PLAZA, PLAZA, DEPOSITO, ID_GRUPO_CLIENTE, GRUPO_CLIENTE, ZONA_NIELSEN, TIPO_ENTREGA, REG_RESPONSABLE, ID_ORACLE, ID_PRODUCTO, PRODUCTO, PRECIO_LITRO, PLAN_LITROS, PLAN_PESOS, VALUADO, ID_DEPOSITO.
```
fuente = spark.sql("""
    SELECT * FROM cat_master_alpura.sch_bronze_layer.27_03_cuota_abril_2026
""")
fuente = fuente.drop(".")
fuente.createOrReplaceTempView("CUOTA_VENTAS_NVO")
```

query: Primer JOIN con canal

WITH lkp_canal AS (
    SELECT CANALSOPP, MAX(ID_CANAL) AS ID_CANAL
    FROM snw_dim_canal GROUP BY CANALSOPP
)
SELECT
    fact.`AÑO` AS ANIO,
    LOWER(fact.MES) AS MES,
    lc.ID_CANAL,
    fact.CANAL,
    fact.ID_GRUPO_CLIENTE,
    fact.GRUPO_CLIENTE,
    fact.ID_ORACLE AS ORACLE,
    lp.CVE_PRO,
    fact.ID_PRODUCTO,
    fact.ID_PLAZA,
    fact.PLAN_LITROS, fact.PLAN_PESOS, ...
    UPPER(fact.MES) AS MES_DESC,
    ...
FROM CUOTA_VENTAS_NVO fact
LEFT JOIN lkp_canal lc ON lc.CANALSOPP = fact.CANAL
LEFT JOIN lkp_producto lp ON lp.ID_PRO = fact.ID_PRODUCTO

query_1: Agrega FCONKL, ID_SKU, depósito y tiempo
- Incorpora:
  - lkp_fconkl desde snw_dim_productos (FCONKL, ID_SKU)
  - lkp_canal (CVE_CANAL)
  - lkp_grupo_cliente (CVE_GPOCLIENTE)
  - lkp_depositos desde snw_deposit (ID_DEPOSITO via ID_PLADIS)
  - lkp_tiempo desde v_dim_date_esp (CVE_PART)
  - Calcula PLAN_PIEZAS = PLAN_LITROS / FCONKL con CASE WHEN
  - Agrega UPC (= ID_SKU)
query_2: Enriquece con producto, categoría preliminar y derivado_seco
- Incorpora:
  - lkp_producto desde snw_dim_productos (CATEGORIA, SUBCATEGORIA, PRODUCTO)
  - lkp_secos desde oon_fact_indi_vb_frios_secos (TEMP → DERIVADO_SECO)
query_3: Añade IDs de categoría y subcategoría

Incorpora:
- lkp_categoria desde snw_dim_categoria → ID_CATEGORIA
- lkp_sub_categoria desde snw_dim_subcategoria → ID_SUBCATEGORIA

query_4: Filtra y agrega CVE_CATEGORIA, CVE_SUBCATEGORIA

Agrega:
- lkp_categoria → CVE_CATEGORIA
- lkp_sub_categoria → CVE_SUBCATEGORIA
- Aplica filtro: CVE_PART is not null
- Cambia tipos: ID_PLAZA → BIGINT, nombres limpios PLAN_LITROS, PLAN_PESOS, PLAN_PIEZAS

Escritura en Silver (partición CVE_PART)

query_4.write.format("delta") \
   .mode("overwrite") \
   .option("overwriteSchema", "true") \
   .option("mergeSchema", "true") \
   .option("replaceWhere", f"CVE_PART = {v_StartDate}") \
   .saveAsTable(f"{v_catalog}.{v_schema_destiny}.as4_fact_vtas_cuota")

Escritura histórica completa (sin partición)

query_4.write.format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .option("mergeSchema", "true") \
    .saveAsTable("cat_master_alpura.sch_silver_layer.as4_fact_vtas_cuota")

Nota

Existe una segunda escritura en modo overwrite sin replaceWhere, lo que implica que en alguna ejecución se sobrescribe la tabla completa. Revisar si es intencional o bug.

Validación de resultados

Query de suma: FCONKL, PLAN_LITROS, PLAN_PESOS, PLAN_PIEZAS para ANIO=2026, MES='ABRIL'
Query de conteo por CANAL
Query de conteo total: resultado 18,773 registros

Diagrama de flujo técnico

Consideraciones

El nombre de la tabla Bronze fuente está HARDCODEADO, actualizar manualmente por mes.
Existen 2 escrituras: una con replaceWhere (incremental) y una con overwrite (total). Verificar si es intencional.
Se filtra campo "." de la fuente al leer (columna vacía).
PLAN_PIEZAS usa COALESCE para evitar nulos.
CVE_PART filtra registros sin dimensión de tiempo.

Artefactos relacionados

Artefacto	Descripción
Diseño Funcional - DS_cuota	Documenta la arquitectura funcional, componentes y dependencias del proceso.
Fuentes - DS_cuota	Documenta las fuentes de información consumidos por el proceso.
Operación - DS_cuota	Describe los controles operativos y actividades de soporte del proceso.
Pipelines - DS_cuota	Describe la secuencia de ejecución y procesamiento de datos.
Reglas de Negocio - DS_cuota	Describe las reglas apliacadas durante la conformación de la dimensión.
Tablas - DS_cuota	Documenta las tablas origen, intermedias y destino involucradas.
Validaciones - DS_cuota	Documenta controles y validaciones asociados al proceso.

Próximos pasos

Revisar si la segunda escritura en modo overwrite sin la cláusula replaceWhere corresponde al comportamiento esperado o si requiere una corrección en la implementación.

Objetivo​

Alcance​

Dentro del alcance​

Contenido principal​

Descripción general​

Componentes técnicos​

Entradas y salidas​

Entradas​

Fuente principal​

Dimensiones​

Indicadores​

Salidas​

Flujo tecnico​

Diagrama de flujo técnico​

Artefactos relacionados​

Próximos pasos​