Cómo validar datos en PostgreSQL

Guía práctica para validar la calidad de datos en PostgreSQL — las seis comprobaciones que necesita toda tabla, el SQL para escribirlas, las trampas de nulos y de bloqueos que conviene evitar, y cómo convertirlas en un contrato de datos versionado.

2026-08-27 · 8 min read

Para validar datos en PostgreSQL, escribe cada expectativa como una consulta de agregación que devuelve un recuento de incumplimientos, ejecútalas juntas contra la tabla y haz que fallen cuando un recuento supere su umbral. Seis comprobaciones cubren la inmensa mayoría de los incidentes reales: nulos en columnas obligatorias, claves duplicadas, valores fuera de un conjunto permitido, números fuera de un rango plausible, cadenas mal formadas y filas obsoletas. Postgres te da expresiones regulares de verdad y una semántica honesta de los nulos, lo que hace esto más fácil que en la mayoría de los motores, pero tiene sus propias trampas, casi todas alrededor de NULL, de las estadísticas y de los escaneos largos sobre una primaria.

Por qué PostgreSQL es un buen punto de partida

Postgres tiene la superficie de validación más rica entre los warehouses habituales:

• Regex de verdad. El operador ~ es POSIX extendido, así que los patrones anclados, la alternancia y los cuantificadores funcionan sin necesidad de traducción.
• information_schema más pg_catalog. Los tipos de columna, la nulabilidad y las claves primarias se pueden consultar en un formato estándar, así que la importación del esquema es exacta en lugar de inferida.
• Recuentos aproximados baratos. pg_class.reltuples te da gratis una estimación del planificador cuando un COUNT(*) exacto sale demasiado caro.

El precio de esa riqueza es que Postgres suele ser tu primaria transaccional, no un warehouse. Las consultas de validación son escaneos completos, y los escaneos completos sobre una primaria compiten con tu aplicación. Todo lo que no es específico de Postgres —dónde ejecutar las comprobaciones, cómo programarlas, qué tablas cubrir primero— está en la guía completa para validar datos.

Las seis comprobaciones que necesita toda tabla

1. Completitud — nulos en columnas obligatorias

SELECT count(*) AS violations
FROM sales.orders
WHERE order_id IS NULL;

2. Unicidad — claves de negocio duplicadas

SELECT count(*) AS violations
FROM (
    SELECT order_id
    FROM sales.orders
    WHERE order_id IS NOT NULL
    GROUP BY order_id
    HAVING count(*) > 1
) dupes;

Un índice único lo impediría en el momento de la escritura. En un esquema analítico cargado por un job de ELT, ese índice normalmente no existe: los modelos de dbt se recrean como tablas planas y CREATE TABLE AS SELECT no arrastra restricciones. La comprobación de duplicados es lo único que ocupa ese lugar.

3. Conformidad — valores fuera de un conjunto permitido

SELECT count(*) AS violations
FROM sales.orders
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

La protección IS NOT NULL no es opcional. NULL NOT IN (...) es NULL, no true, así que los nulos desaparecen del resultado y una columna íntegramente nula declara una conformidad perfecta. Este es el falso pass más habitual en el SQL de calidad de datos escrito a mano.

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

SELECT count(*) AS violations
FROM sales.orders
WHERE total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Usa numeric para el dinero. Los límites en double precision se comparan con error de redondeo, y una comprobación escrita como > 100000 se contradecirá a sí misma de vez en cuando.

5. Conformidad — identificadores mal formados

SELECT count(*) AS violations
FROM sales.orders
WHERE reference IS NOT NULL
  AND reference !~ '^ORD-[0-9]{6}
#39;;

Usa ~ para coincidencia sensible a mayúsculas y ~* para insensible. A diferencia de SQL Server, no hace falta traducir nada: el patrón que escribes es el patrón que se ejecuta.

6. Actualidad — frescura

SELECT count(*) AS violations
FROM sales.orders
WHERE created_at < now() - interval '24 hours';

Prefiere timestamptz a timestamp. Una columna timestamp a secas no tiene zona, así que now() se compara contra la que tenga configurada la sesión en TimeZone, y la misma comprobación da respuestas distintas desde dos clientes.

Integridad referencial entre tablas

Postgres es uno de los pocos motores donde la comprobación entre tablas sale realmente barata, porque el planificador hace un hash join en lugar de ejecutar una subconsulta correlacionada:

SELECT count(*) AS violations
FROM sales.orders o
LEFT JOIN sales.customers c ON o.customer_id = c.id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL;

Las claves foráneas huérfanas son el síntoma clásico de un backfill parcial —una tabla recargada y su padre no— y son invisibles hasta que un dashboard descarta filas en silencio en un inner join.

Cómo crear un rol de solo lectura seguro

CREATE ROLE catalyst_ro LOGIN PASSWORD '<generated>';
GRANT CONNECT ON DATABASE analytics TO catalyst_ro;
GRANT USAGE ON SCHEMA sales TO catalyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO catalyst_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO catalyst_ro;

La línea de ALTER DEFAULT PRIVILEGES es la que todo el mundo olvida: sin ella, cada tabla que tu job de ELT recree mañana será invisible para el rol de validación, y las comprobaciones empezarán a fallar con errores de permisos que parecen problemas de datos.

Después, protege la primaria:

ALTER ROLE catalyst_ro SET statement_timeout = '60s';
ALTER ROLE catalyst_ro SET idle_in_transaction_session_timeout = '30s';

Mejor todavía: apunta la conexión a una réplica de lectura. Las consultas de validación son escaneos agregados de solo lectura sin requisitos de ordenación, que es exactamente la carga de trabajo para la que existen las réplicas.

De SQL improvisado a un contrato de datos

Los scripts improvisados se desincronizan de las tablas que comprueban. Expresar las expectativas de forma declarativa lo soluciona: las reglas viven junto al esquema, en un formato que pueden leer tanto una persona como un runner. Catalyst usa el Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.4.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: uuid
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: customer_id
        logicalType: string
        quality:
          - rule: referentialIntegrity
            dimension: consistency
            severity: error
            mustBe: "customers.id"
      - 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: created_at
        logicalType: timestamp
        physicalType: timestamptz
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst importa las columnas desde information_schema, propone un contrato de partida a partir de los tipos y la nulabilidad que encuentra, compila cada entrada quality al SQL de Postgres que se muestra arriba y registra el resultado de cada comprobación. Como el YAML va y vuelve sin alterarse —los comentarios y el orden de las claves sobreviven a las ediciones hechas en el constructor visual—, el contrato que revisas en un pull request es byte a byte el contrato que se ejecuta.

Trampas de PostgreSQL que conviene conocer

Programación y alertas

Ejecuta las comprobaciones justo después de la carga que produce los datos, no en un cron de hora redonda. Una tabla diaria validada a las 06:00 cuando el ELT termina a las 06:40 falla todas las mañanas por un motivo que no tiene nada que ver con la calidad.

Alerta sobre transiciones en lugar de sobre estados. “Orders ha empezado a fallar” es accionable; “orders sigue fallando” repetido cada hora es la forma más rápida de que se silencie un canal. Separa la severidad en consecuencia: error para las comprobaciones que deberían bloquear a los consumidores aguas abajo, warning para las que quieres como línea de tendencia.

Preguntas frecuentes

¿Puedo validar datos de PostgreSQL sin escribir SQL?

Sí. Declara la expectativa —obligatorio, único, valores permitidos, rango numérico, patrón regex, antigüedad máxima, clave foránea— y deja que la herramienta la compile. Catalyst lee tus columnas y tipos desde information_schema, sugiere un conjunto inicial de reglas y genera las consultas. El SQL escrito a mano queda reservado para la lógica genuinamente a medida, mediante una regla customSql.

¿La validación necesita acceso de escritura a mi base de datos?

No. Cada comprobación es un SELECT que devuelve un número. CONNECT sobre la base de datos, USAGE sobre el esquema y SELECT sobre las tablas es todo el conjunto de permisos necesario. Catalyst guarda solo metadatos y resultados de comprobaciones; las filas nunca salen de tu servidor.

¿Debería ejecutar las validaciones contra una réplica de lectura?

Sí, cuando tengas una. Las comprobaciones son escaneos agregados completos sin más requisito de consistencia que “reciente”, así que un retraso de réplica de unos segundos es irrelevante, y sacarlas de la primaria elimina la principal objeción operativa a ejecutarlas a menudo.

¿Cómo compruebo una expresión regular en Postgres?

Usa el operador ~ para coincidencia POSIX sensible a mayúsculas o ~* para insensible, y niega con !~. Postgres no necesita traducir el patrón, a diferencia de SQL Server, donde las reglas de regex hay que reescribirlas como LIKE.

¿En qué se diferencia esto de los dbt tests o de las restricciones CHECK?

Las restricciones CHECK rechazan filas incorrectas en el momento de la escritura, lo cual es correcto para una tabla transaccional e incorrecto para un warehouse, donde prefieres que los datos aterricen y ponerlos en cuarentena. Los dbt tests se ejecutan dentro de una build de dbt, así que solo cubren los modelos que dbt controla. Un contrato de datos está por encima de ambos: describe las garantías de la tabla en un formato portable, versionado con independencia de cualquier herramienta de pipeline, y se aplica igual a las tablas producidas por dbt, por Airflow o por un cargador hecho a mano.