ETL vs ELT: cómo llegan los datos al warehouse

1-- Staging area: réplica cruda de la fuente
2-- Naming convention: stg_<sistema_fuente>_<tabla>
3
4CREATE SCHEMA staging;
5
6-- Réplica de la tabla orders del sistema operacional
7CREATE TABLE staging.stg_shopify_orders (
8    -- Columnas EXACTAS del sistema fuente (sin transformar)
9    id              VARCHAR(50),
10    customer_id     VARCHAR(50),
11    status          VARCHAR(20),
12    total_amount    VARCHAR(20),     -- ¡Viene como string de la API!
13    currency        VARCHAR(3),
14    created_at      VARCHAR(30),     -- timestamp como string ISO
15    updated_at      VARCHAR(30),
16    -- Metadata del ETL
17    _extracted_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
18    _source_file    VARCHAR(200),
19    _batch_id       VARCHAR(50)
20);
21
22-- Una tabla de staging por tabla de origen. Los pedidos no traen la ciudad ni
23-- el segmento del cliente: eso vive en customers. Si intentas cargar
24-- dim_customer desde stg_shopify_orders, las columnas no están.
25CREATE TABLE staging.stg_shopify_customers (
26    id              VARCHAR(50),
27    name            VARCHAR(200),
28    email           VARCHAR(200),
29    city            VARCHAR(100),
30    segment         VARCHAR(30),
31    updated_at      VARCHAR(30),
32    _extracted_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
33    _source_file    VARCHAR(200),
34    _batch_id       VARCHAR(50)
35);
36
37-- La tabla de control del ETL. No es staging: staging se trunca en cada
38-- ejecución, y la marca de agua tiene que sobrevivir a eso.
39CREATE TABLE etl_control (
40    table_name      VARCHAR(100),
41    loaded_until    TIMESTAMP,
42    loaded_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
43);
44
45-- Notas:
46-- 1. Tipos de dato GENEROSOS (varchar para todo) — no rechazar datos por tipos
47-- 2. Metadata del ETL (_extracted_at, _batch_id) para auditoría
48-- 3. Se TRUNCA y recarga cada ejecución (no es histórica)
49-- 4. etl_control es la excepción: esa NO se trunca nunca

La staging area es tu red de seguridad. Si la transformación falla, puedes re-ejecutarla sin re-extraer.

1-- Patrón: Carga incremental por timestamp
2
3-- 1. Obtener la marca de agua (watermark): hasta dónde llegó la última carga.
4-- Vive en su propia tabla de control, NO en la de staging: staging se trunca en
5-- cada ejecución, así que ahí el máximo sería siempre NULL y cada noche
6-- recargarías el histórico entero creyendo que haces carga incremental.
7SELECT COALESCE(MAX(loaded_until), TIMESTAMP '1900-01-01') AS last_watermark
8FROM etl_control
9WHERE table_name = 'stg_shopify_orders';
10
11-- 2. Extraer solo lo nuevo/modificado de la fuente
12-- (esto se haría desde Python/Spark, aquí es pseudocódigo SQL)
13TRUNCATE staging.stg_shopify_orders;   -- staging arranca vacía en cada carga
14INSERT INTO staging.stg_shopify_orders
15SELECT *, CURRENT_TIMESTAMP AS _extracted_at
16FROM source_shopify.orders
17WHERE updated_at > :last_watermark;  -- solo cambios desde la última vez
18
19-- 3a. Cerrar la versión vigente de los clientes cuyos atributos han cambiado.
20-- Ojo a la tabla: los atributos del cliente están en la staging de CLIENTES,
21-- no en la de pedidos.
22UPDATE dim_customer d
23SET is_current = FALSE,
24    valid_to = CURRENT_DATE - INTERVAL '1 day'
25WHERE d.is_current = TRUE
26  AND EXISTS (
27      SELECT 1 FROM staging.stg_shopify_customers s
28      WHERE s.id = d.customer_id
29        AND (s.city != d.city OR s.segment != d.segment)
30  );
31
32-- 3b. Insertar la versión nueva. Cubre los dos casos de golpe: los que acabamos
33-- de cerrar arriba y los clientes que no existían todavía. Los que no han
34-- cambiado siguen teniendo su fila vigente, así que este INSERT no los toca.
35INSERT INTO dim_customer (customer_id, name, city, segment, is_current, valid_from, valid_to)
36SELECT DISTINCT s.id, s.name, s.city, s.segment, TRUE, CURRENT_DATE, DATE '9999-12-31'
37FROM staging.stg_shopify_customers s
38WHERE NOT EXISTS (
39    SELECT 1 FROM dim_customer d
40    WHERE d.customer_id = s.id AND d.is_current = TRUE
41);
42
43-- 4. Avanzar la marca de agua, y solo ahora: si algo de arriba ha fallado, la
44-- carga de mañana tiene que volver a intentar este mismo tramo.
45-- El CAST no es decorativo: en staging updated_at es VARCHAR (ahí entra todo
46-- sin validar), y loaded_until es TIMESTAMP. Sin castear, o falla o guardas
47-- el máximo ALFABÉTICO de unas cadenas, que no es el máximo cronológico.
48INSERT INTO etl_control (table_name, loaded_until)
49SELECT 'stg_shopify_orders', MAX(CAST(updated_at AS TIMESTAMP))
50FROM staging.stg_shopify_orders;

Carga incremental + SCD Tipo 2 combinados. Este es el patrón real que usarás en producción.

1-- [ERROR] NO IDEMPOTENTE: si se ejecuta 2 veces, duplica datos
2INSERT INTO fact_sales (date_key, customer_key, product_key, amount)
3SELECT date_key, customer_key, product_key, amount
4FROM staging.stg_sales
5WHERE date_key = 20240315;
6
7-- [OK] IDEMPOTENTE: borra y recarga la partición del día
8-- Si se ejecuta 2 veces, el resultado es idéntico
9DELETE FROM fact_sales WHERE date_key = 20240315;
10INSERT INTO fact_sales (date_key, customer_key, product_key, amount)
11SELECT date_key, customer_key, product_key, amount
12FROM staging.stg_sales
13WHERE date_key = 20240315;
14
15-- [OK] ALTERNATIVA IDEMPOTENTE: MERGE/UPSERT con clave natural
16MERGE INTO fact_sales AS target
17USING staging.stg_sales AS source
18ON target.order_number = source.order_number
19   AND target.product_key = source.product_key
20WHEN MATCHED THEN UPDATE SET amount = source.amount
21WHEN NOT MATCHED THEN INSERT (date_key, customer_key, product_key, amount)
22VALUES (source.date_key, source.customer_key, source.product_key, source.amount);

DELETE + INSERT por partición es el patrón idempotente más simple y robusto.