Como fazer upsert idempotente em ELT com MERGE

Quando um pipeline de dados corre mais do que uma vez — porque falhou a meio, porque foi reprocessado ou simplesmente porque está agendado — corremos o risco de duplicar linhas na tabela de destino. Uma carga idempotente resolve isto: podemos executá-la as vezes que quisermos e o resultado final é sempre o mesmo. Aqui vais aprender a fazer um upsert idempotente em ELT com o comando MERGE, para atualizar e inserir dados sem nunca criar duplicados.

Pré-requisitos

• Acesso a uma base de dados ou warehouse que suporte MERGE (SQL Server, Azure Synapse, Snowflake, Databricks/Delta Lake, BigQuery, Oracle ou PostgreSQL 15+).
• Uma tabela de staging com os dados já carregados (o "load" do ELT).
• Conhecimentos básicos de SQL (SELECT, INSERT, UPDATE).

Passo 1: Perceber o que é uma carga idempotente

Em ELT (Extract, Load, Transform), os dados são primeiro carregados em bruto e só depois transformados dentro do warehouse. Se a transformação usar um simples INSERT, cada nova execução acrescenta outra vez as mesmas linhas. Uma carga idempotente evita isso: em vez de "inserir sempre", diz "atualiza se já existir, insere se for novo". A peça central é a chave de negócio — a coluna (ou colunas) que identifica de forma única cada registo, como o cliente_id.

Passo 2: Preparar as tabelas de destino e de staging

Precisamos de duas tabelas: a de staging, que recebe os dados novos em cada execução, e a de destino, que guarda o estado atual. A tabela de destino deve ter uma chave primária pela chave de negócio, para que o motor consiga comparar registos 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)
);

A tabela stg_cliente tem as mesmas colunas e é preenchida a cada carga com os dados mais recentes da origem. É a partir dela que vamos alimentar o destino.

Passo 3: Escrever o upsert com MERGE

O comando MERGE compara a origem (staging) com o destino usando a chave de negócio. Quando encontra correspondência, atualiza a linha; quando não encontra, insere uma nova. É esta lógica de "atualiza ou insere" que torna a carga 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);

Repara que não existe nenhum DELETE: apenas atualizamos e inserimos. Se correres este MERGE dez vezes seguidas com os mesmos dados de staging, a tabela final fica exatamente igual — e isso é, precisamente, ser idempotente. Se o teu motor não suportar MERGE, consegues o mesmo efeito com um DELETE das chaves afetadas seguido de um INSERT, tudo dentro da mesma transação.

Passo 4: Reprocessar com segurança

Como a operação é idempotente, o reprocessamento deixa de ser assustador. Basta voltar a preencher o staging com o período que queres recarregar e executar de novo o MERGE. Não é preciso apagar nada à mão nem ter medo de duplicar dados por engano.

Dica: mantém a coluna atualizado_em para saberes quando cada registo foi tocado pela última vez. Ajuda muito a auditar cargas e a investigar problemas.

Verificar o resultado

A prova de que a carga é idempotente é simples: o número total de linhas tem de ser igual ao número de chaves distintas. Se forem diferentes, é sinal de que há duplicados.

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

Executa o MERGE duas vezes seguidas e volta a correr esta consulta: os dois valores mantêm-se iguais e o total não cresce. Se o total subir na segunda execução, verifica se a condição do ON usa mesmo a chave de negócio correta.

Conclusão

Com o MERGE transformaste uma carga frágil, que duplicava dados a cada tentativa, numa carga idempotente e segura de reprocessar — uma das bases de qualquer pipeline ELT fiável. O próximo passo é combinar esta técnica com cargas incrementais, para tocar apenas nas linhas que mudaram desde a última execução. Quais das tuas tabelas beneficiariam mais de um upsert idempotente?