BigQuery : Comment utiliser SQL pour créer des données de démonstration sur le nom, l’adresse, l’adresse e-mail et le numéro de téléphone des PII

La protection des informations sensibles de vos clients est cruciale. Informations personnellement identifiables (Informations personnelles), tels que les noms, adresses et numéros de téléphone, est fortement réglementé par les lois sur la confidentialité des données telles que RGPD, CCPAet HIPAA. Cependant, les développeurs, les analystes de données et Assurance qualité les équipes doivent toujours travailler avec des données réalistes pour les tests, le développement ou les démonstrations.

Par exemple, j’ai développé un OuvrirINSIGHTS compte démo pour réaliser des campagnes avec nos détaillants IA prédictions des clients. Je souhaite fournir la preuve que les données sont exploitables, ce qui signifie que je peux afficher les enregistrements clients réels avec le résultat. Cependant, je ne souhaite pas afficher les comptes, les noms réels, les adresses, les adresses e-mail, les numéros de téléphone ou tout autre PII dans les données client.

Pourquoi créer de fausses données PII ?

1. Conformité aux lois sur la confidentialité: L’utilisation de véritables informations personnelles dans des environnements de test ou de développement peut enfreindre les réglementations, entraînant de lourdes amendes.
2. Atténuation des risques: De vrais PII dans des environnements inférieurs augmentent le risque de fuites accidentelles.
3. Tests précis: Des données fausses mais réalistes vous permettent de tester efficacement les pipelines de données, les rapports et les interfaces utilisateur.
4. Environnements de démonstration: Les fausses données donnent une apparence professionnelle lors des démos sans risquer la vie privée des utilisateurs.

Les fausses données doivent-elles paraître réalistes ?

Pas nécessairement. Même si les fausses données doivent fonctionner au sein de vos systèmes, elles doivent néanmoins paraître intentionnellement fausses à toute personne observant une démonstration ou un environnement de test. Cela permet de maintenir la transparence et d’éviter toute confusion.

1. Intégrité du système: Les processus internes, tels que le filtrage par ville, état ou code postal, s’appuient souvent sur des formats et des modèles valides. Les fausses données doivent répondre à ces exigences pour garantir que le système fonctionne correctement sans erreurs. Par exemple, FERMETURE ÉCLAIR les codes doivent s’aligner sur les formats attendus, tandis que d’autres champs tels que les noms ou les adresses peuvent être remplacés par des espaces réservés clairement faux.
2. Tests fonctionnels: Le maintien de l’intégrité dans des domaines clés tels que la ville, l’état et le code postal garantit que les filtres internes, les flux de travail et les validations continuent de fonctionner comme prévu. Pendant ce temps, les fausses données pour d’autres champs (comme les noms et les adresses) peuvent aider à tester les cas extrêmes ou les performances sans affecter les fonctionnalités.
3. Clarté de la présentation: lors des démos, de fausses données qui sont clairement fausses (par exemple, Zxy Test St.) évite toute confusion tout en présentant les fonctionnalités du système. Cela établit un équilibre entre une présentation professionnelle et le maintien de la transparence sur l’utilisation des données.

Vous pouvez garantir des tests ou des démonstrations sécurisés, fonctionnels et clairs en concevant intentionnellement de fausses données pour qu’elles paraissent artificielles tout en préservant l’intégrité du système dans les domaines essentiels. Voici un aperçu de ce que j’ai pu construire :

SQL pour générer de fausses données PII

Voici la requête SQL pour créer des données PII fausses mais réalistes dans votre base de données pour le prénom, le nom, la ligne_adresse_client1, la ligne_adresse_client2, l’adresse_e-mail et le numéro de téléphone, ainsi que pour créer un champ de nom complet. J’ai ajouté cette logique uniquement pour ajouter une deuxième ligne d’adresse à 20 % des ménages.

UPDATE `project.demo.pii`
SET 
  first_name = (
    CONCAT(
      UPPER(SUBSTR('bcdfghjklmnpqrstvwxyz', CAST(FLOOR(RAND() * 21) + 1 AS INT64), 1)),
      SUBSTR('aeiou', CAST(FLOOR(RAND() * 5) + 1 AS INT64), 1),
      SUBSTR('bcdfghjklmnpqrstvwxyz', CAST(FLOOR(RAND() * 21) + 1 AS INT64), 1),
      IF(RAND() > 0.5, SUBSTR('aeiou', CAST(FLOOR(RAND() * 5) + 1 AS INT64), 1), '')
    )
  ),
  last_name = (
    CONCAT(
      UPPER(SUBSTR('bcdfghjklmnpqrstvwxyz', CAST(FLOOR(RAND() * 21) + 1 AS INT64), 1)),
      SUBSTR('aeiou', CAST(FLOOR(RAND() * 5) + 1 AS INT64), 1),
      SUBSTR('bcdfghjklmnpqrstvwxyz', CAST(FLOOR(RAND() * 21) + 1 AS INT64), 1),
      IF(RAND() > 0.5, SUBSTR('aeiou', CAST(FLOOR(RAND() * 5) + 1 AS INT64), 1), '')
    )
  ),
  customer_address_line1 = (
    CONCAT(
      CAST(CAST(FLOOR(RAND() * 99999 + 1) AS INT64) AS STRING), " ",
      UPPER(SUBSTR('bcdfghjklmnpqrstvwxyz', CAST(FLOOR(RAND() * 21) + 1 AS INT64), 1)),
      SUBSTR('aeiou', CAST(FLOOR(RAND() * 5) + 1 AS INT64), 1),
      SUBSTR('bcdfghjklmnpqrstvwxyz', CAST(FLOOR(RAND() * 21) + 1 AS INT64), 1),
      " ",
      CASE CAST(FLOOR(RAND() * 19) AS INT64)
        WHEN 0 THEN 'St'
        WHEN 1 THEN 'Ave'
        WHEN 2 THEN 'Blvd'
        WHEN 3 THEN 'Dr'
        WHEN 4 THEN 'Ln'
        WHEN 5 THEN 'Rd'
        WHEN 6 THEN 'Ci'
        WHEN 7 THEN 'Ct'
        WHEN 8 THEN 'Pl'
        WHEN 9 THEN 'Pkwy'
        WHEN 10 THEN 'Ter'
        WHEN 11 THEN 'Way'
        WHEN 12 THEN 'Sq'
        WHEN 13 THEN 'Loop'
        WHEN 14 THEN 'Trail'
        WHEN 15 THEN 'Hwy'
        WHEN 16 THEN 'Row'
        WHEN 17 THEN 'Path'
        WHEN 18 THEN 'Alley'
        ELSE 'Pass'
      END
    )
  ),
  customer_address_line2 = CASE 
    WHEN RAND() <= 0.2 THEN CONCAT(
      CASE CAST(FLOOR(RAND() * 3) AS INT64)
        WHEN 0 THEN 'Apt '
        WHEN 1 THEN 'Suite '
        ELSE 'Unit '
      END,
      CASE CAST(FLOOR(RAND() * 2) AS INT64)
        WHEN 0 THEN CONCAT(UPPER(SUBSTR('ABCDEF', CAST(FLOOR(RAND() * 6) + 1 AS INT64), 1)), CAST(FLOOR(RAND() * 999 + 1) AS STRING))
        ELSE CAST(FLOOR(RAND() * 1000 + 1) AS STRING)
      END
    )
    ELSE customer_address_line2
  END,
  email_address = CONCAT(
    LOWER(first_name), ".", LOWER(last_name), 
    CASE CAST(FLOOR(RAND() * 3) AS INT64)
      WHEN 0 THEN '@example.com'
      WHEN 1 THEN '@testmail.com'
      ELSE '@fakemail.org'
    END
  ),
  phone_number = CONCAT(
    '(', CAST(FLOOR(RAND() * 800 + 200) AS STRING), ') ',
    CAST(FLOOR(RAND() * 900 + 100) AS STRING), '-',
    CAST(FLOOR(RAND() * 9000 + 1000) AS STRING)
  ),
  customer_name = CONCAT(
    UPPER(SUBSTR(first_name, 1, 1)), SUBSTR(first_name, 2), ' ',
    UPPER(SUBSTR(last_name, 1, 1)), SUBSTR(last_name, 2)
  )
WHERE TRUE;

Décrypter le code

1. Génération first_name et last_name

• La requête génère aléatoirement faux prénom et nom en utilisant une combinaison de consonnes et de voyelles.
• Logique:
  • Choisit une consonne au hasard → SUBSTR('bcdfghjklmnpqrstvwxyz', ...)
  • Ajoute une voyelle → SUBSTR('aeiou', ...)
  • Les combine pour former un nom court et lisible avec des voyelles supplémentaires facultatives.
• Les noms semblent quelque peu réels, mais ils sont garantis faux.

2. Création customer_address_line1

• Combine un numéro de maison aléatoire avec un nom et un type de rue générés aléatoirement (par exemple, St, Ave, Blvd).
• Logique:
  • Sélectionne au hasard un nombre compris entre 1 et 99 999.
  • Construit un nom de rue en utilisant des consonnes et des voyelles.
  • Ajoute un type de rue aléatoire à partir d’une liste (par exemple, « Ln », « Way », « Trail »).

3. Manipulation customer_address_line2

• Ajoute des détails sur l’appartement, la suite ou l’unité avec une probabilité de 20 %.
• Logique:
  • Choisissez au hasard « Apt », « Suite » ou « Unit ».
  • Ajoute un numéro ou un identifiant alphanumérique.

4. Création adresse_e-mail

1. email_address:
   • Combine first_name et last_name en minuscule.
   • Ajoute l’un des faux domaines (example.com, testmail.comou fakemail.org).
   • Garantit que le format ressemble à un e-mail mais qu’il est clairement faux.

   Exemple: john.doe@example.com

4. Création d’un numéro de téléphone

1. phone_number:
   • Génère un nombre à 10 chiffres formaté comme (XXX) XXX-XXXX.
   • Indicatif régional (XXX) est compris entre 200 et 999 (les indicatifs régionaux valides commencent par 2 à 9).
   • Assure un formatage réaliste du téléphone mais avec de fausses valeurs.

   Exemple: (425) 678-1234

4. Combiner customer_name

• Formate les faux prénom et nom selon la casse du titre (par exemple, « John Smith »).

Notes finales

Cette requête permet de :

• Générer sécurisé, fausses informations personnelles pour les tests.
• Évitez les risques de non-conformité avec des données réelles.
• Maintenez le réalisme des données, en garantissant des tests et des démonstrations efficaces du système.