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 pipeline | Data warehouse | Manual spreadsheet export | |
|---|---|---|---|---|
| What it is | Any automated route that moves and cleans data | One type of pipeline with a fixed extract, transform, load order | The store the clean data lands in | A person copying rows by hand, once |
| Who runs it | A schedule or an event trigger | A schedule, typically nightly | Nothing; it just holds the data | You, or whoever is on duty |
| When it runs | As often as you set | On a fixed interval | N/A | Whenever someone remembers |
| What breaks it | A source changes its fields or login | Same, plus transformation logic drifts | Nothing, until storage fills up | The 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.
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 studyWhat 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?
Can you give me an example of a data pipeline?
What are the main stages of a data pipeline?
Do I need a data engineer to build one?
How do I deal with messy data before reporting on it?
Is a data pipeline the same as a data warehouse?
Sources
- What is a data pipeline? | IBM, IBM (accessed 24 Oct 2026)
- Types of data pipelines and the benefits of using them, Fivetran (accessed 24 Oct 2026)
- What is a data pipeline?, Register Dynamics (accessed 24 Oct 2026)
- BigQuery pricing, Google Cloud (accessed 24 Oct 2026)