Insights Data Pipelines & ETL
Data pipelines and ETL, explained without the jargon
Nobody wakes up excited about ETL. But almost every frustrating "why is this number wrong?" or "who has the latest file?" moment in a business traces back to the same root cause: data that lives in too many places and has to be moved by hand. This guide explains what data pipelines and ETL actually are, in plain language, and how a reliable pipeline quietly ends the export-paste-and-pray routine for good.
In this guide
Why your data lives in five places
It is not bad planning, it is just how businesses grow. You start with a spreadsheet. Then you add a CRM. Then a billing tool. Then a support platform, a marketing tool, a shared drive of exports. Each one is good at its job and none of them were designed to share data with the others. So the glue becomes a person: someone exports a CSV here, pastes it into a sheet there, reconciles two numbers that should match, and hopes nothing was missed.
That works right up until it does not. The moment the business depends on a number being current and correct, manual data movement becomes a liability. That is the problem data pipelines exist to solve.
What a data pipeline actually is
A data pipeline is an automated route that moves data from where it is created to where it is useful, cleaning and reshaping it along the way. Instead of a person doing the export-clean-import dance, the pipeline runs on a schedule (say, every morning at 6am) or in real time (the moment a new record appears), and does the whole thing without anyone touching it.
A good pipeline does more than copy data. It validates it, checks that a Bloomberg price actually looks like a price, that a date is really a date, standardizes formats so "USA" and "United States" stop being two different things, and lands the result somewhere your team can trust, usually a database like PostgreSQL or a data warehouse.
ETL vs ELT, explained
ETL is the classic term and it stands for three steps:
- Extract, pull the data out of the source, an API, a database, a file feed.
- Transform, clean it, reshape it, join it with other data, make it consistent.
- Load, place it into the destination where it will be used.
You will also hear ELT, which just swaps the order: load the raw data first, then transform it inside a powerful cloud warehouse. ELT has become popular because modern warehouses can crunch huge volumes cheaply. Which one is right depends on your data and tools, ELT for big raw volumes you will reshape later, ETL when you need clean, validated data the moment it arrives. A good specialist picks based on your situation, not on fashion.
In high-stakes environments, "it mostly works" is not good enough. Pipelines should be built assuming things will go wrong, because eventually they do.
What makes a pipeline reliable (and what makes one dangerous)
The difference between a pipeline you can trust and one that becomes a landmine comes down to a few things most people only appreciate after being burned:
- Validation. The pipeline checks the data makes sense and refuses to load garbage that would corrupt downstream reports.
- Idempotency and retries. If it runs twice or a source is briefly down, it recovers cleanly instead of double-counting or silently dropping records.
- Monitoring and alerts. When something breaks, a human is notified immediately, not three weeks later when a report looks off.
- Orchestration. Tools like Prefect or Airflow manage dependencies and schedules so complex flows run in the right order, every time.
At OMERS, one of Canada's largest pension funds, I built pipelines handling live financial data from Bloomberg and MSCI into PostgreSQL, used daily by risk and portfolio teams, and led a migration of the orchestration layer from Airflow to Prefect without disrupting a single day of operations. The lesson from high-stakes data work applies to any business: reliability is a feature, not an afterthought.
The cost of moving data by hand
Put a rough number on your current manual data work. Adjust the sliders for the reporting or reconciliation someone does every week.
Manual data work estimator
Roughly what a reliable pipeline could give back.
Rough estimate for planning only. It ignores the harder-to-price benefit: fewer wrong numbers.
Have a data mess you have been avoiding?
Tell me where your data lives and what someone keeps moving by hand. I will tell you what it would take to build a pipeline that does it reliably, so you stop wondering whether a number is even current.
Tell me what's going onFrequently asked questions
What is a data pipeline in simple terms?
A data pipeline is an automated route that moves data from where it is created to where it is useful, cleaning and reshaping it along the way. Instead of a person exporting CSVs and pasting them into a spreadsheet, the pipeline does it on a schedule or in real time, reliably and without manual effort.
What does ETL stand for?
ETL stands for Extract, Transform, Load. Extract pulls data from a source such as an API, database, or file. Transform cleans and reshapes it into a consistent format. Load places it into a destination like a data warehouse or PostgreSQL database where teams can query and report on it.
What is the difference between ETL and ELT?
ETL transforms data before loading it into the destination. ELT loads the raw data first and transforms it inside a powerful data warehouse afterward. ELT has become popular with modern cloud warehouses because they can transform large volumes cheaply, but ETL still makes sense when you need clean, validated data on arrival.
How do I know if my business needs a data pipeline?
If someone regularly exports data from one system and imports it into another, if your reports require manual assembly across tools, or if you are never quite sure whether a number is current, you likely need a pipeline. The tell-tale sign is repeated manual data movement that has to happen for the business to function.