¿Cómo afectan las claves NOT ENFORCED de BigQuery a la optimización de joins?

Planteamiento y alcance

Eres responsable de un esquema en estrella de BigQuery: store_sales es una tabla de hechos y customer es una tabla de dimensiones. El equipo quiere declaraciones de clave primaria y foránea para que el optimizador pueda usar los metadatos de unicidad y relaciones para reducir los joins. El entrevistador hace tres preguntas: qué garantiza una restricción no forzada, qué reescrituras equivalentes son seguras y cómo evitar errores silenciosos cuando los datos se desvían.

Asume que la consulta proyecta únicamente columnas de hechos, la clave foránea de la tabla de hechos admite valores nulos y la clave primaria de la dimensión es única y no nula. Google Cloud documenta que BigQuery no fuerza estas restricciones; el propietario debe mantener los datos válidos, y las consultas sobre restricciones violadas pueden devolver resultados incorrectos.

Qué evalúa el entrevistador

• Si distingues los metadatos del optimizador de las verificaciones de integridad en tiempo de escritura.
• Si deduces la eliminación de joins a partir de la unicidad y la coincidencia opcional en lugar de decir que una clave es un índice.
• Si sabes que NOT ENFORCED no rechaza claves duplicadas ni claves foráneas huérfanas.
• Si conviertes el contrato en compuertas de carga, monitoreo y reversión en lugar de detenerte en el DDL.

Una respuesta débil dice: “la clave primaria es única y la clave foránea hace referencia a ella”. Una respuesta sólida dice que un optimizador puede reescribir una consulta usando una declaración falsa, por lo que la falla consiste en datos incorrectos y no meramente en datos más lentos; cada declaración necesita evidencia repetible.

Aclaraciones antes de responder

1. ¿Se trata de una tabla nativa de BigQuery o de una tabla externa? El soporte de restricciones y las reglas de reescritura difieren, por lo que primero hay que delimitar la respuesta.
2. ¿La consulta proyecta únicamente columnas del lado izquierdo? Seleccionar columnas del lado derecho suele impedir la eliminación de joins.
3. ¿La clave foránea admite valores nulos? NULL significa que no se requiere ninguna coincidencia y cambia el filtro en una reescritura equivalente.
4. ¿La restricción se mantiene mediante una sola canalización o mediante replicación entre sistemas? Las cargas entre sistemas necesitan verificaciones tanto antes como después de escribir la tabla.

Estas respuestas cambian el resultado: seleccionar columnas del lado derecho, claves primarias duplicadas o claves huérfanas no nulas hacen que la eliminación no sea segura. Si solo se dispone de consistencia eventual, el resultado de la verificación debe convertirse en una compuerta de publicación.

Un marco de respuesta de 30 segundos

“Las claves de BigQuery son metadatos declarativos y son NOT ENFORCED por defecto. No rechazan claves primarias duplicadas ni claves foráneas huérfanas en tiempo de escritura. Su valor consiste en proporcionar al optimizador hechos de unicidad y relación; por ejemplo, un join que devuelve solo columnas de hechos a veces puede reducirse a un filtro de no nulos. Los datos deben satisfacer la declaración o la reescritura puede devolver resultados incorrectos. Primero confirmo la proyección y la semántica de NULL, y luego ejecuto verificaciones de claves duplicadas, claves huérfanas y conciliación de recuentos después de cada carga. Una verificación fallida bloquea la publicación de la restricción o revierte el lote”.

Solución paso a paso

1. Separar los dos roles de una clave

Una declaración de clave primaria significa que cada fila es única y no nula. Una declaración de clave foránea significa que cada valor no nulo debe aparecer en la clave primaria referenciada. BigQuery puede leer esas declaraciones para optimización, pero no realiza validación en la escritura. NOT ENFORCED es un contrato explícito; el defecto son datos inválidos, no una sintaxis inválida.

2. Deducir la eliminación de inner joins

Considera una consulta que selecciona únicamente columnas de hechos:

SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
  ON ss.sales_customer = c.customer_name;

Si customer.customer_name es una clave primaria única y no nula y cada ss.sales_customer no nulo coincide con un solo cliente, el join no puede duplicar una fila de hechos. El optimizador puede reescribirla como:

SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;

La reescritura depende de dos hechos: una clave foránea no nula tiene una coincidencia, y esa coincidencia es a lo sumo una fila. No es equivalente cuando la consulta necesita columnas del lado derecho, necesita distinguir filas no coincidentes o la clave primaria está duplicada.

3. Límites para outer joins y orden de joins

Un left outer join también puede eliminarse cuando la clave del join del lado derecho es única y solo se proyectan columnas del lado izquierdo. En una consulta con múltiples joins, los metadatos de claves pueden proporcionar información de cardinalidad para el reordenamiento de joins. Estas son deducciones basadas en metadatos; BigQuery no escanea la tabla derecha en tiempo de ejecución para demostrar la unicidad.

4. Poner la corrección en la compuerta de carga

Ejecuta al menos tres verificaciones para cada lote:

-- Duplicate or null primary keys
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;

-- Orphan foreign keys
SELECT COUNT(*) AS orphan_count
FROM store_sales AS ss
LEFT JOIN customer AS c
  ON ss.sales_customer = c.customer_name
WHERE ss.sales_customer IS NOT NULL
  AND c.customer_name IS NULL;

Concilia los recuentos de filas del lote, los recuentos de claves foráneas no nulas y los recuentos de claves coincidentes. Almacena el resultado en una tabla de calidad de datos. Solo una versión que supere la compuerta debe publicar una nueva declaración de restricción o permitir que las consultas posteriores confíen en la optimización.

5. Elegir declaración, validación o una reescritura

Las declaraciones se adaptan a claves de dimensión estables y comprobables y permiten que el optimizador use la relación automáticamente. La validación en la aplicación se adapta a las rutas de ingreso que deben rechazar datos erróneos de forma temprana. Una reescritura explícita de la consulta se adapta a un período de migración cuando no se confía en el contrato, a costa de lógica duplicada. Pueden coexistir: valida en la canalización, declara la relación de confianza y concilia los reportes críticos.

6. Diseñar la ruta de fallo

Ante una verificación fallida, mantén la última tabla o vista de confianza, marca el lote actual como no publicable y alerta al propietario de los datos. No elimines la restricción como la única “solución”; eso oculta la causa. Rastrea los orígenes de claves duplicadas, el retraso de replicación o el orden de eliminaciones, y luego elige repetición, deduplicación o reparación de dimensiones.

Respuesta de muestra de alta calidad

“Trato las claves primarias y foráneas de BigQuery como un contrato de optimización, no como una restricción transaccional. Primero confirmo que la consulta proyecte únicamente columnas de hechos, defino el significado de las claves foráneas que admiten nulos y verifico la unicidad de las claves de dimensión. Bajo esas condiciones, un inner join puede convertirse en un filtro de hechos no nulos, algunos left joins pueden desaparecer y el orden de joins puede usar la cardinalidad declarada. El riesgo es que BigQuery no fuerza el contrato: las claves primarias duplicadas o las claves foráneas huérfanas permiten que el optimizador reescriba usando metadatos falsos, lo que puede cambiar silenciosamente los resultados.

“Antes de la publicación, verifico claves primarias nulas y duplicadas, claves foráneas huérfanas y la conciliación del recuento de filas para cada lote. Un lote fallido no puede publicar la tabla ni la nueva restricción; la versión de confianza anterior permanece activa. Durante una migración con metadatos no confiables, deshabilito las reescrituras dependientes o uso un join explícito hasta que pasen varios lotes. Eso preserva el beneficio de optimización al tiempo que hace auditable la corrección”.

Errores comunes

• Error: decir que PRIMARY KEY rechaza duplicados → Por qué falla: BigQuery documenta la restricción como no forzada → Solución: colocar la detección de duplicados en la compuerta de carga.
• Error: eliminar cada join que tenga una clave foránea → Por qué falla: la clave puede estar huérfana y la consulta puede necesitar columnas del lado derecho → Solución: verificar primero las condiciones de los datos y de la proyección.
• Error: verificar los datos históricos una sola vez → Por qué falla: las cargas incrementales, los backfills y el retraso de replicación pueden reintroducir claves erróneas → Solución: ejecutar verificaciones por lote de forma continua y conservar las métricas.
• Error: eliminar la restricción tras un resultado incorrecto → Por qué falla: los metadatos de optimización desaparecen mientras que el defecto de origen permanece → Solución: congelar la publicación, encontrar la causa y restaurar una versión de confianza.

Preguntas de seguimiento y respuestas

¿Qué pasa si la dimensión obtiene claves duplicadas hoy?

Pausa las consultas que dependen de la declaración, cambia a una instantánea deduplicada explícita o de confianza, marca los lotes afectados y repite la conciliación. Restaura la declaración solo después de que se repare la dimensión y se superen las verificaciones.

¿Por qué la clave foránea que admite nulos requiere un filtro de no nulos?

NULL significa que la fila de hechos no tiene ninguna coincidencia de cliente. Un inner join descarta esa fila, por lo que la reescritura equivalente debe mantener WHERE sales_customer IS NOT NULL; omitirlo cambia el resultado.

¿Cómo demuestras que la eliminación de joins preserva los resultados?

Ejecuta las consultas original y reescrita en paralelo sobre particiones representativas. Compara recuentos de filas, conjuntos de claves primarias y agregaciones, y registra la versión de la verificación de restricciones. Promueve la reescritura solo después de que pasen tanto las verificaciones de calidad de datos como la conciliación de resultados.

¿Cuándo evitarías la optimización basada en restricciones?

Evítala mientras las restricciones provengan de replicaciones lentas o no auditables, los backfills sean frecuentes o no haya una compuerta por lotes. Es preferible un escaneo adicional a una consulta más barata que confíe en metadatos falsos.

¿Pueden las restricciones de BigQuery reemplazar una transacción entre tablas?

No. No fuerzan la consistencia de escritura ni proporcionan una confirmación atómica entre tablas. La semántica de transacciones pertenece al sistema ascendente o al orquestador de carga, y el resultado de validación final se traslada al almacén de datos.

Referencias

• Documentación de Google Cloud: BigQuery primary and foreign keys.
• Blog de Google Cloud: Join Optimizations with BigQuery Primary and Foreign Keys.