Hace unos meses escribía sobre la importancia de validación de datos. Es imprescindible asegurarnos de que los datos con los que trabajamos proporcionan información de la manera correcta para conseguir un análisis de la información fiel a la realidad y un correcto procesado.

En este primer artículo os detallaba las situaciones en las que recomendamos validar los datos, así como los aspectos fundamentales a tener en cuenta en el proceso de validación con T-SQL.

A veces podemos realizar conteos a tablas muy grandes que llevan mucho tiempo, o necesitamos comprobar si existe una tabla o un campo dentro de una tabla, o poder comparar los resultados de 2 consultas distintas. Hoy veremos ejemplos de estos casos empleando diferentes técnicas y ejemplos prácticos con T-SQL para detectar posibles errores y su validación.

Cómo validar el tamaño del dato

En ocasiones podemos encontrarnos con que los datos están ‘cortados’ o como si les faltase una parte de la información en un campo. Vamos a ver un ejemplo en el que ocurre eso y como solucionarlo.

Tenemos una tabla origen con los siguientes tipos de dato:

Validación de datos con T-SQL (Ejemplos)

Tenemos un proceso ETL simple que trae los datos desde esa tabla y los inserta en una tabla de staging:

Validación de datos con T-SQL (Ejemplos)

Si ejecutamos la ETL, vemos que ejecuta correctamente a pesar de tener un aviso de que puede truncarse el dato en PostalCode

Validación de datos con T-SQL (Ejemplos)

Vemos que parece haber insertado el dato correctamente.

Validación de datos con T-SQL (Ejemplos)

Pero si comprobamos esos códigos postales en la vista origen:

Validación de datos con T-SQL (Ejemplos)

Vemos que solo nos salieron estos códigos postales:

Validación de datos con T-SQL (Ejemplos)

En la vista origen, el código postal es un nvarchar(15), mientras que en la tabla destino es un nvarchar(5). Esto provocó un truncado de datos lo cual hizo que hace que solo se recojan los 5 primeros caracteres de los códigos postales, a pesar de tener códigos postales con mayor tamaño.

Si corregimos la tabla destino el tipo de dato de nvarchar(5) a nvarchar(15), podemos comprobar que ya no aparece el warning en el proceso ETL:

Validación de datos con T-SQL (Ejemplos)

Y al comprobar el dato en la tabla, podemos ver que ahora algunos códigos postales tienen mayor tamaño:

Validación de datos con T-SQL (Ejemplos)

Así que comprobamos la existencia de esos códigos postales en la vista origen:

SELECT DISTINGS(PostalCode) 
FROM bi.[Address_blog] 
WHERE postalcode IN('32804', 'K4B 1T7', 'H1Y 2H5','T2P 2G8','33000','14111','3000')
Validación de datos con T-SQL (Ejemplos)

Con lo cual ya vemos que ahora si que sale el dato correcto, cosa que también podemos hacer con esta consulta a las tablas del sistema:

SELECT (TABLE_SCHEMA) table_schema_name,
  (TABLE_NAME) Table_Name,
  (REPLACE(REPLACE(COLUMN_NAME, '>', '>'), '>', '<')) Column_name,
DATA_TYPE + CASE DATA_TYPE
   WHEN 'bit' THEN ''
   WHEN 'smallint' THEN ''
   WHEN 'int' THEN ''
   WHEN 'bigint' THEN ''
   WHEN 'bit' THEN ''
   WHEN 'smallint' THEN ''
   WHEN 'int' THEN ''
   WHEN 'bigint' THEN ''
   WHEN 'varchar' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) + ')'
   WHEN 'char' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) + ')'
   WHEN 'nvarchar' THEN '(' + CASE
       WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'Max'
       ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar)
     END + ')'
   WHEN 'nchar' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) + ')'
   WHEN 'decimal' THEN '(' + CAST(NUMERIC_PRECISION AS varchar) + ',' + CAST(NUMERIC_SCALE AS varchar) + ')'
   WHEN 'numeric' THEN '(' + CAST(NUMERIC_PRECISION AS varchar) + ',' + CAST(NUMERIC_SCALE AS varchar) + ')'
   WHEN 'money' THEN ''
   WHEN 'datetime' THEN ''
   WHEN 'uniqueidentifier' THEN ''
   ELSE ''
END AS DATA_TYPE,
ORDINAL_POSITION AS ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'BI'
AND TABLE_NAME = 'Address_Blog'
Validación de datos con T-SQL (Ejemplos)

Cómo comparar el resultado de 2 consultas

A menudo podemos pensar en optimizar nuestras propias consultas, o necesitamos comprobar si el resultado de una consulta que nos envían es el mismo que otra, y por el volumen de datos no es posible comprobarlo a mano. Con esta consulta podemos validar qué datos están en una tabla y no en la otra.

SELECT * FROM(
SELECT ad.Prov_Nombre,’not in b’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id>2 –a
EXCEPT
SELECT ad.Prov_nombre, ‘not in b’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id<7 –b
UNION
SELECT ad.Prov_nombre, ‘not in a’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id<7 –b
EXCEPT
SELECT ad.Prov_Nombre,’not in a’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id>2 –a
) cars
Validación de datos con T-SQL (Ejemplos)

Método alternativo para contar el número de filas de una tabla

Realizar conteos de tablas es muy habitual, pero realizarlos sobre tablas muy grandes resulta muy costoso en tiempo y recursos. Esta es una consulta típica para contar el número de filas de una tabla:

SELECT COUNT(*)AS rowcnt 
FROM [fact].[Invoice]
Validación de datos con T-SQL (Ejemplos)

Lleva 3 segundos en cargar. Sin embargo, esta otra consulta:

SELECT o.name,
   i.rowcnt
FROM sysobjects o
   JOIN   sysindexes I
   ON   o.id = i.id
WHERE o.type = 'u'
   AND i.indid IN (0,1)
   AND o.name IN
   ('Invoice')
ORDER BY o.name
Validación de datos con T-SQL (Ejemplos)

Esta consulta no llega a 1 segundo pues ya está almacenada la información del número de registros en las tablas del sistema que podemos usar para comprobar rápidamente esos conteos. Hay que tener en cuenta que tienen que estar actualizadas las estadísticas de las tablas del sistema para que funcione correctamente esta segunda consulta. En caso de que no estén actualizadas podemos lanzar el comando “UPDATE STATISTICS nombre_tabla”.

Cómo comprobar si existe una tabla en una base de datos

Para simplemente ver si existe una tabla, podemos consultar en la tabla del sistema INFORMATION_SCHEMA.TABLES filtrando por el nombre de la(s) tabla(s) que buscamos.

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME in ('UNIDAD_VENDIDA', 'EXTRA_QUITAR')
Validación de datos con T-SQL (Ejemplos)

Búsqueda de un nombre de campo sin saber en qué tabla está

Existen ocasiones en las que por falta de documentación o no estar familiarizado con el proyecto, no sabemos con qué tablas hay que cruzar datos y comprobar manualmente puede conllevar bastante tiempo de búsqueda. Existen métodos para buscar, como ver si existen nombres similares a ese campo en otras tablas existentes:

Pongamos que necesitamos saber qué tablas contienen un id de proveedor, pero nuestro DWH tiene cientos de tablas. Con esta consulta podemos buscar en las tablas del sistema la metadata que contenga Proveedor y que nos diga el nombre del campo y la tabla en la que está:

SELECT t.name, c.name FROM sys.tables t
LEFT JOIN sys.columns c ON t.object_id=c.object_id
WHERE c.name LIKE '%Proveedor%'
Validación de datos con T-SQL (Ejemplos)

También se podrían buscar en INFORMATION_SCHEMA.COLUMNS filtrando por COLUMN_NAME like ‘%Proveedor%’ y el nombre del esquema.

Cómo comprobar si existen campos nulos

DECLARE @tabla NVARCHAR(255), @sql NVARCHAR(MAX);
SET @tabla = '[etl].[Proveedor]';
SET @sql = N'SELECT IdProveedores, Cedula, EmpresaId, [$sq_row_hash_T2] FROM ' + @tabla + ' WHERE 1 = 0';
SELECT @sql = @sql + N' OR ' + QUOTENAME(name) + ' IS NULL'
   FROM sys.columns
   WHERE [object_id] = OBJECT_ID(@tabla);
EXEC sp_executesql @sql;
Validación de datos con T-SQL (Ejemplos)

Si en lugar de tablas queremos consultar sobre vistas, la consulta sería la siguiente:

DECLARE @tabla NVARCHAR(255), @sql NVARCHAR(MAX);
SET @tabla = N’Proveedor’;
SET @sql = N’SELECT * FROM etl.’ + @tabla + ‘ WHERE 1 = 0’;
SELECT @sql = @sql + N’ OR ‘ + QUOTENAME(c.name) + ‘ IS NULL’
FROM sys.columns c join sys.views v on v.object_id = c.object_id
WHERE v.name =@tabla;
PRINT @sql;
EXEC sp_executesql @sql;

Validaciones de SolidQ ABA Framework

ABA Framework es una solución desarrollada por SolidQ que utilizamos principalmente para descubrir patrones en los proyectos de Business Intelligence y optimizar procesos. Dentro de sus posibilidades de uso, ofrece test automáticos.

Al cargar datos, hace una comparación del conteo de las filas entre origen y destino, lo notifica y deja documentaod en caso de que no coincida, quedando marcado si se da algún error e ingormación sobre este mismo para poder arreglarlo.

Más información en https://www.solidq.com/wiki/aba/automatedtesting/

1- Aquí se puede apreciar la diferencia entre filas de origen y destino:

Validación de datos con T-SQL (Ejemplos)

2- Comprobando estos casos, podemos ver la información del error:

Validación de datos con T-SQL (Ejemplos)
0 Shares:
1 comment
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Leer más

Introducción a Power Query

En esta entrada repasamos la definición de ETL, presentamos SQL Server Integration Services en el contexto de BI corporativo para dar paso a Power Query como solución de BI personal.