Data pipelines: controlling your data in one place rather than multiplying interconnections

A different turnover in the sales dashboard and in the accounting file, a customer duplicated across three tools, an update that forgets one of the systems: these symptoms often share one cause. The data lives in several places, linked by a web of point-to-point connections that each person added over time. This article defends a simple idea: control your data in a single place, prepare it, then publish it everywhere, rather than multiplying interconnections.

The plate of spaghetti

Every time one piece of software must exchange with another, a link is created. With four tools, that is up to six links; with ten, up to forty-five. The number of links grows much faster than the number of tools. This is the “plate of spaghetti”: nobody knows any more which flow feeds what.

• Cost: each link has to be developed, tested and maintained. Changing one tool means redoing all its links.
• Fragility: an update at a vendor breaks a flow, and the failure spreads without being seen.
• Diverging figures: each link transforms the data in its own way (rounding, calculation rules, filters), so two tools show two truths.
• Security: access credentials scattered about, data copies everywhere, hard to inventory and erase.

The principle: one place, one truth, several publications

The alternative is to make all sources converge on a single system, which becomes the source of truth, then publish from it to the consumers. Instead of ten tools talking to each other, each talks to one central point: a data warehouse or data platform or, for a more modest need, the data model of a business intelligence (BI) product.

The outline is as follows:

1. Sources (management software, shop, spreadsheets, APIs);
2. Ingestion into a central storage;
3. Transformation and modelling, in successive layers;
4. Quality control, with explicit rules;
5. Publication to the consumers: dashboards, APIs, exports, applications.

Each calculation rule is written only once, in one place. When it changes, every consumer benefits.

Ingestion: bringing the data in

There are two main ways to load data: in batches (an export every night, every hour) or continuously (each event is sent as soon as it occurs). Batches are enough for most management needs; continuous loading is justified when freshness matters. In both cases I keep the data raw, as it arrived, before any change: it serves as evidence and allows everything to be recalculated.

ETL or ELT?

In the ETL approach you extract, transform, then load: the transformation happens before the data reaches the central system. In the ELT approach you extract, load the raw data, then transform it inside the central system, which has the power to do so. ELT is widespread today because it keeps the original data and lets you redo a transformation without rerunning the extraction. ETL remains relevant when you must filter or anonymise before storing.

The bronze, silver and gold layers

The medallion architecture organises data in three layers:

Layer	Content	Use
Bronze	Raw data, kept as received, with its arrival date	Traceability, recovery after an error
Silver	Cleaned data: types fixed, duplicates removed, formats harmonised, reference data matched	Reliable base for analysis
Gold	Ready-to-use data, modelled by business subject (sales, customers, stock) with calculated indicators	Dashboards, APIs, exports

Each layer answers a different question. Bronze says “what arrived?”, silver “what is correct?”, gold “what is useful for deciding?”.

Modelling

Modelling means deciding how data is organised to answer the business’s questions. For analysis, a common organisation separates facts (what happens: a sale, an order, a visit) from dimensions (what describes them: customer, product, date, place). It makes questions natural: “sales by product and by month”. The important point is to define each indicator once: what is an active customer? a net turnover? See also choosing indicators that help you decide.

Quality and data contracts

Centralised but wrong data is worse than scattered data, because it inspires trust. So I plan checks at each layer transition:

• completeness: mandatory fields are filled in;
• uniqueness: no duplicates on the identifier;
• consistency: amounts and dates stay within plausible bounds;
• freshness: the last update is recent enough;
• reconciliation: a layer’s total matches the previous one’s.

A data contract formalises the agreement between whoever produces a piece of data and those who consume it: fields, types, meaning, update frequency, and what happens when the format changes. It prevents a change at the source from silently breaking dashboards.

Governance

• An owner per dataset: someone decides its definition and answers for its quality.
• A catalogue: what exists, where it comes from, what it means.
• Access rights limited to what is necessary, and logged.
• Personal data identified: where it is, why it is kept, when it is deleted (GDPR).
• Traceability: for a displayed figure, finding the source data that produced it.

Publishing to the consumers

Once the data is ready, it is distributed without being copied by hand:

• dashboards (BI): they read the gold layer, without recalculating their own version;
• APIs: an application, a site or a partner retrieves the data through a controlled interface;
• scheduled exports: a clean, identical file sent to whoever needs it;
• applications: an internal tool fed by the same base.

The gain is that everyone shows the same figures, calculated the same way. For the consumption side, see from spreadsheet to dashboard.

Concrete examples

• Online and in-store sales: two sources in different formats, matched in the silver layer by a common product reference, then a turnover indicator defined once in the gold layer.
• Excel files from several departments: instead of exchanging files by email, each department drops them, they are archived in bronze, cleaned in silver and consolidated in gold.
• A customer present in several tools: a single identifier is established in silver, and all tools read the same record.

And integration tools?

Removing every interconnection is neither realistic nor necessary. Source systems do have to be plugged into the central platform, and sometimes data must be sent back to a tool that offers no other way. This is the role of an integration tool (middleware): receive, transform, route. The difference is that these links become inputs and outputs of a central point, not a mesh between all the tools. This is what I explored in La Passerelle, one of my personal projects. For the basics, see middleware: making data exchanges compatible.

Where to start

1. List the sources and consumers, and draw the existing links.
2. Choose one subject (for example sales) and define its indicators once.
3. Keep the raw data before any transformation.
4. Set up a few simple quality checks.
5. Plug a single dashboard into the gold layer first, then extend.

Common mistakes

• Centralising everything at once: choose one subject, get it right, then extend.
• Transforming without keeping the raw data: it becomes impossible to redo a calculation or prove a figure.
• Letting each tool recalculate its own indicators: you are back to diverging figures.
• Forgetting the data owner: without a person in charge, definitions drift.

Tools: a matter of size, not of fashion

The tooling can be modest. For a small business, a well-designed data model in a BI product, fed by scheduled imports, may be enough. A warehouse or a data platform becomes worthwhile when volumes, the number of sources or the number of consumers grow. What matters first is the discipline: raw data kept, rules written once, quality checked.

In short

Single data, prepared in layers, checked and published from one place, is cheaper to evolve, breaks less often and gives everyone the same figures. You do not need a large platform to start: one subject, one source of truth and written rules are enough.