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 (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

Dimensiones Gold

• Dimensiones (Gold — vista):
  • v_dim_date_esp → lookup CVE_PART (partición)
  • Dimensiones generadas (notebooks DIM_*):
  • snw_dim_categoria → generada por DIM_CATEGORIA
  • snw_dim_subcategoria → generada por DIM_SUBCATEGORIA

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

1. Limpiar caché: CLEAR CACHE

2. Cargar librerías del framework: %run /Workspace/Shared/alpura_DEV/00_Framework/NB_FRAMEWORK_LIBRARIES

3. 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

4. Leer variables:

   • Asignación de variables Python: v_StartDate, v_EndDate, v_catalog, v_schema_origin, v_schema_destiny.

5. Generar v_run_id: v_run_id = str(uuid.uuid4())

6. 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")

7. 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

8. 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)

9. 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)

10. 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

11. 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

12. 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")

13. 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.

14. 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.