Cómo validar datos en BigQuery

Guía práctica para validar la calidad de datos en Google BigQuery — las seis comprobaciones que necesita toda tabla, cómo escribirlas sin escanear terabytes y cómo convertirlas en un contrato de datos versionado que se ejecuta de forma programada.

2026-08-29 · 8 min read

Para validar datos en BigQuery, escribe cada expectativa como una consulta de agregación que devuelva un recuento de violaciones y ejecuta el lote después de cada carga. Las seis comprobaciones que detectan la mayoría de las roturas son: nulos en columnas obligatorias, claves duplicadas, valores fuera de un conjunto permitido, números fuera de un rango plausible, cadenas mal formadas y particiones obsoletas. Lo que hace distinto a BigQuery es que cada comprobación cuesta dinero: se factura por byte escaneado, así que una suite de validación ingenua que lea una tabla de hechos completa en cada ejecución es una partida de gasto, no solo un problema de latencia. Casi todo el trabajo consiste en abaratar las comprobaciones.

Por qué validar en BigQuery es ante todo un problema de coste

BigQuery no tiene índices ni rutas de acceso a nivel de fila. Una cláusula WHERE sobre una columna no particionada no reduce los bytes escaneados: filtra después de la lectura. Dos mecanismos sí reducen el coste:

Poda de particiones. Un filtro sobre la columna de particionado (o sobre _PARTITIONTIME / _PARTITIONDATE en el particionado por hora de ingesta) restringe qué particiones se leen. Esta es la palanca grande.
Poda de columnas. BigQuery es columnar, así que SELECT COUNT(*) FROM t WHERE col IS NULL lee solo col, no la fila. No escribas nunca SELECT * en una comprobación.

Hay una tercera palanca que es gratis: INFORMATION_SCHEMA.PARTITIONS y los metadatos de la tabla llevan recuentos de filas y marcas de última modificación sin escanear absolutamente ningún dato.

Pon un techo estricto en cualquier caso. Toda consulta de validación debería ejecutarse con maximum_bytes_billed, de modo que un filtro mal escrito haga fallar el job en lugar de escanear 40 TB. El resto del flujo de trabajo —las seis comprobaciones, la programación, el contrato que las contiene— es igual que en cualquier otro sitio, y está expuesto en la guía completa para validar datos; lo específico de BigQuery es el coste.

Las seis comprobaciones que necesita toda tabla

Supongamos que orders está particionada por DATE(created_at) y que estás validando la carga del último día.

1. Completitud — nulos en columnas obligatorias

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND order_id IS NULL;

Aquí el filtro de partición es el que hace el trabajo de verdad: sin él, esto lee todos los bytes de la tabla.

2. Unicidad — claves de negocio duplicadas

SELECT COUNT(*) AS violations
FROM (
    SELECT order_id
    FROM `proj.sales.orders`
    WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
      AND order_id IS NOT NULL
    GROUP BY order_id
    HAVING COUNT(*) > 1
);

BigQuery no tiene claves primarias que haga cumplir, y las inserciones en streaming son de entrega «al menos una vez». Las claves duplicadas no son aquí un caso límite: son el modo de fallo esperable de una carga reintentada, y esta comprobación suele ser la regla de mayor valor de todo el contrato.

Una salvedad que conviene conocer: una comprobación de duplicados acotada a una partición no verá una fila duplicada entre dos días. Si tus claves deben ser únicas globalmente, esa comprobación tiene que escanear la tabla entera, así que ejecútala a diario en vez de cada hora y presupuéstala.

3. Conformidad — valores fuera de un conjunto permitido

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

NULL NOT IN (...) es NULL en GoogleSQL igual que en todas partes, así que mantén la protección IS NOT NULL o los nulos desaparecerán del recuento.

4. Exactitud — números fuera de un rango plausible

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Usa NUMERIC (o BIGNUMERIC) para el dinero. FLOAT64 es IEEE 754 y discrepará con un límite justo en la frontera.

5. Conformidad — identificadores mal formados

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND reference IS NOT NULL
  AND NOT REGEXP_CONTAINS(reference, r'^ORD-[0-9]{6}
#39;);

REGEXP_CONTAINS usa RE2, así que no hay retrorreferencias ni lookarounds; pero todo lo demás que es probable que escribas funciona, y la garantía de tiempo lineal de RE2 significa que un patrón patológico no puede colgar la consulta.

6. Oportunidad — frescura

La versión barata no lee ningún dato de la tabla:

SELECT COUNT(*) AS violations
FROM `proj.sales.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'orders'
  AND last_modified_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND partition_id = FORMAT_DATE('%Y%m%d', CURRENT_DATE());

La versión a nivel de fila —contar las filas cuyo created_at es anterior al umbral— mide algo sutilmente distinto: no «¿se cargó la tabla?», sino «¿son recientes los datos que contiene?». Merece la pena tener ambas. La comprobación de metadatos detecta un pipeline que no se ejecutó; la de filas detecta un pipeline que sí se ejecutó y produjo filas obsoletas.

Una trampa específica de BigQuery: las filas del búfer de streaming se pueden consultar pero no actualizan de inmediato last_modified_time, así que una comprobación de frescura basada solo en metadatos puede ir con retraso en una tabla de streaming.

Una cuenta de servicio de solo lectura

La validación necesita dos roles en el proyecto que contiene los datos:

roles/bigquery.dataViewer — leer datos y metadatos de las tablas
roles/bigquery.jobUser — ejecutar jobs de consulta

Concede dataViewer a nivel de dataset y no de proyecto si quieres acotar el acceso a tablas concretas. Después fija el techo de coste en la conexión para que ninguna comprobación se desboque:

-- Enforced per job by the client, not in SQL:
--   maximum_bytes_billed = 10_000_000_000   (10 GB)

Catalyst se conecta con una clave de cuenta de servicio, aplica un techo de bytes facturados por comprobación e informa de una comprobación que lo supere como error y no como fail: un límite de infraestructura nunca debería reportarse como un problema de datos.

De SQL ad hoc a un contrato de datos

Las consultas anteriores son correctas e inmantenibles: el filtro de partición está copiado y pegado seis veces, los umbrales son invisibles y nada te dice qué comprobaciones siguen ejecutándose. Declarar las expectativas lo arregla. Catalyst usa el Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 2.0.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: NUMERIC
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}#39;"
      - name: created_at
        logicalType: timestamp
        physicalType: TIMESTAMP
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst importa el esquema desde INFORMATION_SCHEMA.COLUMNS, compila cada regla a GoogleSQL y registra el resultado de cada comprobación junto con una muestra de las filas que fallan. Como el YAML es la fuente de verdad y va y vuelve por el constructor visual sin alterarse, el contrato que se revisa en una pull request es exactamente el que se ejecuta.

Trampas de BigQuery que conviene conocer

Trampa	Qué ocurre	Qué hacer
Sin filtro de partición	Cada comprobación escanea la tabla entera	Filtra por la columna de particionado
`SELECT *` en una comprobación	Lee todas las columnas	Referencia solo la columna comprobada
Streaming «al menos una vez»	Claves duplicadas en silencio	Incluye siempre una regla `duplicateCount`
Búfer de streaming	La frescura por metadatos se retrasa	Combina frescura por metadatos y a nivel de fila
Dinero en `FLOAT64`	Los límites discrepan en la frontera	Usa `NUMERIC`
Expresiones regulares RE2	Sin lookarounds ni retrorreferencias	Reescribe el patrón o usa `customSql`
Claves no aplicadas	Las restricciones son solo declarativas	Valida la unicidad de forma explícita

Programación y alertas

Dispara la validación desde el final de la carga, no desde un reloj. En BigQuery esto importa por partida doble: una comprobación que se ejecuta antes de que termine la carga reporta un fallo falso y además te factura el escaneo.

Divide la suite por coste. Las comprobaciones baratas acotadas a una partición pueden ejecutarse en cada carga; las caras que recorren toda la tabla —unicidad global, integridad referencial entre tablas— corresponden a una programación diaria. Alerta sobre la transición de pass a fail en vez de repetir el estado, y mantén las comprobaciones de patrón en severidad warning para que informen en lugar de despertar a nadie.

Preguntas frecuentes

¿Cuánto cuesta la validación de datos en BigQuery?

Es el número de bytes que escanea cada comprobación, facturado a la tarifa bajo demanda, o el tiempo de slot en una reserva. Una comprobación acotada a una partición y a una sola columna suele ser de megabytes; la misma comprobación sin filtro de partición puede ser de terabytes. Escribe filtros de partición en todas las reglas, limita cada consulta con maximum_bytes_billed y usa INFORMATION_SCHEMA para todo lo que solo necesite metadatos.

¿Puedo validar datos de BigQuery sin escribir SQL?

Sí. Declara la expectativa y deja que la herramienta genere el GoogleSQL. Catalyst importa columnas y tipos desde INFORMATION_SCHEMA, propone un conjunto de reglas de base y las compila según el dialecto: solo escribes SQL para lógica genuinamente a medida, mediante una regla customSql.

¿Qué permisos necesita una herramienta de validación?

roles/bigquery.dataViewer sobre los datasets que quieras comprobar, más roles/bigquery.jobUser sobre el proyecto para poder ejecutar consultas. No hace falta acceso de escritura: cada comprobación es un SELECT de agregación.

¿Por qué tengo filas duplicadas en BigQuery aunque haya declarado una clave primaria?

Las restricciones de clave primaria y foránea de BigQuery son metadatos no aplicados que usa el optimizador de consultas; no rechazan inserciones duplicadas. Combinado con la entrega «al menos una vez» de la API de streaming, eso convierte los duplicados en un resultado normal de una carga reintentada, no en un error raro. Aquí una regla duplicateCount no es opcional.

¿Funciona esto con vistas y tablas externas de BigQuery?

Las vistas se validan sin problema: la comprobación se ejecuta contra el resultado de la vista. Las tablas externas (Cloud Storage, Sheets, BigLake) también funcionan, pero en la mayoría de ellas no hay poda de particiones, así que cada comprobación lee la fuente entera. Presupuéstalo en consecuencia, o materializa antes la tabla externa.