What is a data pipeline, in plain terms?

Imagine someone walking past all your systems at six every morning: accounting, CRM, web shop, time tracking. They collect what has changed since yesterday, put everything in the same format, match customer numbers and file it neatly in one cabinet. If something is missing, they call you. That is a data pipeline, only as software.

Many SMEs already have such a route, just done by hand. Every Monday an export from accounting, an export from the CRM, pasted into Excel and matched up with VLOOKUP. That works, until the person who does it is on holiday or a column is named differently from last week.

A data pipeline does the same work, but at a fixed time, in the same way and with an alert when something goes wrong.

What does ETL mean, and how is it different from ELT?

ETL describes the three steps almost every data pipeline goes through:

  1. Extract. Getting data out of the source, usually through an API, sometimes straight from a database or from a file left somewhere.
  2. Transform. Making the data usable. Putting dates in the same time zone, treating VAT consistently, removing duplicate records, matching customers from different systems.
  3. Load. Storing the result where reports and automations pick it up, usually a central database.

With ELT you swap the last two steps. You first store the raw data unchanged in your database and transform it there afterwards. That has a practical advantage: you always keep the original data. If you change a definition later, you recalculate everything without querying the source again.

For an SME the difference is mainly a design choice, not a matter of principle. We usually go for a mix: store raw, and keep the transformations in one fixed place in the database, so they are not repeated in every report.

When does an SME need a data pipeline?

Not always. If you report from one system and the numbers are right, a direct connection between that system and Power BI is often enough. A pipeline becomes worth it as soon as one of these situations applies:

  • You combine sources. Revenue from accounting next to deals from the CRM, or hours next to invoices. Then there has to be a place where that data lines up.
  • You want history. Many systems mainly show today's position. If you want to see how your pipeline or stock develops, you have to record those snapshots yourself.
  • The same figures appear in several reports. Without a pipeline, the calculation logic sits in each report separately, and sooner or later you get two versions of the same revenue.
  • Your source is slow or limited. Some APIs limit the number of requests per minute. A pipeline fetches the data once, and reports then read from your own database.

Not sure whether you need a full data warehouse straight away? Read data warehouse for SMEs: when you need one and when not first.

What does a data pipeline consist of?

An SME-sized pipeline has five parts. None of them needs to be big.

  1. Source connections that fetch the data per system, with the right permissions and with tokens that are refreshed in time.
  2. A schedule that decides when the pipeline runs. For most reporting, once a night or a few times a day is plenty.
  3. A central database where the data lands, ideally with a clear split between raw and transformed tables.
  4. Transformations where you record your definitions: what counts as revenue, when a customer is active, which stages belong together.
  5. Monitoring: checks that flag when fewer records arrive than usual, and an alert to someone who fixes it.

That last part is the one most often forgotten. A pipeline that is down for a week without anyone noticing leaves a gap in your history that you can never fill.

Which tools do you use to build a data pipeline without a large IT team?

There is no best tool, only a fitting one. Roughly there are four routes, and they can be combined.

  • An automation platform such as Make.com. Suitable for simple pipelines with a few sources and modest record counts. You get scheduling, HTTP modules for APIs and error handling in one environment. Less suitable when you process large volumes of data. How to make such a scenario robust is covered in Make.com error handling.
  • The Microsoft platform. If you already use Power BI, Power BI dataflows, Data Factory in Microsoft Fabric or Azure Data Factory are obvious options. Powerful and well integrated, but they take more knowledge to set up properly.
  • Ready-made connectors. For some systems there are connectors that deliver the data ready to use. That saves building work, but you depend on what the connector supports.
  • Your own scripts. A Python or SQL script on a scheduled task gives the most freedom. You are then responsible for maintenance, documentation and monitoring yourself.

Our rule of thumb: choose the simplest route that answers your question and that someone else can still understand a year from now. A clever pipeline that only its builder understands is a risk.

Step by step: your first data pipeline in six steps

  1. Start with one question. Not "we want all our data in one place", but for example "which customers are ordering less than last year?" That question decides which sources and fields you need.
  2. Map the sources. Per system: is there an API, which permissions do you need, is there a limit on the number of requests, and in which time zone are dates returned?
  3. Fetch only what has changed. Many APIs let you filter on the date of the last change. You fetch everything the first time and only the differences after that. Faster, and it stays within the limits.
  4. Store raw first, transform afterwards. Put the fetched data unchanged into your database and record the transformations in one place after that. That way you can always go back to the source.
  5. Build monitoring in from the start. An alert on a failed run, and a check on counts: if zero orders suddenly arrive today, something is probably wrong.
  6. Check against the source and document. Do the totals match what you see in the system? Write down briefly what the pipeline does, when it runs and who gets alerted.

How to spread such a first project over four weeks is worked out in from Excel to a data foundation in 30 days.

An example: a pipeline from CRM and accounting

A fictional but recognisable example. A consultancy of fifteen people uses a CRM for deals and an accounting package for invoices. Management wants to see every month how many of the won deals have actually been invoiced, and how long that takes on average.

The pipeline:

  1. Extract. Every night a scheduled scenario fetches from the CRM the deals and customers changed since the previous run. From accounting come the invoices and debtors of the past few days.
  2. Load. Everything lands raw in a small database, in separate tables per source.
  3. Transform. A mapping table records which CRM customer belongs to which debtor number. Dates are converted to Dutch time. Revenue is calculated excluding VAT everywhere.
  4. Monitoring. If a run fails, or zero records arrive from either source on a working day, the administrator gets an email.
  5. Report. In Power BI there is one page with won deals, invoiced revenue and the lead time in between.

If that consultancy uses Cirqll as its CRM, Cirqll data in your own database explains how to build up the history of your pipeline as well.

Where does a data pipeline go wrong?

  • Wanting everything at once. Fetching every table from every system "just in case" makes the pipeline slow and hard to follow. Start small.
  • No agreement on definitions. If nobody has recorded what revenue is, the pipeline neatly builds the wrong number.
  • Missing deleted records. If you only fetch changed records, you will not see that something was deleted at the source. Schedule a full check now and then, or use webhooks if the source offers them.
  • Time zones. Many APIs return times in UTC. An order placed at 00:30 Dutch time then falls on the previous day. Convert it once, in the pipeline.
  • Knowledge in one head. Without documentation, the pipeline is only as strong as the person who built it.

Help with your data pipeline

Building connections, the central database, the transformations and the monitoring is exactly what we mean by data engineering. The systems we can already connect are listed under integrations. If it is mainly about passing data automatically between systems, see workflow automation, and for the reports on top, dashboarding. Want to see the bigger picture first? Read data engineering for SMEs.