How to Build a Working MDM Foundation Using Just SQL Server and a Shared Drive

Everyone wants the shiny new MDM platform.

But most teams haven’t earned it yet.

You don’t need Informatica, Reltio, or Semarchy to get started.

You don’t even need a budget.

If I were leading a small data team with no tools, no budget, and just SQL Server and a shared drive, here’s exactly what I’d build first.

It won’t be flashy. But it’ll work.

Step 1 – Lock Down Your Source-of-Truth Tables

Most teams let source data sprawl.

They grab from APIs, CRM exports, vendor files, and staging tables, whatever works in the moment.

Stop.

Pick one table per entity to serve as your provisional master. Start with core entities like:

• Customer
• Product
• Location
• Vendor

Give each table a stable name:

CREATE SCHEMA mdm;
GO

CREATE TABLE mdm.CustomerMaster (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(255),
    CustomerType NVARCHAR(50),
    Region NVARCHAR(100),
    Status NVARCHAR(50),
    SourceSystem NVARCHAR(100),
    SourceSystemID NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    UpdatedDate DATETIME
);

These are not staging tables. These are your version of truth, scoped to what you know today.

Step 2 – Document Terms in Plain Text

Don’t wait for a data catalog.

Open Notepad.

Create a shared folder:

\\SharedDrive\DataDocs\MasterData\

Inside, create one markdown or text file per domain:

• CustomerMaster_Definition.txt
• ProductMaster_KeysAndRules.txt

Document:

• What the entity means
• How you define uniqueness
• What values matter (e.g., customer status, region, category)
• Where the source comes from

Yes, it’s manual. Yes, it lives in a shared drive.

But now your team can stop guessing.

Step 3 – Build Basic Match Logic in SQL

You don’t need fuzzy match software.

In SQL Server, build stored procedures to:

• Normalize names (UPPER, TRIM, REPLACE junk)
• Identify duplicates by known rules (name + zip, email + phone)
• Log results to a simple match log table:

CREATE TABLE mdm.MatchLog (
    MatchLogID INT IDENTITY(1,1) PRIMARY KEY,
    EntityName NVARCHAR(100),
    SourceRecordID NVARCHAR(100),
    PotentialMatchID NVARCHAR(100),
    MatchType NVARCHAR(50), -- 'Exact', 'Close', 'Manual'
    ConfidenceScore INT, -- 0–100
    Reviewed BIT DEFAULT 0,
    CreatedDate DATETIME DEFAULT GETDATE()
);

This gives you traceability. Even without a UI.

Step 4 – Use Excel for Manual Stewardship

Yup. Excel.

Output your match exceptions, nulls, and edge cases to .xlsx files.

Drop them in a folder like:

\\SharedDrive\StewardshipQueue\2025-07-Week29\CustomerExceptions.xlsx

Let your ops or business team review and annotate them. Then import the results back into your SQL Server staging tables using a flat INSERT pattern or SSIS.

Bonus: You now have a human-in-the-loop review process without a single license fee.

Step 5 – Expose a Cleansed View Layer

Once your CustomerMaster table is shaped, matched, and enriched, expose it as a SQL view:

CREATE VIEW mdm.vwCustomerMaster_Cleansed AS
SELECT
    CustomerID,
    CustomerName,
    CustomerType,
    Region,
    Status
FROM mdm.CustomerMaster
WHERE Status = 'Active';

Make this the only place downstream systems grab customer data.

Don’t let anyone hit raw source tables.

Don’t let anyone touch your matching logic.

Guard the view like it’s production.

If they want to enrich it, they go through your pipeline.

This is how you build trust in your version of truth before any platform exists.

Step 6 – Set Up an Audit Trail

One table:

CREATE TABLE mdm.AuditLog (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EntityName NVARCHAR(100),
    RecordID NVARCHAR(100),
    Action NVARCHAR(100),
    PerformedBy NVARCHAR(100),
    ActionDate DATETIME DEFAULT GETDATE(),
    Notes NVARCHAR(1000)
);

Every time someone changes a golden record or resolves a match, log it.

Even minimal logging is better than silence.

Later, this gives you leverage when you do migrate to a full MDM tool.

Step 7 – Schedule Work Like It’s Production

Use SQL Agent to:

• Run daily match routines
• Output exception files
• Archive previous versions
• Refresh views

Store logs in one folder:.

\\SharedDrive\MDMLogs\

If it’s not scheduled and traceable, it’s not sustainable.

No Platform? No Problem. This Is Your Real Foundation

No licenses.

No new platforms.

Just discipline, SQL Server, and a shared drive.

You’ll build:

• Core golden tables
• Basic match rules
• Business ownership
• Auditable stewardship
• A pipeline you can improve over time

This isn’t a prototype. It’s a foundation.

When the Budget Comes, You’ll Be Ready

You’ll be ready.

Because you’ll already know:

• What your master data looks like
• Who owns it
• Where it breaks
• What’s possible with low tech

That’s more valuable than any sales demo.