System Design Cases
ETL vs ELT
ETL vs ELT data pipelines: classic ETL (Informatica -> MS SQL DW), modern ELT (Fivetran -> S3 -> Snowflake -> dbt -> Looker), reverse ETL (warehouse -> Hightouch -> Salesforce), and an ADR scenario showing when ETL still wins for PII redaction pre-load (Spark redactor masks before warehouse to satisfy GDPR).
ETL and ELT: transformation placement, trust boundaries and idempotent loads
ETL vs ELT отвечает прежде всего «где выполняется transformation относительно load». Raw layer, privacy, data quality и lineage — отдельные design choices, которые обязательны в обоих вариантах.
Корректная модель
- ETL transforms before load; ELT transforms after landing. Raw retention is independent of that naming.
- Every run/range needs identity, checksum, lineage and replay policy.
- Restricted raw/landing zones need encryption, access control, audit and retention just like serving systems.
- Hashing/tokenization can be pseudonymization and does not automatically remove personal-data obligations.
Границы и компоненты
| Компонент | Ответственность |
|---|---|
| Operational Sources | Выдают versioned extracts/CDC under source contracts. |
| Idempotent Extract Loader | Назначает run/range identity, checkpoints и validates transport. |
| Pre-Load Transform and Tokenize | ETL path removes/normalizes fields before lower-trust destination. |
| Restricted Versioned Landing | Encrypted raw/staged data with retention and least-privilege policy. |
| Warehouse Transform Jobs | ELT path builds deterministic models from pinned inputs/code. |
| Schema and Quality Gate | Checks contracts, uniqueness, reconciliation and privacy policy. |
| Curated Analytical Models | Published versioned outputs with lineage and SLOs. |
| Analytics and ML Consumers | Reads only authorized product/model versions. |
Сценарии
ETL before a trust boundary
Sensitive source fields are validated/tokenized in a controlled zone before loading a destination that should never receive originals.
Проверяемый исход: Destination contains only the approved representation; mapping/keys remain separately protected where reversibility exists.
ELT into restricted landing
Authorized raw records land encrypted in a tightly controlled zone, then target-side compute creates models. Raw does not mean public, ungoverned or permanent.
Проверяемый исход: Only quality-gated outputs are promoted; landing access/retention is narrower than curated access.
Incremental idempotent load
A loader retry after timeout uses the same run/range identity. Landing and model merge either replay the exact batch or reject checksum mismatch.
Проверяемый исход: Retry cannot append duplicate logical rows or overwrite a newer source version.
Privacy and quality fail closed
A new source column contains direct identifiers and violates the approved classification/contract. Hashing alone is not assumed to anonymize it.
Проверяемый исход: Pipeline quarantines the run, alerts the owner and publishes no partial candidate.
Failure, concurrency и replay checklist
- Atomic completion marker after all files/rows and metadata are durable.
- Quarantine schema drift and publish no partial model.
- Reconcile source counts/totals/keys and target invariants per run.
- Test deletion/retention/legal-hold propagation and restore.
Формулы, units и допущения
- Run completeness is a set/range proof, not row count alone; compare keys, checksums and business aggregates.
- Ideal transfer time = bytes / sustained end-to-end bytes/s; compression, retries, source throttling and transform CPU are separate.
- Incremental watermark must include a tie-breaker/version; timestamp-only greater-than predicates can miss equal-time rows.
Числа выше — учебные inputs или размерностные формулы. Их нельзя выдавать за benchmark или SLA конкретного продукта.
Связанные темы
Первичные источники
- https://docs.getdbt.com/terms/elt
- https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html
- https://eur-lex.europa.eu/eli/reg/2016/679/oj
- https://www.nist.gov/privacy-framework
Scope note
Диаграмма показывает причинные границы и recovery contracts, а не скрытую реализацию конкретного managed-сервиса. Любая stronger guarantee действует только в явно названной transaction/checkpoint/acknowledgement boundary.