Explainer
ELT vs ETL: choosing a pipeline pattern
When to transform data in flight, and when to land it raw and transform it later.
2 min read
The distinction in one paragraph
ETL transforms data before it lands in the warehouse; ELT lands raw data first and transforms it inside the warehouse, usually with SQL. The acronyms are old, but the trade-off is current: transforming in flight gives you control and smaller tables, while landing raw data first gives you an audit trail and room to change your mind.
Why ELT became the default
Columnar warehouses made transforming data in place practical, and cheaper storage made keeping a raw copy easy. With the raw data kept, any downstream table can be rebuilt when the logic changes, without extracting from the source system again.
Landing raw data first also makes debugging honest. When a number looks wrong, you can trace it from the dashboard back through each transformation to the rows it came from.
Where ETL still wins
Three cases. Personal data you may not store should be masked or dropped before it lands. High-volume event data often needs to land only as aggregates. And when a source's format is hard to work with, a typed, validated landing step spares everyone downstream from dealing with it.
In practice: both, on purpose
Most platforms end up with a mix: light clean-up in flight, such as types, duplicates and personal data, and business logic where it can be versioned and tested. The pattern to avoid is the accidental mix, with logic scattered across pipeline steps and warehouse views and no rule for what lives where. Decide the boundary once and write it down.
DataSquares Pipelines supports both shapes. Transform steps reshape rows with SQL, code steps in Python handle what SQL cannot, and a load step writes to a managed dataset, to your own database or warehouse, or to files. Business definitions then live in the data model, as relationships and SquareX measures that every dashboard shares.