Procesy ETL a transformace dat: extrakce, transformace a načítání

Telekomunikace
Peter Kráľ
ETL procesy a transformace dat: Extrakce, transformace, načítání

Co jsou procesy ETL a proč jsou zásadní pro datové sklady

ETL (Extract–Transform–Load) je soubor procesů, kterými se data získávají ze zdrojových systémů, transformují do konzistentní podoby a nahrávají do cílového úložiště (datového skladu, lakehouse, datových martů). Kvalita a spolehlivost ETL přímo ovlivňuje spolehlivost reportingu, rychlost analytiky a důvěru v data. Moderní platformy rozšiřují pojem ETL o ELT (transformace po nahrání) a hybridní přístupy, které využívají výpočetní výkon cílového enginu (MPP/SQL, Spark) pro škálování.

ETL vs. ELT: kdy použít který přístup

Aspekt ETL (transformace před nahráním) ELT (transformace po nahrání)
Výpočetní zátěž Na integračním serveru/enginu Na cílovém DWH/jezeru (SQL/MPP/Spark)
Flexibilita schématu Pevně definované mapování Schema-on-read, rychlé iterace
Náklady Vyšší na integrační vrstvě Lepší využití škálovatelného výpočetního výkonu
Governance Silná kontrola vstupů Silná auditovatelnost v tabulkové vrstvě
Případ použití Legacy zdroje, omezená šířka pásma Cloud, velké objemy, rychlé prototypy

Referenční architektura zpracování dat

  • Zdrojové systémy: databáze OLTP, aplikace (ERP/CRM), soubory, API, datové proudy událostí.
  • Příjem/staging: surová vrstva (raw/bronze) pro bezztrátový příjem dat bez transformací, často s politikou append-only.
  • Transformační vrstva: normalizace, sjednocení typů, konformní dimenze, business logic (silver).
  • Prezentační/serving vrstva: hvězdicová schémata, datové marty, sémantická vrstva (gold).
  • Řídicí a podpůrné služby: katalog metadat, lineage, kvalita dat, orchestrace, monitoring, zabezpečení.

Extract: způsoby získávání dat (dávkové zpracování, CDC, streamování)

  • Plné dávky (full load): kompletní načtení datasetu, vhodné pro malé tabulky nebo inicializaci.
  • Inkrementální načítání: watermark podle updated_at, identifikace změn pomocí Change Data Capture (CDC) – transakční logy, binlog, redo log, triggery.
  • Příjem datových proudů: události z integračních platforem (např. Kafka), event-carried state, webhooky, telemetrie IoT.
  • Formáty a přenos: CSV/Parquet/Avro/JSON, komprese, šifrování, idempotentní přenosy a checkpointing.

Staging: surová data a kontrakty

  • Bezztrátové uložení: ukládat přesně tak, jak data dorazila (včetně metadat envelope). Zachovat origin timestamp, zdroj a ingestion id.
  • Datové kontrakty: definice schémat (Avro/Protobuf), kompatibilita verzí (backward/forward), řízené změny.
  • Kontroly kvality při příjmu: validace schématu, rozsah hodnot, unikátní klíče, detekce duplicit (přirozený vs. náhradní klíč).

Transformace: typy a pořadí kroků

  1. Čištění (cleansing): ořezání mezer, normalizace diakritiky, standardizace kódů (ISO, číselníky), opravy datových typů.
  2. Obohacení: doplnění geokódů, kurzů, referenčních dat, data augmentation.
  3. Konformita: mapování na konformní dimenze (zákazník, produkt), sjednocení granularit a měrných jednotek.
  4. Byznysová logika: výpočet metrik, odvozené sloupce, alokace, zpracování late arriving facts.
  5. Historizace: implementace typů SCD pro dimenze, audit trail.

Pomalu se měnící dimenze (SCD) a faktové tabulky

  • SCD Type 1: přepsání hodnot (bez historie). Jednoduché, ale minulost se ztrácí.
  • SCD Type 2: historizace pomocí valid_from, valid_to, is_current, případně hash diff pro detekci změn.
  • SCD Type 3: omezená historie v několika sloupcích (např. previous_value).
  • Fakta: aditivní/semiaditivní, granularita (den, transakce, položka), cizí klíče na dimenze, degenerate klíče (např. číslo objednávky).

CDC: detekce a aplikace změn

-- Pseudokód MERGE pro aplikaci CDC do dimenze (SCD2) MERGE INTO dim_customer d USING stage_customer s ON d.natural_key = s.natural_key AND d.is_current = TRUE WHEN MATCHED AND HASH(d.cols) != HASH(s.cols) THEN UPDATE SET d.valid_to = s.change_ts, d.is_current = FALSE WHEN NOT MATCHED THEN INSERT (sur_key, natural_key, cols..., valid_from, valid_to, is_current) VALUES (NEXTVAL(), s.natural_key, s.cols..., s.change_ts, '9999-12-31', TRUE);
  • CDC založené na logu: spolehlivé, s minimálním dopadem na zdroj; vyžaduje přístup k transakčním logům.
  • CDC založené na triggerech: jednodušší z hlediska oprávnění, má větší dopad na OLTP.
  • Na základě časových razítek: levné na implementaci, náchylné k vynechání změn při nesynchronizovaných hodinách; používat s rezervou ve watermarku.

Datové modelování v DWH: hvězdice, sněhová vločka, Data Vault

  • Star schema: fakta uprostřed, konformní dimenze; jednoduché pro BI, rychlé dotazy.
  • Snowflake: normalizované dimenze (referenční tabulky); šetří místo, ale vede ke složitějším spojením.
  • Data Vault 2.0: hub–link–satellite, auditovatelný a adaptabilní model vhodný pro historizaci a časté změny zdrojů; pro BI vyžaduje prezentační vrstvu.

Výkon ETL/ELT: oddíly, paralelizace, pushdown

  • Rozdělení na oddíly: by ingestion date, by business key; minimalizace small files (v datových jezerech).
  • Paralelismus: rozdělení podle klíčů, work stealing, dávky (micro-batch) pro streamování.
  • SQL pushdown: využití MPP/vektorových enginů; minimalizace přesunů dat mezi uzly.
  • Inkrementální MERGE: pouze změněné oddíly/klíče; change tables pro minimalizaci I/O.

Kvalita dat (DQ): pravidla, měření a řízení výjimek

  • Typy pravidel: úplnost (completeness), platnost (validity), konzistence (consistency), přesnost (accuracy), jedinečnost (uniqueness), aktuálnost (timeliness).
  • Implementace: deklarativní testy v pipeline (SQL assertions), samostatná vrstva DQ (profilování, prahové hodnoty p95/p99), anomaly detection.
  • Řízení výjimek: quarantine záznamů, správa tiketů, datové stewardství, feedback loop do zdrojů.

Metadata, katalog a lineage

  • Technická metadata: schémata, typy, graf lineage (sloupec->sloupec), plánovače a závislosti DAG.
  • Byznysová metadata: definice metrik, vlastník, SLA/SLO, klasifikace (PII, citlivost).
  • Aktualizace a governance: automatizovaný sběr metadat, validace změn schémat v CI/CD, proces schvalování.

Bezpečnost a compliance v ETL

  • Šifrování: při přenosu (TLS), v klidovém stavu (TDE, KMS), selektivní šifrování sloupců.
  • Maskování a pseudonymizace: deterministická vs. náhodná, tokenization; zabezpečení na úrovni row-level/column-level v prezentační vrstvě.
  • Audit a dohledatelnost: auditní logy transformací, who/when/what, data contracts pro producenty/konzumenty.

Orchestrace a provoz: plánování, opakování, idempotence

  • Orchestrace DAG: závislosti úloh, spouštěče time-based a event-based, parametrizace.
  • Idempotence: možnost bezpečného opětovného spuštění (zámky, upsert/merge, insert overwrite oddílu, kontrolní body).
  • Strategie opakování: exponenciální prodleva, fronty dead-letter pro zprávy, circuit breaker u nestabilních zdrojů.
  • Monitoring: metriky (průtok, latence, chybovost), SLIs/SLOs, upozornění na zpoždění a porušení DQ.

Testování ETL/ELT: od jednotkových testů po end-to-end

  • Jednotkové testy transformací: deterministické vstupy/výstupy, hraniční hodnoty, zpracování null.
  • Testy kontraktů: simulace změn schématu zdroje; validace kompatibility.
  • Rozdíly v datech: reconciliation vůči zdrojům (počty, součty, kontrolní součty), vzorkování řádků.
  • Výkonnostní testy: škálování, scénáře backfill, degradace při nárůstu objemů.

Vrstvení v lakehouse: bronze, silver, gold

  • Bronze: surová data, pouze přidávání, auditní stopa změn.
  • Silver: vyčištěná a harmonizovaná data, klíče, referenční integrita, deduplikace.
  • Gold: byznysové datové marty, předagregace, semantic models pro BI/AI.

Typické transformační vzory (SQL)

-- Dedup podle klíče s preferencí nejnovější změny SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY updated_at DESC) AS rn FROM silver.orders t ) x WHERE rn = 1;
-- Konformní dimenze: normalizace kódu země
UPDATE dim_customer
SET country_code = UPPER(TRIM(country_code))
WHERE country_code IS NOT NULL AND country_code != UPPER(TRIM(country_code));

Chyby a anti-patterny v ETL

  • Skrytá byznysová logika ve skriptech: bez dokumentace a testů → neauditovatelné rozdíly.
  • Přílišná závislost na časových oknech: namísto deterministických watermarků a CDC.
  • Problém malých souborů: mnoho drobných souborů v datovém jezeru → degradace výkonu.
  • Nedostatečná idempotence: opětovné spuštění vede k duplicitám a nekonzistenci.

Výběr nástrojů a platforem

  • Orchestrace: nástroje s DAG (Airflow, Dagster, Prefect).
  • Transformace: SQL-first (dbt), MPP (Snowflake/BigQuery/Redshift), Spark/Databricks pro škálované ELT.
  • Příjem dat/CDC: konektory (Fivetran, Hevo), open source (Debezium), integrační sběrnice (Kafka, Connect).
  • Kvalita a katalog: Great Expectations, Deequ, DataHub/Amundsen/Atlas.

Spotřeba zdrojů a nákladový model

  • Optimalizace výpočetního výkonu: cluster sizing, automatické škálování, uzly spot/preemptible pro zpětné doplnění dat.
  • Úložiště: formáty s kompresí a statistikami (Parquet/ORC), z-ordering/clustering pro rychlé ořezávání dat.
  • FinOps: měření nákladů na úlohu/tabulku, chargeback/showback podle týmu/tenantů.

Praktická strategie implementace programu ETL

  1. Inventura zdrojů: typy, SLA, citlivost, objemy a frekvence změn.
  2. Standardy a kontrakty: konvence pojmenování, datové typy, časová pásma (UTC), surrogate keys.
  3. Proof of Value: pilotní pipeline s metrikami od začátku do konce (latence, aktuálnost, chybovost, náklady).
  4. Automatizace: šablony pro příjem dat, jednotné knihovny pro práci s chybami, sdílené komponenty (merge, SCD2).
  5. Provoz a zlepšování: SLO a rozpočty chyb, postmortemy, backlog optimalizací (skew, malé soubory, dlouhý konec rozdělení).

Závěr: klíčové principy úspěšných řešení ETL/ELT

Úspěšná realizace ETL stojí na determinismu (idempotentní kroky, auditovatelná lineage), modularitě (znovupoužitelné vzory), kvalitě dat (měřitelná a řízená), škálování (pushdown, rozdělení na oddíly) a governanci (metadata, bezpečnost, kontrakty). Kombinace pevných základů (staging → harmonizace → prezentace) s moderními postupy (ELT, lakehouse, CDC) umožňuje budovat datové platformy, které dlouhodobě poskytují spolehlivé informace pro rozhodování i pokročilou analytiku.