Whenever I stumbled upon a newly discovered data-engineering tool, I’d dive into its documentation, head straight to the “Getting Started” section, copy commands or docker-compose files, tinker a bit…

Data Engineer Things

The author reflects on their journey from chasing the latest data engineering tools to focusing on foundational concepts, emphasizing the shift from ETL (Extract, Transform, Load) to ELT (Extract, Load, Transform). The traditional ETL process, necessitated by the high costs and limitations of early data warehouses, is contrasted with the modern ELT approach, facilitated by advancements in cloud data warehousing. ELT offers greater flexibility and efficiency by loading raw data into the warehouse and handling transformations within the warehouse, aligning better with agile development practices.

ETL and ELT

Nice distinction between the two, but I would offer another perspective on the use of ETL vs ELT.
ETL is critical for organizations that seek:
<ul>
<li>
Correctness &amp; Consistency - having quality &amp; normalization that is consistent for all should be the primary requirement of a data layer. Leaving that task to an individual group introduces a higher rate of error and inconsistency propagation across the enterprise.
</li>
<li>
Governance - somewhat tied into quality, but also in who has access to the data, how often they are working and viewing, and what manner. These are all unanswered questions when you are opening your data up to further, arbitrary workloads on a wide array of raw data.
</li>
<li>
Cost - Data processing is not cheap, and ELT leaves an organization exposed supporting multiple redundant workloads that bring potential high compute, data transfer, and storage costs.
</li>
</ul>

I applaude all “back to basics” articles! While new tools are important it’s more important not to lose the sight of the foundations, and reviewing them regularily in the context of ever increasing scope of the industry, as it can murk the waters somewhat. Thank you!

Thanks for the post! ELT makes lot of sence to do transform in reporting layer when buisness logic is dynamic in nature.

ELT has its place, but I have seen multiple businesses switch from ETL to ELT for the wrong reasons – such as data engineers following the latest hype or wanting a ‘modern’ data stack while not considering whether it was fit for purpose.
The essence of ELT is that you’re loading raw data into the (normally SQL-based) data warehouse and starting the transformations from there. In many cases, the warehouse in question has a proprietary storage layer (Snowflake, Oracle, Redshift w/o Spectrum) meaning the only way to access it is through the proprietary query engine, costing you additional license $$ every time you want to access your own data.
That’s fine when it comes to data that belongs in a warehouse – wide columnar, highly indexed data optimized for aggregations, drilldowns - in short, efficient OLAP query patterns. You want the performance and flexibility of a data warehouse for that kind of data, and this costs a little bit. I get that.
But raw data is different. Raw data is big – sometimes VERY big. And raw data is not fit for direct ad-hoc querying because it’s messy and unrefined. But the warehouse takes its tax anyway, because you stuffed it full of raw data so it’s going to optimize that data, index it, profile it, partition it and maybe generate some clever behind-the-scenes cubes since it is trying to help you do efficient OLAP – because that’s what it’s designed to do.
And for raw data – petabytes and petabytes of it – you’ll pay big money for all that behind-the-scenes work that you have zero use for.
The Snowflake sales reps will NOT tell you this, because they make $$$$ every time you put tons of unfit-for-Snowflake data inside their pay-to-access proprietary data vault. Snowflake is fundamentally built to do ELPPPPP - Extract-Load-Pay-Pay-Pay-Pay-Pay, and using it as the compute engine in an ELT setup instead of as a warehousing destination in an ETL setup is a fantastic way to pour more of your company’s money needlessly into Snowflake’s pockets.
Now, if you have many small data assets and lots of schema evolution then an ELT setup is more flexible and the added cost is manageable. But if you have a few data pipelines with dramatically more data that very rarely changes significantly, then an ETL setup – at least for those big pipelines – is a far better option.