Explainer · Data

What is a data pipeline and does a small business need one?

What is a data pipeline? A data pipeline automatically moves data from the tools you use, cleans it up and lands it in one place for reporting.

Published Reviewed 11 min read

You’re a small business owner, and every week you spend Monday morning manually copying and pasting your ad spend, Shopify and accounts package numbers into a spreadsheet to decide what to do.

The problem is, when you check, the numbers don’t match because there isn’t an automated path to bring all those figures into the same place. Until you fix that, you continue the manual copy-paste dance every week.

A data pipeline is an automated process for moving numbers from different systems into one place. It automatically checks your source systems at regular intervals, tidies up the figures, and drops them into one place where they can be used for reports. This means you can automate the building of reports instead of manually copying and pasting numbers.

Here we explain how data pipelines work, the difference between ETL (extraction, transformation, loading) and a data warehouse, whether you need a pipeline for a business of five to fifty people, and what happens when your pipeline breaks after you launch it. We won’t be promoting a particular vendor here, but will give our views based on what we’ve seen working with clients. We will give definitions when terminology matters. Let’s get started.

What a data pipeline actually is

It’s an automated route for taking multiple sources of raw data and moving them to a single store where you can generate reports and analyses from.

IBM makes the comparison with water, saying that the systems are like dirty lakes, the pipeline is like a treatment plant that cleans the water, which is then returned via pipes to your tap. You can only use it once it’s been through the treatment process.

Your data is the same. The work a data pipeline does can be split into four buckets: moving data from one system to another, checking the data is of good quality as it goes, enriching the rows in a dataset with lookups and applying the rules to ensure everyone uses the same definitions (for example, what is revenue).

This can be as simple as a few scripts that lead into each other at a minimum, but it’s up to you how complex you choose to make it, but essentially, a data pipeline automates the process.

The three stages

IBM says the three stages of a data pipeline architecture are ingestion, transformation, and storage.

The ingestion stage is where data is collected. An ingestion connector extracts data from your various sources, whether that’s shop orders, Google and Meta ad spend, Xero invoices, or CRM leads.

The transformation stage involves data cleaning. It’s where you standardise date formats, remove duplicate leads, and map abbreviated to full field values, which allows for reliable table joins in the data warehouse.

Storage is landing the clean rows of data that you’ve created into a database that’s read by the dashboard.

Why automation matters

On forums such as r/smallbusiness, small-business owners have described how manual data integration translates into a delay in getting useful reporting, because of mismatched data formats, inconsistent naming, duplicates, and hidden errors.

And for most small business teams, cleaning data is a time-intensive process, in our experience it wastes several hours each week.

By setting up a data pipeline, you can remove the manual layer entirely, freeing up the time to focus on making better decisions.

Pipeline vs ETL vs warehouse vs spreadsheet export

Pipeline, ETL, warehouse and spreadsheet export are often confused, as they all involve moving data from one place to another. But they are not the same thing, so it’s easy to make wrong budgeting and tool selection choices.

Data pipeline (general)ETL pipelineData warehouseManual spreadsheet export
What it isAny automated route that moves and cleans dataOne type of pipeline with a fixed extract, transform, load orderThe store the clean data lands inA person copying rows by hand, once
Who runs itA schedule or an event triggerA schedule, typically nightlyNothing; it just holds the dataYou, or whoever is on duty
When it runsAs often as you setOn a fixed intervalN/AWhenever someone remembers
What breaks itA source changes its fields or loginSame, plus transformation logic driftsNothing, until storage fills upThe person goes on holiday, or a column gets renamed

ETL is one kind of data pipeline. Every ETL pipeline is a data pipeline, but not every pipeline is ETL.

ELT (extract, load, transform) is the cloud-era variant where raw data gets landed first, and then you transform it. We think ELT is the sensible default for small businesses, because it leaves a copy of the raw data around so that you can re-run the historical data again if you change business logic. Read our plain-English explainer on ETL.

The warehouse is the tank, the pipeline is the pipe. When you do a spreadsheet export, that’s a one-off run by a person, with no rules written down anywhere, so it works until columns change or that person is off. Read our data warehouse explainer.

What one looks like in a five-person shop

So, say you have an online retailer that uses a stack consisting of Shopify, Xero and Pipedrive, coupled with an ad platform for running ads. You have a batch nightly pipeline that runs at 2am every night, pulling in yesterday’s Shopify orders, ad spend and impressions from the ad platform, invoices and payments from Xero, and new leads from Pipedrive. There’s a step where dates are transformed into ISO format, leads are deduplicated by email, and order status is mapped to order complete. Then, everything is loaded into a single table set in BigQuery.

Monday morning, at 8am, a report is built from the data in that table that shows how much revenue was made, how much was spent on cost of goods, how much was spent on ads, how many leads came in and the conversion rate.

At this small scale, a batch pipeline on a schedule is almost every business’ go-to. Real-time streaming pipelines are for when you’re detecting fraud or building live dashboards for events where low latency is important. However, the cost of engineering this type of pipeline far outweighs any benefits you’ll get for monthly or weekly decisions.

There’s an event-driven pipeline in the middle, that’s triggered once a new order lands in the shop. You don’t need either of those extras for a Monday report.

One owner on r/smallbusiness described their project as “building data pipelines from source to databases which I could then use to create dashboards/reporting for the business”. Building a data pipeline on this scale is achievable using off-the-shelf connectors and a simple schedule.

Do you need one yet?

If you have one or two tools, you don’t necessarily need a pipeline initially. If you’re happy to just pull all the data from a tool into a spreadsheet for a monthly report, then a spreadsheet will do.

If you’ve already got a tool that’s doing the job, then don’t go and get a new data infrastructure. There’s no point.

A pipeline generally comes in when data cleaning occurs. When you have data coming in from various sources with different formats, names, and duplicates, it’s worth looking at how to tidy up the data if you don’t want to spend hours each month doing it manually. A pipeline tidies it up for you as soon as you get it.

Other signs from experience:

  • The same number gets copied between systems by hand every week.
  • Two reports disagree on last month’s revenue and nobody knows which is right.
  • A dashboard exists but only one person knows how to refresh it.
  • Reporting waits on someone being in the office or at a particular laptop.
  • Decisions get delayed because the numbers aren’t ready yet.

Until you get to the point at which you’re experiencing two or three of these warning signs, it’s ok to be in the “spreadsheet is fine” stage of your journey, but once you do, this will keep getting worse, so it’s time to think about automating your move and cleaning.

Does this apply to you?

Probably not yet

If none or only one of these situations apply, a spreadsheet or native export is still the right tool.

See our data service

What it costs and how long it takes

The cost of your data pipeline will depend on several factors, but will essentially come down to the number of connectors, storage and people involved. Connectors are what will be used to move your data, storage is where it will be stored, and people will be responsible for building and maintaining the system.

These parts of the budget will not have a single price tag, because they will vary depending on how many sources and what your needs are. Be suspicious of any flat price figure until you know what you need.

Connectors versus scripts

Fivetran, for example, offers over 700 pre-built connectors. Through a managed connector service, it’s possible to ingest data without coding.

The trade is the subscription cost against the hours someone spends maintaining scripts. A sequenced set of scripts is a good choice as long as you have a relatively small and stable number of data sources and you’re willing to trade off the subscription cost against spending staff time on writing and maintaining the scripts, and the risk of potential change to the APIs.

Storage

This is going to be the cheapest step if you stay small. In US regions BigQuery’s storage is charged at a rate per MiB per second. 100 GiB of storage costs $1.15 USD for roughly half a month, and 1 TiB costs $23.552 USD for a month in us-central1. BigQuery reduces the rate further for long-term storage, dropping the rate if it’s unmodified for a 90-day period. The prices vary for different regions, but these are for a US region.

At this stage we typically see no more than a few million rows of data, so storage costs are a rounding error against the actual cost of the connectors that you’ll use for your data pipeline.

Build time and running cost

Building a data pipeline that pulls data from four or five sources may take a few weeks of focused work, especially when the biggest time sink is figuring out what counts as correct or incomplete data. However, once it’s running, the costs of running a data pipeline are pretty small, meaning subscribing to connectors and the like.

If there’s nobody in-house to own a data pipeline, we usually see the upkeep taking just a few hours of work each month, or you may want to consider an ongoing support retainer.

In practice · Case study

Building a Data Warehouse

Turning five disconnected platforms into one source of truth.

Read the case study
8+platforms connected into one warehouse
Minutesto generate any cross-platform report

What usually breaks once it is running

This section gets skipped on most vendor pages, yet it’s where projects often fail quietly after launch. We’ve seen the same failures repeat across clients, and they’re all fixable if you know to look for them early.

Connectors fail silently. A source renames a field, changes its login flow or deprecates an API endpoint, and if nobody checks the pipeline logs the report goes stale for weeks while everyone assumes the numbers are just wrong this month. Good pipelines alert on failure; treat that as non-negotiable from day one.

Definitions drift. “A lead” means something different in your CRM than in your ad platform, and nobody wrote the reconciliation rule down anywhere. The transformation step enforces whatever rule it was given at build time; if the business changes what a lead means and the rule doesn’t change, you get two different numbers from one pipeline. Write definitions down before you build, not after.

Nobody owns it. After the build someone must be named to watch alerts, update rules when the business changes, and keep connectors current. If that “someone” is the team, it means nobody actually does it. We tell clients plainly: name an owner or don’t build it at all.

Building for real-time when batch would do. A streaming pipeline costs more to run and breaks more easily because there are more moving parts in flight, which buys you nothing if your decision cycle is weekly or monthly. Stick with batch unless a genuine latency requirement forces otherwise.

Transforming before landing raw data. If you clean on the way in and discard the source copy, you cannot re-run history when a rule changes later. Land raw first, transform second; this cheap insurance saves you from bad weeks where “correct” turns out to mean something different than it did last month.

Most systems a small business touches will have messy inputs: different formats, inconsistent naming, duplicates and hidden errors.

Therefore you should plan to invest time in establishing cleaning rules from day one, because it won’t be tidy.

What to do next

First off, list every system that holds a number you report on: ads account, shop, accounts package, CRM and anything else touching your P&L. Pick the single report that costs the most copying time each week and ignore the rest for now.

For that one report write down where each number comes from and what "correct" means in plain words. “Revenue is completed orders minus refunds, as recorded in Shopify.” That sentence becomes your first transformation rule before any tool is chosen.

For a batch schedule, nightly is the default for almost every business at this scale, so start there unless you have a clear latency need that justifies more.

Our data and reporting service can help you figure out what connectors, data storage, and how much time is required to build a data pipeline. There’s no obligation or vendor lock-in. We’ll be honest about how ready you are; if spreadsheets are doing the job for another six months, then that’s fine. But we’ll just have a short call and look at your sources to give you a realistic plan.

Key terms

Data pipeline
An automated route that moves data from multiple sources, cleans it, and loads it into a storage location for reporting.
ETL
Extract, Transform, Load – a specific type of data pipeline that extracts raw data, transforms it, then loads it into a target system.
ELT
Extract, Load, Transform – a cloud-native variant where raw data is first loaded into a warehouse and transformed there.
Data warehouse
A centralised store that holds cleaned, structured data and serves it to dashboards and analytics tools.
Connector
A pre-built integration that pulls data from a specific source into a pipeline without custom coding.

Frequently asked questions

What is the difference between ETL and a data pipeline?

ETL is a specific type of data pipeline that follows a fixed extract-transform-load order, while a data pipeline is the broader term that also includes ELT, streaming and event-driven designs. All ETL pipelines are data pipelines, but not all data pipelines are ETL.

Can you give me an example of a data pipeline?

Imagine an online retailer that pulls yesterday’s orders from Shopify, ad spend from Google Ads, invoices from Xero and leads from a CRM each night. The pipeline cleans dates, removes duplicate leads and writes the tidy rows into a data store, so the Monday report is ready without any manual copying.

What are the main stages of a data pipeline?

Most pipelines have three core stages: ingestion (collecting raw data from sources), transformation (cleaning and reshaping the data), and storage (loading the cleaned data into a warehouse or database). The transformation stage is where most of the effort goes because real-world data is rarely tidy.

Do I need a data engineer to build one?

Not necessarily for a small business. Managed connector services like Fivetran provide pre-built connectors that avoid any coding, and a simple pipeline can be a handful of scripts. You will still need someone to own the pipeline after it’s built and to update it when sources change.

How do I deal with messy data before reporting on it?

The cleaning step of the pipeline standardises formats, removes duplicates and resolves naming inconsistencies. A best practice is to land the raw data first and then apply transformation rules, so you can re-run history if a rule changes.

Is a data pipeline the same as a data warehouse?

No. A data pipeline moves and cleans data; a data warehouse stores the cleaned data and serves it to dashboards and analytics. The pipeline feeds the warehouse, which acts as the tank that holds the final, trusted numbers.

Sources

  1. What is a data pipeline? | IBM, IBM (accessed 24 Oct 2026)
  2. Types of data pipelines and the benefits of using them, Fivetran (accessed 24 Oct 2026)
  3. What is a data pipeline?, Register Dynamics (accessed 24 Oct 2026)
  4. BigQuery pricing, Google Cloud (accessed 24 Oct 2026)

Let's talk

Not sure where to start?

Book a free 20-minute call. We'll look at your setup and tell you exactly what's worth doing first.

Book a free 20-min call

No commitment · No sales pressure · Just clarity