Cómo hacer un upsert idempotente en ELT con MERGE

Cuando un pipeline de datos se ejecuta más de una vez — porque falló a mitad, porque se reprocesó o simplemente porque está programado — corremos el riesgo de duplicar filas en la tabla de destino. Una carga idempotente resuelve esto: podemos ejecutarla las veces que queramos y el resultado final es siempre el mismo. Aquí aprenderás a hacer un upsert idempotente en ELT con la instrucción MERGE, para actualizar e insertar datos sin crear duplicados.

Requisitos previos

• Acceso a una base de datos o warehouse que soporte MERGE (SQL Server, Azure Synapse, Snowflake, Databricks/Delta Lake, BigQuery, Oracle o PostgreSQL 15+).
• Una tabla de staging con los datos ya cargados (el "load" del ELT).
• Conocimientos básicos de SQL (SELECT, INSERT, UPDATE).

Paso 1: Entender qué es una carga idempotente

En ELT (Extract, Load, Transform), los datos se cargan primero en bruto y solo después se transforman dentro del warehouse. Si la transformación usa un simple INSERT, cada nueva ejecución vuelve a añadir las mismas filas. Una carga idempotente lo evita: en lugar de "insertar siempre", dice "actualiza si ya existe, inserta si es nuevo". La pieza central es la clave de negocio — la columna (o columnas) que identifica de forma única cada registro, como cliente_id.

Paso 2: Preparar las tablas de destino y de staging

Necesitamos dos tablas: la de staging, que recibe los datos nuevos en cada ejecución, y la de destino, que guarda el estado actual. La tabla de destino debe tener una clave primaria por la clave de negocio, para que el motor pueda comparar registros de forma eficiente.

-- Destination table: current state of customers
CREATE TABLE dim_cliente (
    cliente_id    INT           NOT NULL,   -- business key
    nome          VARCHAR(100),
    email         VARCHAR(150),
    atualizado_em DATETIME,
    CONSTRAINT pk_dim_cliente PRIMARY KEY (cliente_id)
);

La tabla stg_cliente tiene las mismas columnas y se rellena en cada carga con los datos más recientes del origen. Es la que usaremos para alimentar el destino.

Paso 3: Escribir el upsert con MERGE

La instrucción MERGE compara el origen (staging) con el destino usando la clave de negocio. Cuando encuentra coincidencia, actualiza la fila; cuando no la encuentra, inserta una nueva. Esta lógica de "actualiza o inserta" es la que hace que la carga sea idempotente.

MERGE INTO dim_cliente AS destino
USING stg_cliente     AS origem
    ON destino.cliente_id = origem.cliente_id
WHEN MATCHED THEN
    UPDATE SET
        destino.nome          = origem.nome,
        destino.email         = origem.email,
        destino.atualizado_em = origem.atualizado_em
WHEN NOT MATCHED THEN
    INSERT (cliente_id, nome, email, atualizado_em)
    VALUES (origem.cliente_id, origem.nome, origem.email, origem.atualizado_em);

Fíjate en que no hay ningún DELETE: solo actualizamos e insertamos. Si ejecutas este MERGE diez veces seguidas con los mismos datos de staging, la tabla final queda exactamente igual — y eso es, precisamente, ser idempotente. Si tu motor no soporta MERGE, puedes lograr el mismo efecto con un DELETE de las claves afectadas seguido de un INSERT, todo dentro de la misma transacción.

Paso 4: Reprocesar con seguridad

Como la operación es idempotente, reprocesar deja de dar miedo. Basta con volver a rellenar el staging con el período que quieras recargar y ejecutar de nuevo el MERGE. No hace falta borrar nada a mano ni temer duplicar datos por error.

Consejo: mantén la columna atualizado_em para saber cuándo se tocó por última vez cada registro. Ayuda mucho a auditar las cargas e investigar problemas.

Verificar el resultado

La prueba de que la carga es idempotente es simple: el número total de filas debe ser igual al número de claves distintas. Si son diferentes, es señal de que hay duplicados.

SELECT COUNT(1)                    AS total_linhas,
       COUNT(DISTINCT cliente_id)  AS chaves_distintas
FROM dim_cliente;

Ejecuta el MERGE dos veces seguidas y vuelve a lanzar esta consulta: ambos valores se mantienen iguales y el total no crece. Si el total sube en la segunda ejecución, comprueba que la condición del ON usa realmente la clave de negocio correcta.

Conclusión

Con MERGE convertiste una carga frágil, que duplicaba datos en cada intento, en una carga idempotente y segura de reprocesar — una de las bases de cualquier pipeline ELT fiable. El siguiente paso es combinar esta técnica con cargas incrementales, para tocar solo las filas que cambiaron desde la última ejecución. ¿Cuáles de tus tablas se beneficiarían más de un upsert idempotente?