When a company asks us for a dashboard “by next week”, we almost always find the same thing: the visible part —the charts— is the tip of the iceberg. Underneath there is quiet work that decides whether the project succeeds or fails. That work has a name: ETL. In this article we explain what ETL is, what each of its phases is for, and why it is the foundation on which every Business Intelligence project rests.
What ETL means
ETL stands for Extract, Transform, Load. It describes the process that takes data from wherever it lives, cleans it and shapes it, and deposits it in a destination where it can be analysed —usually a data warehouse or the data model of your BI tool.
Put simply: ETL turns raw, scattered, inconsistent data into reliable information that is ready to query. Without it, every report is built by hand, every figure is argued over, and nobody fully trusts the numbers.
Extract: gathering the data wherever it is
The first phase is to extract the data from its source systems: the ERP, the CRM, a SQL database, Excel files, a cloud service or an API.
The challenge is rarely technical; it is one of diversity. In a typical project, sales live in the ERP, customers in the CRM, and commercial targets in a spreadsheet that someone updates by hand. Extracting means connecting to all of that in an orderly, repeatable way —not copying and pasting once.
Transform: where the magic (and the work) happens
The transform phase is the heart of the process and where almost all the effort goes. This is where raw data becomes usable data. Some common examples:
- Cleaning: removing duplicates, fixing date formats, unifying “London”, “london” and “LDN” into a single value.
- Standardising: making sure every amount is in the same currency and every unit on the same scale.
- Joining: matching the sales table with the customer table so you know who bought what.
- Calculating: deriving metrics such as margin, average order value or customer tenure.
- Business rules: applying the agreed definition of “active customer” or “net sale” so it means the same thing across the whole organisation.
It is in this phase that the classic BI problem gets solved: two reports that give different figures for the same question. When the logic lives in the ETL rather than in each analyst’s head, there is only one figure.
Load: leaving the data ready to query
Finally, the load phase deposits the transformed data in its destination: a data warehouse, a data mart, or directly the model of a tool such as Qlik, Tableau or Power BI. From here, building a dashboard is fast, because the data already arrives clean, joined and with the metrics calculated.
ETL vs ELT: a quick note
You may have heard of ELT (Extract, Load, Transform). The difference is the order: in ELT the raw data is loaded first into a powerful store —usually in the cloud— and the transformation happens afterwards, once it is already inside.
It is not that one replaces the other. ELT has gained ground thanks to the power of modern data warehouses and to very large data volumes, while classic ETL remains ideal when you need to transform before loading for governance or performance reasons. The important thing is to understand that both do the same job —extract, transform and load; only the moment and place of the transformation change.
Why ETL is often 80% of the project
There is a rule that holds true in almost all our projects: ETL takes around 80% of the time and dashboards the remaining 20%. This is not a planning failure; it is the nature of the work.
Real-world data is messy. It comes from systems that were never meant to talk to each other, with different criteria and errors accumulated over years. Bringing order to all of that is slow and unglamorous, yet it is exactly what makes the final chart trustworthy. A spectacular dashboard on dirty data is not an asset: it is a risk with a nice presentation.
ETL best practices
- Quality first. Validate the data at the door: duplicate records, null values, impossible amounts. An error caught in the ETL costs minutes; the same error in a board report costs credibility.
- Work in layers. Separate a raw layer, a transformed layer and a consumption layer. That way you can rebuild one part without breaking the rest.
- Document the business rules. Write down what a “net sale” is and why. Whoever touches the process next —perhaps you, a year from now— will be grateful.
- Automate and schedule the loads. An ETL that has to be launched by hand ends up never being launched.
Conclusion
ETL never shows up in the screenshots and never impresses in a demo, but it is the foundation everything else is built on. Investing well in this phase is what separates a data project people actually use from one nobody believes.
At Digital Fox Data we design solid data pipelines as part of our Qlik consulting, and if your team wants to master the load script we support you with our Qlik courses. Have a project on your hands? Let’s talk.