Trasformare il caos in profitto: comprendere il processo ETL

Pubblicato: 2022-03-11

Uno degli errori più critici che impediscono alle aziende di fornire informazioni vitali per il processo decisionale agli utenti aziendali è l'assenza di dati affidabili provenienti da una o più fonti di dati, raccolte in un'unica posizione, organizzate e preparate per essere utilizzate.

Immagina questo scenario: sei il responsabile IT di un'azienda di custodie per cellulari con punti vendita in tutti gli Stati Uniti. Il tuo consiglio di amministrazione sta lottando per prendere decisioni basate sulle vendite perché le informazioni sono disponibili ma si trovano in luoghi e formati diversi. Uno dei direttori ti chiede di generare una dashboard con le informazioni sulle vendite di tutti i punti vendita da presentare alla prossima riunione del consiglio.

Sai già che è quasi impossibile consolidare le informazioni a causa di formati e strutture diversi. Alcuni dei chioschi al dettaglio utilizzano ancora un sistema proprietario in un database Oracle. I negozi più grandi utilizzano un nuovo sistema Salesforce. I chioschi più recenti che hanno iniziato le operazioni durante la transizione del sistema hanno fogli di calcolo condivisi utilizzati per calcolare le vendite.

In che modo raccoglierai tutti i dati provenienti da posizioni, formati e strutture diverse in un database unico pronto per l'accesso per la generazione di dashboard?

Fondamenti di ETL

ETL sta per Estrai, Trasforma e Carica . ETL è un gruppo di processi progettati per trasformare questo complesso archivio di dati in un processo organizzato, affidabile e replicabile per aiutare la tua azienda a generare più vendite con i dati che già possiedi.

Nel nostro caso, riceveremo i dati da un database Oracle (la maggior parte dei kiosk), da Salesforce (negozi) e da fogli di calcolo (kiosk più recenti), estrarremo i dati, trasformeremo quando necessario e caricheremo in un unico database di data warehouse per essere accessibile da strumenti di reporting e utilizzato per generare dashboard e scorecard.

Immergiamoci nei tre passaggi di ETL per approfondire il processo.

Estrazione

L'estrazione è il processo di ricezione dei dati da una o più origini. Le origini possono avere diversi formati e strutture, come documenti, fogli di calcolo, file CSV, file flat, database relazionali come Oracle, MySQL, SQL Server, database non relazionali e così via.

Esistono due tipi principali di estrazione: totale e parziale .

  • L'estrazione completa viene utilizzata per le estrazioni iniziali o quando la quantità di dati e di conseguenza il tempo di estrazione sono accettabili.
  • L'estrazione parziale è consigliata quando non è necessario estrarre nuovamente tutti i dati o la quantità di dati è abbastanza grande da rendere impossibile un'estrazione completa. In un'estrazione parziale verranno estratti solo i dati aggiornati o nuovi.

Oltre a questi aspetti, nella scelta tra estrazione totale o parziale sono necessarie alcune altre considerazioni, e voglio descriverne una: disponibilità e integrità dei dati .

Significa che solo le transazioni completate verranno prese in considerazione per l'estrazione, eliminando i dati che potrebbero causare un difetto di integrità. Ad esempio, un test online per identificare le conoscenze di un ingegnere con 10 domande. Se un ingegnere è nel mezzo del test e ha risposto ad alcune domande ma non l'ha ancora terminato, il processo di estrazione non può leggere le domande dei test non completati. Potrebbe causare un errore di integrità.

Trasformazione

Dopo aver estratto i dati, possiamo iniziare il processo di trasformazione: pulire, manipolare e trasformare i dati in base a regole aziendali e criteri tecnici per mantenere un livello accettabile di qualità dei dati.

A seconda di una serie di fattori, potrebbe essere necessario l'uso di un'area di sosta. Un'area di gestione temporanea è uno spazio di archiviazione intermedio utilizzato per archiviare temporaneamente i dati estratti dalle origini dati da trasformare.

In alcuni progetti, normalmente quelli con una piccola quantità di dati, non è necessario utilizzare aree di staging, ma la maggior parte dei progetti lo utilizza.

C'è una serie di compiti eseguiti durante la fase di trasformazione:

  • Selezione: criteri per selezionare i dati estratti. La selezione può essere effettuata durante la fase di estrazione, durante la fase di trasformazione o in entrambe le fasi.
  • Integrazione: la combinazione dei dati dalla fase di estrazione all'area di staging. Questa combinazione significa aggiungere, eliminare e aggiornare i dati nell'area di gestione temporanea in base ai dati estratti.
  • Join: utilizzati per unire dati estratti, in modo simile ai join SQL (join interno, join sinistro, join destro, join completo, ecc.)
  • Pulizia o pulizia: rimuove i dati incoerenti o non validi o i dati con errori per migliorare la qualità dei dati. L'utilizzo di più origini dati aumenta le possibilità di avere problemi con i dati che necessitano di pulizia, ad esempio:
    • Integrità referenziale (cliente con categoria inesistente)
    • Valori mancanti (cliente senza ID)
    • Unicità (più di una persona con lo stesso SSN)
    • Errori di ortografia (Sun Diego, Cannada, L.Angeles)
    • Valori contraddittori (Alex data di nascita 27.04.1974, Alex data di nascita 14.04.2000)
    • e molti altri
  • Riepiloghi: riepilogano insiemi di dati per un uso successivo
  • Aggregazioni: dati raccolti e riassunti in gruppi
  • Consolidamenti: dati provenienti da più fonti o strutture consolidate in un unico insieme di dati

Ecco alcuni tipi di trasformazione comuni:

  • Elimina i dati duplicati
  • Scissione e fusione
  • Conversioni (data, ora, maschere numeriche, misure)
  • Codifica (maschio a M)
  • Calcoli (valore_articolo = prezzo_unità * quantità)
  • Generazione di chiavi

Caricamento in corso

Ultimo ma non meno importante, il processo finale in ETL consiste nel caricare i dati nella destinazione. Il caricamento è l'atto di inserire i dati trasformati (da un'area di staging o meno) nel repository, normalmente un database di data warehouse.

Esistono tre tipi principali di caricamento dei dati: completo o iniziale, incrementale e aggiornamento.

  • Completo o iniziale indica un carico completo di dati estratti e trasformati. Tutti i dati nell'area di sosta verranno caricati nella destinazione finale per essere preparati per gli utenti business.
  • Il caricamento incrementale è il processo di confronto dei dati trasformati con i dati nella destinazione finale e di caricamento solo di nuovi dati. Il carico incrementale può essere utilizzato insieme al carico di aggiornamento, spiegato di seguito.
  • Il caricamento di aggiornamento è il processo di aggiornamento dei dati nella destinazione finale per riflettere le modifiche apportate nell'origine originale. Un aggiornamento può essere completo o incrementale.

In sintesi, ogni azienda, indipendentemente dalle sue dimensioni, può utilizzare i processi ETL per integrare informazioni già esistenti e per generare un patrimonio di informazioni ancora maggiore per il processo decisionale, trasformando dati che prima non potevano essere utilizzati in una nuova fonte di reddito.

Test

Il test è una delle fasi più importanti dell'ETL, ma anche una delle più trascurate.

Trasformare dati da diverse fonti e strutture e caricarli in un data warehouse è molto complesso e può generare errori. Gli errori più comuni sono stati descritti nella fase di trasformazione sopra.

L'accuratezza dei dati è la chiave del successo, mentre l'imprecisione è una ricetta per il disastro. Pertanto, i professionisti ETL hanno la missione di garantire l'integrità dei dati durante l'intero processo. Dopo ogni fase, è necessario eseguire un test. Sia che si estraggano dati da un'unica fonte o da più fonti, i dati devono essere verificati per stabilire che non vi siano errori.

Lo stesso deve essere fatto dopo ogni trasformazione. Ad esempio, quando si riepilogano i dati durante la fase di trasformazione, i dati devono essere verificati per garantire che nessun dato sia andato perso e che le somme siano corrette.

Dopo aver caricato i dati trasformati nel data warehouse, è necessario applicare nuovamente il processo di test. I dati caricati devono essere confrontati con i dati trasformati e successivamente con i dati estratti.

Nel nostro esempio dell'azienda di custodie per cellulari, stiamo lavorando con tre diverse fonti (database Oracle proprietario, Salesforce e fogli di calcolo) e formati diversi. La fase di test può utilizzare dati campione dalle fonti originali e confrontarli con i dati che si trovano nell'area di staging per garantire che l'estrazione sia avvenuta senza errori.

I dati campione, che in questo caso possono essere informazioni sulle vendite provenienti da tre diverse località (negozi, vecchi chioschi, nuovi chioschi), devono essere confrontati con la fonte originale. Le eventuali differenze devono essere analizzate per vedere se sono accettabili o se sono errori.

Se vengono rilevati errori, è necessario correggerli e ci sono alcune decisioni da prendere se è necessario correggerli: i dati originali devono essere modificati? È possibile farlo? Se gli errori non possono essere corretti nella fonte originale, possono essere corretti con qualche trasformazione?

In alcuni casi, i dati con errori devono essere eliminati e deve essere attivato un avviso per informare i responsabili.

Alcuni esempi di test:

  • I dati richiedono la convalida
  • Qualità dei dati
  • Prestazione
  • Regole dei dati
  • Modellazione dei dati

Registrazione

La registrazione dei processi ETL è la garanzia chiave di disporre di sistemi manutenibili e facili da riparare.

Un ETL con il corretto processo di registrazione è importante per mantenere l'intera operazione ETL in uno stato di miglioramento costante, aiutando il team a gestire bug e problemi con origini dati, formati dati, trasformazioni, destinazioni, ecc.

Un solido processo di registrazione aiuta i team a risparmiare tempo consentendo loro di identificare i problemi in modo più rapido e semplice e i lead engineer hanno bisogno di meno tempo per individuare direttamente il problema. A volte, si verificano errori durante l'estrazione di tonnellate di dati e, senza un registro, identificare il problema è difficile, a volte quasi impossibile. Senza log, l'intero processo deve essere eseguito nuovamente. Utilizzando i log, il team può identificare rapidamente il file e la riga che hanno causato il problema e correggere solo quei dati.

L'unico caso che posso immaginare in cui i registri non sono così importanti è con sistemi non automatizzati molto piccoli, in cui il processo viene eseguito manualmente e c'è una piccola quantità di dati che possono essere monitorati manualmente.

I log migliorano l'automazione. I processi ETL con una grande quantità di dati che vengono eseguiti automaticamente necessitano di sistemi di log. Se sono ben pianificati ed eseguiti, tutto lo sforzo profuso nella creazione di un sistema di registrazione ripagherà i dividendi sotto forma di un'identificazione degli errori più rapida, dati più affidabili e punti di miglioramento trovati nei file di registro.

Ci sono tre passaggi principali nella creazione di un sistema di log: generare, archiviare e analizzare .

  • Genera è il processo di documentazione di ciò che sta accadendo durante l'esecuzione delle pipeline ETL: quando il processo è stato avviato, quale file o tabella viene estratto, i dati che vengono salvati nell'area di staging, i messaggi di errore e altro ancora. Tutte le informazioni importanti che possono aiutare gli ingegneri devono essere registrate. Avviso : prestare attenzione a non generare così tante informazioni che consumeranno solo tempo e spazio e non saranno utili.
  • Archiviare i dati di registro significa tenere traccia delle esecuzioni passate per cercare scenari passati in modo da identificare errori o confrontare con lo scenario attuale alla ricerca di miglioramenti. È importante verificare la pertinenza di un punto specifico della storia da salvare: non vale la pena conservare i dati di molto tempo fa, in cui la struttura è cambiata molte volte.
  • Analizza . L'analisi dei log è di fondamentale importanza. Archiviare tonnellate di dati che non vengono analizzati non ha senso. La generazione e l'archiviazione dei dati costa solo tempo e denaro. L'analisi dei log è importante non solo per aiutare a cercare gli errori, ma anche per identificare i punti di miglioramento e aumentare la qualità complessiva dei dati.

Prestazione

I processi ETL possono funzionare con tonnellate di dati e possono costare molto, sia in termini di tempo impiegato per configurarli sia in termini di risorse computazionali necessarie per elaborare i dati. Quando si pianifica un'integrazione, gli ingegneri devono tenere presente la necessità di tutti i dati utilizzati. La cosa migliore da fare è lavorare con la quantità minima di dati per raggiungere gli obiettivi, piuttosto che spendere tempo e denaro solo per migrare dati inutili. Inoltre, tieni presente che le quantità di dati tendono ad aumentare nel tempo, quindi cerca di considerare le tue esigenze future.

Quanto sforzo dovrebbe essere dedicato alla progettazione di un sistema di registrazione?

Dipende da una serie di fattori diversi come la quantità e la frequenza dei dati da elaborare. Piccoli sistemi con pochi dati possono essere gestiti manualmente, senza la necessità di investire in sistemi di registrazione avanzati.

Le aziende con una grande quantità di dati, molti processi diversi, origini dati e formati diversi e pipeline complesse devono investire nella creazione di sistemi di registrazione. Come abbiamo già detto, un buon sistema di log può far risparmiare molto tempo e denaro.

Per un approccio più ovvio, indipendentemente dalle dimensioni dell'azienda, dalla quantità di dati e dalla frequenza di integrazione, un processo ETL deve essere redditizio . Un investimento in tempo, denaro e risorse tecniche deve generare rendimenti, come ha affermato l'economista Milton Friedman: "Non c'è pranzo gratis".

In sintesi, un processo ETL può aiutare le aziende ad aumentare i profitti con i dati che già hanno ma che non vengono utilizzati nel modo giusto. Le pipeline ETL possono integrare diversi sistemi, quei fogli di calcolo con informazioni importanti e altre porzioni di dati che sono distribuite in diversi dipartimenti e filiali, consentendo alle organizzazioni di ottenere il massimo dai propri dati.