How to Create a Single Source of Truth: A Full Guide
Two people open two spreadsheets and report two different revenue figures for the same month. Both are careful. Both can defend their number. Neither is wrong.
That is not a data problem. It is a definition problem wearing a data problem's clothes.
Most attempts to fix it start with the wrong move, which is picking a tool. The fix starts with writing down what each metric means and who decides.
This guide covers what the term really means and the four decisions that come before any spreadsheet work. It then walks the three manual routes and where each one stops holding.
What a single source of truth actually is
Workday's guide to SSOT defines it as "a centralized system where all critical, up-to-date business data is aggregated, cleaned, and made universally accessible."
The sentence that matters more comes later on the same page. A single source of truth "is often the outcome of using technical infrastructure correctly, not the infrastructure itself."
Read that twice before you buy anything. A warehouse gives you storage. Agreement gives you truth.
The same page names data silos as the root cause of conflicting numbers. It describes them as "isolated pools of information trapped in disconnected tools, spreadsheets, and departmental servers." Its recommended order starts with governance rather than platform: define terms, appoint owners, then choose technology, then clean, then roll out in phases.
Why two teams get two numbers
Almost always the cause is one of four things, and none of them is arithmetic.
Different filters. One report excludes internal accounts and test records. The other includes them. Nobody wrote that down, so nobody noticed.
Different time boundaries. One team cuts the month at the invoice date, the other at the payment date. Both are defensible, and they will never agree.
Different denominators. A rate has two halves. Change the bottom half and the percentage moves without a single record changing.
Different vintages. One export ran on the 1st, the other on the 3rd, and three days of late data landed in between.
The four decisions to settle first
Write these down before touching a formula. A definitions tab beside your numbers is the cheapest governance you will ever implement.
| Decision | The question to answer | Why it matters |
|---|---|---|
| Definition | What exactly counts as one unit of this metric? | Decides who is in and out of scope |
| Filters | Which records are excluded, and why? | Usually the single biggest source of gaps |
| Boundary | Which date field cuts the period? | Two valid choices, two different answers |
| Owner | Who approves a change to this definition? | Without a name, definitions drift silently |
The owner row is the one teams skip. A definition with no owner is a suggestion, and suggestions get re-interpreted every quarter.
Pair this with the metric layer you already report on. Our guide to leading and lagging indicators covers which metrics deserve this treatment, because not all of them do.
How to do it manually
Option 1: Reconcile two versions line by line
Start with the disagreement rather than the theory. Pull both reports into one workbook and match records with XLOOKUP on a shared key.
Rows that fail to match are your finding. They tell you what one side includes and the other does not, which is exactly the filter nobody documented.
Watch for near-duplicate labels while you do it. EXACT compares two text values and is case-sensitive. It catches the "ACME Corp" versus "Acme Corp." pairs that a human eye slides straight past.
The ceiling is that this fixes one month. Next month the same two reports diverge again, because nothing structural changed.
Option 2: Build one dataset, then derive every report from it
Consolidate first, report second. Power Query's merge supports several join kinds including anti joins. An anti join lists records present in one source and absent from the other.
Then produce every downstream figure from that one table. If a number cannot be traced back to it, that number does not get presented.
Use UNIQUE to check the key you joined on is actually unique. A key that repeats will inflate totals quietly, and totals that are quietly wrong are worse than totals that break.
The limit is maintenance. Somebody has to refresh it, and that somebody becomes a dependency.
Option 3: Keep a definitions tab next to the numbers
One row per metric: name, definition, filters applied, date field used, owner, last reviewed.
This is what makes a report comparable next quarter. It is also what lets you answer "why is this different from last time" in ten seconds instead of a day.
The limitation is that writing a definition does not enforce it. The tab and the formulas can drift apart, and usually do.
The shared ceiling. All three assume the disagreement is discoverable from the files. When two departments use different systems entirely, reconciliation starts with getting comparable exports at all.
Where the manual route slows down
The first reconciliation is interesting. By the third, it is the same afternoon repeated with new dates.
Definitions decay whenever the business changes. A new plan type, a new region, a renamed field, and the rule you wrote in March no longer covers the data in September.
Exports drift too. Someone changes a filter in the source system, and the file that arrives looks identical while meaning something different.
Then there is the cost that only shows up in a meeting. When two figures collide live, you need to explain the gap immediately, and the explanation lives in a workbook you did not bring.
This is why the category exists at all. The roundup of AI tools for business intelligence without a data team covers the tooling side of this problem.
How to build it with Powerdrill Bloom
Step 1: Upload both conflicting exports
Upload the two files that disagree, together. Powerdrill Bloom profiles the columns on arrival, so mismatched key formats, inconsistent labels, and blank fields surface before any total is computed.
Step 2: State the definition in natural language
Describe the rule rather than building it. Name the metric, the records to exclude, the date field that cuts the period, and the grouping.
Then ask the questions that expose the gap. Ask which records appear in one file and not the other. Ask which labels differ only by case or punctuation. Then ask what the total becomes under each date boundary.
Step 3: Export the reconciled report
Take out the reconciled table, a variance summary, or slides that carry the agreed definition alongside the number.
Why this beats reconciling by hand
| Manual route | Powerdrill Bloom | |
|---|---|---|
| Finding unmatched records | Anti join per pair of files | Ask which records are missing |
| Catching near-duplicate labels | Case-sensitive helper columns | Surfaces on upload |
| Testing an alternative boundary | Rebuild the filter and re-total | State the other rule and ask |
| Re-running next month | Refresh and hope nothing moved | Swap the file, keep the rules |
The third row is the one that ends arguments. Being able to show both date boundaries side by side turns "your number is wrong" into "our rule differs," which is a solvable conversation.
Common mistakes
Buying a platform before agreeing on definitions. Storage is not agreement. Define terms and appoint owners first, in that order.
Leaving the denominator unstated. Every rate needs both halves written on the chart. Without them the percentage is decoration.
Treating one big consolidation as the finish line. Definitions decay with product and org changes, so schedule a review rather than a rebuild.
Assuming identical labels mean identical records. Case, punctuation, and trailing spaces all create silent duplicates. Check before joining, not after.
Reconciling exports taken on different days. Late-arriving data will look like a discrepancy. Match the extraction timestamp before you match the rows.
Documenting definitions somewhere nobody opens. Keep them in the same file as the numbers, and label the chart with the rule it used.
Skipping the owner. A definition without a named approver is a definition that will be reinterpreted. Put a person on every row.
Conclusion
A single source of truth is agreement first and infrastructure second. Define each metric, write down the filters, fix the date boundary, name an owner, then derive every report from one dataset.
The expensive part is not the first reconciliation. It is keeping the definitions alive while the business changes underneath them.
If that cycle is eating your month-end, try Powerdrill Bloom on the two files that disagree. See also our guides to building a KPI dashboard from a spreadsheet and setting KPI targets from your own data. The AI data cleaning page covers the preparation step.
Frequently asked questions
What is a single source of truth?
It is one agreed set of definitions, sources, and calculations that every report derives from. Workday's guide describes it as the outcome of using infrastructure correctly rather than the infrastructure itself.
Do I need a data warehouse to have one?
No. A warehouse provides storage and scale, while the agreement on definitions and ownership is what makes numbers match. Small teams achieve it in a single well-governed workbook.
Why do two reports show different totals?
Usually filters, date boundaries, denominators, or export timing. Check those four before assuming a calculation error, because arithmetic is rarely the cause.
Who should own a metric definition?
One named person per metric, with authority to approve changes. Shared ownership tends to mean the definition quietly diverges across teams.
How often should definitions be reviewed?
Whenever the underlying business changes, and on a fixed cadence otherwise. New plans, regions, and renamed fields all invalidate rules that were correct when written.