Cadre de qualité des données dans Snowflake

Cadre de qualité des données dans Snowflake


Dans une solution traditionnelle d’extraction, de chargement et de transformation (ELT) ou d’entrepôt de données, vous devez ingérer des données dans votre zone de staging à partir de diverses sources et préparer les données avant qu’elles ne puissent être traitées ultérieurement par des applications en aval. Si la qualité des données est négligée, les utilisateurs de l’entrepôt de données disposeront de données inexactes et incomplètes. Cela entraîne des résultats inexacts produits par des requêtes analytiques exécutées sur l’ensemble de données « sales ».

Ce cadre de qualité des données est basé sur des règles de qualité des données configurables appliquées à une colonne spécifique ou à un ensemble de colonnes d’une table Snowflake (intermédiaire), conservant ainsi un ensemble de données pour éliminer les enregistrements erronés.

L’objectif de cet article est d’aider à créer un cadre de qualité des données basé sur la configuration qui peut être exploité pour nettoyer tout flux de système source avec des modifications de code minimales ou nulles, réduisant ainsi le TTM.

Avantages commerciaux :

Vous pouvez obtenir les avantages suivants en utilisant ce framework :

  • Organisez n’importe quelle table Snowflake en plaçant les règles sous forme de configurations. Cela garantit que votre délai de mise sur le marché (TTM) est court car vos développeurs n’ont pas besoin de créer de code. De plus, ce cadre peut leur donner un bon départ pour personnaliser rapidement et raccourcir la phase de construction.
  • Les développeurs n’ont qu’à appliquer les détails de la règle de qualité des données à une table CONFIG. Cela signifie qu’aucune modification de code n’est impliquée pour nettoyer toute nouvelle source de données.
  • Ce cadre prend en charge l’évolution du schéma. Toute modification de la structure de table d’une table existante n’a pas d’impact sur le cadre de la solution, éliminant ainsi le besoin de modifier le code.
  • Les développeurs/utilisateurs n’ont pas besoin d’avoir une expertise dans Snowflake pour utiliser ce framework. Des connaissances de base en SQL sont suffisantes.
Présentation du cadre de la solution
mR1QDD8MaUzZVDp VzUgnsvK4FRVnud228gUCh8W nRFvZNSUd79gsOFDokUoTumTHgNbkFCfU9mSTlfu56AYdj711M3kgjtNnKOq9WFXDGMsbkI5UXO0dk
Règles de qualité des données :

Une procédure stockée JavaScript est créée pour chaque règle de qualité des données. Lorsque la règle de qualité des données est appliquée à une colonne d’une table source, le framework insère l’enregistrement correspondant de cette table source pour lequel la colonne en cours de contrôle de qualité des données ne satisfait pas la règle de qualité des données concernée dans le DQ_RULE_VALIDATION_RESULTS table. En outre, il insère d’autres informations de métadonnées, telles que TABLE_NAME, COL_NAME, INVALID_VALUE, DQ_RULE, et ERR_MSG dans le même DQ_RULE_VALIDATION_RESULTS table.

Chaque procédure stockée de règle de qualité des données prend les cinq paramètres suivants :

  • DB_NAME : Nom de la base de données source
  • SCHEMA_NAME : Nom du schéma source
  • NOM DE LA TABLE: Nom de la table source
  • CONFIG_NAME : Une chaîne JSON avec la valeur du paramètre
  • REQD_CLAEANSED_RECORD : Un indicateur pour indiquer s’il faut créer une table CLEANSED

Les règles de qualité des données suivantes ont été créées :

  • RULE_DATE : Utilisé pour vérifier la valeur de la date conformément au modèle fourni
  • RULE_DECIMAL : Utilisé pour vérifier une valeur décimale
  • RULE_INTEGER : Utilisé pour vérifier une valeur entière
  • RULE_LENGTH : Utilisé pour vérifier si la longueur d’un champ est dans la valeur fournie
  • RULE_NOT_NULL : Utilisé pour vérifier si un champ contient une valeur NULL
  • RULE_REGEX : Utilisé pour vérifier si un champ est conforme au modèle regex fourni
  • RULE_SQL_FILTER : Utilisé pour vérifier si un enregistrement satisfait un prédicat SQL
  • RULE_UNIQUE : Utilisé pour valider si un champ contient des valeurs uniques
  • RULE_VALID_VALUES : Utilisé pour vérifier si un champ contient des valeurs spécifiées dans le tableau de valeurs fourni
Exemple de procédure stockée de règle de qualité des données :

Base de données: ANALYTIQUE

Schéma: SNOWDQ

Table: STORE_DQ

Nom de la règle : RULE_DATE

Description : Valide un champ date/datetime selon le format fourni

CREATE OR REPLACE PROCEDURE RULE_DATE (
  	DB_NAME VARCHAR, SCHEMA_NAME VARCHAR, TABLE_NAME VARCHAR, 
  	CONFIG_NAME VARIANT, REQD_CLEANSE_RECORD BOOLEAN)
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
    var error_msg = [];
--validate_date is a JAVA UDF used to check whether a date is valid.
    var qry = `
	insert into analytics.snowdq.DQ_RULE_VALIDATION_RESULTS (table_name,col_name,invalid_value,DQ_RULE,err_msg,err_rec)
    SELECT CONCAT('${DB_NAME}','.','${SCHEMA_NAME}','.','${TABLE_NAME}'),
	    '${CONFIG_NAME["COL"]}',
		${CONFIG_NAME["COL"]},
		concat('RULE_DATE: ','${CONFIG_NAME["FORMAT"]}'),
        '${CONFIG_NAME["COL"]} HAS INVALID DATE' AS ERR_MSG,
		object_construct(*)
    FROM "${DB_NAME}"."${SCHEMA_NAME}"."${TABLE_NAME}"
    WHERE validate_date(${CONFIG_NAME["COL"]},'${CONFIG_NAME["FORMAT"]}') = false
	and ${CONFIG_NAME["COL"]} IS NOT NULL
    ;`
--Create a temporary table to store the BAD records
    try {
      var rs = snowflake.execute({ sqlText: qry });
	  if(REQD_CLEANSE_RECORD)
	  {
	  var qry = `
	  create temporary table if not exists ${DB_NAME}.${SCHEMA_NAME}.${TABLE_NAME}_TEMP
	  like ${DB_NAME}.${SCHEMA_NAME}.${TABLE_NAME};`
	   
	  var rs_temp = snowflake.execute({ sqlText: qry });
	  
	  var qry = `
	  insert into ${DB_NAME}.${SCHEMA_NAME}.${TABLE_NAME}_TEMP
	  select * from ${DB_NAME}.${SCHEMA_NAME}.${TABLE_NAME}
	  WHERE validate_date(${CONFIG_NAME["COL"]},'${CONFIG_NAME["FORMAT"]}') = false
	  and ${CONFIG_NAME["COL"]} IS NOT NULL;`
	   
	  var rs_ins = snowflake.execute({ sqlText: qry });
	  
	  }
	  return "RULE_DATE VALIDATION COMPLETED Successfully";
    } catch (err) {
        error_msg.push(` {
        sql statement : ‘${qry}’,
          error_code : ‘${err.code}’,
          error_state : ‘${err.state}’,
          error_message : ‘${err.message}’,
          stack_trace : ‘${err.stackTraceTxt}’
          } `);
      return error_msg;		  
    }
$$;


Extrait de code pour valider_date Java udf stocké dans une étape interne Snowflake :

create or replace function validate_date(datestring varchar, format varchar)
returns boolean
language JAVA
IMPORTS = ('@dq_udf_jar/dq_udf_utility.jar')
handler="IsDate.apply";

Une procédure stockée maître nommée DATA_QUALITY_RULE_VALIDATIONcomme mentionné précédemment dans le diagramme du cadre de présentation de la solution, est créé pour appeler la qualité des données individuelles RÉGNER procédures stockées basées sur les entrées effectuées dans DQ_RULE_CONFIG tableau (un instantané des données du tableau est donné ci-dessous) pour un TABLEAU DES SOURCESAPPLY_RULE le drapeau est défini sur vrai. Il prend les arguments suivants :

  • DB_NAME: nom de la BD source
  • SCHEMA_NAME: nom du schéma source
  • NOM DE LA TABLE: nom de la table source
  • REQD_CLAEANSED_RECORD: Un indicateur pour indiquer s’il faut créer un NETTOYÉ table

Si REQD_CLAEANSED_RECORD est réglé sur vraiil crée alors un NETTOYÉ table ayant exactement la même structure que celle de la table source contenant dynamiquement uniquement les enregistrements propres.

Un exemple d’appel à cette procédure stockée principale ressemblerait à ceci :

appeler DATA_QUALITY_RULE_VALIDATION('ANALYTICS','SNOWDQ','STORE_DQ',true);

Cette procédure stockée appellera toutes les procédures stockées de règle de qualité des données comme indiqué dans le DQ_RULE_CONFIG table pour toute table source en cours de validation de la qualité des données. Un instantané de la DQ_RULE_CONFIG les données du tableau sont données ci-dessous.

Fonctionnalités du cadre de qualité des données :

  • DQ_RULE_CONFIG table contiendra le mappage de règles pour une table, y compris le nom de la règle, le paramètre de la règle et l’indicateur d’application de la règle.
  • L’ajout ou la suppression de règles sur un ensemble de données ne nécessite aucune modification du code. Seulement CONFIG les entrées de table doivent être mises à jour.
  • Si une règle de qualité des données pour une table doit être ignorée, seule la APPLY_RULE l’indicateur doit être défini sur FAUX pour cette entrée.
  • Une procédure stockée maître est créée pour appeler les procédures de règle de qualité des données en fonction des entrées effectuées dans le DQ_RULE_CONFIG table pour une table à valider.
  • Tous les enregistrements validés peuvent éventuellement être chargés dans un NETTOYÉ table pour le traitement en aval. REQD_CLEANSE_RECORD Le paramètre d’entrée de la procédure maître est utilisé pour le déterminer.
Instantané de la table DQ_RULE_CONFIG

Toutes les règles DQ appliquées à STORE_DQ tableau sont présentés ci-dessous :

a78UAKVHH9Set aoPLBco zjQ3OMJ7 5vv dZIKEM6db22fJ1kpfPay9BNeUkuFUuTO7uU HSpCWtt6OEbTXgnaz1hFztaE lrOiBKPOI2n8ZXfWor5yBg2pN9B5z2UxWAV8EL7da K9FGAf1W8SjQ
Instantané de la table DQ_RULE_VALIDATION_RESULTS

Vous trouverez ci-dessous quelques-uns des enregistrements de STORE_DQ table qui n’a pas satisfait aux vérifications de la règle DQ :

Vous trouverez ci-dessous un exemple de mauvais enregistrement qui n’a pas satisfait RULE_DATE DQ règle en mode d’affichage d’enregistrement :

fMP8mjoQpZnKXTrbj29d0UjdIB78exYJXwJ4LAaaXMhkNDh SToibNCNJ2jCsQ0rwm7c8TtLqH0 PubDz QN5PMHRQ9TwkJ3NG053DAQOwizxKqq47m uHiXDY5XmDhO3fK9pci92 bsqaezO1dnUQ
Conclusion

Ce cadre de qualité des données peut être étendu pour inclure des règles de nettoyage plus complexes afin de répondre aux exigences du système. DQ_RULE_VALIDATION_RESULTS La table peut être utilisée pour créer des tableaux de bord dans Snowsight ou dans tout autre outil de BI pour capturer le résumé des enregistrements incorrects au niveau de la table, de la règle DQ ou au niveau de l’enregistrement.

Le framework peut être programmé dans la tâche pour une solution Snowflake à part entière ou il peut être intégré à n’importe quel outil ETL/ELT tel que Talend, Informatica, dbt, etc. Avec le besoin croissant de données précises et propres, ce framework devrait aider pour organiser les données post-acquisition dans la plate-forme de données.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.