Entrevista de Ingeniería de Datos: ¿Por qué MERGE en PostgreSQL 18 puede fallar ante filas de origen duplicadas?

Pregunta y escenario

Una tabla de instantáneas diarias de clientes, staging_customer, se combina mediante merge en customer_dim. En un lote, el mismo customer_id aparece dos veces: una desde el CRM y otra desde una corrección manual. El destino no tiene claves duplicadas, pero la sentencia genera una infracción de cardinalidad. Explique cómo PostgreSQL 18 forma las filas candidatas para cambios, por qué una fila de destino no puede ser modificada nuevamente por otra fila de origen y cómo RETURNING merge_action() puede producir un resultado auditable.

Qué evalúa el entrevistador

• Distinguir entre filas de origen duplicadas, claves de destino duplicadas y una condición ON excesivamente amplia.
• Explicar que MERGE ejecuta únicamente la primera rama WHEN que coincida para cada fila candidata a cambio.
• Tomar una decisión defendible entre desduplicar, rechazar el lote y conservar la corrección más reciente.
• Utilizar RETURNING como evidencia de auditoría a nivel de fila sin tratarlo como un log de transacciones.
• Gestionar la concurrencia, reejecuciones, privilegios y alertas de calidad de datos.

Preguntas aclaratorias antes de responder

• ¿Es customer_id la clave de negocio, o el tenant y la fecha de vigencia deben formar una clave compuesta?
• ¿Tienen ambos registros de origen una versión confiable, hora de evento o prioridad de corrección? Esa respuesta determina la desduplicación.
• ¿Debe rechazarse un lote inválido de forma atómica, o pueden escribirse primero las filas resueltas? Esto modifica el diseño de transacciones y de repetición (replay).
• ¿Requiere la auditoría valores anteriores, valores nuevos, campos de origen y la acción, o solo el conteo de filas?
• ¿Pueden llegar registros de origen tardíos entre lotes, y admite el destino borrado lógico (soft delete)?

Estructura de respuesta en 30 segundos

“Primero demuestro que la condición ON mapea cada clave de destino a como máximo una fila de origen. PostgreSQL MERGE construye filas candidatas de cambio y luego ejecuta una acción por fila en orden de WHEN; si múltiples filas de origen coinciden con una fila de destino, se produce un error de cardinalidad y la transacción falla. Yo desduplicaría de forma determinista por versión u hora de evento, y rechazaría el lote cuando el conflicto no pueda resolverse. RETURNING merge_action() registra inserciones, actualizaciones y eliminaciones, mientras que una tabla de control de lotes, una restricción de unicidad y una clave de idempotencia hacen que las reejecuciones sean seguras.”

Respuesta detallada paso a paso

Demostrar primero la cardinalidad de coincidencia

Ejecute una consulta de calidad de datos exactamente con las claves utilizadas por ON y busque claves de destino con múltiples filas de origen. No aplique simplemente DISTINCT al origen: dos filas con la misma clave aún pueden describir hechos contradictorios. Si la clave de negocio es (tenant_id, customer_id), utilice ambas columnas en el MERGE y en la consulta de calidad.

Hacer la desduplicación determinista

Prefiera un número de versión. Si no existe, use la hora del evento, una prioridad de origen confiable y un criterio de desempate estable. Una función de ventana puede elegir un ganador:

WITH ranked AS (
  SELECT s.*, row_number() OVER (
    PARTITION BY tenant_id, customer_id
    ORDER BY version DESC, event_at DESC, source_priority DESC, ingest_id DESC
  ) AS rn
  FROM staging_customer AS s
)
SELECT * FROM ranked WHERE rn = 1;

Si no se puede demostrar que alguna de las fuentes sea más reciente, escriba el conflicto en una tabla de cuarentena y rechace el lote. Nunca confíe en que la base de datos elija una fila arbitrariamente.

Diseñar ramas WHEN y salida de auditoría

Las condiciones WHEN se evalúan en el orden en que están escritas, y se ejecuta la primera rama verdadera. Coloque primero las condiciones de protección, como actualizar solo cuando la versión entrante sea superior, luego maneje las inserciones NOT MATCHED y cualquier limpieza intencional NOT MATCHED BY SOURCE. RETURNING de PostgreSQL 18 puede exponer columnas de origen, valores de destino anteriores y nuevos, y merge_action(), pero reporta las filas modificadas por esta sentencia; no reemplaza a una tabla de control de lotes.

Controlar transacciones y concurrencia

Mantenga el MERGE del lote, la inserción de auditoría y la actualización de estado del lote en una sola transacción. Asigne a cada lote de entrada una clave de idempotencia y detecte lotes exitosos antes de la repetición. Siga las reglas de aislamiento de la base de datos para ejecuciones concurrentes y garantice como máximo una fila de origen candidata por fila de destino antes de la ejecución, en lugar de descubrir duplicados en tiempo de ejecución.

Respuesta de muestra de alta calidad

“El error no se explica únicamente por una clave de destino duplicada; el hecho relevante es que la condición ON conecta una sola fila de destino con múltiples filas de origen. Yo verificaría la cardinalidad del origen utilizando las mismas claves de tenant y cliente, y luego clasificaría por versión, hora de evento, prioridad de origen e ID de ingesta. Los conflictos no resolubles van a cuarentena y hacen fallar el lote. Debido a que MERGE ejecuta la primera rama WHEN que coincida, coloco la protección de versión superior en primer lugar. RETURNING merge_action() de PostgreSQL 18 registra la inserción, actualización o eliminación más los valores anteriores y nuevos; las filas de auditoría y de control de lotes permanecen en la misma transacción para que las reejecuciones, alertas y reproducciones cuenten con evidencia.”

Errores comunes

• Error: Aplicar DISTINCT al origen → Por qué falla: Se pueden fusionar hechos diferentes de manera incorrecta → Corrección: Defina un ganador mediante versión y prioridad del negocio, y ponga en cuarentena los conflictos.
• Error: Asumir que MERGE elige una fila de origen al azar → Por qué falla: Múltiples modificaciones de una misma fila de destino generan un error de cardinalidad → Corrección: Demuestre la correspondencia uno a uno antes de la ejecución.
• Error: Tratar los conteos de RETURNING como éxito del lote → Por qué falla: Faltan el estado de cero cambios, fallas y repetición → Corrección: Utilice una tabla de control de lotes y estado de transacción independientes.
• Error: Probar con un solo hilo → Por qué falla: El aislamiento, los datos tardíos y la repetición quedan sin verificar → Corrección: Pruebe concurrencia, reejecuciones y lotes tardíos.

Preguntas de seguimiento y respuestas

¿Qué sucede si una fila de origen duplicada es un marcador de eliminación?

Coloque las eliminaciones y actualizaciones en el mismo ordenamiento por versión y deje que solo gane la versión más reciente. Si la eliminación no tiene una versión comparable, ponga en cuarentena el conflicto en lugar de permitir que un solo lote decida silenciosamente el estado del cliente.

¿Puede RETURNING registrar filas de origen que no coincidieron con nada?

Devuelve las filas afectadas por INSERT, UPDATE o DELETE; no es un reporte de calidad de elementos no coincidentes del lado origen. Ejecute una estadística con anti-join por separado, o guarde los candidatos y las acciones previstas en una tabla de pre-auditoría antes de ejecutar MERGE.

¿Por qué no usar INSERT ... ON CONFLICT directamente?

Para una inserción o actualización simple basada en clave única, ON CONFLICT puede ser más sencillo. Elija MERGE cuando se requiera coincidencia de origen, limpieza de registros no presentes en el origen o múltiples ramas condicionales. No asuma que su semántica de concurrencia y privilegios son intercambiables.

¿Qué cambia cuando el destino tiene triggers?

Verifique que los triggers no modifiquen la clave de coincidencia ni conviertan la misma fila de destino en una nueva candidata dentro de la sentencia. Distinga la acción de MERGE de los efectos secundarios de los triggers en los datos de auditoría y pruebe el rollback en un entorno de integración.

Referencias

• PostgreSQL Documentation 18: MERGE
• PostgreSQL Documentation 18: Merge Support Functions
• Greg Low: SQL Interview: 35 T-SQL Merge Statement Clauses
• Simplyblock: PostgreSQL MERGE tutorial

Consejo para responder

Demuestre primero la cardinalidad uno a uno en ON, explique el orden de WHEN y la infracción de cardinalidad, y luego presente la desduplicación determinista, el control transaccional, la auditoría con RETURNING merge_action() y el manejo de reejecuciones.

Conclusión en una frase

Una respuesta sólida sobre MERGE vincula la cardinalidad del origen, el orden de las ramas y la salida de auditoría en un contrato de datos reproducible.