ETL

What it means

ETL stands for extract, transform, load. It is a process that regularly pulls data from different sources, such as the accounts, the online shop, the CRM or attendance records, brings it into a common format and stores it in a data warehouse or reporting tool.

Most of the work usually lies in the transformation. Data from different systems use different customer codes, product names, currencies and dates. The transformation standardises them, cleans out duplicates and errors and recalculates them so they can be compared and combined.

ETL is the invisible part of business intelligence. Users see a dashboard with charts, but whether those are right depends on whether the ETL loaded the data completely, on time and joined it correctly.

How it works in practice

In management reporting we recommend that financial figures on a dashboard come straight from the accounts, with the ETL only carrying them over rather than recalculating them by its own logic. Otherwise there will be two truths: one in the books and another in the charts.

Master data are a key part of it. If the same customer is labelled differently in the CRM and in the accounts, the ETL will not link them and revenue will be split between two customers. Common identifiers, such as the company ID, make the work much simpler.

Modern tools allow ETL without programming through ready-made connectors and APIs. For small companies a simple overnight transfer into a spreadsheet or reporting tool is often enough. With sensitive data, where it is stored and who can access it must be settled.

What to watch out for

The most common problem is a silent failure: one night the ETL does not run or loads only part of the data, and nobody notices. Management then decides on incomplete figures. Every transfer should have a completeness check and an alert on failure.

The second risk arises when a source system changes. When the accounting software or the shop’s structure changes, the old ETL stops working or starts producing nonsense. A system change should therefore always include updating the data flows.