Limpieza de datos en SQL: patrones prácticos

Cuando un panel de control experimenta un pico repentino durante la noche, la primera explicación suele ser la actividad comercial. Luego, alguien revisa el almacén y encuentra pedidos duplicados, claves externas faltantes o fechas analizadas en dos formatos diferentes. El informe no estaba "casi correcto". Sus entradas eran inconsistentes y cada cálculo posterior heredó el problema.

Esa es la realidad operativa de la limpieza de datos en SQL. El trabajo se trata menos de una sintaxis ingeniosa y más de medir defectos, aislar registros de riesgo, aplicar reparaciones deterministas y evitar que vuelvan a aparecer los mismos defectos. A escala de almacén, un UPDATE descuidado puede dañar más datos de los que corrige, mientras que un flujo de trabajo de SQL bien estructurado puede hacer que la lógica de calidad sea repetible, revisable y eficiente.

Tabla de contenidos

• Por qué SQL sigue siendo la columna vertebral de la limpieza de datos

• Perfilar sus datos antes de escribir una sola corrección

  • Establecer una línea base de defectos

  • Encontrar duplicados sin tocar la producción

• Manejo de nulos y eliminación de duplicados a escala

  • Deduplicar con una regla de supervivencia explícita

  • Saber cómo trata la unicidad a los nulos

• Estandarización de formatos de texto y corrección de tipos de datos

  • Hacer explícitas las conversiones

  • Estandarizar antes de la deduplicación

• Asegurar la calidad con restricciones y reglas de validación

  • Elegir el rechazo estricto o la cuarentena flexible

• Ir más allá de los scripts únicos hacia el monitoreo continuo

Por qué SQL sigue siendo la columna vertebral de la limpieza de datos

Una gran carga de trabajo de analítica puede dedicar más tiempo a preparar datos que a analizarlos. Un punto de referencia ampliamente citado dice que los analistas y científicos de datos pueden pasar hasta el 80% de su tiempo limpiando y preparando datos, un hallazgo analizado en este resumen de limpieza de datos en SQL de Domo. Esa asignación explica por qué operaciones como filtrar nulos, eliminar duplicados, estandarizar formatos y validar rangos se convirtieron en habilidades básicas para los ingenieros de datos y de analítica.

SQL se sitúa cerca de los datos, lo que importa en las arquitecturas modernas de ELT. Los registros sin procesar se pueden cargar en un almacén y transformar donde ya viven, en lugar de extraerse, moverse y procesarse repetidamente en otro sistema. La base de datos se convierte en la capa de ejecución de la lógica de calidad, mientras que las sentencias SQL proporcionan un registro determinista de qué cambió y por qué.

Un incidente típico comienza con un pequeño defecto que se convierte en un gran problema de informes:

• Eventos de negocio duplicados: Un reintento en un trabajo de ingesta crea dos filas para una transacción, inflando los ingresos o el volumen.

• Claves de relación faltantes: Una clave de cliente o producto nula impide que se realice una unión, por lo que el panel de control subestima la actividad.

• Desviación de formato: Una fuente envía una fecha como texto en una convención diferente, desplazando los registros al período de informe incorrecto.

• Valores centinela: Cadenas vacías, fechas de marcador de posición o ceros reemplazan la información faltante y superan las comprobaciones de nulos superficiales.

Regla de producción: Trate la limpieza como una operación de datos controlada, no como una serie improvisada de correcciones dentro de una consulta de panel de control.

La distinción importa porque SQL puede tanto reparar como ocultar defectos. Un COALESCE puede hacer que se represente un informe mientras enmascara un valor faltante que debería desencadenar un incidente ascendente. Un DELETE amplio puede eliminar duplicados y, al mismo tiempo, borrar el registro válido que debería haberse conservado. Una buena lógica de limpieza clasifica los errores primero, aísla las filas afectadas y conserva suficiente evidencia para auditar la decisión.

Para los lectores que desean ejemplos de SQL más amplios y perspectivas prácticas, los recursos de SQL de Wonderment Apps ofrecen un contexto útil sobre el lenguaje y sus aplicaciones. Para los equipos que evalúan la ejecución de calidad nativa del almacén, la guía de calidad de datos en base de datos de digna es relevante porque mantener las comprobaciones cerca de los datos reduce el movimiento innecesario y separa la lógica de validación de los scripts externos frágiles.

Perfilar sus datos antes de escribir una sola corrección

El error de limpieza más costoso suele ser el primero: escribir un UPDATE antes de medir el problema. Un perfil le brinda una línea base, revela si el problema es aislado o sistémico, y le permite comparar el conjunto de datos antes y después de la remediación.

Comience con una muestra representativa cuando la tabla sea grande. Una muestra no reemplazará una validación completa, pero ayuda a inspeccionar formatos y valores de negocio sin forzar inmediatamente un escaneo masivo. Ejecute agregaciones específicas contra la tabla completa cuando el motor del almacén y la partición hagan que esas comprobaciones sean prácticas.

Establecer una línea base de defectos

Para las columnas que admiten nulos, cuente los valores faltantes directamente en lugar de confiar en una muestra visual:

SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
    SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_order_date
FROM staging.orders;

SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
    SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_order_date
FROM staging.orders;

SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
    SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_order_date
FROM staging.orders;

Una tasa de nulos es útil porque hace que el defecto sea medible y comparable entre ejecuciones de pipelines. También puede agrupar la falta de datos por sistema de origen, partición o fecha de ingesta para distinguir una característica de datos de larga data de una interrupción reciente.

Las comprobaciones de valores distintos exponen categorías inesperadas:

SELECT
    status,
    COUNT(*) AS row_count
FROM staging.orders
GROUP BY status
ORDER BY row_count DESC;

SELECT
    status,
    COUNT(*) AS row_count
FROM staging.orders
GROUP BY status
ORDER BY row_count DESC;

SELECT
    status,
    COUNT(*) AS row_count
FROM staging.orders
GROUP BY status
ORDER BY row_count DESC;

Busque variantes de ortografía, mayúsculas y minúsculas inconsistentes, cadenas vacías y valores que violen el vocabulario comercial. Una columna que parece contener un pequeño conjunto de estados puede contener varias representaciones del mismo estado.

Encontrar duplicados sin tocar la producción

La detección exacta de duplicados comienza por agrupar las columnas que definen el registro:

SELECT
    order_id,
    customer_id,
    order_date,
    COUNT(*) AS duplicate_count
FROM staging.orders
GROUP BY order_id, customer_id, order_date
HAVING COUNT(*) > 1;

SELECT
    order_id,
    customer_id,
    order_date,
    COUNT(*) AS duplicate_count
FROM staging.orders
GROUP BY order_id, customer_id, order_date
HAVING COUNT(*) > 1;

SELECT
    order_id,
    customer_id,
    order_date,
    COUNT(*) AS duplicate_count
FROM staging.orders
GROUP BY order_id, customer_id, order_date
HAVING COUNT(*) > 1;

Esta consulta le indica dónde existen duplicados, pero no le dice qué fila debe sobrevivir. Capture los registros sospechosos en una tabla de preparación separada e incluya metadatos de ingesta, prioridad de origen, marca de tiempo de actualización y una clave sustituta estable cuando esté disponible.

CREATE TABLE staging.suspicious_orders AS
SELECT *
FROM raw.orders
WHERE order_id IS NULL;

CREATE TABLE staging.suspicious_orders AS
SELECT *
FROM raw.orders
WHERE order_id IS NULL;

CREATE TABLE staging.suspicious_orders AS
SELECT *
FROM raw.orders
WHERE order_id IS NULL;

La sintaxis exacta varía según el almacén de datos, pero el principio de funcionamiento es constante: nunca experimente directamente en la relación de producción cuando puede aislar a los candidatos primero. La guía práctica de limpieza con SQL también recomienda validar las transformaciones en subconjuntos pequeños antes de aplicarlas ampliamente, lo que reduce el radio de impacto de un predicado incorrecto. Las técnicas de perfilado de datos utilizadas en la limpieza de almacenes complementan este enfoque al hacer que la fase de inspección sea explícita en lugar de tratarla como una preparación opcional.

Mida primero, repare después. Si no puede determinar cuántas filas se ven afectadas, no puede revisar el cambio de manera segura.

Manejo de nulos y eliminación de duplicados a escala

El manejo de nulos es una decisión comercial disfrazada de expresión SQL. Reemplazar cada valor faltante con un valor predeterminado puede simplificar las consultas posteriores, pero también puede convertir "desconocido" en una afirmación falsa. Mantenga el valor original disponible cuando la distinción sea importante.

COALESCE es apropiado cuando una alternativa tiene un significado claro:

SELECT
    order_id,
    COALESCE(currency_code, 'UNKNOWN') AS currency_code
FROM staging.orders;

SELECT
    order_id,
    COALESCE(currency_code, 'UNKNOWN') AS currency_code
FROM staging.orders;

SELECT
    order_id,
    COALESCE(currency_code, 'UNKNOWN') AS currency_code
FROM staging.orders;

Ese patrón es más seguro para la presentación que para el almacenamiento irreversible. Si una moneda ausente significa que el origen no proporcionó la información requerida, una bandera de validación es más honesta:

SELECT
    order_id,
    currency_code,
    CASE
        WHEN currency_code IS NULL THEN 'MISSING_CURRENCY'
        ELSE 'OK'
    END AS quality_status
FROM staging.orders;

SELECT
    order_id,
    currency_code,
    CASE
        WHEN currency_code IS NULL THEN 'MISSING_CURRENCY'
        ELSE 'OK'
    END AS quality_status
FROM staging.orders;

SELECT
    order_id,
    currency_code,
    CASE
        WHEN currency_code IS NULL THEN 'MISSING_CURRENCY'
        ELSE 'OK'
    END AS quality_status
FROM staging.orders;

NULLIF ayuda a convertir marcadores de posición conocidos en nulos verdaderos antes de perfilar:

SELECT
    NULLIF(TRIM(phone_number), '') AS phone_number
FROM raw.customers;

SELECT
    NULLIF(TRIM(phone_number), '') AS phone_number
FROM raw.customers;

SELECT
    NULLIF(TRIM(phone_number), '') AS phone_number
FROM raw.customers;

También puede utilizar la lógica condicional cuando el reemplazo correcto dependa de una regla documentada. No infiera un atributo de cliente a partir de un campo no relacionado simplemente porque la consulta necesita un valor que no sea nulo.

Deduplicar con una regla de supervivencia explícita

ROW_NUMBER() es el patrón duradero para identificar un sobreviviente dentro de cada grupo duplicado:

WITH ranked_orders AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC, ingestion_id DESC
        ) AS row_num
    FROM staging.orders
)
SELECT *
FROM ranked_orders
WHERE row_num = 1;

WITH ranked_orders AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC, ingestion_id DESC
        ) AS row_num
    FROM staging.orders
)
SELECT *
FROM ranked_orders
WHERE row_num = 1;

WITH ranked_orders AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC, ingestion_id DESC
        ) AS row_num
    FROM staging.orders
)
SELECT *
FROM ranked_orders
WHERE row_num = 1;

La cláusula ORDER BY es la parte importante. "Mantener el más reciente" solo funciona si la marca de tiempo es confiable y los empates tienen una alternativa determinista. Si los registros difieren en su integridad, clasifique según una regla de integridad documentada en lugar de asumir que la última fila en llegar es la mejor.

Para tablas a escala de almacén, materialice el resultado clasificado en una nueva relación o partición de reemplazo cuando sea posible. Reconstruir una partición limpia puede ser más seguro que emitir una eliminación masiva fila por fila, especialmente cuando la tabla está agrupada o particionada. Conserve las filas rechazadas en una tabla de auditoría si los duplicados pueden requerir corrección en el sistema de origen.

Saber cómo trata la unicidad a los nulos

SQL Server tiene un comportamiento particularmente importante: una restricción UNIQUE permite NULL, pero solo se permite un único valor NULL por columna restringida, tal como se documenta en la referencia de restricciones únicas y de verificación de Microsoft. Ese comportamiento puede sorprender a los equipos que limpian campos opcionales, porque el manejo de nulos afecta si los registros posteriores son aceptados o rechazados.

Utilice comprobaciones de integridad de datos para separar "faltante pero permitido" de "faltante e inválido". Elimine duplicados solo cuando la regla de identidad sea inequívoca. De lo contrario, marque el grupo para su revisión y conserve la evidencia necesaria para explicar por qué se seleccionó un registro específico.

Estandarización de formatos de texto y corrección de tipos de datos

Las inconsistencias de texto a menudo sobreviven a las pruebas básicas porque parecen correctas para una persona. Un espacio final en una clave de unión, diferentes mayúsculas y minúsculas en un estado, o un carácter Unicode que se asemeja a un carácter ASCII pueden producir uniones no coincidentes y agregaciones fragmentadas.

Normalice los valores primero en una proyección controlada:

SELECT
    customer_id,
    UPPER(TRIM(country_code)) AS country_code,
    LOWER(TRIM(email_address)) AS email_address,
    REPLACE(TRIM(phone_number), ' ', '') AS phone_number
FROM staging.customers;

SELECT
    customer_id,
    UPPER(TRIM(country_code)) AS country_code,
    LOWER(TRIM(email_address)) AS email_address,
    REPLACE(TRIM(phone_number), ' ', '') AS phone_number
FROM staging.customers;

SELECT
    customer_id,
    UPPER(TRIM(country_code)) AS country_code,
    LOWER(TRIM(email_address)) AS email_address,
    REPLACE(TRIM(phone_number), ' ', '') AS phone_number
FROM staging.customers;

TRIM elimina los espacios en blanco circundantes, mientras que UPPER y LOWER establecen una forma de comparación consistente. REPLACE puede eliminar caracteres de formato conocidos, pero los reemplazos amplios son riesgosos cuando la puntuación tiene significado. Las expresiones regulares son útiles para la validación de patrones y la corrección dirigida donde el almacén las admita, pero deben probarse contra variantes de origen reales en lugar de aplicarse como un depurador universal.

Hacer explícitas las conversiones

Las conversiones implícitas son convenientes durante la exploración pero peligrosas en producción. Un valor de texto puede convertirse de manera diferente según el motor, la configuración de la sesión, la configuración regional o el tipo de destino. Las sentencias explícitas CAST o CONVERT hacen visible la representación prevista:

SELECT
    CAST(quantity_text AS INTEGER) AS quantity,
    CAST(amount_text AS DECIMAL(18, 2)) AS amount
FROM staging.order_lines;

SELECT
    CAST(quantity_text AS INTEGER) AS quantity,
    CAST(amount_text AS DECIMAL(18, 2)) AS amount
FROM staging.order_lines;

SELECT
    CAST(quantity_text AS INTEGER) AS quantity,
    CAST(amount_text AS DECIMAL(18, 2)) AS amount
FROM staging.order_lines;

Antes de realizar la conversión, perfile los valores que no cumplan con el formato esperado. Una conversión fallida debe capturarse como una excepción de calidad, no descartarse. Compruebe también la precisión y la escala, ya que un tipo numérico de destino puede perder detalles significativos si es más estrecho que el de origen.

Los datos temporales necesitan aún más cuidado. Una marca de tiempo sin contexto de zona horaria puede cambiar cuando los sistemas la interpretan bajo diferentes configuraciones de sesión. Estandarice la convención de origen, convierta con una política de zona horaria explícita y conserve el valor original sin procesar hasta que el resultado pase la validación.

Estandarizar antes de la deduplicación

El orden importa. Una secuencia de limpieza práctica consiste en inspeccionar nulos, duplicados y formatos inusuales, luego estandarizar texto, corregir tipos y deduplicar después de que los valores equivalentes se hayan llevado a una forma común. Este orden se describe en la guía de limpieza de datos SQL sobre inspección y estandarización.

Si elimina los duplicados antes de recortar y normalizar, registros como ACME y ACME permanecerán separados, a pesar de que el negocio los trate como una única clave. Si agrega restricciones antes de la conversión de tipos, la base de datos puede rechazar registros entrantes válidos o conservar una representación no adecuada. Mantenga las columnas sin procesar, normalizadas y validadas de forma distinta durante el desarrollo para que los revisores puedan comparar cada transformación.

Asegurar la calidad con restricciones y reglas de validación

Un script de limpieza corrige el lote actual. Una restricción protege el siguiente lote. Utilice medidas de seguridad en la base de datos cuando la regla sea estable, local para el registro y lo suficientemente importante como para rechazar datos no válidos en la ingesta.

NOT NULL es sencillo, pero debe reflejar un requisito real. Aplicarlo a un atributo opcional crea fricción operativa sin mejorar la corrección. UNIQUE funciona bien para identificadores naturales o claves de negocio compuestas, siempre que haya definido cómo deben comportarse los nulos y las actualizaciones que llegan tarde.

Las restricciones CHECK expresan reglas como:

ALTER TABLE curated.orders
ADD CONSTRAINT chk_order_dates
CHECK (order_date IS NULL OR shipped_date IS NULL OR shipped_date >= order_date);

ALTER TABLE curated.orders
ADD CONSTRAINT chk_order_dates
CHECK (order_date IS NULL OR shipped_date IS NULL OR shipped_date >= order_date);

ALTER TABLE curated.orders
ADD CONSTRAINT chk_order_dates
CHECK (order_date IS NULL OR shipped_date IS NULL OR shipped_date >= order_date);

Las expresiones ANSI SQL CHECK pueden evaluarse como TRUE, FALSE o UNKNOWN, y se limitan a la integridad del dominio. Pueden validar los valores de una fila, pero no pueden inspeccionar otras filas para verificar la consistencia entre filas, como se explica en esta referencia de migración de restricciones SQL. Un CHECK puede rechazar un monto negativo o un orden de fechas inválido. No puede confirmar que el total de una cuenta se concilie a través de una tabla separada.

Elegir el rechazo estricto o la cuarentena flexible

Las restricciones estrictas son apropiadas cuando la aceptación de una fila defectuosa corrompería una tabla crítica y la fuente puede corregir las fallas rápidamente. Son menos adecuadas cuando los sistemas ascendentes envían regularmente registros parciales que necesitan investigación antes de su finalización.

Un patrón flexible almacena el registro mientras agrega columnas de validación como is_valid, failure_reason o rule_name. Los modelos posteriores pueden excluir las filas fallidas, mientras que los equipos de operaciones mantienen la visibilidad del defecto de origen. Este enfoque cuesta más esfuerzo de diseño, pero evita convertir un problema de origen transitorio en una carga fallida sin contexto de diagnóstico.

Las reglas de Data Validation de SQL y la guía de calidad continua proporcionan un marco útil para separar los valores requeridos, los formatos, la integridad, la unicidad y la integridad referencial. En la práctica, combine las restricciones con la validación de la etapa de preparación. Las restricciones son la puerta final, no un sustituto del perfilado, la clasificación de errores o un registro de auditoría.

Ir más allá de los scripts únicos hacia el monitoreo continuo

La limpieza con SQL es reactiva. Repara registros después de que un defecto ha ingresado al pipeline, mientras que el monitoreo continuo busca las condiciones que indican una regresión.

Un flujo de trabajo maduro superpone varias señales sobre el conjunto de datos limpio:

• Trabajos de SQL programados: Ejecute transformaciones deterministas en un horario definido y registre los recuentos de filas afectadas.

• Reglas de calidad: Verifique los valores requeridos, formatos válidos, unicidad, integridad referencial y condiciones comerciales.

• Detección de anomalías: Compare las distribuciones y volúmenes actuales con el comportamiento establecido para detectar cambios inusuales.

• Comprobaciones de Timeliness: Detecte entregas faltantes, retrasadas o inesperadamente tempranas.

• Seguimiento de esquemas: Identifique columnas agregadas o eliminadas y cambios en los tipos de datos antes de que fallen las consultas posteriores.

La inversión adecuada depende del modo de falla. Escriba un script SQL cuando la regla sea determinista, la transformación sea repetible y el conjunto de datos afectado esté claramente delimitado. Agregue Observability automatizada cuando los defectos se repitan, el comportamiento del origen cambie, los tiempos de entrega importen o una falla en el panel de control se descubra demasiado tarde mediante revisión manual.

Plataformas como digna ejecutan comprobaciones de calidad y anomalías dentro del entorno de base de datos del cliente, lo que permite a los equipos monitorear el comportamiento de los datos sin mover los registros de producción a una capa de procesamiento externa. Sus capacidades de monitoreo de calidad de datos cubren la brecha entre la limpieza programada y la detección continua al combinar validación, anomalías, Timeliness y monitoreo estructural.

Utilice digna para ejecutar validaciones en la base de datos, detección de anomalías, comprobaciones de Timeliness y seguimiento de esquemas en todos los conjuntos de datos de los que dependen sus pipelines de SQL. Visite digna para ver cómo el monitoreo continuo puede convertir la lógica de limpieza de una sola vez en un sistema operativo de calidad de datos.