Automate · Pillar 03
Data pipelines, so every report starts from the same numbers.
Three departments produce three figures for last month’s revenue and all three are defensible. That is not a reporting problem to be argued about in a meeting; it is a plumbing problem with an engineering answer.
What is a data pipeline?
A data pipeline is an automated process that extracts data from business systems, cleans and reshapes it, and loads it into a central store for reporting and analysis. It runs on a schedule, validates what it moved, and records whether each run succeeded — which is what makes the resulting numbers trustworthy rather than merely available.
- Extracts from each system without disturbing its transactions.
- Applies one agreed definition of each metric, in one place.
- Reconciles to source and alerts when a run fails or drifts.
Why it matters
Everyone has numbers; nobody has the numbers.
The classic symptom is a meeting that becomes a discussion about whose figure is right. Sales reports from the CRM, finance from the ledger, operations from the ERP, and each uses a slightly different definition of a sale, a slightly different date, and a slightly different treatment of credits.
Nobody is wrong, which is why the argument never resolves. The fix is not another dashboard — it is agreeing each definition once, implementing it in one place, and having every report read from there. The modelling layer is the actual deliverable; the pipeline is how it gets fed.
What a pipeline settles
Three versions of the truth
These are definitions, not calculations, and they have to be agreed by people before they are implemented once in code:
- Two departments report different revenue for the same month.
- A key report is built by exporting to Excel and using lookups.
- Nobody is certain whether last night’s data load actually ran.
- Historic data is lost when a system archives or is replaced.
- Analysis queries slow down the live operational system.
- “Active customer” means three different things in three reports.
Scope
What data engineering covers.
Extraction is routine. The definitions layer and the reconciliation are where the value and the difficulty both sit.
-
Extraction from source systems
ERP, CRM, e-commerce, finance, WMS and spreadsheets, pulled without competing with transactions for resources.
-
Cleaning & conforming
Deduplication, consistent customer and product identity across systems, standardised dates and currencies, and explicit rules for the records that do not fit.
-
Warehouse & modelling
A dimensional model with one agreed definition of every metric, so a sale means the same thing in every report that uses it.
-
Orchestration & scheduling
Dependencies handled, retries built in, and a clear record of what ran, when, and whether it succeeded.
-
Testing & reconciliation
Automated checks that totals match source, that row counts are plausible and that nothing arrived twice — run every time, not at go-live only.
-
Lineage & documentation
Where every field came from and how it was transformed, so a number in a board pack can be traced back to its source record.
Deliverables
What the pipeline gives you.
The real deliverable is agreement. The technology is just how the agreement gets enforced.
-
One definition of each metric
Revenue, active customer, on-time delivery and margin, defined once and implemented once. Meetings stop being about whose number is right.
-
History that survives your systems
Data retained in the warehouse beyond what the source system keeps, so replacing an application or passing an archive threshold no longer loses your trend data.
-
Reporting that does not slow operations
Analysis runs against the warehouse, so a heavy month-end query cannot compete with despatch or order entry.
-
Trust, because it is checked
Every run reconciled to source and alerting on failure. The quiet confidence that yesterday’s data is complete is what makes people actually use the reports.
Is this the right answer?
When data pipelines is worth doing — and when it is not.
We would rather lose a project at this stage than six weeks in. If the right-hand column describes you, say so and we will tell you what we would do instead.
Worth doing when
- Two departments report different figures for the same month.
- A key report is assembled in Excel with lookups every month.
- Analysis queries are slowing down the live operational system.
- History disappears when a source system archives or is replaced.
Probably not when
- You have one system and one definition and the reporting in it is fine.
- Nobody will agree the definitions, in which case the warehouse inherits the argument.
- The source data is so poor that nothing downstream can be trusted yet.
- You want a dashboard this month and the modelling work is the actual job.
How we deliver
How a pipeline gets built.
Definitions before engineering. Building the pipeline first and arguing about definitions afterwards guarantees a rebuild.
-
Phase one
Agree the definitions
A workshop that produces written definitions of the fifteen or twenty metrics that matter. Usually uncomfortable, invariably the most valuable phase.
-
Phase two
One domain, end to end
Sales, or stock, taken all the way from extraction to a working report, with reconciliation proving it matches source.
-
Phase three
Extend the model
Further domains onto the same conformed dimensions, so customer and product mean the same thing across every subject area.
-
Phase four
Harden & hand over
Monitoring, alerting, documented lineage and a runbook, so your own team or another supplier can operate and extend it.
What you receive
The things that actually land.
Artefacts, not adjectives. Everything below is listed in the scope document before a phase starts, so “done” is a defined state rather than an opinion.
-
Written metric definitions
What revenue, margin, active customer and on-time delivery actually mean here, agreed by the people who disagree about them. The hardest and most valuable part.
-
A modelled warehouse
Dimensional model with conformed customer and product identity, so a sale means the same thing in every report that uses it.
-
Orchestrated pipelines
Scheduled, dependency-aware, retrying, with a clear record of what ran, when and whether it succeeded.
-
Automated reconciliation
Totals and record counts checked against source every run, with alerts when something fails or looks implausible.
-
Documented lineage
Where every field came from and how it was transformed, so a number in a board pack traces back to a source record.
-
History you keep
Retained beyond what the source systems hold, so replacing an application later does not cost you your trend data.
Golden Triangle
Data in a multi-system operation.
Businesses in this corridor usually run more systems than they realise, and the joins between them are where reporting breaks.
- Many systems, one business ERP plus six A typical Triangle distributor runs an ERP, a WMS, a CRM, a webshop, carrier portals and a payroll system. Conforming customer and product identity across those is the core of the work.
- Operational metrics OTIF and cost to serve On-time-in-full, cost per drop and stock turn are the numbers that run a distribution business, and they each require data from two or three systems to calculate honestly.
- Seasonality Peak comparison Retail-facing distribution here lives or dies on peak. Comparing this peak with the last three requires history the source systems have usually already archived away.
We build pipelines and reporting warehouses for distribution, manufacturing and multi-site service businesses across Birmingham, Nottingham, Leicester, Northampton, Derby and Coventry.
Technology
Data technology.
Sized to the business. A mid-market company does not need a platform built for a bank, and overbuilding here is a common and expensive mistake.
- PostgreSQL
- SQL Server
- Azure Synapse
- BigQuery
- Snowflake
- dbt
- Airflow / Dagster
- Change data capture
- Data tests
- Lineage documentation
Where we deliver this
Data Pipelines across the Golden Triangle.
35 locations, each with a page written for it — the sectors it is actually built on, and what that means for this work. See all areas we serve.
Northamptonshire 10
Warwickshire 5
West Midlands 5
Buckinghamshire 4
Leicestershire 4
Staffordshire 4
Derbyshire 1
Greater London 1
Nottinghamshire 1
Questions
Data pipelines, answered plainly.
Asked after the third dashboard failed to settle the argument.
Why do our reports disagree with each other?
Almost always because each report applies a different definition, not because one system is wrong. Sales may count an order when it is placed, finance when it is invoiced, operations when it is despatched, and each may treat credits and intercompany differently. Until those definitions are agreed and implemented in one place, every new dashboard adds a fourth answer.
Do we need a data warehouse, or will Power BI do?
Power BI is the presentation layer, not the foundation. It can connect directly to source systems for small cases, but as soon as you need data from several systems, history beyond what they retain, or one agreed definition of a metric, you need a modelled store underneath. Without it you end up with the same logic duplicated and quietly diverging in a dozen reports.
How much does a data pipeline project cost?
A first domain — one subject area, modelled, reconciled and reporting — is typically a low five-figure project, with further domains cheaper because they reuse the dimensions and infrastructure. The definitions workshop that comes first is small, and it is the part that prevents the expensive rebuild.
Will extracting data slow down our live systems?
Not if it is done properly. We extract from replicas or during defined windows, use incremental loads and change data capture rather than full table reads, and agree the schedule with whoever owns the operation. Analysis then runs against the warehouse, so a heavy query cannot affect order entry or despatch.
How do we know the data is right?
By reconciling it, automatically, every run. The pipeline compares totals and record counts against source, checks for duplicates and nulls where they should not exist, and alerts when anything fails or looks implausible. Lineage documentation then lets you trace any number in a report back to the source record behind it.
Can we keep history after replacing a source system?
Yes, and it is one of the strongest reasons to build the warehouse before you replace anything. Once history is in a store you control, migrating or retiring a source system no longer costs you your trend data — which is otherwise one of the hidden losses in every system replacement.
How often should the data refresh?
As often as the decision it supports requires, and no more. Operational dashboards used to reallocate labour need to be near real time; a monthly margin review does not. Refreshing more frequently than the decision cycle adds cost and load for no benefit, so we set it per pipeline rather than globally.
What if two systems have different customer records?
That is the conforming problem and it is most of the work. We agree matching rules — usually company number, domain and normalised name — and build a mapping that survives both systems continuing to disagree, because they will. Pretending the mismatch does not exist is what makes cross-system reporting untrustworthy.
Can we keep using Excel?
Yes, and many people should. The point is not to remove Excel but to make it read from one governed source instead of from six exports with the logic re-implemented in each. Analysts keep the tool they are fast in, and the numbers finally agree.
What happens if a pipeline fails overnight?
It alerts a person, retries where that is safe, and the reconciliation report shows exactly what did not arrive. The failure mode we design against is the silent one: a pipeline that stopped three days ago and reports that look plausible but are quietly incomplete.
Related services
Built with this.
-
Automate
Dashboards & Reporting
Reporting that answers a decision, not dashboards nobody opens twice.
Explore -
Connect
ERP Integration
ERP connected to everything around it, without touching the core.
Explore -
Grow
Conversion & Analytics
Measuring what happens after the click, then fixing where it leaks.
Explore
Next step
Start by agreeing twenty definitions.
A single workshop that writes down what revenue, margin, active customer and on-time delivery actually mean in your business. It is the cheapest and most valuable part of any reporting project.