0
Shares
0
0
0

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:

Más ejemplos de validación de datos con T-SQL

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

Más ejemplos de validación de datos con T-SQL

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

Más ejemplos de validación de datos con T-SQL

Vemos que parece haber insertado el dato correctamente.

Más ejemplos de validación de datos con T-SQL

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

SELECT DISTINCT(PostalCode) 
FROM bi.[Address] 
WHERE postalcode IN ('32804', 'K4B 1', 'H1Y 2','T2P 2','33000','14111','3000')

Vemos que solo nos salieron estos códigos postales:

Más ejemplos de validación de datos con T-SQL

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 cambiando el tipo de dato de nvarchar(5) a nvarchar(15), podemos comprobar que ya no aparece el warning en el proceso ETL:

Más ejemplos de validación de datos con T-SQL

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

Más ejemplos de validación de datos con T-SQL

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')

Más ejemplos de validación de datos con T-SQL

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'
Más ejemplos de validación de datos con T-SQL

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



Más ejemplos de validación de datos con T-SQL


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]


Más ejemplos de validación de datos con T-SQL


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


Más ejemplos de validación de datos con T-SQL


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')
Más ejemplos de validación de datos con T-SQL




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%'

Más ejemplos de validación de datos con T-SQL


Si tenemos una Base de Datos con múltiples esquemas, podemos hacer que los muestre también si cruzamos con SYS.SCHEMAS en la búsqueda:


SELECT t.name AS tabla, s.name AS esquema, c.name AS columna
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name NOT LIKE '%$%'
AND s.name IN ('dbo','stg')
AND c.name LIKE '%Proveedor%'
ORDER BY t.schema_id, t.name


Más ejemplos de validación de datos con T-SQL


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;


Más ejemplos de validación de datos con T-SQL


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/


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


Más ejemplos de validación de datos con T-SQL


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


Más ejemplos de validación de datos con T-SQL


 


¿Necesitas mejorar tus skills con T-SQL? ¿Has aprendido por tu cuenta y necesitas entender mejor la lógica detrás del lenguaje para hacer más efectivo tu código? Podría interesarte el curso T-SQL Fundamentals, impartido por Itzik Ben-Gan.


		

				


							

							
0 Shares:

						

				
			


										


							

																	
								
								
																	Share
								
																	0
															

							
							
													

											


							

																	
								
								
																	Tweet
								
																	0
															

							
							
													

											


							

																	
								
								
																	Share
								
																	0
															

							
							
													

								

		

	




	


			

			

				

					
				

				

					

						
							
						
					

					

							

			

										

							
								
															
						

								

		

						

			

		

	
	











	
	
		

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



 


 


	

	




	


				

			
	



					

						
					

						

							 — Previous article
						


						

							Cuatro recetas para que tus proyectos de Machine Learning tengan un impacto real en el negocio						

					

				
							
					

						

							Next article — 
						


						

							Depurar aplicaciones contra datos de producción: ofuscación y GDPR						

					

				
				

		


			


				
				

					

						You May Also Like					

				


				


					






	

		
AArtículos de Data

				

			

				

					

																	

					

						Leer más
						
  • 4 minute read
											

					
				

			

		

		
		

						

				
Aplicando seguridad a métricas SSAS
			


							

											

							Aplicando seguridad a métricas SSAS. Para controlar la seguridad de los objetos, operaciones y datos de Analysis Services se utilizan roles (grupos de usuarios). Los usuarios se pueden añadir o quitar de los roles y para esos roles se determinan unos permisos.