Centralize Business Data
A founder once told me she wanted "one place where everything lives." Then she described her week. Sales numbers came from the CRM, revenue from the accounting tool, support volume from a help desk, and the real customer list from a spreadsheet her office manager kept because "the CRM is never quite right." Every Monday she opened four tabs and made peace with the fact that they disagreed.
Wanting one source of truth is reasonable. The phrase hides a trap, though. There is no single place that is right for everything, and a plan to move all your data into one big container is how companies spend a year and end up with a fifth tab. This article is for founders and owners who want to do it properly. We will separate three kinds of storage that people confuse, explain how data actually gets moved, cover modeling and governance in plain language, and say clearly when you should leave things where they are.
First, what do you mean by "truth"?
Truth has at least three meanings in a business, and they need different homes.
- Truth about a record. What is this customer's current email? Which address do we ship to? This is operational truth, and it must be accurate right now.
- Truth about the past. What did revenue look like last quarter by product? This is analytical truth, and it must be consistent and explainable.
- Truth about a definition. What counts as an active customer? Two reasonable people give two answers.
Most of the pain people call "bad data" is actually a disagreement about the third kind. No amount of storage fixes a definition that nobody wrote down. Keep this in mind, because it changes the order of work. Definitions first, plumbing second.
Operational store, warehouse, spreadsheet
Three tools do three jobs. Here is how they compare.
| Operational store | Data warehouse | Spreadsheet | |
|---|---|---|---|
| Typical examples | CRM, accounting, order system, a database behind an app | A separate analytics database | Excel or Google Sheets |
| Built for | Doing the work, one record at a time | Asking questions across lots of history | Quick, flexible calculation |
| Freshness | Live | Minutes to a day behind | As fresh as the last paste |
| Who edits | Staff, through the application | Pipelines, not people | Anyone with the file |
| Strength | Rules, permissions, consistency | History, joins across systems | Speed and familiarity |
| Weakness | Poor at cross-system questions | Not where work happens | Hidden errors, no audit trail |
An operational store, sometimes called a system of record, is where a fact is created and changed. Your accounting tool is the system of record for invoices. Your CRM is the system of record for deals. The principle is that each kind of fact has one owner system, and everything else holds a copy.
A warehouse is a copy of data from many systems, arranged for questions. It is read-heavy and you do not type into it. Its job is to let you ask "revenue by customer segment by month" without touching the live tools.
A spreadsheet is a superb thinking tool and a poor database. It is fine for analysis, and it becomes dangerous the moment other processes depend on it. I will say more about that near the end.
How data gets from the systems to one place
The usual pattern today is called ELT, which stands for extract, load, transform. You extract data from each source, load it as is into the warehouse, and then transform it there into clean shapes. The older pattern, ETL, transformed before loading. The difference sounds academic and matters in practice. Loading raw data first means you can always go back and rerun a transformation when you discover a mistake, because the original is still there.
Within extraction, there are a few mechanisms worth knowing.
- Full refresh. Copy the whole table every time. Simple, fine for small tables, slow and costly for large ones.
- Incremental load. Copy only rows changed since the last run, usually using an updated-at timestamp. Efficient, but it misses deletions and it trusts that timestamp.
- Change data capture. Read the source database's own log of changes, so inserts, updates, and deletes all arrive. Precise, and it needs more setup and access than a small team usually has.
- API pulls. Most SaaS tools expose their data through an API, a documented way for software to request records. You pull in pages, respect rate limits, and handle retries. Many small companies use a hosted connector service for this rather than writing code.
Whichever you choose, make each load idempotent. That means running it twice produces the same result as running it once. A load that duplicates rows when it retries will, sooner or later, double your revenue on a Monday.
Model the data in a way people can actually read
Modeling means deciding how raw tables become something a person can query. You do not need a doctoral thesis. Three ideas get you most of the way.
Choose a grain. The grain is what one row represents. One row per invoice? Per invoice line? Per customer per month? Mixing grains in one table is the most common cause of totals that are inexplicably too high. Write the grain at the top of every table as a sentence.
Separate facts from dimensions. A fact is an event or measurement, such as an invoice line or a support ticket. A dimension is a thing you describe the facts by, such as customer, product, or date. Keeping them apart lets you slice any fact by any dimension without rewriting everything.
Keep stable keys. Every customer needs an identifier that does not change. Names and emails change. If your CRM and your accounting tool each have their own customer ID, you need a mapping table that links them. Building that mapping is usually the single hardest and most valuable task in the whole project, and I would budget time for it on purpose.
One more concept comes up quickly. Customers change over time, moving between plans, addresses, and owners. A technique called a slowly changing dimension stores each version of a customer with the dates it was valid, so last year's report still describes last year as it was. You do not need it on day one. You do want to know it exists before someone asks why historic numbers shifted.
Governance, kept small
Governance is a heavy word for a light idea. It means somebody is responsible, the rules are written, and access is deliberate. For a company with 10 to 60 people, a light version is enough.
- Name an owner for each important dataset. A person, not a team. When a number looks wrong, they decide what is right.
- Keep a data dictionary. A one-page list of each metric with its definition, its source, and its owner. "Active customer means at least one paid invoice in the last 90 days" is a definition. Put it where everyone can read it.
- Control access by role. Finance data and personal data should not be open to every staff member. Use groups, as you would for software access.
- Know what personal data you hold and where. Copying customer records into a warehouse creates another place that must be protected, retained, and deleted on request. Depending on your customers and where they live, privacy rules may apply. This is not legal advice, so check with counsel.
- Write down retention. How long you keep data is a decision, and the default of forever is not a good one.
A worked example with illustrative numbers
This is a hypothetical scenario with invented numbers. A company with 25 employees sells services to about 300 active customers. The CRM lists 340 customers. Accounting lists 310. The office manager's spreadsheet lists 295. Which is right?
A first step is to build the mapping table. Suppose a match on email and tax ID links 280 customers across all three. That leaves 60 in the CRM only, 30 in accounting only, and 15 in the spreadsheet only. Review shows that of the 60 CRM-only records, 35 are prospects who never became customers, 15 are duplicates under slightly different names, and 10 are real customers missing from accounting because invoices were sent from a different tool.
Now the definition matters. If "customer" means "has been invoiced in the last 12 months," the CRM's 340 shrinks sharply and the argument stops. The fix here was a definition and a mapping, and a warehouse was merely where the result could be stored and recomputed every night. This is the point I most want you to take away. Most centralization wins come from agreement about keys and definitions, not from a new database.
When not to centralize
You will save yourself a lot of money if you can recognize these cases.
- You have a single system that already holds nearly everything. If your business runs on one decent platform, use its own reporting first.
- Your questions are few and stable. If you ask the same five questions monthly, a well-built spreadsheet that pulls an export may be enough.
- Nobody owns the definitions. A warehouse will faithfully store the disagreement.
- The data is highly sensitive and the benefit is small. Every copy is extra exposure. If you do not need health or financial details in analytics, do not copy them. Pseudonymize or leave them out.
- You cannot maintain it. A pipeline that breaks quietly is worse than no pipeline, because people trust it.
- Your volume is tiny. Under a few thousand records per system, a scheduled export into a managed sheet or a lightweight database is often the right size.
Centralization is a cost with a benefit, not a virtue.
Failure modes to expect
- Silent staleness. A connector fails and the dashboard keeps showing old numbers with no warning. Show a "last updated" time on every report and alert someone when a load fails.
- Schema drift. A vendor renames a field and a transformation quietly produces blanks. Add simple checks, such as row counts and not-null tests, that stop the pipeline loudly.
- Time zones. An evening order in Puerto Rico can land on the next day in UTC. Decide the reporting time zone once and apply it everywhere.
- Late-arriving data. Refunds and corrections arrive after the period closed. Decide whether history is restated or whether adjustments post to the current period, and say so.
- Connectivity and power. If your pipeline runs on a machine in your office, a storm can take it down. Managed, cloud-hosted loads are usually more resilient than a computer under someone's desk.
What to ask a vendor
- Can I export all my data, including history, in an open format?
- How do you handle deletions and changes in the source?
- What happens when a load fails, and how will I find out?
- How is access controlled, and is it logged?
- Where is the data stored, and can I choose the region?
- What are the limits on rows, connectors, and refresh frequency at my plan, and what happens when I pass them?
What you can do Monday morning
- List the five questions you ask most often and which systems each one needs.
- Write a one-sentence definition for each metric you report. Where two people disagree, resolve it now.
- Pick the system of record for each kind of fact, such as customers, invoices, and deals.
- Pull the customer lists from two systems and count how many match on email. That number tells you how big the mapping job is.
- Decide whether a good spreadsheet or a warehouse is the right size for your first step.
If you would like help thinking through which pieces to centralize and which to leave alone, Ozcorp InfraOps works with small teams on exactly that, and InfraOps Base starts at $500 per month with vendor and license charges billed separately. I am also happy to simply hear how your four tabs disagree. It is usually more interesting than people expect.
Discuss your infrastructure requirements
Review the current service offer or schedule a conversation about the scope you need.
Stay in touch
Follow Ozcorp
Leave your details for occasional notes about new articles and Ozcorp updates.