Aller au contenu principal

Détection des anomalies des transactions financières et traçabilité des données avec Delta Lake

Introduction​

Dans le secteur financier, la détection des anomalies dans les données de transaction est essentielle pour la prévention de la fraude, la conformité et la gestion des risques. Des montants d’opération inhabituels, des types d’opérations irrégulières et des modèles atypiques peuvent indiquer des activités frauduleuses ou des erreurs qui nécessitent une attention immédiate. L’exploitation des technologies de big data et des cadres de traitement des données avancés peut améliorer la capacité à détecter efficacement de telles anomalies.

Superset ilum sql transaction dashboard

Cet exemple présente un simplifié cas d’utilisation qui montre comment effectuer la détection d’anomalies de transaction financière à l’aide de Spark SQL et Lac Delta. La solution s’intègre également à la OpenLineage pour capturer des informations détaillées sur la traçabilité des données tout au long du pipeline de traitement des données.

info

La même chose peut être accomplie avec l’utilisation de Iceberg ou Hudi. Nous avons choisi Delta car il s’agit d’un format de table par défaut dans Ilum.

Le tableau de bord BI se fait à l’aide de Sur-ensemble Apache, qui est l’un des modules disponibles dans Ilum. Vous pouvez trouver le tableau de bord de ce cas d’utilisation ici

Description de l’ensemble de données​

L’ensemble de données utilisé dans ce cas d’utilisation se compose d’enregistrements de transactions financières simulés avec les colonnes suivantes :

  • TransactionTimestamp: Horodatage de la date à laquelle la transaction a eu lieu.
  • ID de transaction: Identifiant unique pour chaque transaction.
  • ID de compte: Identifiant unique du compte bancaire concerné.
  • Quantité: Valeur monétaire de la transaction.
  • Commerçant: Le commerçant ou l’entité impliquée dans la transaction.
  • Type de transaction: Type de transaction (par exemple, achat, retrait, transfert).
  • Emplacement: Lieu géographique où la transaction a eu lieu.

Exemples de données :

TransactionTimestampID de transactionID de compteQuantitéCommerçantType de transactionEmplacement
2023-01-01 08:00:00TXN1127ACC495071.92MarchandAchatTokyo
2023-01-01 08:01:00TXN1639ACC1015607.89MarchandAchatLondres
2023-01-01 08:02:00TXN872ACC865092.34MarchandRetraitLondres
2023-01-01 08:03:00TXN2001ACC4500.50MarchandATransfertNew York
2023-01-01 08:04:00TXN2002ACC101200.00MarchandBAchatParis
2023-01-01 08:05:00TXN2003ACC8300.00Marchand CRetraitTokyo

Objectifs​

Les principaux objectifs de ce cas d’utilisation sont les suivants :

  • Détection d’anomalies: Identifiez les transactions qui constituent des anomalies en fonction du dépassement d’une limite supérieure dérivée de la distribution des données.
  • Identification irrégulière des transactions: Détectez les types de transactions irrégulières pour des comptes spécifiques.
  • Reconnaissance des formes: Identifiez les modèles inhabituels en fonction de l’horodatage et de l’emplacement des transactions.
  • Analyse comportementale: Suivez les comportements de dépenses en fonction des marchands.
  • Résumés statistiques: Calculez le nombre total de transactions, les montants moyens, minimums et maximums par compte.
  • Intégration du lac Delta: Utilisez les fonctionnalités de Delta Lake telles que le voyage dans le temps, le flux de données modifiées et l’évolution du schéma pour améliorer le traitement des données.
  • Suivi de la traçabilité des données: Capturez et visualisez le lignage des données à l’aide du framework OpenLineage.

Présentation de la solution​

La solution consiste à créer une série de tables Delta Lake pour traiter et analyser les données de transaction. Les étapes comprennent :

  1. Ingestion de données: Chargez les données de transaction dans une table Delta Lake.
  2. Analyse statistique: Calculer des paramètres statistiques pour définir des seuils d’anomalies.
  3. Détection d’anomalies: Identifiez les transactions et les comptes qui dépassent les limites définies.
  4. Analyse des modèles: Détectez les types de transactions irrégulières et les modèles temporels.
  5. Analyse comportementale: Analysez les comportements de dépenses en fonction des marchands.
  6. Amélioration des données: Utilisez les fonctionnalités de Delta Lake pour les mises à jour de données, l’évolution du schéma et le voyage dans le temps.
  7. Rapports: Générer des rapports de synthèse pour les informations commerciales.
  8. Traçabilité des données: Tirez parti d’OpenLineage pour capturer la traçabilité des données tout au long du pipeline.

Mise en œuvre technique​

Étape 1 : Créer la table Delta des transactions​

Nous commençons par créer le Transactions table en tant que table Delta Lake pour stocker les données de transaction financière.

Code SQL :

CRÉER TABLE Transactions (
    TransactionTimestamp HORODATAGE COMMENTAIRE « Date et heure de la transaction »,
    Chaîne TransactionID COMMENTAIRE « Identifiant unique de la transaction »,
    AccountID STRING COMMENTAIRE « Identifiant unique du compte concerné »,
    Quantité DÉCIMAL(18, 2) COMMENTAIRE « Valeur monétaire de l’opération »,
    Marchand STRING COMMENTAIRE « Marchand impliqué dans la transaction »,
    TransactionType STRING COMMENTAIRE « Type de transaction (par exemple, achat, retrait) »,
    Emplacement STRING COMMENTAIRE « Lieu où la transaction a eu lieu »
)
UTILISANT DELTA
COMMENTAIRE « Tableau contenant les opérations financières »;

Insérer des exemples de données :

info

Le lien vers le fichier d’exemple se trouve ici

Il est nécessaire de télécharger le fichier sur le stockage lié à votre instance Ilum.

INSÉRER ÉCRASER TABLE Transactions
CHOISIR
   _c0,_c1,_c2,_c3,_c4,_c5,_c6
DE Le CSV.`s3a ://ilum-data/financial_anomaly_data.csv`

Explication:

  • UTILISATION DE DELTA: Spécifie qu’il s’agit d’une table Delta Lake.
  • COMMENTAIRES: Fournir des métadonnées pour une meilleure documentation et compréhension.

Exemple de sortie :

Superset ilum sql transaction dashboard

Exemple de visualisation :

Superset ilum sql transaction dashboard

Étape 2 : Détection des anomalies dans les montants des transactions​

Nous visons à identifier les transactions dont le montant dépasse une limite supérieure en fonction de la distribution des Quantité colonne.

2.1 Calculer les paramètres statistiques​

Code SQL :

CRÉER VUE amount_stats COMME
CHOISIR
    AVG(Quantité) COMME avg_amount,
    STDDEV(Quantité) COMME stddev_amount,
    PERCENTILE(Quantité, 0.95) COMME percentile_95
DE
    Transactions;

Explication:

  • AVG(Montant): Calcule le montant moyen des transactions.
  • STDDEV(Montant): Calcule l’écart-type, utile pour comprendre la dispersion des données.
  • PERCENTILE(Montant, 0,95): Calcule le 95e centile, utilisé comme limite supérieure pour les transactions normales.

Exemple de sortie :

Résultats d’ILUM SQL

2.2 Identifier les transactions dépassant la limite​

Code SQL :

CRÉER VUE Anomalies
COMME
CHOISIR
    ID de transaction,
    ID de compte,
    Quantité,
    CAS
        QUAND Quantité > (CHOISIR percentile_95 DE amount_stats) ALORS 'Dépasse-Limite'
        AUTRE 'À l’intérieur de la limite'
    FIN COMME AnomalieStatut
DE
    Transactions;

Explication:

  • AnomalieStatut: Une nouvelle colonne indiquant si la transaction dépasse la limite.

Exemple de sortie :

ID de transactionID de compteQuantitéAnomalieStatut
TXN1127ACC495071.92ExceedsLimit
............
Résultats d’ILUM SQL

Exemple de visualisation :

Résultats d’ILUM SQL

Étape 3 : Calcul des statistiques du compte​

Calculez le nombre total de transactions, les montants moyens, minimums et maximums par compte.

Code SQL :

CRÉER TABLE account_totals
UTILISANT DELTA
COMME
CHOISIR
    ID de compte,
    COMPTER(ID de transaction) COMME Nombre de transactions,
    SOMME(Quantité) COMME TotalMontant,
    AVG(Quantité) COMME Montant moyen,
    MIN(Quantité) COMME MinAmount,
    MAX(Quantité) COMME MaxAmount
DE
    Transactions
GROUPE PAR
    ID de compte;

Explication:

  • REGROUPER PAR AccountID: Agrège les données par compte.
  • Fonctions d’agrégation: Calculer des mesures statistiques pour chaque compte.

Exemple de sortie :

ID de compteNombre de transactionsTotalMontantMontant moyenMinAmountMaxAmount
ACC4295572.4247786.21500.5095071.92
..................
Résultats d’ILUM SQL

Étape 4 : Identification des comptes dépassant les limites​

Identifiez les comptes dont les transactions dépassent la limite prédéfinie.

4.1 Déterminer les comptes excédentaires​

Code SQL :

CRÉER VUE exceeding_accounts COMME
CHOISIR DISTINCT ID de compte
DE Anomalies
OÙ AnomalieStatut = 'Dépasse-Limite';

4.2 Compter les comptes excédentaires et non excédentaires​

Code SQL :

CHOISIR
    (CHOISIR COMPTER(*) DE exceeding_accounts) COMME ExceedingAccounts,
    (CHOISIR COMPTER(DISTINCT ID de compte) DE Transactions) - (CHOISIR COMPTER(*) DE exceeding_accounts) COMME NonExceedingAccounts;

Exemple de sortie :

ExceedingAccountsNonExceedingAccounts
123
Résultats d’ILUM SQL

Étape 5 : Reconnaître les modèles temporels inhabituels​

Détectez les transactions effectuées en dehors des heures normales de bureau (p. ex., de 9 h à 17 h).

Code SQL :

CRÉER VUE after_hours_transactions
COMME
CHOISIR
    *,
    HEURE(TransactionTimestamp) COMME Heure de transaction
DE
    Transactions
OÙ
    HEURE(TransactionTimestamp) NON ENTRE 9 ET 17;

Explication:

  • HOUR(Horodatage): Extrait l’heure de l’horodatage.
  • PAS ENTRE 9 ET 17 ANS: Filtre les transactions en dehors des heures d’ouverture.

Étape 7 : Analyse des comportements de dépenses par commerçant​

Agrégez les données de dépenses pour comprendre les comportements des clients en fonction des interactions avec les commerçants.

Code SQL :

CRÉER TABLE merchant_spending
UTILISANT DELTA
COMME
CHOISIR
    Commerçant,
    COMPTER(*) COMME Nombre de transactions,
    SOMME(Quantité) COMME TotalDépensé,
    AVG(Quantité) COMME AverageTransactionAmount,
    MIN(Quantité) COMME MinAmount,
    MAX(Quantité) COMME MaxAmount
DE
    Transactions
GROUPE PAR
    Commerçant;

Exemple de sortie :

CommerçantNombre de transactionsTotalDépenséAverageTransactionAmountMinAmountMaxAmount
Marchand2110679.8155339.9115607.8995071.92
..................
Résultats d’ILUM SQL

Exemple de visualisation :

Résultats d’ILUM SQL

Étape 8 : Tirer parti des fonctionnalités de Delta Lake​

8.1 Activer le flux de données de modification​

Activez le flux de données de modification de Delta Lake pour suivre les modifications dans le Transactions table.

Code SQL :

CHANGER TABLE Transactions
POSER TBLPROPERTIES (delta.enableChangeDataFeed = vrai);

8.2 Effectuer des mises à jour et suivre les modifications​

Mettez à jour les montants des transactions en raison des corrections et faites le suivi de ces changements.

Code SQL :

-- Mettre à jour une transaction
METTRE À JOUR Transactions
POSER Quantité = 1300.00
OÙ ID de transaction = 'TXN1127';
-- Vérifiez la dernière version
DÉCRIRE HISTOIRE Transactions;
Résultats d’ILUM SQL 

-- Interroger les modifications à l’aide du flux de données de modification avec la version de table appropriée
CHOISIR *
DE table_changes(« Transactions », 3)
OÙ ID de transaction = 'TXN1127';
Résultats d’ILUM SQL

8.3 Evolution du schéma : Ajouter une nouvelle colonne​

Ajouter une nouvelle colonne FraudFlag pour signaler les transactions frauduleuses présumées.

Code SQL :

CHANGER TABLE Transactions
AJOUTER COLONNES (FraudFlag BOOLÉEN COMMENTAIRE « Indicateur indiquant une fraude présumée »);

8.4 Mettre à jour les transactions frauduleuses​

Signalez les transactions dépassant la limite supérieure.

Code SQL :

METTRE À JOUR Transactions
POSER FraudFlag = VRAI
OÙ Quantité > 95071.92;
Résultats d’ILUM SQL

8.5 Voyage dans le temps et restauration de la table​

Afficher l’historique des tables​

Code SQL :

DÉCRIRE HISTOIRE Transactions;
Résultats d’ILUM SQL
Interroger une version précédente​

Code SQL :

CHOISIR * DE Transactions VERSION COMME DE 3;
Restaurer la table à une version précédente​

Code SQL :

RESTAURER TABLE Transactions À VERSION COMME DE 3;

Explication:

  • Voyage dans le temps: Accédez aux versions précédentes des données à des fins d’audit ou de récupération.
  • Restauration de table: Rétablit l’état de la table à un état dont le fonctionnement a été vérifié après des modifications involontaires.

Étape 9 : Génération des rapports sommaires finaux​

Générez des rapports récapitulant les comptes dépassant les limites et calculez les moyennes.

Code SQL :

CRÉER TABLE account_exceeding_summary
UTILISANT DELTA
COMME
CHOISIR
    un.ID de compte,
    un.Nombre de transactions,
    un.TotalMontant,
    un.Montant moyen,
    un.MinAmount,
    un.MaxAmount,
    CAS
        QUAND e.ID de compte EST NON ZÉRO ALORS 'Dépasse-Limite'
        AUTRE 'À l’intérieur de la limite'
    FIN COMME ExceedStatus
DE
    account_totals un
GAUCHE UNIR
    exceeding_accounts e SUR un.ID de compte = e.ID de compte;
Résultats d’ILUM SQL 
-- Calculer les moyennes pour les comptes excédentaires et non excédentaires
CHOISIR
    ExceedStatus,
    COMPTER(ID de compte) COMME Comptage,
    AVG(TotalMontant) COMME AverageTotalAmount,
    AVG(Montant moyen) COMME AverageTransactionAmount
DE
    account_exceeding_summary
GROUPE PAR
    ExceedStatus;
Résultats d’ILUM SQL

Caractéristiques du lac Delta utilisées​

  • Delta Tables: Toutes les tables sont créées à l’aide de Delta Lake pour tirer parti des transactions ACID et de l’application des schémas.
  • Voyage dans le temps: Accédez aux versions précédentes des données à des fins d’audit et de récupération.
  • Restauration de table: Restaurer les tables à un état précédent si nécessaire.
  • Modifier le flux de données: Suivez les modifications apportées aux données pour le traitement et l’audit incrémentiels.
  • Évolution du schéma: Ajouter de nouvelles colonnes (FraudFlag) sans perturber les données existantes.
  • Contraintes et contrôles: Renforcer l’intégrité des données grâce à des définitions de schéma.
  • Partitionnement: Bien qu’il ne soit pas explicitement utilisé ici, Delta Lake prend en charge le partitionnement pour l’optimisation des performances.

Intégration de Data Lineage et d’OpenLineage​

En exécutant ces opérations SQL dans un environnement intégré à OpenLineage, chaque opération génère des métadonnées de lignage. Ces métadonnées capturent :

  • Flux de données: Comment les données se déplacent d’une table à une autre (par exemple, à partir de Transactions À Anomalies).
  • Transformations: Les transformations spécifiques appliquées aux données (par exemple, l’ajout de AnomalieStatut).
  • Modifications du schéma: Modifications apportées au schéma de la table (par exemple, ajout de FraudFlag colonne).
  • Gestion des versions: Modifications des versions de table dues aux mises à jour et aux restaurations.

Avantages:

  • Transparence: Une compréhension claire de la manière dont les données sont traitées et transformées.
  • Audit: Possibilité de tracer la traçabilité des données à des fins de conformité et d’audit.
  • Débogage: Identification plus facile des problèmes dans les pipelines de traitement des données.
  • Optimisation: Les informations sur le flux de données peuvent aider à optimiser les performances.
lignage des données ilum

Conclusion​

Ce cas d’utilisation montre comment effectuer la détection d’anomalies financières à l’aide de Spark SQL et de Delta Lake, en intégrant des fonctionnalités clés telles que le voyage dans le temps, le flux de données modifiées et l’évolution du schéma. En s’intégrant à OpenLineage, la solution fournit un suivi complet de la traçabilité des données, améliorant ainsi la transparence et la conformité.

Principaux points à retenir :

  • Détection efficace des anomalies: Utiliser des méthodes statistiques pour identifier les transactions et les comptes qui s’écartent des modèles normaux.
  • Gestion robuste des données: Tirer parti des fonctionnalités de Delta Lake pour garantir l’intégrité des données, faciliter les modifications de schéma et activer les options de récupération.
  • Visibilité de la traçabilité des données: Capture d’informations de traçabilité détaillées pour prendre en charge les exigences d’audit et de conformité.
  • Perspectives commerciales: Générer des rapports et des analyses qui fournissent des informations précieuses sur les comportements des clients et les risques potentiels.

Appendice​

Schéma de l’ensemble de données​

transactions Schéma de la table :

ColonneType de donnéesDescription
TransactionTimestampHORODATAGEDate et heure de l’opération
ID de transactionCORDEIdentifiant unique de la transaction
ID de compteCORDEIdentifiant unique du compte concerné
QuantitéDÉCIMAL(18,2)Valeur monétaire de la transaction
CommerçantCORDEMarchand impliqué dans la transaction
Type de transactionCORDEType de transaction (par exemple, achat, retrait)
EmplacementCORDELieu où l’opération a eu lieu
FraudFlagBOOLÉENIndicateur indiquant une fraude présumée (ajouté ultérieurement)

Références:

Last updated on