Summary
Master data and transactional data have fundamentally different lifecycles, change patterns, and quality requirements. Managing them in the same system — or worse, with the same processes — is the root cause of most data quality problems in mid-market IT. This post explains the distinction, shows where it breaks down in practice, and describes what a proper separation looks like.
I spent a week last year helping a logistics company figure out why their Power BI dashboards kept showing contradictory revenue numbers for the same region. Three different teams had three different definitions of "region." Sales used a dropdown in Salesforce. Finance had a lookup table in their ERP. Operations maintained a spreadsheet that the warehouse managers updated when they felt like it.
The transactions were fine. Every order, every invoice, every shipment was recorded accurately. The problem was not the events — it was the reference points those events were recorded against. The master data.
This is the single most common data quality problem I see, and it always traces back to the same root cause: nobody separated master data from transactional data, so nobody governed it properly.
The actual difference, without the textbook fluff
Master data describes the entities your business operates on. Products. Customers. Suppliers. Locations. Cost centers. Chart of accounts. These are the nouns. They change infrequently — a product name gets updated, a branch closes, a new cost center gets added. When they do change, the impact ripples across every system that references them.
Transactional data records the events that happen between those entities. A customer placed an order for a product, shipped to a location, charged to a cost center. These are the verbs. They're high-volume, append-mostly, and once written they rarely change. Nobody edits an invoice from two years ago.
| Master Data | Transactional Data | |
|---|---|---|
| Examples | Products, customers, locations, cost centers | Orders, invoices, shipments, journal entries |
| Volume | Hundreds to tens of thousands of records | Millions to billions of records |
| Change pattern | Infrequent updates, each one matters | Constant inserts, rarely modified after creation |
| Error impact | One wrong code corrupts thousands of transactions | One wrong order is one wrong order |
| Who owns it | Data stewards, business analysts, IT | Operational users, automated systems |
| What it needs | Governance, approval workflows, audit trails | Throughput, indexing, archival strategies |
The table above is the textbook version. The practical version is simpler: master data is the data that every other system references. Get it wrong, and the error multiplies. Get it right, and nobody notices — which is exactly the point.
Why your ERP is not the right place for master data
Most organizations start by managing master data inside their transactional systems. Product records live in the ERP. Customer records live in the CRM. Location codes live in the WMS. It works well enough until it does not.
The breaking point is almost always the same: a second system needs the same data. Your ERP has a product list. Your e-commerce platform needs the same product list. Your data warehouse joins on product codes. Suddenly "Amsterdam" is AMS in the ERP, Amsterdam in the CRM, and AMS-01 in the warehouse system. Three systems, three versions of reality, zero governance.
ERPs are built for transactional throughput. They optimize for processing orders, not for governing the entities those orders reference. There are no approval workflows for changing a product category. No audit trail showing who renamed a cost center and why. No mechanism for reviewing a batch of changes before they go live. We covered this dynamic in more detail in ERP is not MDM — if you are still using your ERP as your system of record for reference data, that post explains why it fails at scale.
The multiplication problem
A transactional data error affects one record. An order gets the wrong quantity? Fix the order. A payment is allocated to the wrong account? Correct the journal entry. The blast radius is contained.
Master data errors multiply. If someone miscategorizes a product, every transaction involving that product — past, present, and future — carries the wrong category. Revenue reports break. Margin analysis is off. Regulatory filings based on product classifications contain errors. And nobody catches it for weeks because the transactions themselves look normal. The reference data they point to is quietly wrong.
I worked with a manufacturing company that discovered their cost center hierarchy had a misclassification that had been in place for seven months. By the time someone noticed, 40,000+ journal entries were allocated against the wrong cost center. The financial restatement took three people two weeks. The actual fix — changing one field on one master data record — took about four seconds.
That is the multiplication problem. Low volume, high impact. The opposite of transactional data.
The spreadsheet phase (everyone goes through it)
When teams realize their ERP is not the right place to govern master data, the first instinct is always Excel. Export the product list to a spreadsheet. Let the data steward update it. Email it around for review. Import it back.
I have seen this workflow at companies with 50 employees and at companies with 5,000. It never scales. The problems are predictable: version conflicts (who has the latest file?), no change tracking (what changed between Tuesday and Thursday?), no access control (the intern can overwrite the cost center hierarchy), and no integration path (how does the updated spreadsheet get back into the ERP and the data warehouse?).
We wrote about this in why Excel fails as your MDM tool. The short version: spreadsheets are editing tools, not governance tools. Master data needs governance.
What proper separation looks like
The right architecture gives master data its own system — an MDM tool — that sits alongside your transactional systems, not inside them. The MDM tool is the authoritative source. Your ERP, CRM, data warehouse, and BI layer all consume from it. Changes go through the MDM tool first, get reviewed and approved, and then propagate outward.
The flow, simplified:
Data steward proposes a change in the MDM tool (new product, updated cost center, renamed location)
Reviewer approves or rejects the change — with comments, audit trail, the works
Approved change is committed to the master data store
Downstream systems pick up the change via integration views, API, or scheduled sync
New transactions are recorded against the updated master data — clean from the start
This is not a theoretical architecture. It is exactly what Microsoft MDS was supposed to provide for SQL Server teams. MDS has been deprecated as of SQL Server 2025, but the problem it was solving has not gone away. If anything, it has gotten worse — more systems, more integrations, more places for master data to go wrong.
How Primentra handles this
Primentra is built specifically to manage master data — not transactional data. The entire data model is organized around models, entities, and attributes: you define what your master data looks like (a Branch has a Code, a Name, an Area reference, a Status), and Primentra handles the governance layer around it. Role-based permissions. Approval workflows. Full audit trail. Integration views that your ERP and BI tools can query directly.
The deliberate choice is what Primentra does not do. It does not process orders. It does not store invoices. It does not try to be a transactional system. It manages the reference data that transactional systems depend on, and it does that one thing properly — with the governance, change control, and distribution mechanisms that master data actually requires.
For SQL Server teams specifically, this means the master data lives in a SQL Server database you own and control. Your DBA can back it up, query it, include it in Always On. Your ETL processes can read from integration views using standard T-SQL. No proprietary APIs, no cloud dependency, no new infrastructure to learn. We covered the DBA perspective in MDM for the SQL Server DBA.
The test: can you answer these questions about your master data?
If you are unsure whether your organization manages master data properly, try answering these:
- Who changed the description of product
SKU-4419last month, and why? - How many systems have their own copy of your location master? Are they in sync?
- If someone adds a new cost center right now, which systems will pick it up — and when?
- Who has permission to rename a customer segment? Is there an approval step?
- What was the state of your product hierarchy on January 1st?
If you can answer all five with confidence, your master data governance is in good shape. If you are not sure about even one, your master data is being managed like transactional data — reactively, without a paper trail, without controlled distribution. That works until it does not, and when it stops working, the cleanup is expensive.
Frequently asked questions
What is the difference between master data and transactional data?
Master data describes the core business entities that remain relatively stable over time — products, customers, locations, cost centers. Transactional data records events that happen to or between those entities — orders, invoices, shipments, payments. Master data is the "nouns" of your business; transactional data is the "verbs."
Why should master data and transactional data be managed differently?
Transactional data is append-only and high-volume — you insert new records and rarely update them. Master data is low-volume but high-impact — a single wrong product code or cost center can corrupt thousands of transactions. Master data needs governance, approval workflows, and audit trails. Transactional data needs throughput and archival strategies.
What happens when you manage master data inside your ERP or transactional system?
You end up with duplicate records, inconsistent naming, no change tracking, and no approval process. Different departments create their own versions of the same entity. Your BI reports show conflicting numbers because "Amsterdam" is spelled three different ways across systems.
What is a master data management (MDM) tool?
An MDM tool is a dedicated system for defining, governing, and distributing master data. It provides a structured data model, role-based permissions, approval workflows, audit trails, and integration views so that downstream systems — ERP, BI, data warehouse — all consume the same authoritative version of each entity.
Ready to give your master data its own system?
Primentra runs on your own SQL Server. Define your entities, set up permissions and approval workflows, and start distributing clean reference data to every system that needs it. The 60-day trial includes everything — no credit card, no sales call.