Vai al contenuto principale

Rilevamento delle anomalie delle transazioni finanziarie e derivazione dei dati con Delta Lake

Introduzione​

Nel settore finanziario, il rilevamento di anomalie nei dati delle transazioni è fondamentale per la prevenzione delle frodi, la conformità e la gestione del rischio. Importi di transazione insoliti, tipi di transazione irregolari e modelli atipici possono indicare attività fraudolente o errori che richiedono un'attenzione immediata. L'utilizzo delle tecnologie dei big data e dei framework avanzati di elaborazione dei dati può migliorare la capacità di rilevare tali anomalie in modo efficace.

Dashboard delle transazioni SQL di Superset ilum

Questo esempio presenta un semplificato caso d'uso che illustra come eseguire il rilevamento delle anomalie delle transazioni finanziarie utilizzando Spark SQL e Lago Delta. La soluzione si integra anche con il OpenLineage Framework per acquisire informazioni dettagliate sulla derivazione dei dati in tutta la pipeline di elaborazione dei dati.

Informazioni

La stessa cosa può essere ottenuta con l'uso di Iceberg o Hudi. Abbiamo scelto Delta perché è un formato di tabella predefinito in Ilum.

Il cruscotto BI viene eseguito con l'uso di Apache Superset, che è uno dei moduli disponibili in Ilum. Puoi trovare la dashboard per questo caso d'uso qui

Descrizione del set di dati​

Il set di dati utilizzato in questo caso d'uso è costituito da record di transazioni finanziarie simulate con le seguenti colonne:

  • TransactionTimestamp: Timestamp di quando è avvenuta la transazione.
  • ID transazione: identificatore univoco per ogni transazione.
  • ID account: Identificatore univoco del conto bancario interessato.
  • Importo: Valore monetario della transazione.
  • Mercante: l'esercente o l'entità coinvolta nella transazione.
  • Tipo di transazione: Tipo di transazione (ad esempio, Acquisto, Prelievo, Trasferimento).
  • Ubicazione: Posizione geografica in cui è avvenuta la transazione.

Dati di esempio:

TransactionTimestampID transazioneID accountImportoMercanteTipo di transazioneUbicazione
2023-01-01 08:00:00TXN1127ACC495071.92Commerciante HAcquistaTokyo
2023-01-01 08:01:00TXN1639ACC1015607.89Commerciante HAcquistaLondra
2023-01-01 08:02:00TXN872ACC865092.34CommercianteRitiroLondra
2023-01-01 08:03:00TXN2001ACC4500.50Commerciante ATrasferimentoNew York
2023-01-01 08:04:00TXN2002ACC101200.00Mercante BAcquistaParigi
2023-01-01 08:05:00TXN2003ACC8300.00Commerciante CRitiroTokyo

Obiettivi​

Gli obiettivi principali di questo caso d'uso sono:

  • Rilevamento anomalie: Identificare le transazioni che sono anomalie in base all'importo che supera un limite superiore derivato dalla distribuzione dei dati.
  • Identificazione irregolare delle transazioni: Rileva i tipi di transazione irregolari per account specifici.
  • Riconoscimento: Riconosci modelli insoliti in base ai timestamp e alle posizioni delle transazioni.
  • Analisi comportamentale: Monitora i comportamenti di spesa in base ai commercianti.
  • Riepiloghi statistici: Calcola le transazioni totali, gli importi medi, minimi e massimi delle transazioni per account.
  • Integrazione Delta Lake: Utilizza le funzionalità di Delta Lake come il viaggio nel tempo, il feed di dati delle modifiche e l'evoluzione dello schema per migliorare l'elaborazione dei dati.
  • Monitoraggio della derivazione dei dati: Acquisisci e visualizza la derivazione dei dati utilizzando il framework OpenLineage.

Panoramica della soluzione​

La soluzione prevede la creazione di una serie di tabelle Delta Lake per elaborare e analizzare i dati delle transazioni. I passaggi includono:

  1. Acquisizione dei dati: carica i dati della transazione in una tabella Delta Lake.
  2. Analisi statistica: consente di calcolare i parametri statistici per definire le soglie di anomalia.
  3. Rilevamento anomalie: Identifica le transazioni e i conti che superano i limiti definiti.
  4. Analisi dei modelli: Rileva tipi di transazione irregolari e modelli temporali.
  5. Analisi comportamentale: analizza i comportamenti di spesa in base ai commercianti.
  6. Miglioramento dei dati: utilizza le funzionalità Delta Lake per gli aggiornamenti dei dati, l'evoluzione dello schema e i viaggi nel tempo.
  7. Cronaca: genera report di riepilogo per approfondimenti aziendali.
  8. Derivazione dei dati: Sfrutta OpenLineage per acquisire la derivazione dei dati in tutta la pipeline.

Implementazione tecnica​

Passaggio 1: Creare la tabella Delta delle transazioni​

Iniziamo creando il Transazioni tabella come tabella Delta Lake per memorizzare i dati delle transazioni finanziarie.

Codice SQL:

CREARE TAVOLO Transazioni (
    TransactionTimestamp TIMESTAMP COMMENTO «Data e ora in cui è avvenuta la transazione»,
    TransactionID STRING COMMENTO «Identificativo unico per la transazione»,
    STRINGA AccountID COMMENTO "Identificatore univoco per l'account interessato",
    Importo DECIMALE(18, 2) COMMENTO «Valore monetario dell'operazione»,
    STRINGA commerciante COMMENTO "Commerciante coinvolto nella transazione",
    TransactionType STRING COMMENTO "Tipo di transazione (ad esempio, acquisto, prelievo)",
    Posizione STRING COMMENTO «Luogo in cui è avvenuta la transazione»
)
UTILIZZANDO DELTA
COMMENTO «Tabella contenente le operazioni finanziarie»;

Inserisci dati di esempio:

Informazioni

Il link al file di esempio può essere trovato qui

È necessario caricare il file nello storage collegato all'istanza Ilum.

INSERIRE SOVRASCRIVERE TAVOLO Transazioni
SELEZIONARE
   _c0,_c1,_c2,_c3,_c4,_c5,_c6
DA CSV.`s3a://ilum-data/financial_anomaly_data.csv`

Spiegazione:

  • UTILIZZO DI DELTA: specifica che la tabella è una tabella Delta Lake.
  • COMMENTI: Fornisci metadati per una migliore documentazione e comprensione.

Output di esempio:

Dashboard delle transazioni SQL di Superset ilum

Esempio di visualizzazione:

Dashboard delle transazioni SQL di Superset ilum

Passaggio 2: rilevamento di anomalie negli importi delle transazioni​

Il nostro obiettivo è identificare le transazioni in cui l'importo supera un limite massimo in base alla distribuzione del Importo colonna.

2.1 Calcola i parametri statistici​

Codice SQL:

CREARE VISTA amount_stats COME
SELEZIONARE
    MEDIO(Importo) COME avg_amount,
    STDDEV(Importo) COME stddev_amount,
    PERCENTILE(Importo, 0.95) COME percentile_95
DA
    Transazioni;

Spiegazione:

  • AVG(Importo): calcola l'importo medio della transazione.
  • STDDEV(Importo): Calcola la deviazione standard, utile per comprendere la dispersione dei dati.
  • PERCENTILE(Importo, 0,95): calcola il 95° percentile, utilizzato come limite superiore per le normali transazioni.

Output di esempio:

Risultati ILUM SQL

2.2 Identificare le transazioni che superano il limite​

Codice SQL:

CREARE VISTA Anomalie
COME
SELEZIONARE
    ID transazione,
    ID account,
    Importo,
    CASO
        QUANDO Importo > (SELEZIONARE percentile_95 DA amount_stats) ALLORA 'Supera il limite'
        ALTRO 'Entro il limite'
    FINE COME AnomalyStatus
DA
    Transazioni;

Spiegazione:

  • AnomalyStatus: una nuova colonna che indica se la transazione supera il limite.

Output di esempio:

ID transazioneID accountImportoAnomalyStatus
TXN1127ACC495071.92Supera il limite
............
Risultati ILUM SQL

Esempio di visualizzazione:

Risultati ILUM SQL

Passaggio 3: calcolo delle statistiche dell'account​

Calcola le transazioni totali, gli importi medi, minimi e massimi delle transazioni per account.

Codice SQL:

CREARE TAVOLO account_totals
UTILIZZANDO DELTA
COME
SELEZIONARE
    ID account,
    CONTARE(ID transazione) COME Conteggio transazioni,
    SOMMA(Importo) COME Importo totale,
    MEDIO(Importo) COME Importo medio,
    MIN(Importo) COME Importo minimo,
    MAX(Importo) COME Importo massimo
DA
    Transazioni
GRUPPO PRESSO
    ID account;

Spiegazione:

  • RAGGRUPPA PER AccountID: aggrega i dati per account.
  • Funzioni di aggregazione: Calcola le misure statistiche per ogni account.

Output di esempio:

ID accountConteggio transazioniImporto totaleImporto medioImporto minimoImporto massimo
ACC4295572.4247786.21500.5095071.92
..................
Risultati ILUM SQL

Passaggio 4: identificare gli account che superano i limiti​

Identifica i conti con transazioni che superano il limite predefinito.

4.1 Determinare i conti eccedenti​

Codice SQL:

CREARE VISTA exceeding_accounts COME
SELEZIONARE DISTINTO ID account
DA Anomalie
DOVE AnomalyStatus = 'Supera il limite';

4.2 Conteggio superiore e non superiore​

Codice SQL:

SELEZIONARE
    (SELEZIONARE CONTARE(*) DA exceeding_accounts) COME ExceedingAccounts,
    (SELEZIONARE CONTARE(DISTINTO ID account) DA Transazioni) - (SELEZIONARE CONTARE(*) DA exceeding_accounts) COME NonExceedingAccounts;

Output di esempio:

ExceedingAccountsNonExceedingAccounts
123
Risultati ILUM SQL

Passaggio 5: Riconoscere modelli temporali insoliti​

Rileva le transazioni che si verificano al di fuori del normale orario lavorativo (ad esempio, dalle 9 alle 17).

Codice SQL:

CREARE VISTA after_hours_transactions
COME
SELEZIONARE
    *,
    ORA(TransactionTimestamp) COME TransactionHour
DA
    Transazioni
DOVE
    ORA(TransactionTimestamp) NON TRA 9 E 17;

Spiegazione:

  • ORA(Timestamp transazione): estrae l'ora dal timestamp.
  • NON TRA I 9 E I 17 ANNI: filtra le transazioni al di fuori dell'orario di lavoro.

Passaggio 7: analisi dei comportamenti di spesa per commerciante​

Aggrega i dati di spesa per comprendere i comportamenti dei clienti in base alle interazioni con i commercianti.

Codice SQL:

CREARE TAVOLO merchant_spending
UTILIZZANDO DELTA
COME
SELEZIONARE
    Mercante,
    CONTARE(*) COME Conteggio transazioni,
    SOMMA(Importo) COME TotaleSpeso,
    MEDIO(Importo) COME AverageTransactionAmount,
    MIN(Importo) COME Importo minimo,
    MAX(Importo) COME Importo massimo
DA
    Transazioni
GRUPPO PRESSO
    Mercante;

Output di esempio:

MercanteConteggio transazioniTotaleSpesoAverageTransactionAmountImporto minimoImporto massimo
Commerciante H2110679.8155339.9115607.8995071.92
..................
Risultati ILUM SQL

Esempio di visualizzazione:

Risultati ILUM SQL

Passaggio 8: Utilizzo delle funzionalità di Delta Lake​

8.1 Abilita il feed di dati delle modifiche​

Abilita il feed di dati delle modifiche di Delta Lake per tenere traccia delle modifiche nel Transazioni tavolo.

Codice SQL:

ALTERARE TAVOLO Transazioni
METTERE TBLPROPRIETÀ (delta.enableChangeDataFeed = vero);

8.2 Eseguire aggiornamenti e tenere traccia delle modifiche​

Aggiorna gli importi delle transazioni dovuti alle correzioni e tieni traccia di queste modifiche.

Codice SQL:

-- Aggiornare una transazione
AGGIORNARE Transazioni
METTERE Importo = 1300.00
DOVE ID transazione = 'TXN1127';
-- Controlla l'ultima versione
DESCRIVERE STORIA Transazioni;
Risultati ILUM SQL 

-- Interroga le modifiche utilizzando il feed di dati delle modifiche con la versione corretta della tabella
SELEZIONARE *
DA table_changes(«operazioni», 3)
DOVE ID transazione = 'TXN1127';
Risultati ILUM SQL

8.3 Evoluzione dello schema: aggiunta di una nuova colonna​

Aggiungere una nuova colonna Segnalazione di frode per segnalare transazioni sospette di frode.

Codice SQL:

ALTERARE TAVOLO Transazioni
AGGIUNGERE COLONNE (Segnalazione di frode BOOLEANO COMMENTO "Indicatore che indica una sospetta frode");

8.4 Aggiornamento delle transazioni fraudolente​

Contrassegna le transazioni che superano il limite massimo.

Codice SQL:

AGGIORNARE Transazioni
METTERE Segnalazione di frode = VERO
DOVE Importo > 95071.92;
Risultati ILUM SQL

8.5 Viaggio nel tempo e ripristino della tabella​

Visualizza la cronologia delle tabelle​

Codice SQL:

DESCRIVERE STORIA Transazioni;
Risultati ILUM SQL
Interrogare una versione precedente​

Codice SQL:

SELEZIONARE * DA Transazioni VERSIONE COME DI 3;
Ripristino della tabella a una versione precedente​

Codice SQL:

RESTAURARE TAVOLO Transazioni A VERSIONE COME DI 3;

Spiegazione:

  • Viaggio nel tempo: consente di accedere alle versioni precedenti dei dati per il controllo o il ripristino.
  • Ripristino tabella: Ripristina la tabella a uno stato valido noto dopo modifiche non intenzionali.

Passaggio 9: Generazione dei rapporti di riepilogo finali​

Genera report che riepilogano i conti che superano i limiti e calcola le medie.

Codice SQL:

CREARE TAVOLO account_exceeding_summary
UTILIZZANDO DELTA
COME
SELEZIONARE
    un.ID account,
    un.Conteggio transazioni,
    un.Importo totale,
    un.Importo medio,
    un.Importo minimo,
    un.Importo massimo,
    CASO
        QUANDO e.ID account È NON NULLO ALLORA 'Supera il limite'
        ALTRO 'Entro il limite'
    FINE COME ExceedingStatus
DA
    account_totals un
A sinistra UNIRE
    exceeding_accounts e SU un.ID account = e.ID account;
Risultati ILUM SQL 
-- Calcola le medie per i conti eccedenti e non eccedenti
SELEZIONARE
    ExceedingStatus,
    CONTARE(ID account) COME Conteggio account,
    MEDIO(Importo totale) COME AverageTotalAmount,
    MEDIO(Importo medio) COME AverageTransactionAmount
DA
    account_exceeding_summary
GRUPPO PRESSO
    ExceedingStatus;
Risultati ILUM SQL

Funzionalità del Delta Lake utilizzate​

  • Tabelle Delta: tutte le tabelle vengono create utilizzando Delta Lake per sfruttare le transazioni ACID e l'applicazione dello schema.
  • Viaggio nel tempo: consente di accedere alle versioni precedenti dei dati per il controllo e il ripristino.
  • Ripristino tabella: se necessario, ripristina le tabelle a uno stato precedente.
  • Feed di dati di modifica: tenere traccia delle modifiche apportate ai dati per l'elaborazione incrementale e il controllo.
  • Evoluzione dello schema: Aggiungere nuove colonne (Segnalazione di frode) senza alterare i dati esistenti.
  • Vincoli e Verifiche: Applicare l'integrità dei dati tramite le definizioni dello schema.
  • Partizionamento: anche se non viene utilizzato in modo esplicito in questo caso, Delta Lake supporta il partizionamento per l'ottimizzazione delle prestazioni.

Integrazione di Data Lineage e OpenLineage​

Eseguendo queste operazioni SQL in un ambiente integrato con OpenLineage, ogni operazione genera metadati di derivazione. Questi metadati acquisiscono:

  • Flusso di dati: come i dati si spostano da una tabella all'altra (ad esempio, da Transazioni A Anomalie).
  • Trasformazioni: le trasformazioni specifiche applicate ai dati (ad esempio, l'aggiunta di AnomalyStatus).
  • Modifiche allo schema: Modifiche allo schema della tabella (ad esempio, l'aggiunta di Segnalazione di frode colonna).
  • Controllo delle versioni: Modifiche alle versioni delle tabelle dovute ad aggiornamenti e ripristini.

Benefici:

  • Trasparenza: Chiara comprensione di come i dati vengono elaborati e trasformati.
  • Controllo: Capacità di tracciare la derivazione dei dati per scopi di conformità e controllo.
  • Debug: Identificazione più semplice dei problemi nelle pipeline di elaborazione dei dati.
  • Ottimizzazione: le informazioni dettagliate sul flusso di dati possono aiutare a ottimizzare le prestazioni.
Derivazione dei dati ILUM

Conclusione​

Questo caso d'uso illustra come eseguire il rilevamento delle anomalie finanziarie usando Spark SQL e Delta Lake, incorporando funzionalità chiave come il viaggio nel tempo, il feed di dati delle modifiche e l'evoluzione dello schema. Grazie all'integrazione con OpenLineage, la soluzione fornisce un monitoraggio completo della derivazione dei dati, migliorando la trasparenza e la conformità.

Punti chiave:

  • Rilevamento efficace delle anomalie: Utilizzo di metodi statistici per identificare le transazioni e i conti che si discostano dai normali schemi.
  • Solida gestione dei dati: Sfruttare le funzionalità di Delta Lake per garantire l'integrità dei dati, facilitare le modifiche allo schema e abilitare le opzioni di ripristino.
  • Visibilità della derivazione dei dati: acquisizione di informazioni dettagliate sulla derivazione per supportare i requisiti di controllo e conformità.
  • Approfondimenti aziendali: Generazione di report e analisi che forniscono informazioni preziose sui comportamenti dei clienti e sui potenziali rischi.

Appendice​

Schema del set di dati​

Schema della tabella delle transazioni:

ColonnaTipo di datiDescrizione
TransactionTimestampTIMESTAMPData e ora in cui è avvenuta la transazione
ID transazioneCORDAIdentificatore univoco della transazione
ID accountCORDAIdentificatore univoco per l'account coinvolto
ImportoDECIMALE(18,2)Valore monetario dell'operazione
MercanteCORDACommerciante coinvolto nella transazione
Tipo di transazioneCORDATipo di transazione (ad esempio, Acquisto, Prelievo)
UbicazioneCORDALuogo in cui è avvenuta la transazione
Segnalazione di frodeBOOLEANOContrassegno che indica sospetta frode (aggiunto in seguito)

Referenze:

Last updated on