ETL in Practice: How Companies Actually Move and Reshape Their Data

ETL stands for extract, transform, load: pull data out of the systems that hold it, reshape it into something consistent, and load it somewhere it can be reported on. It is the plumbing behind almost every dashboard a business relies on, and it is usually invisible until it breaks.

This is a plain guide to what ETL is actually used for, industry by industry, and what tends to go wrong. The scenarios below are typical patterns rather than accounts of particular companies.

Retail: one view of stock and sales across sites

A retailer with several branches usually ends up with sales in a till system, stock in a second system, and supplier information in spreadsheets. Nothing reconciles, because nothing shares a product code.

The ETL work is mostly the transform step: mapping each system's product identifiers onto one master list, standardising units and dates, then loading a nightly snapshot into a single reporting table. The payoff is being able to ask "what sold where, and what is left" and get one answer instead of three.

Healthcare: joining records that were never meant to be joined

Patient records, lab results and billing typically live in separate systems, each with its own patient identifier. The hard part is not moving the data, it is matching people reliably without creating duplicates — and doing it under data-protection rules that dictate what may be copied, where it may be stored, and who may see it.

ETL in this setting usually includes a de-identification step, so analysis runs on data that cannot be traced back to individuals.

Finance: reporting that has to be reproducible

Finance teams pull from ledgers, banking feeds and internal spreadsheets. The distinguishing requirement is auditability: you need to be able to say where a figure came from and reproduce it months later.

That shapes the design. Loads are usually append-only rather than overwriting, each row carries the date and source it arrived from, and failed loads stop the process rather than partially completing. A pipeline that silently drops rows is worse than no pipeline, because the reports still look fine.

Manufacturing: machine data at an awkward volume

Production lines generate readings constantly, often in formats that predate the reporting tools by decades. The volume forces decisions: what to aggregate on the way in, what to keep at full resolution, and how long to keep it.

Aggregate too aggressively and you cannot investigate a fault after the fact. Keep everything and storage and query times grow until the reports stop being used.

E-commerce: the same customer, five times over

Web analytics, the store platform, email marketing and support tickets each hold a partial version of the same customer. Merging them is a matching problem — email addresses, order references, sometimes nothing but a browser identifier — and the merge rules need deciding by someone who understands the business, not left to whatever the tool does by default.

What ETL work usually gets wrong

• No handling of failure. A load that fails halfway and reports success produces confidently wrong numbers. Every step needs to fail loudly.
• Transform rules nobody wrote down. "Returns are excluded from this figure" is a business decision. If it lives only in the pipeline, nobody can check it.
• Full reloads that should be incremental. Reloading everything nightly works until the data outgrows the window, and then it fails at the worst moment.
• Fixing dirty data downstream. If two systems disagree about a customer's address, the answer is to fix the source, not to patch it in transit forever.
• No monitoring. Pipelines that ran fine for a year fail silently when a source system changes a field. Someone needs to be told.

Do you actually need ETL?

Not always. If all your data sits in one system and you need better reports, you need better reports. ETL earns its cost when data genuinely lives in several places and the reconciliation is currently being done by a person with a spreadsheet each month — that person is the pipeline, and they are the one who leaves.

The commonest starting point we see is not a data warehouse at all. It is an Access database or a set of Excel files that grew into the reporting system by accident. If that is where you are, the first question is whether the underlying system should be consolidated before anything is built on top of it.

Our services page covers database and reporting work, and a free system review will tell you which problem you are actually solving.