ETL vs ELT vs ETLT: Where Should Data Transformation Live?

Core Question: Where Should Data Be Transformed?

The article opens by stating that ETL, ELT, and ETLT are not merely about letter ordering; they reflect a fundamental architectural decision: where transformation happens . This choice directly impacts data freshness, compute cost, raw data preservation, data quality, metric iteration, and long‑term maintenance.

E, T, L Defined

Extract (E): Pull data from diverse sources—ERP orders, CRM customers, MES production data, database logs, Excel files, APIs, Kafka streams.

Transform (T): Convert raw data into usable form: type casting, null/exception handling, deduplication, encoding unification, multi‑table joins, master data mapping, metric calculation, pivot/unpivot, aggregation.

Load (L): Write processed data to target—ODS, data warehouse, data lake, ClickHouse, StarRocks, or business databases.

The article notes that all three patterns contain E, T, L; the difference is the position of T .

ETL: Transform Before Load

Sequence: Extract → Transform → Load. Data is cleaned, standardized, joined, and calculated before entering the warehouse.

Typical Scenario

ERP sales orders with inconsistent customer codes, date formats, null amounts, test orders, and mismatched product codes. ETL pipeline: extract → clean → standardize → join → calculate → load.

Best‑Fit Scenarios

Target system (e.g., core financial DB, regulatory DB) must receive only clean data.

Sensitive fields (phone, ID, bank card) must be masked before leaving production.

Target compute capacity is limited; offloading heavy joins/aggregation to a separate ETL engine protects query performance.

Limitation

T blocks L. As transformation complexity grows, load latency increases. With billions of daily logs and sub‑10‑minute refresh requirements, the pipeline becomes a bottleneck.

ELT: Load Then Transform

Sequence: Extract → Load → Transform. Raw data lands first in ODS/Raw Layer/data lake; transformation runs later using the target platform’s compute (MPP, Spark, lakehouse).

Core Philosophy

Raw data is an asset. Example: business rule for "active customer" changes from "purchased in last 12 months" to "transacted in last 6 months + spend > ¥10k". If only the final label was kept, historical recomputation is impossible. ELT preserves raw orders, enabling re‑processing.

Best‑Fit Scenarios

Large volumes, strong target compute, frequent business logic changes, need for raw data retention.

Modern cloud warehouses, MPP databases, Spark, lakehouse architectures.

Misconception

ELT does not mean "dump everything unchecked." Structurally broken data, mandatory masking, unparsable messages, and obviously illegal records still need pre‑load handling.

ETLT: Hybrid – Light Pre‑Transform, Land Raw, Heavy Post‑Transform

Sequence:

Extract → Transform (technical) → Load → Transform (business)

.

Two Types of Transformation

Technical Transform (pre‑load): Type correction, JSON parsing, anomaly filtering, sensitive field masking, basic encoding conversion, CDC event normalization. Goal: ensure data can safely and correctly enter the platform.

Business Transform (post‑load): Customer segmentation, revenue calculation, profit margins, wide tables, common metrics, regional roll‑ups, operational models. Goal: turn landed data into analyzable assets.

Real‑Time Order Example

CDC captures order inserts/updates/deletes.

Pre‑load: event parsing → field conversion → necessary filtering → basic standardization.

Load into ODS.

Post‑load: ODS → DWD → DWS → ADS layers using SQL/warehouse compute for joins, metrics, and aggregations.

This separates the real‑time ingestion pipeline (light, stable) from complex business modeling (centralized in warehouse). The article notes that FineDataLink 5.0 can play the role of the real‑time pipe while leaving heavy modeling to downstream warehouse tasks.

Terminology Note

ETLT is not a strict standard term; it describes a light pre‑transform + raw landing + heavy post‑transform hybrid pattern increasingly common in modern data architectures.

Five Decision Criteria

Can this transform wait until after load? If not (e.g., mandatory masking before leaving prod), it must be pre‑load.

Will this logic change often? If yes (customer tiers, profit definitions, channel classifications, valid order rules), push it post‑load to avoid rewriting ingestion pipelines.

Does raw data have future value? For historical backfill, model training, metric recalculation, troubleshooting, algorithmic analysis – retain raw data.

Where is compute cheapest? Consider server cost, dev/maintenance cost, task coupling, resource contention, and latency. If the warehouse has strong compute, large joins/aggregations need not stay in the ETL engine.

How fast must data be? T+1 reports, hourly ops analytics, and second‑level device monitoring are different problems. Real‑time paths should minimize transforms: short collection path, few transformations, stable sync; complex calc can move downstream.

The mature architecture is not ETL vs ELT but different data, different stages, different transforms – each placed where it fits best .

Beyond ETL/ELT: Building a Stable Data Pipeline

Initial focus: "Can data flow?" Production reality: "Will it keep flowing?"

2 AM task failure – what about downstream dependencies?

Source adds a column – does downstream auto‑adapt?

50 M rows sync halts halfway – restart or resume?

Single bad record – fail whole batch or divert to dead‑letter queue?

Upstream late by 30 min – when does downstream metric refresh?

A real data pipeline is:

Source → Extract → Transform → Load → Schedule → Dependency → Monitor → Alert → Retry → Backfill → Validate

. ETL/ELT covers only a small middle segment. As task count grows from dozens to thousands, engineering time shifts from writing transforms to debugging failures, impact analysis, and recovery. The article cites FineDataLink 5.0 as an example of a platform that manages scheduling, dependencies, run history, exception handling, and re‑runs alongside sync/transform – preventing a "forest of unmaintainable data scripts."

Three Questions to Internalize the Patterns

When does data enter the target platform? Transform‑then‑load = ETL; load‑then‑transform = ELT.

Must raw data be retained? Stronger need for raw retention, historical replay, flexible modeling → lean toward ELT.

Should all transforms live in one place? Most modern platforms answer no . Security, format, protocol, basic quality → pre‑load. Metrics, subject areas, models, business definitions → post‑load. ETLT’s real insight: transformation itself should be layered .

Analogy

ETL = cook fully, then serve.

ELT = stock ingredients in kitchen, cook on demand.

ETLT = wash & prep ingredients, then cook properly in kitchen.

No pattern is universally superior. Excellent data architecture judges: what data lands ASAP, what rules run early, what logic stays in the warehouse, what raw info must be kept . Understanding this means grasping the core architectural principle: place every Transform in its most suitable position .