Seguramente todos los que trabajamos con bases de datos y datawarehouses nos hemos enfrentado a la problemática de la duplicidad en los datos. Los datos duplicados pueden llegar a nuestras tablas debido a errores humanos, por problemas en la extracción dentro de nuestras ETLs o por un cambio en la definición de lo que se entiende por un duplicado.
Cuando ingestamos datos a una tabla es habitual que estos lleven asociados una natural key que se utiliza como identificador único en cada fila (primary key), pero ¿qué ocurre cuándo, por ejemplo, debemos de introducir datos que provienen de diferentes fuentes donde la primary key puede tener valores similares en origen? ¿o si por algún tipo de error se ingestan varias veces los mismos datos?
Tengamos en cuenta, también, que estos datos podrían acabar utilizándose para entrenar un modelo matemático en una tarea de aprendizaje automático. La frase “garbage in, garbage out” define, de manera muy acertada, los problemas asociados a la mala calidad de los datos utilizados en estas aplicaciones.
Es en este punto donde entran en juego las surrogate keys. Este tipo de keys, a diferencia de las primary keys, no tienen ningún significado de negocio, lo que las hace persistentes en el tiempo y además no permite introducir valores ilimitados. Estas características hacen de ella una herramienta muy potente a la hora de atajar la problemática de las duplicidades en nuestros datos.
En este post veremos algunas ideas sobre cómo podemos utilizar Big Query para eliminar los duplicados y presentaremos dos ejemplos para ver de manera didáctica su uso dentro de un contexto concreto.
Generando surrogate keys
En el caso de Big Query y utilizando lenguaje SQL hay varias maneras de generar este tipo de keys:
- Utilizando la función ROW_NUMBER() OVER()
- Utilizando la función GENERATE_UUID()
- Utilizando funciones de hashing
Función ROW_NUMBER()
Una de las formas más sencillas de crear nuestra surrogate key sería asignar a cada una de las filas un número incremental de manera que hiciésemos cada fila única (Figura 1). Esto puede realizarse utilizando la función del lenguaje SQL ROW_NUMBER():
- SELECT ROW_NUMBER() OVER() AS surrogateKey, *
- FROM `bigquery-testing.testing-data.testing01`
Función GENERATE_UUID()
Esta función disponible en Big Query nos genera un identificador único para cada fila del resultado de la query. El resultado es una string consistente en 32 dígitos hexadecimales en grupos de 5 y separados por guiones (Figura 2).
- SELECT GENERATE_UUID() AS surrogateKey, *
- FROM `bigquery-testing.testing-data.testing01`
Funciones de hashing
En este caso utilizamos una función de hashing para crear la key utilizando uno o varios campos de la fila. La función (normalmente se utiliza el algoritmo SHA256) nos devuelve una serie de caracteres basados en los valores de los campos sobre los que hacemos el computo, siendo este resultado el que utilizaremos como surrogate key (Figura 3).
- SELECT (SHA256(field1)) AS surrogateKey, *
- FROM `bigquery-testing.testing-data.testing01`
Casos de uso
Para poner en contexto la utilidad de este tipo de keys vamos a utilizar como ejemplo dos casos de uso que necesitan enfoques diferentes a la hora de trabajar con los duplicados. En ambos ejemplos lo que tratamos es de llevar una serie de datos a un dashboard añadiendo un paso intermedio entre las tablas de Big Query y el dashboard para solucionar la duplicidad.
Sistema con dispositivos IoT
En este supuesto tendríamos una red de dispositivos desplegados IoT desplegados en diferentes localizaciones midiendo diferentes variables meteorológicas y conectados a la Cloud. La problemática aquí está en la duplicidad de los valores del campo Device ID generados en las tablas origen. Una solución, en este caso, es utilizar la función GENERATE_UUID() en una vista para añadir la surrogate key a cada fila de manera que podamos referenciarla de manera unívoca y no tengamos problema al agregar datos provenientes de diferentes sensores.
Ingesta de logs
En este segundo ejemplo tenemos un servidor enviando datos sobre ciertos eventos, cada uno con un TIMESTAMP asociado. Consideramos que cada una de las filas que se generan con información del evento debería ser única, es decir, una fila se va a considerar como duplicado de otra si todos los valores de los campos son iguales entre ambas.
En la Figura 2 vemos la arquitectura propuesta y la vista utilizada para filtrar los duplicados. Se ha utilizado en este caso la función de hashing sobre todos los campos de cada fila de manera que en caso de que haya dos filas iguales estas tendrían la misma key. La query desplegada dentro de la vista nos ayuda a mostrar solamente filas distintas basadas en la surrogate key eliminando, por tanto, los duplicados antes de su ingesta en el dashboard.
En estos dos casos de uso hemos visto algunas de las soluciones más interesantes a la hora de evitar que la duplicidad de datos dentro de nuestros datawarehouses desvirtúen la calidad de los datos y se propaguen dentro de nuestra pipeline.
Os invito a que descubráis de qué manera este tipo de keys pueden ayudaros en vuestros propios casos de uso o que compartáis otro tipo de soluciones que estáis utilizando para gestionar esta problemática.
