What is data engineering, in plain terms?
Data engineering is building and maintaining the pipes your business data flows through. Your accounting, CRM, web shop and time tracking each keep their own data. Data engineering makes sure that data is fetched at fixed times, cleaned, matched up and ready for whoever needs it: a dashboard, an automation or a colleague.
Think of it as the plumbing in a building. Nobody visits for the pipes, but without them the tap does not work. A dashboard is the tap. Data engineering is what makes sure clean water comes out, every time.
Many SMEs already do a form of data engineering, just by hand. Someone exports a list from accounting every Monday, pastes it next to a CRM export and uses VLOOKUP to match customer numbers. That is exactly the work data engineering automates.
What is the difference between data engineering and Power BI?
Power BI is the report: the charts, tables and filters you look at. Data engineering is everything underneath. Power BI can fetch and transform data itself, and for a simple report on one source that is fine.
It gets harder once you combine several sources, want to keep history or use the same figures in several reports. The logic then ends up scattered across separate reports, and sooner or later you get two versions of the same revenue. With data engineering you take that logic out of the report and put it in one place underneath. The report gets simpler and the numbers match everywhere.
When that step is worth it is covered in more detail in our article data warehouse for SMEs: when you need one and when not.
What does data engineering consist of for an SME?
For an SME, data engineering comes down to four parts. You need all four, but none of them has to be big.
- Connecting sources. Connections that fetch data from your software, usually through an API, sometimes from a database or a file. Questions here include: how often do you fetch, what happens if a system is briefly unavailable, and what if the vendor changes the API?
- Storing and organising. A central database where the fetched data lands. This is where you decide what tables are called, which keys tie customers and products together and which history you keep.
- Definitions and transformations. The agreements on what a number means. Is revenue including or excluding credit notes? When does a customer count as active? You write those rules once, in the data layer, instead of again in every report.
- Quality and monitoring. Checks that flag missing, duplicate or oddly deviating data, and an alert to someone who fixes it. Without this part, you only find out a connection has stopped when someone in a meeting says the numbers are wrong.
Together these parts make up what we call a data foundation. How that fits together is explained in what is a data foundation.
What does an SME really need, and what not?
Search for data engineering and you quickly run into terms like data lake, streaming, Spark and lakehouse. Those are meant for organisations with huge volumes of data or data arriving every second. For most SMEs that is not the situation.
What you usually do need:
- Connections to the three to five systems that hold your key figures.
- One central database of modest size. A standard SQL database is more than enough for the data of an average SME.
- A refresh rhythm that fits the question. For most reporting, once a night or a few times a day is plenty.
- A short list of agreed definitions for your core concepts.
- Monitoring with alerts, and an agreement on who responds.
- Documentation, so you do not depend on one person who knows how it works.
What you usually do not need:
- An in-house data team on the payroll.
- Real-time data for reports you look at once a week.
- Every table from every system "just in case".
- A platform choice before you know which question you want answered.
The rule of thumb: start with the question, not the technology. The technology you choose should fit how many sources you have, how often it needs refreshing and who will maintain it.
An example: data engineering at a 25-person wholesaler
A fictional but recognisable example. A wholesaler with around 25 employees sells through a web shop and through field sales. Revenue sits in the accounting system, web shop orders in the shop platform and customer appointments in the CRM. Every Monday the managing director wants to know which customers are ordering less than last year, so field sales can call them.
Right now that costs the office manager two hours of Excel work every Monday morning. And when she is on holiday, it does not happen.
This is how you tackle it with SME-sized data engineering:
- Sharpen the question. "Which customers have ordered less in the past twelve weeks than in the same period last year?" That tells you which data you need: orders, invoice lines and customers.
- Build three connections. Every night, customers, invoices and orders are fetched from accounting, web shop and CRM and loaded into the central database.
- Match customers. One mapping table records which web shop account and which CRM company belong to which accounting customer. This is often where most of the work is, because each system names a customer slightly differently.
- Agree definitions. Revenue excludes VAT and includes credit notes. A customer counts as "declining" at more than 20 percent less revenue than last year. Those rules live once, in the database.
- Set up monitoring. If no new orders arrive overnight or a connection fails, the administrator gets an email.
- Put the report on top. Only now does a Power BI report appear, listing declining customers sorted by revenue difference.
The result is not a big data platform. It is a handful of connections, a small database and one report. But it runs every week by itself, the numbers match the accounts and the next question (margin per customer, for example) builds on the same foundation. How to plan such a first project in four weeks is described in from Excel to a data foundation in 30 days.
Should you do data engineering in-house or outsource it?
That mainly depends on who maintains it afterwards. Building is one thing; connections keep needing attention, because systems get updates, fields change and new questions come up.
- In-house works if you have someone with SQL and API knowledge who gets structural time for it. The risk is that everything lives in one head.
- Outsourcing makes sense if that knowledge is not there, or if you want the first foundation set up quickly and properly. Make sure you get documentation and that the setup allows someone else to take over.
- A mix is common: an external party builds the foundation and the tricky connections, while an in-house employee manages the reports and small changes.
Whichever route you choose: agree up front who gets alerted when something goes wrong. That is the part most often forgotten.
How do you get started with data engineering?
Write down the one or two questions that currently cause the most manual work or debate. Add which systems the data comes from, how often you need the answer and whether you want to look back at history. With that list it quickly becomes clear how big the first step should be.
At Celsius BI we build exactly this foundation under data engineering: connections, the central database, definitions and monitoring. The systems we can already connect are listed under integrations. The reports on top fall under dashboarding, and how a project runs in phases is described in our approach.