ETL and ELT: how much difference can the order of those letters really make? To explore that question, we first need to understand what each step is.
E is for ExtractAPI, database connection, flat file, JSON, XML… Whatever it is, Extract grabs raw data from a source system.
L is for LoadNext, Load that data into the repository (expect big differences depending on whether this step comes before or after Transform).
T is for TransformMake sense of the data with respect to your use case. Structure, clean, model, join, blend, govern and secure the data.
To dive into the detail, our blogs on Extract and Load and the Transformation layer are definitely worth a read.
For more on ETL and ELT, you’re in the right place. Why does the order matter and what difference could it make to your business?
ETL has been the approach used to construct a data warehouse for many years. On-premise servers have physical constraints in both storage and compute power. This means a great deal of thought and planning is needed. Transforming data before loading it into a data warehouse minimises the volume of data. And it reduces data repetition and redundancy.
That sounds good – but what’s the cost? Let’s examine that question by thinking about the following metrics:
Cloud infrastructure has effectively removed the constraints associated with on-premise servers (Think AWS, GCP, Azure and others). Storage is cheap enough to not be a consideration for the vast majority of use cases. And compute power is scalable, flexible and on demand.
By loading the data once, without transform, you can remove the programmatic overhead. The barrier to entry is lower and the time to execute a pipeline is dramatically reduced. Crucially, the data transformation challenge ends up in the hands of the people best placed to solve it: your analysts.
Consider a column store cloud-based data warehouse – scalable and quick. It can empower your analysts to execute necessary data transformations super-fast. They can also deploy SQL to apply the business logic iteratively and transparently.
No more communication breakdown between analysts and engineers. No more wasting of engineers’ talents and skills on lift-and-shift data tasks. And most important of all, no more delays on insight or answers to your questions.
ELT unlocks the power in your data. kleene unlocks ELT for your business.
Want to know more? We’d love to chat. Please get in touch.
We’d love to show off a bit, so get in touch below.
Messy data causes a headache in companies of every size. Many businesses we speak to think that they need...
Beginning a data warehousing project is often seen as a daunting task, believed to be an expensive and time...