Prueba Técnica — Pipeline BI E-Commerce con dbt

Descripción

Pipeline de transformación de datos para una empresa de ecommerce, construido con dbt sobre datos crudos con problemas de calidad intencionales. El proyecto implementa una arquitectura de capas (staging → intermediate → marts) que entrega datos limpios y confiables para su análisis.

Propuesta de Valor para Manuable

Este pipeline no es solo un ejercicio técnico — está diseñado pensando en las necesidades reales del rol:

• Dashboards operativos: mart_orders_summary está listo para conectarse directamente a Metabase o Looker Studio, con datos limpios y métricas pre-calculadas.
• Pricing y catálogo: mart_product_performance (modelo adicional) clasifica productos por rendimiento, habilitando decisiones de pricing basadas en datos y optimización de catálogo.
• Segmentación de clientes: mart_customer_ltv permite identificar VIPs, clientes nuevos y oportunidades de retención — input directo para campañas y estrategia comercial.
• Escalabilidad: La arquitectura de capas y la lógica centralizada en macros permiten agregar nuevas fuentes de datos o reglas de negocio (ej. costeo, descuentos) sin reestructurar el pipeline.
• Automatización: El DAG de Airflow ejecuta el pipeline completo diariamente con validación de calidad en cada paso. Si los datos fallan las validaciones, el pipeline se detiene antes de contaminar los marts.

Uso de Inteligencia Artificial en el Desarrollo

Para este proyecto use asistencia utilizando Claude y Claude Code como herramientas de asistencia en el desarrollo, aplicando técnicas de:

• Context management: Archivo CLAUDE.md como contexto persistente del proyecto para Claude Code, incluyendo estructura, reglas de negocio, datos de muestra y convenciones.
• Prompt engineering: Prompts estructurados para generación de modelos dbt, tests, y documentación con especificaciones claras de inputs/outputs esperados.
• Iteración asistida: Ciclo de desarrollo acelerado — Claude Code ejecuta dbt run/dbt test, detecta errores y propone correcciones en el mismo flujo.

Esto demuestra cómo la IA puede integrarse en flujos de trabajo de analytics engineering para reducir tiempos de desarrollo sin sacrificar calidad ni comprensión del código.

Stack Tecnológico

Herramienta	Uso
dbt-core 1.11.7	Transformación y testing de datos
DuckDB 1.10.1	Base de datos local para desarrollo
Apache Airflow	Orquestación del pipeline (DAG)
Git	Control de versiones
Claude / Claude Code	Asistencia IA en desarrollo
BigQuery	Target de producción (sintaxis compatible)

Nota: El proyecto usa DuckDB como adaptador local para desarrollo y testing. Todo el SQL es compatible con BigQuery para despliegue en producción.

Estructura del Proyecto

manuable_ecommerce/
├── models/
│   ├── staging/           ← Limpieza 1:1 de tablas raw
│   │   ├── stg_orders.sql
│   │   ├── stg_customers.sql
│   │   ├── stg_products.sql
│   │   ├── stg_order_items.sql
│   │   ├── _stg_sources.yml
│   │   └── _stg_schema.yml
│   ├── intermediate/      ← Joins y lógica de negocio
│   │   ├── int_orders_items.sql
│   │   ├── int_orders_revenue.sql
│   │   └── _int_schema.yml
│   └── marts/             ← Tablas finales para consumo
│       ├── mart_orders_summary.sql
│       ├── mart_customer_ltv.sql
│       ├── mart_product_performance.sql  ← Modelo adicional
│       └── _marts_schema.yml
├── macros/
│   ├── normalize_text.sql
│   └── calculate_customer_segment.sql
├── seeds/                 ← Datos de prueba (CSV)
├── tests/
│   └── assert_positive_revenue.sql
├── dags/
│   └── dbt_ecommerce_pipeline.py
├── analyses/
│   ├── top_products_quarterly.sql
│   └── respuesta_teorica_bigquery.md
├── docs/
│   ├── lineage_graph.png
│   └── dbt_docs_interface.png
└── dbt_project.yml

Cómo Ejecutar el Proyecto

Prerrequisitos

• Python 3.11+
• Git

Instalación

# Clonar el repositorio
git clone <URL_DEL_REPOSITORIO>
cd manuable-dbt-assessment/manuable_ecommerce

# Crear entorno virtual
python -m venv .venv

# Activar entorno virtual
# Windows PowerShell:
.venv\Scripts\activate
# Linux/Mac:
source .venv/bin/activate

# Instalar dependencias
pip install -r requirements.txt

Configuración

Crear el archivo ~/.dbt/profiles.yml:

manuable_ecommerce:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      threads: 4

Ejecución

# 1. Verificar configuración
dbt debug

# 2. Cargar datos de prueba
dbt seed

# 3. Ejecutar modelos
dbt run

# 4. Ejecutar tests (28 tests)
dbt test

# 5. Generar documentación
dbt docs generate
dbt docs serve

Problemas de Calidad Resueltos

Los datos crudos contienen problemas intencionales que el pipeline resuelve:

Problema	Tabla	Solución
Orden duplicada (O002)	raw_orders	Deduplicación con ROW_NUMBER() en stg_orders
Fecha nula (O003)	raw_orders	Filtrado en stg_orders
Status inconsistente (COMPLETED vs completed)	raw_orders	Normalización con macro normalize_text
Cliente inexistente (C99)	raw_orders	Filtro EXISTS en mart_orders_summary
Nombre nulo (C03)	raw_customers	COALESCE con valor 'unknown' en stg_customers
Producto inexistente (P99)	raw_order_items	INNER JOIN en int_orders_items descarta items sin producto válido
Cantidad negativa (O005, -1)	raw_order_items	Filtro quantity > 0 en stg_order_items

Modelos

Marts (tablas finales)

mart_orders_summary — Una fila por orden con métricas agregadas:

• order_id, customer_id, order_date
• total_items, total_revenue
• order_status (normalizado)

mart_customer_ltv — Una fila por cliente con valor de vida:

• customer_id, customer_name, email
• first_order_date, last_order_date
• total_orders, total_revenue
• customer_segment: 'VIP' (revenue > $5,000), 'Regular' (revenue > $500), 'Nuevo' (< 2 órdenes)

mart_product_performance (valor agregado) — Una fila por producto con métricas de rendimiento:

• product_id, product_name, unit_price
• total_orders, total_units_sold, total_revenue, avg_order_value
• first_sale_date, last_sale_date
• product_tier: 'Estrella', 'Estable', 'Bajo rendimiento'

Macros Reutilizables

• normalize_text: Aplica lower(trim()) a cualquier columna. Usada en stg_orders, stg_customers, stg_products.
• calculate_customer_segment: Clasifica clientes por revenue y cantidad de órdenes. Usada en mart_customer_ltv.

Tests

El proyecto incluye 28 tests que validan la integridad de los datos:

• 11 tests unique: Garantizan que las PKs no tienen duplicados
• 9 tests not_null: Validan que columnas críticas no son nulas
• 4 tests accepted_values: Verifican valores permitidos (status, segment, tier)
• 1 test relationships: Valida integridad referencial (customer_id)
• 1 test singular: Regla de negocio — ninguna orden con revenue ≤ 0

Source Freshness

Configurado en _stg_sources.yml para alertar si raw.orders tiene más de 24 horas sin actualizarse.

dbt source freshness

Orquestación con Airflow

El DAG (dags/dbt_ecommerce_pipeline.py) ejecuta el pipeline diariamente a las 2:00 AM:

dbt run staging → dbt test staging → dbt run marts → dbt test marts

Si cualquier paso falla, el pipeline se detiene automáticamente (comportamiento por defecto de Airflow).

Documentación Visual

Lineage Graph

Flujo completo del pipeline: sources (verde) → staging → intermediate → marts, incluyendo el test singular y la query analítica.

Interfaz de Documentación dbt

Vista de la documentación auto-generada mostrando mart_product_performance con sus columnas, tipos de dato, descriptions y tests configurados.

Consulta Analítica (Sección 4.2)

La query en analyses/top_products_quarterly.sql responde: "¿Cuáles son los 5 productos más vendidos por mes durante el último trimestre, y cuál fue su variación porcentual en revenue vs el mes anterior?"

Utiliza window functions:

• LAG() para obtener el revenue del mes anterior
• RANK() para clasificar los top 5 por mes
• SAFE_DIVIDE() para calcular variación porcentual