Modelado de Datos

Entity-Relationship (ER) Modeling

El modelado Entidad-Relación es la técnica fundamental para diseñar bases de datos relacionales. Define las entidades (cosas del mundo real), sus atributos y las relaciones entre ellas.

Conceptos Clave

Diagrama ER: Sistema de E-Commerce

Implementación SQL del ER

-- Implementación del diagrama ER en PostgreSQL
CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(200) UNIQUE NOT NULL,
    phone        VARCHAR(20),
    created_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE categories (
    category_id        SERIAL PRIMARY KEY,
    name               VARCHAR(100) NOT NULL,
    parent_category_id INTEGER REFERENCES categories(category_id)  -- Self-reference
);

CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    category_id INTEGER NOT NULL REFERENCES categories(category_id),
    name        VARCHAR(200) NOT NULL,
    sku         VARCHAR(50) UNIQUE NOT NULL,
    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock       INTEGER DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE addresses (
    address_id  SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    street      VARCHAR(200) NOT NULL,
    city        VARCHAR(100) NOT NULL,
    country     VARCHAR(100) NOT NULL,
    zip_code    VARCHAR(20)
);

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INTEGER NOT NULL REFERENCES customers(customer_id),
    address_id   INTEGER NOT NULL REFERENCES addresses(address_id),
    order_date   TIMESTAMP DEFAULT NOW(),
    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
    status       VARCHAR(20) DEFAULT 'pending'
                 CHECK (status IN ('pending','processing','shipped','delivered','cancelled'))
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id      INTEGER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id    INTEGER NOT NULL REFERENCES products(product_id),
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price    DECIMAL(10,2) NOT NULL,  -- Precio al momento de la compra
    discount      DECIMAL(5,2) DEFAULT 0 CHECK (discount BETWEEN 0 AND 100)
);

Normalización y Desnormalización

La normalización es el proceso de organizar los datos en una base de datos para reducir redundancia y mejorar la integridad. En Data Engineering, entender cuándo normalizar y cuándo desnormalizar es crítico.

-- ❌ Viola 1NF: múltiples teléfonos en una columna
customer_id | name  | phones
1           | Ana   | "555-1234, 555-5678"  -- No atómico!

-- ✅ Cumple 1NF
customer_id | name | phone_number | phone_type
1           | Ana  | 555-1234     | mobile
1           | Ana  | 555-5678     | home

-- ❌ Viola 2NF: product_name depende solo de product_id, no de la PK completa
-- PK: (order_id, product_id)
order_id | product_id | product_name | quantity
1        | 101        | Laptop       | 2       -- product_name dep. solo de product_id

-- ✅ Cumple 2NF: separar en dos tablas
-- Tabla order_items (PK: order_id, product_id)
order_id | product_id | quantity
1        | 101        | 2

-- Tabla products (PK: product_id)
product_id | product_name
101        | Laptop

-- ❌ Viola 3NF: zip_code → city (dependencia transitiva)
customer_id | name | zip_code | city      -- city depende de zip_code, no de customer_id
1           | Ana  | 28001    | Madrid

-- ✅ Cumple 3NF
-- Tabla customers
customer_id | name | zip_code
1           | Ana  | 28001

-- Tabla zip_codes
zip_code | city
28001    | Madrid

-- Tabla normalizada (OLTP) - requiere 3 JOINs para análisis
SELECT o.order_id, c.name, p.name, oi.quantity
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON oi.product_id = p.product_id;

-- Tabla desnormalizada (DWH) - todo en una tabla wide
SELECT order_id, customer_name, product_name, quantity
FROM fact_order_items;  -- No JOINs necesarios!

-- fact_order_items incluye columns de todas las tablas:
-- order_id, order_date, customer_id, customer_name, customer_email,
-- product_id, product_name, category_name, quantity, unit_price, etc.

Star Schema

El Star Schema (esquema en estrella) es el modelo dimensional más utilizado en Data Warehouses. Fue popularizado por Ralph Kimball y es el estándar para OLAP y BI.

La estructura es simple: una tabla de hechos central conectada a múltiples tablas de dimensión desnormalizadas. La forma resultante del diagrama asemeja una estrella.

Ventajas del Star Schema

• Simplicidad: Fácil de entender para analistas de negocio. Una tabla central y dimensiones claras.
• Performance: Pocos JOINs necesarios. El optimizador de queries puede trabajar eficientemente.
• Compatibilidad BI: Herramientas como Power BI, Tableau y Looker están optimizadas para Star Schema.
• Mantenimiento: Agregar nuevas métricas (columnas en fact) o nuevas dimensiones es relativamente simple.

Implementación SQL: Star Schema de Ventas

-- TABLA DE HECHOS (Fact Table)
CREATE TABLE fact_sales (
    sale_id         BIGSERIAL PRIMARY KEY,
    -- Foreign Keys a dimensiones (surrogate keys)
    date_key        INTEGER NOT NULL,
    customer_key    INTEGER NOT NULL,
    product_key     INTEGER NOT NULL,
    store_key       INTEGER NOT NULL,
    promotion_key   INTEGER,
    -- Métricas (medidas)
    quantity_sold   DECIMAL(10,2) NOT NULL DEFAULT 0,
    sale_amount     DECIMAL(12,2) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    net_amount      DECIMAL(12,2) GENERATED ALWAYS AS (sale_amount - discount_amount) STORED,
    cost_amount     DECIMAL(12,2) NOT NULL DEFAULT 0,
    profit_amount   DECIMAL(12,2) GENERATED ALWAYS AS (net_amount - cost_amount) STORED
) PARTITION BY RANGE (date_key);  -- Particionamiento por fecha

-- Dimensión Fecha (la más importante)
CREATE TABLE dim_date (
    date_key        INTEGER PRIMARY KEY,  -- YYYYMMDD como integer
    full_date       DATE NOT NULL,
    year            SMALLINT NOT NULL,
    quarter         SMALLINT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
    month           SMALLINT NOT NULL CHECK (month BETWEEN 1 AND 12),
    month_name      VARCHAR(10) NOT NULL,
    week            SMALLINT NOT NULL,
    day_of_week     SMALLINT NOT NULL CHECK (day_of_week BETWEEN 1 AND 7),
    day_name        VARCHAR(10) NOT NULL,
    is_weekend      BOOLEAN NOT NULL DEFAULT FALSE,
    is_holiday      BOOLEAN NOT NULL DEFAULT FALSE,
    fiscal_year     SMALLINT,
    fiscal_quarter  SMALLINT
);

-- Poblar dim_date con un rango de fechas
INSERT INTO dim_date
SELECT
    TO_CHAR(d, 'YYYYMMDD')::INTEGER AS date_key,
    d AS full_date,
    EXTRACT(YEAR FROM d)::SMALLINT AS year,
    EXTRACT(QUARTER FROM d)::SMALLINT AS quarter,
    EXTRACT(MONTH FROM d)::SMALLINT AS month,
    TO_CHAR(d, 'Month') AS month_name,
    EXTRACT(WEEK FROM d)::SMALLINT AS week,
    EXTRACT(ISODOW FROM d)::SMALLINT AS day_of_week,
    TO_CHAR(d, 'Day') AS day_name,
    EXTRACT(ISODOW FROM d) IN (6, 7) AS is_weekend,
    FALSE AS is_holiday
FROM GENERATE_SERIES('2020-01-01'::DATE, '2030-12-31'::DATE, '1 day'::INTERVAL) d;

-- Query analítica típica en Star Schema
SELECT
    d.year,
    d.month_name,
    p.category,
    s.country,
    SUM(f.sale_amount)   AS total_sales,
    SUM(f.profit_amount) AS total_profit,
    COUNT(*)             AS transaction_count,
    AVG(f.sale_amount)   AS avg_ticket
FROM fact_sales f
JOIN dim_date     d ON f.date_key     = d.date_key
JOIN dim_product  p ON f.product_key  = p.product_key
JOIN dim_store    s ON f.store_key    = s.store_key
WHERE d.year = 2025
GROUP BY d.year, d.month_name, d.month, p.category, s.country
ORDER BY d.year, d.month, total_sales DESC;

Snowflake Schema

El Snowflake Schema es una variación del Star Schema donde las dimensiones están normalizadas en sub-dimensiones, creando una estructura que asemeja un copo de nieve.

-- SNOWFLAKE: dim_product se divide en tablas normalizadas
-- En lugar de tener category y brand directamente en dim_product...

-- Tabla de categorías separada (normalizada)
CREATE TABLE dim_category (
    category_key    INTEGER PRIMARY KEY,
    category_id     VARCHAR(20),
    category_name   VARCHAR(100),
    parent_cat_key  INTEGER REFERENCES dim_category(category_key)  -- jerarquía
);

-- Tabla de marcas separada
CREATE TABLE dim_brand (
    brand_key   INTEGER PRIMARY KEY,
    brand_id    VARCHAR(20),
    brand_name  VARCHAR(100),
    country     VARCHAR(100)
);

-- dim_product referencia las sub-dimensiones
CREATE TABLE dim_product_snowflake (
    product_key  INTEGER PRIMARY KEY,
    product_id   VARCHAR(20),
    name         VARCHAR(200),
    sku          VARCHAR(50),
    category_key INTEGER REFERENCES dim_category(category_key),  -- FK a sub-dim
    brand_key    INTEGER REFERENCES dim_brand(brand_key),        -- FK a sub-dim
    list_price   DECIMAL(10,2)
);

-- Ahora una query necesita más JOINs:
SELECT p.name, c.category_name, b.brand_name
FROM dim_product_snowflake p
JOIN dim_category c ON p.category_key = c.category_key
JOIN dim_brand b ON p.brand_key = b.brand_key;

Fact Tables y Dimension Tables en Profundidad

Tipos de Fact Tables

-- Cada fila es un ítem de una venta
-- Granularidad: 1 fila por order_item
fact_sales:
sale_id | date_key | customer_key | product_key | quantity | amount
1       | 20260101 | 1001         | 5001        | 2        | 299.98
2       | 20260101 | 1002         | 5002        | 1        | 89.99
3       | 20260101 | 1001         | 5003        | 3        | 150.00

-- Snapshot diario del inventario
-- Granularidad: 1 fila por producto por día
fact_inventory_snapshot:
snapshot_date_key | product_key | warehouse_key | qty_on_hand | qty_reserved | reorder_point
20260101          | 5001        | W01           | 150         | 30           | 50
20260101          | 5002        | W01           | 45          | 10           | 20
20260102          | 5001        | W01           | 148         | 28           | 50

-- Snapshots del saldo bancario diario
fact_account_daily:
date_key | account_key | customer_key | balance | available_credit

-- Proceso de un pedido (se ACTUALIZA con cada etapa)
fact_order_lifecycle:
order_key | date_ordered_key | date_picked_key | date_shipped_key | date_delivered_key | days_to_deliver
1001      | 20260101         | 20260102        | 20260103         | 20260106           | 5
1002      | 20260101         | NULL            | NULL             | NULL               | NULL  -- aún en proceso

-- Ventajas: análisis de duración de etapas, identificar cuellos de botella
-- Cuánto tiempo entre pedido y envío?
SELECT AVG(date_shipped_key - date_ordered_key) as avg_days_to_ship
FROM fact_order_lifecycle
WHERE date_shipped_key IS NOT NULL;

Tipos de Dimensiones

Slowly Changing Dimensions (SCD)

Las SCDs resuelven uno de los problemas más comunes del DWH: ¿qué pasa cuando cambia un atributo de una dimensión? Por ejemplo, un cliente cambia de ciudad, o un producto cambia de categoría. ¿Cómo manejas el historial?

-- El campo birth_date nunca se actualiza
UPDATE dim_customer SET birth_date = '...'  -- ¡PROHIBIDO!

-- Simple UPDATE - pierde historial
UPDATE dim_customer
SET email = 'new@email.com'
WHERE customer_key = 1001;

-- Dimensión con soporte SCD2
customer_key | customer_id | name | city     | is_current | valid_from | valid_to
1001         | C001        | Ana  | Madrid   | FALSE      | 2020-01-01 | 2024-06-15
1550         | C001        | Ana  | Barcelona| TRUE       | 2024-06-15 | 9999-12-31

-- El customer_id (natural key) identifica al cliente real
-- customer_key (surrogate key) identifica la versión histórica

-- Solo guarda el valor actual y el anterior
customer_key | name | current_city | previous_city | changed_at
1001         | Ana  | Barcelona    | Madrid        | 2024-06-15

-- dim_customer: solo valor actual
-- dim_customer_history: todo el historial
customer_hist_id | customer_key | city   | changed_at
1                | 1001         | Madrid | 2020-01-01
2                | 1001         | BCN    | 2024-06-15

customer_key | city      | current_city | is_current | valid_from | valid_to
1001         | Madrid    | Barcelona    | FALSE      | 2020-01-01 | 2024-06-15
1550         | Barcelona | Barcelona    | TRUE       | 2024-06-15 | 9999-12-31

Implementación SCD Tipo 2 con dbt

-- dbt: snapshot para SCD Tipo 2 automático
-- Archivo: snapshots/dim_customer_snapshot.sql
{% snapshot dim_customer_snapshot %}
  {{
    config(
      target_database='dw',
      target_schema='snapshots',
      unique_key='customer_id',
      strategy='timestamp',
      updated_at='updated_at',
    )
  }}

  SELECT
    customer_id,
    name,
    email,
    city,
    country,
    customer_segment,
    updated_at
  FROM {{ source('operational', 'customers') }}

{% endsnapshot %}

-- dbt genera automáticamente las columnas:
-- dbt_scd_id, dbt_updated_at, dbt_valid_from, dbt_valid_to, dbt_is_current

Data Vault

Data Vault es una metodología de modelado de datos para DWH enterprise, desarrollada por Dan Linstedt. Está optimizada para auditabilidad, flexibilidad a cambios y carga paralela. Es especialmente popular en industrias reguladas (banking, insurance).

Los 3 Componentes de Data Vault

-- DATA VAULT: Hub, Link y Satellite

-- HUB CUSTOMER: solo business key + metadata
CREATE TABLE hub_customer (
    customer_hash_key  BYTEA PRIMARY KEY,  -- Hash de la business key
    customer_id        VARCHAR(50) NOT NULL,  -- Business key natural
    load_timestamp     TIMESTAMP NOT NULL DEFAULT NOW(),
    record_source      VARCHAR(100) NOT NULL  -- De dónde vino el dato
);

-- HUB ORDER
CREATE TABLE hub_order (
    order_hash_key  BYTEA PRIMARY KEY,
    order_id        VARCHAR(50) NOT NULL,
    load_timestamp  TIMESTAMP NOT NULL DEFAULT NOW(),
    record_source   VARCHAR(100) NOT NULL
);

-- LINK: relación Order → Customer (muchos a muchos)
CREATE TABLE link_order_customer (
    link_hash_key      BYTEA PRIMARY KEY,
    order_hash_key     BYTEA NOT NULL REFERENCES hub_order(order_hash_key),
    customer_hash_key  BYTEA NOT NULL REFERENCES hub_customer(customer_hash_key),
    load_timestamp     TIMESTAMP NOT NULL DEFAULT NOW(),
    record_source      VARCHAR(100) NOT NULL
);

-- SATELLITE: atributos del customer (con historial automático)
CREATE TABLE sat_customer_details (
    customer_hash_key  BYTEA NOT NULL REFERENCES hub_customer(customer_hash_key),
    load_timestamp     TIMESTAMP NOT NULL,
    load_end_timestamp TIMESTAMP,          -- NULL = registro activo
    hash_diff          BYTEA NOT NULL,     -- Hash de todos los atributos (para detección de cambios)
    name               VARCHAR(100),
    email              VARCHAR(200),
    city               VARCHAR(100),
    country            VARCHAR(100),
    record_source      VARCHAR(100) NOT NULL,
    PRIMARY KEY (customer_hash_key, load_timestamp)
);

Data Vault vs Star Schema: ¿Cuándo usar cada uno?