ETL versus ELT through a model-training pipeline

ETL transforms data before loading the analytical destination. ELT loads raw data first and transforms inside or near the destination. The useful question is which path meets governance, cost, latency and reproducibility needs while producing the correct model dataset.

Reconcile both paths

The local data-engineering lab generates 2,000 invented events. Its ETL path aggregates daily region features before loading. Its ELT path loads raw rows to SQLite, reads them back and applies the same transformation.

from data_engineering_cases import etl_elt_case

result = etl_elt_case()
assert result["same_keys"] is True
assert result["maximum_amount_difference"] < 1e-9
print(result["raw_rows"], result["output_rows"])

Both paths produce 270 date-region rows with zero amount difference. This establishes transformation equivalence on one local fixture. It is not a cloud cost or scalability benchmark.

Choose from constraints

ETL can remove sensitive fields before the destination and reduce stored volume. It can also hide raw detail needed for a new feature. ELT preserves raw history and lets a governed warehouse execute shared SQL, but requires access control, storage policy and compute management.

For model training, preserve event time, ingestion time and source version. A transformation must be rerunnable as of a forecast cutoff. Test row counts, keys, nulls and aggregate reconciliation at the boundary.

Keep business logic portable

Express transformations as testable functions or versioned SQL rather than embedding them only inside an orchestrator. Store output schema and hash. If ETL and ELT variants coexist, run a dual-path reconciliation before migration.

The Data Science course uses this comparison to connect data architecture with point-in-time model evidence.

Exercise

Add a refund rule and late events to both paths. Compare outputs, compute/storage steps and access boundaries. Choose one path for a declared organization and explain the trade-off.

Continue learning

This article is part of the Data engineering for data science sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Continue with Design a training dataset contract.
• Then apply it in Batch versus streaming data for an actual prediction need.

Reference: Google Cloud ETL overview.