Best data pipeline tools: the four layers and what fills them
Ingestion, transformation, orchestration and streaming are separate jobs with separate tools. Which layer your problem sits in, and what the realistic options are in each.
Osman Ramadan11 min read
On this page
"Data pipeline tool" describes at least four different products, and comparing them against each other produces nonsense. Airflow and Fivetran are not alternatives; they occupy different layers and a great many companies run both.
This page maps the layers, says what fills each, and gives the questions that place your problem in one of them. For choosing specifically as a small team without a data engineer, see ETL tools for small teams, which covers the same ground from a very different budget.
What we'll cover
The four layers
Ingestion moves data from where it is produced to where it will be analysed. Source systems, APIs, databases, files, into a warehouse or lake.
Transformation turns raw arrivals into something usable: cleaned, joined, aggregated, modelled into the tables analysts actually query.
Orchestration decides what runs when, in what order, with what dependencies, and what happens when a step fails.
Streaming handles data continuously rather than in batches, for cases where waiting until tonight is not acceptable.
A complete stack has all four, sometimes with one tool covering two. The common mistake is buying an orchestrator when the problem was ingestion, which produces an elaborate scheduler running connectors you still had to write yourself.
Ingestion: getting data in
The layer where the most money is spent and the least differentiated work happens, because moving data out of Salesforce is a solved problem that somebody should have solved once.
Managed connectors — Fivetran, Airbyte Cloud, Stitch and similar — maintain the connectors for you. A source API changes and it is their problem. This is the value, and it is worth more than teams expect, because connector maintenance is unglamorous work that arrives without warning.
Open-source and self-hosted — Airbyte self-hosted, Meltano, Singer taps. No licence cost, and you maintain the deployment and deal with the connectors that break.
Writing your own — genuinely correct for a handful of simple sources. A script pulling one API into a warehouse table is an afternoon, and buying a platform to do it is buying the platform for the fourth source you do not yet have.
The cost model to understand: most managed ingestion prices on rows or records moved, which means an unexpectedly chatty source or a badly configured sync can produce a bill nobody predicted. Check what a full resync costs before enabling one, since that is the moment the arithmetic surprises people.
When it is worth paying: once you have more than a few sources, or one source whose API changes frequently. The break-even is usually fewer sources than people assume, because the maintenance is the cost rather than the initial build.
Transformation: making it usable
Raw ingested data is rarely queryable. It needs cleaning, joining, deduplicating, and modelling into tables that answer questions.
dbt has become the default here, and for a reason: it makes transformations version-controlled SQL with dependencies, testing, and documentation. Analysts write SQL, it manages the DAG, and the models live in a repository under review rather than in someone's saved queries.
SQL in the warehouse directly, scheduled, is entirely reasonable for a small number of transformations. The thing dbt adds is structure, testing, and lineage, which matter once there are more than a few models and more than one person.
Python-based transformation for anything SQL expresses badly — complex reshaping, calling an external service, applying a model.
The practice that matters more than the tool: test your transformations. Not that the SQL runs, but that the output is plausible — row counts in range, no unexpected nulls in key columns, totals reconciling against the source. Most data quality incidents are silently wrong numbers rather than failed jobs, and only assertions catch those.
Orchestration: running it in the right order
The scheduler and dependency manager. Run ingestion, then transformation, then the report, and handle the case where the second step fails.
Airflow is the established option with the largest ecosystem and the most people who know it. It is also the heaviest to operate and its scheduling model shows its age in places. Managed versions from the cloud providers remove most of the operational burden.
Dagster treats data assets rather than tasks as the primary concept, which suits analytics work well, and has stronger local development and testing than Airflow.
Prefect is lighter to adopt, with a Python-native feel and less ceremony.
Your warehouse's own scheduler, or a cron job, is the right answer more often than this section implies. If you have four jobs in a fixed order, an orchestration platform is a great deal of machinery for the problem.
The question that decides whether you need one: do your jobs have dependencies that matter? If step three must not run when step two failed, and you currently handle that by hoping, you want an orchestrator. If everything is independent, a scheduler is enough.
Streaming, and whether you need it
Continuous processing rather than batches: Kafka, Kinesis, Pulsar for transport, with Flink or similar for processing.
When it is genuinely required: fraud detection, live operational dashboards, anything where a decision must be made on data seconds old.
When it is not, which is most of the time: reporting, analytics, daily or hourly aggregates, anything a person reads. Streaming infrastructure is substantially more complex to build, operate, debug and reason about than batch, and adopting it for work that could run hourly is a large ongoing cost for latency nobody needed.
The honest test: what decision gets made differently because the data is five seconds old rather than one hour old? If there is no answer, batch is correct. If the answer is "it would feel more modern", batch is definitely correct.
The middle ground most teams actually want: frequent batches. Running every fifteen minutes gives most of the perceived freshness at none of the operational cost, and it is a configuration change rather than an architecture.
ETL, ELT, and why the order changed
Worth understanding because the terms are used interchangeably and mean different things.
ETL extracts, transforms, then loads. Transformation happens before the data lands, in a separate processing system. This was necessary when warehouse storage and compute were expensive, because you only stored what you had already refined.
ELT extracts, loads, then transforms. Raw data lands in the warehouse and transformation happens there in SQL. Cheap warehouse storage and separated compute made this viable, and it is now the default for analytics.
Why ELT usually wins: the raw data is retained, so when a transformation turns out to be wrong you can rebuild without re-extracting from the source. That property is worth a great deal the first time somebody discovers a modelling error six months old.
When ETL still applies: when data must be filtered or masked before it lands, which is common with personal data and regulatory constraints. Loading raw personal data into a warehouse and cleaning it afterwards means the raw data is in the warehouse, which may be precisely what you were required to avoid.
Placing your problem
Four questions locate almost any data pipeline problem in a layer.
Where is the pain? Getting data in, making it usable, running things in order, or latency. Each is a different layer and a different purchase.
How many sources? One or two, write scripts. Several with awkward APIs, buy managed ingestion. The maintenance is the cost.
Who will maintain it? An orchestration platform assumes someone owns it. Without that person, a simpler scheduler you all understand beats a better tool nobody operates.
How fresh does it actually need to be? Answered honestly, this saves most teams from streaming infrastructure they would have regretted.
There is also a layer underneath all of this that rarely gets named: the operational plumbing around the pipeline. Alerting when a sync fails, notifying the owning team, reconciling row counts between source and warehouse, opening a ticket when a freshness check misses. That is ordinary automation rather than data engineering, and describing it is faster than wiring it together. On CodeWords you describe what should happen in plain language and Cody, the automation builder, builds it, connects it to your warehouse and chat tools, and deploys it. Automations connect to more than 3,000 integrations. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.
Frequently asked questions
Do I need Airflow?
Only if your jobs have dependencies that matter and enough of them that managing the order by hand has become error-prone. Four jobs in sequence is a cron job. Forty jobs with branching dependencies and retry logic is an orchestrator.
What is the difference between ETL and ELT?
Where the transformation happens. ETL transforms before loading, ELT loads raw and transforms in the warehouse. ELT is the modern default because storage is cheap and keeping the raw data lets you rebuild when a transformation turns out wrong. ETL still applies where data must be masked or filtered before it lands.
Is Fivetran worth the cost?
If you have several sources with APIs that change, usually yes, because you are buying connector maintenance rather than the initial build. If you have two stable sources, writing and owning the scripts is cheaper and entirely reasonable. Model the cost at your real row volume, including a full resync, before committing.
Should I use streaming?
Only if a decision genuinely depends on data seconds old. For reporting and analytics, batch is simpler to build, operate and debug. Frequent batches give most of the perceived benefit at a fraction of the complexity.
How do I know a pipeline is working?
Check the output rather than the job status. Row counts in expected ranges, freshness within tolerance, key columns not unexpectedly null, totals reconciling against source. A pipeline that completes successfully while producing wrong numbers is the failure that actually hurts, and only assertions catch it.
Can one tool cover all four layers?
Some platforms claim to, and in practice most teams end up with two or three specialized tools plus their warehouse. That is fine and generally better than one tool doing everything adequately, provided somebody can describe how they fit together.
What is the most common mistake?
Buying an orchestrator when the problem was ingestion. The symptom is a well-managed schedule running connectors you still maintain yourself, which is the expensive half of the problem left unsolved.
How do I stop pipeline costs running away?
Two things dominate: rows moved on managed ingestion and compute on the warehouse. Set a budget alert on both before the first production sync rather than after, check what a full resync costs, and watch for a source that suddenly becomes chatty. Most alarming data bills are one misconfigured sync rather than genuine growth.
Should transformations live in the warehouse or before it?
In the warehouse, for analytics, because keeping the raw data means you can rebuild when a model turns out to be wrong. Transform before loading only where you must not land the raw data at all, which in practice means personal data under a regulatory constraint.
How long should raw data be kept?
Longer than feels necessary, because the case for keeping it only becomes obvious when a transformation turns out to have been wrong for months. Storage is the cheap part of a warehouse; re-extracting from a source system that has since changed, or that no longer holds the history, is sometimes impossible. Keep the raw landing tables and let the modelled ones be rebuilt from them.