Proyecto: diseñar el warehouse completo de un e-commerce

1-- ═══ SISTEMA FUENTE: PostgreSQL operacional de TechMart ═══
2
3-- Clientes
4CREATE TABLE app.customers (
5    id              SERIAL PRIMARY KEY,
6    email           VARCHAR(200) UNIQUE,
7    first_name      VARCHAR(100),
8    last_name       VARCHAR(100),
9    phone           VARCHAR(20),
10    created_at      TIMESTAMP,
11    updated_at      TIMESTAMP
12);
13
14CREATE TABLE app.addresses (
15    id              SERIAL PRIMARY KEY,
16    customer_id     INTEGER REFERENCES app.customers(id),
17    street          VARCHAR(300),
18    city            VARCHAR(100),
19    state           VARCHAR(50),
20    country         VARCHAR(50),
21    postal_code     VARCHAR(10),
22    is_default      BOOLEAN
23);
24
25-- Productos
26CREATE TABLE app.products (
27    id              SERIAL PRIMARY KEY,
28    sku             VARCHAR(20) UNIQUE,
29    name            VARCHAR(300),
30    description     TEXT,
31    category_id     INTEGER REFERENCES app.categories(id),
32    brand_id        INTEGER REFERENCES app.brands(id),
33    price           DECIMAL(10,2),
34    cost            DECIMAL(10,2),
35    stock           INTEGER,
36    is_active       BOOLEAN,
37    created_at      TIMESTAMP,
38    updated_at      TIMESTAMP
39);
40
41CREATE TABLE app.categories (
42    id              SERIAL PRIMARY KEY,
43    name            VARCHAR(50),
44    parent_id       INTEGER REFERENCES app.categories(id)
45);
46
47CREATE TABLE app.brands (
48    id              SERIAL PRIMARY KEY,
49    name            VARCHAR(100),
50    country         VARCHAR(50)
51);
52
53-- Pedidos
54CREATE TABLE app.orders (
55    id              SERIAL PRIMARY KEY,
56    customer_id     INTEGER REFERENCES app.customers(id),
57    status          VARCHAR(20), -- pending, confirmed, shipped, delivered, cancelled
58    channel         VARCHAR(20), -- web, app, marketplace
59    total_amount    DECIMAL(12,2),
60    discount_code   VARCHAR(30),
61    shipping_cost   DECIMAL(8,2),
62    created_at      TIMESTAMP,
63    confirmed_at    TIMESTAMP,
64    shipped_at      TIMESTAMP,
65    delivered_at    TIMESTAMP
66);
67
68CREATE TABLE app.order_items (
69    id              SERIAL PRIMARY KEY,
70    order_id        INTEGER REFERENCES app.orders(id),
71    product_id      INTEGER REFERENCES app.products(id),
72    quantity        INTEGER,
73    unit_price      DECIMAL(10,2),
74    discount_pct    DECIMAL(5,2)
75);

Esquema normalizado típico de una app. 8 tablas con FKs cruzadas. No apto para análisis directo.

1-- ═══ DIMENSIONES CONFORMED DE TECHMART ═══
2
3CREATE TABLE dim_date (
4    date_key        INTEGER PRIMARY KEY,   -- YYYYMMDD
5    full_date       DATE NOT NULL,
6    day_name        VARCHAR(10),
7    month_name      VARCHAR(10),
8    month_number    SMALLINT,
9    quarter         SMALLINT,
10    year            SMALLINT,
11    is_weekend      BOOLEAN,
12    is_holiday      BOOLEAN,
13    fiscal_quarter  SMALLINT,
14    week_of_year    SMALLINT
15);
16
17CREATE TABLE dim_customer (
18    customer_key        BIGINT PRIMARY KEY,
19    customer_id         INTEGER NOT NULL,      -- natural key de app.customers
20    full_name           VARCHAR(200),
21    email               VARCHAR(200),
22    country             VARCHAR(50),
23    city                VARCHAR(100),
24    -- Segmentación (calculada por el warehouse, no por la app)
25    customer_segment    VARCHAR(30),           -- 'VIP', 'Regular', 'Nuevo', 'Inactivo'
26    acquisition_month   VARCHAR(7),            -- '2023-04'
27    lifetime_orders     INTEGER,
28    -- SCD Tipo 2 para: country, city, customer_segment
29    is_current          BOOLEAN DEFAULT TRUE,
30    valid_from          DATE,
31    valid_to            DATE DEFAULT '9999-12-31'
32);
33
34CREATE TABLE dim_product (
35    product_key         BIGINT PRIMARY KEY,
36    product_id          INTEGER NOT NULL,
37    sku                 VARCHAR(20),
38    product_name        VARCHAR(300),
39    category            VARCHAR(50),           -- desnormalizado (no FK a otra tabla)
40    parent_category     VARCHAR(50),           -- jerarquía plana
41    brand               VARCHAR(100),
42    brand_country       VARCHAR(50),
43    current_price       DECIMAL(10,2),
44    current_cost        DECIMAL(10,2),
45    -- SCD Tipo 2 para: category, current_price
46    is_current          BOOLEAN DEFAULT TRUE,
47    valid_from          DATE,
48    valid_to            DATE DEFAULT '9999-12-31'
49);
50
51CREATE TABLE dim_channel (
52    channel_key         BIGINT PRIMARY KEY,
53    channel_name        VARCHAR(30),           -- 'Web', 'App Móvil', 'Marketplace'
54    channel_type        VARCHAR(20),           -- 'owned', 'third_party'
55    platform            VARCHAR(50)            -- 'techmart.com', 'iOS App', 'Amazon'
56);

Dimensiones conformed compartidas entre las 3 fact tables. Nota el SCD Tipo 2 en customer y product.

1-- ═══ FACT TABLES DE TECHMART ═══
2
3-- 1. TRANSACCIONAL: cada línea de venta
4CREATE TABLE fact_sales (
5    sales_key           BIGINT PRIMARY KEY,
6    -- FKs a dimensiones
7    date_key            INTEGER NOT NULL REFERENCES dim_date(date_key),
8    customer_key        INTEGER NOT NULL REFERENCES dim_customer(customer_key),
9    product_key         INTEGER NOT NULL REFERENCES dim_product(product_key),
10    channel_key         INTEGER NOT NULL REFERENCES dim_channel(channel_key),
11    -- Dimensión degenerada
12    order_number        VARCHAR(20),
13    -- Métricas aditivas
14    quantity            SMALLINT NOT NULL,
15    unit_price          DECIMAL(10,2) NOT NULL,
16    unit_cost           DECIMAL(10,2) NOT NULL,
17    discount_amount     DECIMAL(10,2) DEFAULT 0,
18    net_revenue         DECIMAL(12,2) NOT NULL,  -- qty * price * (1-discount)
19    gross_profit        DECIMAL(12,2) NOT NULL,  -- net_revenue - (qty * cost)
20    shipping_allocated  DECIMAL(8,2)             -- shipping prorrateado por línea
21);
22
23-- 2. ACCUMULATING SNAPSHOT: ciclo de vida del pedido
24CREATE TABLE fact_order_fulfillment (
25    order_key           BIGINT PRIMARY KEY,
26    order_number        VARCHAR(20),
27    customer_key        INTEGER REFERENCES dim_customer(customer_key),
28    channel_key         INTEGER REFERENCES dim_channel(channel_key),
29    -- Date keys por hito (se llenan progresivamente)
30    created_date_key    INTEGER REFERENCES dim_date(date_key),
31    confirmed_date_key  INTEGER,
32    shipped_date_key    INTEGER,
33    delivered_date_key  INTEGER,
34    -- Métricas
35    order_amount        DECIMAL(12,2),
36    num_items           SMALLINT,
37    -- Duración entre hitos
38    hours_to_confirm    INTEGER,
39    hours_to_ship       INTEGER,
40    hours_to_deliver    INTEGER,
41    total_hours         INTEGER,
42    -- Estado actual
43    current_status      VARCHAR(20)
44);
45
46-- 3. SNAPSHOT PERIÓDICO: inventario diario
47CREATE TABLE fact_daily_inventory (
48    date_key            INTEGER NOT NULL REFERENCES dim_date(date_key),
49    product_key         INTEGER NOT NULL REFERENCES dim_product(product_key),
50    -- Métricas semi-aditivas
51    quantity_on_hand    INTEGER,
52    quantity_reserved   INTEGER,
53    days_of_supply      DECIMAL(5,1),
54    is_out_of_stock     BOOLEAN,
55    PRIMARY KEY (date_key, product_key)
56);

3 fact tables para 3 procesos: ventas (transaccional), fulfillment (accumulating) e inventario (snapshot).

1-- ═══ ETL NOCTURNO DE TECHMART ═══
2-- Se ejecuta cada día a las 02:00
3
4-- PASO 1: Extraer a staging (carga incremental por updated_at)
5-- Python extrae de app.customers WHERE updated_at > last_watermark
6-- Python extrae de app.orders WHERE updated_at > last_watermark
7-- Python extrae de app.order_items via JOIN con orders nuevas
8-- Python extrae de app.products WHERE updated_at > last_watermark
9
10-- PASO 2: Cargar dim_customer (SCD Tipo 2 para segment/city)
11-- Detectar cambios:
12WITH customer_changes AS (
13    SELECT
14        s.id AS customer_id,
15        s.first_name || ' ' || s.last_name AS full_name,
16        a.country, a.city,
17        CASE
18            WHEN o.total_orders >= 10 AND o.total_spent >= 1000 THEN 'VIP'
19            WHEN o.total_orders >= 3 THEN 'Regular'
20            WHEN o.first_order > CURRENT_DATE - 90 THEN 'Nuevo'
21            ELSE 'Inactivo'
22        END AS customer_segment
23    FROM staging.stg_customers s
24    LEFT JOIN staging.stg_addresses a ON s.id = a.customer_id AND a.is_default
25    LEFT JOIN (
26        SELECT customer_id, COUNT(*) total_orders, SUM(total_amount) total_spent,
27               MIN(created_at) first_order
28        FROM staging.stg_orders WHERE status != 'cancelled'
29        GROUP BY customer_id
30    ) o ON s.id = o.customer_id
31)
32-- Cerrar versiones que cambiaron + insertar nuevas (SCD Tipo 2)
33-- (ver patrón de lección 5)
34
35-- PASO 3: Cargar dim_product (SCD Tipo 2 para category/price)
36-- Similar: detectar cambios en categoría o precio → nueva versión
37
38-- PASO 4: Cargar fact_sales (idempotente: DELETE + INSERT del día)
39DELETE FROM fact_sales
40WHERE date_key = CAST(strftime(CURRENT_DATE - INTERVAL '1 day', '%Y%m%d') AS INTEGER);
41
42INSERT INTO fact_sales (date_key, customer_key, product_key, channel_key,
43                        order_number, quantity, unit_price, unit_cost,
44                        discount_amount, net_revenue, gross_profit)
45SELECT
46    CAST(strftime(o.confirmed_at::DATE, '%Y%m%d') AS INTEGER),
47    dc.customer_key,
48    dp.product_key,
49    dch.channel_key,
50    'ORD-' || o.id,
51    oi.quantity,
52    oi.unit_price,
53    p.cost,
54    oi.quantity * oi.unit_price * COALESCE(oi.discount_pct, 0) / 100,
55    oi.quantity * oi.unit_price * (1 - COALESCE(oi.discount_pct, 0) / 100),
56    oi.quantity * oi.unit_price * (1 - COALESCE(oi.discount_pct, 0) / 100)
57        - oi.quantity * p.cost
58FROM staging.stg_orders o
59JOIN staging.stg_order_items oi ON o.id = oi.order_id
60JOIN staging.stg_products p ON oi.product_id = p.id
61LEFT JOIN dim_customer dc ON o.customer_id = dc.customer_id AND dc.is_current
62LEFT JOIN dim_product dp ON oi.product_id = dp.product_id AND dp.is_current
63LEFT JOIN dim_channel dch ON o.channel = dch.channel_name
64WHERE o.status = 'confirmed'
65  AND o.confirmed_at::DATE = CURRENT_DATE - 1;

ETL completo: staging → dims (SCD) → facts (idempotente). Este es el pipeline real de producción.

1-- ═══ QUERIES DE NEGOCIO SOBRE EL WAREHOUSE ═══
2
3-- 1. Revenue mensual por canal (dashboard principal)
4SELECT d.year, d.month_name, ch.channel_name,
5       SUM(f.net_revenue) AS revenue,
6       COUNT(DISTINCT f.order_number) AS orders
7FROM fact_sales f
8JOIN dim_date d ON f.date_key = d.date_key
9JOIN dim_channel ch ON f.channel_key = ch.channel_key
10WHERE d.year = 2024
11GROUP BY d.year, d.month_name, d.month_number, ch.channel_name
12ORDER BY d.month_number;
13
14-- 2. Top 10 productos por margen (para compras)
15SELECT p.product_name, p.brand, p.category,
16       SUM(f.gross_profit) AS total_profit,
17       SUM(f.gross_profit) / NULLIF(SUM(f.net_revenue), 0) * 100 AS margin_pct
18FROM fact_sales f
19JOIN dim_product p ON f.product_key = p.product_key
20WHERE f.date_key >= 20240101
21GROUP BY p.product_name, p.brand, p.category
22ORDER BY total_profit DESC LIMIT 10;
23
24-- 3. Tiempo medio de fulfillment por canal (SLA check)
25SELECT ch.channel_name,
26       AVG(f.total_hours) AS avg_hours_total,
27       AVG(f.hours_to_ship) AS avg_hours_to_ship,
28       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY f.total_hours) AS p95_hours
29FROM fact_order_fulfillment f
30JOIN dim_channel ch ON f.channel_key = ch.channel_key
31WHERE f.delivered_date_key IS NOT NULL
32  AND f.created_date_key >= 20240101
33GROUP BY ch.channel_name;
34
35-- 4. Productos en riesgo de rotura de stock
36SELECT p.product_name, p.category,
37       inv.quantity_on_hand, inv.days_of_supply
38FROM fact_daily_inventory inv
39JOIN dim_product p ON inv.product_key = p.product_key
40JOIN dim_date d ON inv.date_key = d.date_key
41WHERE d.full_date = CURRENT_DATE - 1
42  AND inv.days_of_supply < 7
43  AND p.is_current = TRUE
44ORDER BY inv.days_of_supply ASC;

Queries simples gracias al buen diseño. Un analista junior puede escribir cualquiera de estas.

1-- Data dictionary como comentarios SQL (funciona en PostgreSQL/Redshift)
2COMMENT ON TABLE fact_sales IS
3    'Tabla de hechos transaccional. Grain: una fila por línea de pedido confirmado.
4     Actualización: diaria a las 02:00 (ETL nocturno). Retención: 5 años.';
5
6COMMENT ON COLUMN fact_sales.net_revenue IS
7    'Revenue neto = quantity * unit_price * (1 - discount_pct/100).
8     Aditiva en todas las dimensiones. Fuente: app.order_items.';
9
10COMMENT ON COLUMN fact_sales.gross_profit IS
11    'Beneficio bruto = net_revenue - (quantity * unit_cost).
12     Aditiva. Útil para análisis de margen por producto/categoría.';
13
14COMMENT ON TABLE dim_customer IS
15    'Dimensión de cliente. SCD Tipo 2 en: country, city, customer_segment.
16     SCD Tipo 1 en: email, phone. customer_segment se recalcula cada noche.';

COMMENT ON es infrautilizado. Es documentación que vive DENTRO de la base de datos, siempre actualizada.