Most mid-market organizations reach a predictable inflection point: the business has grown, data is being produced by a dozen systems, and the CFO and VP of Operations are presenting different revenue numbers in the same executive meeting. The standard response is to call it a “Single Source of Truth problem” and begin evaluating data warehouse platforms. That diagnosis is partially right and the prescription is almost always wrong. The platform is rarely the issue. The architecture decisions — what to centralize, what to leave alone, how to define entities, and how to protect downstream consumers from upstream changes — are the issue. Getting those decisions right before selecting tooling is the difference between a data initiative that delivers durable value and one that becomes the next expensive thing the organization needs to fix.

Why the “just put it all in one place” instinct fails

The most common mistake organizations make when pursuing an SSOT is treating it as a data consolidation project rather than a semantic alignment project. They migrate tables from Salesforce, their ERP, their e-commerce platform, and their support ticketing system into a central data warehouse, declare victory, and discover six months later that “customer” means four different things across those four systems. The warehouse now contains all four definitions, joined inconsistently across reports, with no mechanism to adjudicate which is correct.

Consolidation without semantic alignment creates a single source of confusion. The numbers are in one place, but the definitions are still in conflict. The reports still disagree. The only thing that changed is that all the problems are now visible in one tool, which feels like progress but is actually just a different kind of noise.

Before any architecture decision is made, the organization needs to map its critical business entities — customer, product, transaction, employee, location — and identify, for each entity, which system is the system of record. This is a business decision, not a technical one, and it must involve the business owners of those systems. When two systems both claim to be authoritative for “customer,” the resolution is political before it is technical.

Medallion architecture vs. ODS vs. traditional data warehouse: choosing the right pattern

The three dominant structural patterns for mid-market data environments are the operational data store (ODS), the traditional dimensional data warehouse (DWH), and the medallion architecture. Each has a legitimate use case. Each is also frequently adopted for the wrong reasons.

An ODS is a near-real-time integration layer that reflects the current state of operational systems. It is the right choice when operational users need a consolidated view for day-to-day decision-making — a contact centre agent who needs to see a customer’s full interaction history across CRM and billing in a single screen, for example. It is the wrong choice when the primary requirement is analytical reporting, because it carries no historical dimension, no business-defined aggregations, and no separation between operational and analytical workloads. Organizations that build an ODS to solve a reporting problem are building the wrong thing.

A traditional dimensional data warehouse — star schemas, fact and dimension tables, Kimball or Inmon methodology — is purpose-built for analytical workloads and historical analysis. It performs well when requirements are stable, when the modeling team has time to design schemas properly, and when the primary consumers are structured reports and dashboards. It becomes expensive to maintain when source systems change frequently, because schema changes require significant rework in the transformation layer.

Medallion architecture — organizing data into bronze (raw ingestion), silver (cleaned and conformed), and gold (business-ready aggregations) layers — has become the practical default for mid-market organizations over the past several years, and for defensible reasons. It separates concerns cleanly: the bronze layer absorbs changes from source systems without downstream impact; the silver layer handles entity resolution and data quality enforcement; the gold layer exposes business-defined metrics to reporting tools. When a source system changes its schema, only the bronze-to-silver transformation needs to be updated. The gold layer — and every dashboard connected to it — is insulated.

The limitation of medallion architecture is not technical — it is organizational. Without clear ownership of each layer and explicit rules about what belongs at silver versus gold, organizations end up with business logic scattered across all three layers, three copies of the same data with subtle differences, and analysts who have learned (correctly) not to trust any of them. The architecture works when the governance works. The governance requires designated owners, not just documentation.

What to centralize and what to leave federated

Centralization is not the goal. Consistent definitions and reliable accessibility are the goal. These are related but not identical, and conflating them is one of the more expensive mistakes a data architecture project can make.

In our experience, mid-market organizations achieve the best outcomes with a federated ownership model paired with centralized semantic definitions. Business units retain ownership of their domain data — sales owns sales pipeline, finance owns GL entries, operations owns fulfillment metrics — but the definitions of shared entities and the metrics that cross domains are governed centrally. What constitutes “revenue” for the purposes of the executive dashboard is a centrally defined and enforced calculation, not something each team derives independently.

Practically, this means the gold layer of a medallion architecture — the business-ready metrics layer — should contain the organization’s agreed definitions of cross-domain KPIs. Revenue recognition logic, customer lifetime value calculation, headcount definitions, conversion rate methodology. These should be versioned, documented, and owned by a named individual or committee. Any report that needs “revenue” pulls it from this layer. No team builds their own revenue calculation downstream of it.

What should remain federated: raw operational data, domain-specific metrics that do not cross organizational boundaries, and any data whose primary consumer is a single operational system. Finance does not need its general ledger entries in the gold layer. It needs them available for the cross-domain metrics that reference them.

The master data management minimum viable program

MDM is frequently oversold as a platform problem and undersold as a process problem. Organizations invest in MDM tooling before they have resolved the underlying question: who has the authority to define what a customer is, and what happens when two systems disagree?

The minimum viable MDM program for a mid-market organization does not require dedicated MDM software. It requires four things:

1. A written entity registry. A document — a spreadsheet, a wiki page, anything that is maintained — that lists each critical business entity, its authoritative source system, its canonical definition, and its key attributes. “Customer” is the entity in Salesforce. The canonical identifier is the Salesforce Account ID. Name, billing address, and industry classification are attributes. This document does not need to be sophisticated. It needs to exist and be updated when things change.
2. Named data stewards. Each entity has a named owner — a business-side person, not an IT person — who is responsible for resolving definitional disputes and approving changes to the canonical definition. Without named stewards, governance conversations have no one to conclude them.
3. A conflict surfacing mechanism. When two systems produce different values for the same attribute of the same entity — two different addresses for the same customer — something needs to flag it. This can be as simple as a daily reconciliation query that writes exceptions to a shared log. It does not need to be a sophisticated matching engine until the volume of conflicts justifies it.
4. A deduplication standard. The algorithm or rules used to determine that two records in different systems refer to the same real-world entity. For most mid-market organizations, a combination of email address, company name normalized to a standard form, and phone number is sufficient to resolve 80-90 percent of matches. The remaining 10-20 percent go to the data steward for manual resolution.

Many organizations we work with spend twelve to eighteen months evaluating MDM platforms before they have operationalized these four elements. The platform will not provide them. The platform assumes they exist.

Data contracts: the architecture that prevents cascade failures

The most common failure mode in mid-market data environments is not a technology failure — it is a coordination failure. An engineering team modifies a source system (renames a column, changes a data type, splits a field into two fields), and three weeks later a finance report silently breaks or — worse — begins producing wrong numbers without breaking. The downstream consumers had no warning. The upstream team had no idea anyone was depending on that structure.

Data contracts are formal, versioned agreements between the producers of data (source systems, ingestion pipelines) and the consumers of that data (transformation layers, reporting tools, downstream applications). A data contract specifies: the schema of the data being produced, the expected data quality thresholds (null rates, value ranges, referential integrity), the refresh cadence, and — critically — the notification and deprecation process that must be followed before any breaking change is made.

In practice, data contracts for mid-market organizations can be implemented in several ways, ranging from simple to sophisticated:

• Schema registry with automated validation. Tools like dbt source freshness tests, Great Expectations, or even custom SQL assertion queries can validate that incoming data meets specified schema and quality expectations. When validation fails, the pipeline fails loudly rather than silently propagating bad data downstream.
• Documented change notification process. A written policy — enforced through engineering team agreements, not just documentation — that requires source system owners to notify data platform owners a minimum number of days before any schema change, and to receive sign-off before the change is deployed to production. Simple, but effective when actually enforced.
• Semantic versioning for data models. Treating data schemas the way software teams treat APIs: major version changes (breaking) require migration plans and extended parallel-run periods; minor version changes (additive) are allowed with notification; patch changes (non-structural) require no process. This gives downstream consumers predictability about when they need to take action.

The organizations that implement data contracts consistently report a significant reduction in the class of incident where a source system change causes downstream reporting failures. More importantly, they report that the discipline of writing contracts forces conversations between technical and business stakeholders that surface ambiguities in data definitions before those ambiguities cause production issues.

The sequencing that actually works

The right sequence for building a durable SSOT architecture is not “buy a platform, migrate data, define governance later.” In our experience, the sequence that produces durable outcomes is:

1. Identify the three to five reporting questions that matter most to executive decision-making and work backward from those to the entities and source systems they require.
2. Resolve system-of-record questions for those entities before touching any infrastructure.
3. Establish the minimum viable MDM program — entity registry, stewards, conflict surfacing — before ingesting data.
4. Choose an architecture pattern appropriate to the organization’s operational maturity and source system stability (medallion for most mid-market environments).
5. Implement data contracts on the highest-priority source systems before building downstream transformations.
6. Build the gold layer definitions with named business owners, not technical owners, driving the metric definitions.
7. Expand coverage incrementally, enforcing contract requirements on each new source before it is connected to the analytical layer.

This sequence feels slower than buying a platform and migrating everything. It consistently produces faster time-to-trust — the point at which executives actually believe the numbers — because definitional conflicts are resolved before they are baked into infrastructure.

Frequently asked questions

Do we need a cloud data warehouse to build a Single Source of Truth?

No. A cloud data warehouse — Snowflake, BigQuery, Redshift, Databricks — is an excellent platform for an SSOT in many mid-market environments, but it is not a prerequisite. Organizations with smaller data volumes and fewer source systems have built effective SSOTs on Microsoft SQL Server, PostgreSQL, or even well-governed Excel and Power Query environments. The architecture decisions described in this post apply regardless of platform. That said, for organizations with five or more operational source systems and reporting requirements that span business units, a cloud warehouse typically provides meaningful advantages in scalability, separation of compute from storage, and native integration with modern transformation tools like dbt.

How do we handle the situation where two departments refuse to agree on a shared metric definition?

This is the most common governance impasse in mid-market SSOT programs, and it is a leadership problem before it is a data problem. The practical resolution is to escalate to a named executive sponsor who has authority over both departments and make the cost of the impasse explicit: two departments using different definitions of the same metric means the organization cannot produce a consolidated view of that metric at the executive level. In our experience, framing the cost that way — “we cannot tell the board what our net revenue retention is because Finance and Sales use different definitions” — tends to produce resolution faster than abstract governance conversations. Where genuine definitional differences exist for legitimate reasons, the answer is often to maintain both definitions with explicit names (“Sales ARR” and “Finance ARR”) and a documented reconciliation between them, rather than forcing a single definition that neither team accepts.

How long does it take to build an SSOT that executives actually trust?

In typical mid-market deployments covering three to five source systems and a core set of cross-domain KPIs, organizations reach an initial trusted reporting state in four to six months when governance decisions are resolved in parallel with technical build. The most common delay is not technical — it is the time required to resolve system-of-record disputes and get named stewardship commitments from business leaders. Organizations that defer governance to “after the platform is built” frequently extend their timelines to twelve to eighteen months and still do not achieve full trust because definitional conflicts persist. Starting governance work in week one, in parallel with platform assessment, is the single highest-leverage schedule optimization available.

What is the minimum we need to spend to get a functional SSOT?

This depends heavily on existing infrastructure, source system complexity, and internal technical capacity. For organizations with moderate data volumes and existing SQL competency, a functional SSOT covering core business KPIs can be built on open-source tooling — dbt Core, PostgreSQL or a cloud warehouse free tier, and a modern BI tool — for technology costs in the range of a few thousand dollars annually. The majority of the investment is in the hours required to resolve governance questions, define data contracts, and build transformation logic. Organizations that underestimate the governance and definition work and overestimate the technology cost consistently overspend on platforms and underspend on the work that actually produces trust.

Should we buy an MDM platform?

For most mid-market organizations — those with under 2,000 employees and fewer than ten distinct source systems — a dedicated MDM platform is rarely justified before the minimum viable MDM program described in this post has been operating for at least twelve months. MDM platforms provide value at scale: when match volumes exceed what manual processes can handle, when regulatory requirements mandate specific audit trails, or when entity complexity (global hierarchies, complex ownership structures) exceeds what spreadsheet-based registries can manage. The risk of purchasing an MDM platform too early is that it creates a false sense of governance progress while the underlying definitional conflicts remain unresolved. Govern first. Platform when governance demands it.

Building a Single Source of Truth: The Architecture Decisions That Actually Matter

Most senior operations and finance leaders have experienced the same credibility-eroding moment: two reports, one meeting, two different numbers for the same metric. This post provides a practical architecture framework — covering medallion vs. ODS vs. DWH tradeoffs, federated ownership models, minimum viable MDM, and data contracts — for organizations that want to build reporting infrastructure that actually holds up under executive scrutiny.