Building a single source of truth by unifying your data into one BI layer

A single source of truth is one trusted place where data from all your systems is collected, cleaned and modelled so that every report uses the same numbers. In practice it means automated pipelines pulling from each source, a data warehouse with a clear model, a BI tool on top, and checks that tell you when something is wrong.

It is not a single giant database that replaces your ERP, shop and CRM. Those systems keep doing their jobs. The single source of truth sits beside them and answers one question reliably: what are the numbers, and can we trust them? This article walks through each layer, the decisions involved and how to roll it out in phases.

Signs you need one

Most companies arrive here the same way. Reports are built by exporting from several systems and combining them in spreadsheets. It works until it does not:

• Reports are late. By the time the weekly pack is assembled, the data is days old.
• Numbers disagree. Sales says one revenue figure, finance another, because each uses a different export and definition.
• Analysts spend their time on preparation, not analysis.
• Problems are found too late: a pricing error or stock issue is noticed weeks after it started.

A multi-channel retail group we worked with had sales, inventory, logistics and marketing data in six systems, including SAP, Shopify, Google Ads and three regional logistics providers. Leadership was deciding on pricing and stock using week-old reports assembled by hand. The case study describes the pipeline we built; the principles below come from that kind of work.

The architecture in five layers

1. Extraction: ETL or ELT

Data is pulled from each source through APIs, database connections or scheduled file exports.

• ETL (extract, transform, load) cleans and reshapes data before it lands in the warehouse.
• ELT (extract, load, transform) loads raw data first, then transforms it inside the warehouse.

ELT has become common with cloud warehouses because raw data is kept and transformations can be rerun when definitions change. ETL still makes sense when data must be filtered or anonymised before it is stored. In our experience the choice matters less than keeping raw extracts, so you can rebuild history when a definition changes.

2. Orchestration

Pipelines need to run in the right order, retry on failure and alert someone when they break. Apache Airflow is a widely used open-source platform for this. Workflows are written in Python as DAGs: sets of tasks with explicit dependencies, such as "load orders and products, then build the sales model, then refresh dashboards". Managed alternatives exist on the major cloud platforms; what matters is that scheduling, dependencies and failures are visible in one place rather than in scattered cron jobs.

3. The warehouse and its model

The warehouse (for example AWS Redshift, BigQuery, Snowflake or PostgreSQL at smaller scale) stores the cleaned data. The model is more important than the product.

For reporting, we usually recommend a star schema, the dimensional approach popularised by Ralph Kimball. It separates:

• Fact tables: measurable events at a defined grain, such as one row per order line or per shipment.
• Dimension tables: the descriptive context, such as product, customer, store, channel and date.

A simplified example:

fact_sales (date_key, product_key, store_key, channel_key, quantity, net_revenue)
dim_product (product_key, sku, name, category, supplier)
dim_store (store_key, name, region)
dim_date (date_key, date, week, month, season)

Star schemas are easy for BI tools to query and easy for business users to understand. The hardest part is agreeing definitions: what counts as revenue, when an order is "shipped", how returns are handled. Write those definitions down and implement them once in the model, not separately in every report.

4. The BI layer

Power BI, Tableau, Looker and similar tools connect to the warehouse and present dashboards. Because the logic lives in the model, dashboards stay thin: they filter and visualise, they do not redefine metrics. That is what keeps the numbers consistent across departments.

5. Data quality checks and anomaly alerts

A single source of truth is only trusted if it is checked automatically. Useful checks include:

• Freshness: did each source load today?
• Volume: is the row count within the normal range?
• Completeness: are required fields populated?
• Uniqueness: no duplicate orders or SKUs.
• Reconciliation: do warehouse totals match the source system for the same period?

On top of technical checks, business anomaly alerts watch the KPIs themselves. For the retail group, scripts compared each KPI with its 30-day rolling average and sent a Slack alert when it deviated by more than 15%. Within the first month, those alerts caught a logistics pricing error worth €28K.

Ownership and governance

Technology is the easier half. The single source of truth fails if nobody owns the definitions.

• Name a data owner per domain: sales, inventory, finance. They approve definitions and are contacted when checks fail.
• Keep a short metric glossary: each KPI with its definition, source and owner.
• Change definitions through review, the same way code changes are reviewed, so a dashboard does not silently change meaning.
• Control access by role, especially for margins, salaries or customer data.
• Retire old reports once the new ones are trusted, or people will keep using the spreadsheets.

Phased rollout

We recommend delivering value in weeks, not after a long build:

1. Discovery. List every source, current report and decision it supports. Pick the one or two reports leadership uses most.
2. First pipeline. Connect the sources those reports need, model them, and rebuild the reports in the BI tool.
3. Parallel run. Compare new reports with the old manual ones until differences are explained and resolved.
4. Add checks and alerts for the sources now in production.
5. Expand by domain. Add sources and subject areas one at a time, reusing the same dimensions so everything stays connected.
6. Hand over. Document pipelines, definitions and runbooks so your team can operate and extend the system.

For the retail group, this approach took 10 weeks. Reporting turnaround dropped from 5 days to same-day, the CEO dashboard went from a weekly manual effort to a 30-second automated run, and overstock incidents fell by 34% in the first quarter after launch.

Readiness checklist

You are ready to start if most of these are true:

• At least two or three systems feed the reports leadership relies on.
• Someone spends significant time each week assembling reports by hand.
• Teams disagree about key numbers.
• Your source systems offer APIs, database access or reliable scheduled exports.
• A business owner is willing to define and approve the core metrics.
• You know which decisions faster or more reliable data would improve.

If your reporting still lives in a few spreadsheets, start with signs your business has outgrown spreadsheets. If manual data entry between systems is the bigger problem, our guide to automation ROI helps you size it.

Frequently asked questions

Do we need to replace our existing systems?

No. Your ERP, shop, CRM and other systems stay as they are. The pipelines read from them and the warehouse combines the data for reporting.

How fresh will the data be?

That depends on what decisions need. Nightly loads are enough for most management reporting, and they are simpler and cheaper to run. Specific sources can be loaded more often where it matters, such as stock levels during peak season.

Which BI tool should we choose?

Choose the one that fits your existing stack and licensing, and that your team will actually use. If you already use Microsoft 365, Power BI is often the natural choice. Because metrics are defined in the warehouse, changing BI tool later is far less painful.

What does it cost to run?

The main running costs are warehouse compute and storage, BI licences, and the time to maintain pipelines when source systems change their APIs. Volume, refresh frequency and the number of sources drive all three, so estimate them for your expected growth.

If your team spends days assembling reports, see how our data and analytics service builds pipelines and BI layers, or read the full case study. Book a call to map your data sources with us, or request a quote.