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.

Границы и компоненты

Сценарии

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 конкретного продукта.

Связанные темы

[CONCEPT]change-data-capture

[CONCEPT]data-mesh

Первичные источники

• 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.