Somebody at your company has been told you need to buy a master data management platform to get one clean list of wells. You don’t. You need a surrogate key, a handful of per-source dimension tables, and a business decision about which system wins when they disagree.
We built exactly this for an upstream operator running more than twenty vendor systems, and the master well table that fell out of it is a few hundred lines of SQL, not a six-figure product with its own admin console and consultant. The full case study covers the whole estate. This post is the reusable version of the one piece everybody asks about after they read it: how do you get to a single source of truth on wells without an MDM platform.
The same well, five different identities
Walk any mid-size operator’s stack and count how many ways a single well is identified. WellView keys it on idwell, a system-generated GUID that means nothing outside WellView. The production accounting system (POD2, ProdView, whatever) has its own integer well key and a separate well name. Accounting refers to the well by a cost-center or GL well number that the field never uses. SCADA identifies it by a device or tag ID that was set when the RTU was commissioned. Land has a lease-and-well-name string.
None of these agree. None of them were meant to. Each system was bought at a different time, by a different team, to solve a different problem, and each one assigned the well an identity that made sense inside its own four walls.
The moment you try to answer a question that crosses two systems, the seams show. Production for the well SCADA calls DEV-4471, joined to the working interest for the well land calls SMITH 1-17H, tied to the AFE the accounting system booked against well number 100442. There is no join key. There is a person with a spreadsheet who knows that those three things are the same well, and that person is your master data platform right now.
Why the API number won’t save you
The obvious fix is the API number. In upstream oil and gas, API stands for American Petroleum Institute, not application programming interface. It’s a numeric code assigned by state agencies to uniquely identify a wellbore, and it’s the closest thing upstream has to a national well identifier, it’s in most of the source systems, so make it the primary key and be done.
It doesn’t hold up, and we’ve watched teams lose a month learning why. The API number is a fine natural identifier and a bad primary key, for a few concrete reasons.
It arrives in at least three formats. The same well shows up as a 10-digit, 12-digit, or 14-digit number depending on which system emitted it and when, because the 12- and 14-digit forms tack on directional sidetrack and event codes. Join 3501720044 to 35017200440000 and you get nothing.
It’s frequently wrong or missing at the source. A well that was permitted but not yet drilled may have no API number. A hand-keyed one has a transposed digit. We covered the format-normalization mechanics in the OCC ingestion post, and even with clean normalization you’re left with wells that legitimately have no API number yet and wells where two source systems disagree on the digits.
And it isn’t stable at the grain you care about. Recompletions, sidetracks, and re-entries mean the physical hole, the regulatory API, and the thing your accountant calls “the well” drift apart over the decades. A well drilled in 1982, recompleted twice and sidetracked once, has a defensible argument for being one well or three depending on who’s asking.
Use the API number. Just don’t make it the key. It’s an attribute you resolve against, not the thing your foreign keys point at.
The pattern: per-source dimensions, then conform
The pattern that works is older than every MDM product on the market. It’s conformed dimensions, straight out of the Kimball playbook, applied to well identity. Two layers.
First, a per-source well dimension for every system that has wells. dim_wellview_well, dim_pod2_well, dim_bolo_well, dim_scada_well, one per source. Each one is a faithful, lightly cleaned mirror of how that system sees wells: its native key, its native name, whatever API number it carries, the attributes it owns. You are not resolving anything yet. You are just giving each source’s well list a stable home in your warehouse with the source’s own identity preserved exactly.
Second, a single system-agnostic master, dim_well, that every per-source dimension rolls up into. This is the table the whole business joins to. It has a surrogate key that belongs to you, resolved attributes, and lineage back to each source. The per-source dimensions carry a foreign key up to the master.
The important property: the master doesn’t own the source keys, and the sources don’t own the master key. Each per-source row knows which master well it belongs to. The master knows, for each well, which source it trusts for the canonical version of each attribute. That’s the whole trick, and it’s why you don’t need a platform to enforce it.
The surrogate key and the lineage columns
Give the master its own key that means nothing to any source system. A BIGINT IDENTITY, a hash, a UUID, whatever your platform likes. It exists so that when SCADA renames a device or accounting re-numbers a cost center, your master key and every downstream report survive the change. Natural keys leak the source system’s problems into your model. A surrogate absorbs them.
Then carry two columns that do the load-bearing governance work:
primary_source_system: which source is authoritative for this well’s canonical identity (WELLVIEW,POD2,BOLO).primary_source_key: that source’s native key for this well, so you can always trace the master row back to the record it was minted from.
On SQL Server the master looks about like this:
CREATE TABLE dim_well (
well_id BIGINT IDENTITY(1,1) PRIMARY KEY, -- surrogate, ours
well_name NVARCHAR(200) NOT NULL, -- resolved canonical name
api_number_14 CHAR(14) NULL, -- normalized, nullable on purpose
primary_source_system VARCHAR(30) NOT NULL, -- who owns the truth
primary_source_key NVARCHAR(100) NOT NULL, -- their native key
is_active BIT NOT NULL DEFAULT 1,
effective_from DATE NOT NULL,
effective_to DATE NULL,
CONSTRAINT uq_primary_source UNIQUE (primary_source_system, primary_source_key)
);
Each per-source dimension carries the surrogate back down:
CREATE TABLE dim_wellview_well (
wellview_well_sk BIGINT IDENTITY(1,1) PRIMARY KEY,
idwell NVARCHAR(50) NOT NULL, -- WellView's native GUID
well_name NVARCHAR(200) NULL,
api_number_raw NVARCHAR(20) NULL,
api_number_14 CHAR(14) NULL, -- normalized copy
well_id BIGINT NULL -- FK up to dim_well, NULL until resolved
REFERENCES dim_well(well_id),
CONSTRAINT uq_wellview_idwell UNIQUE (idwell)
);
The well_id being nullable is not sloppiness. It’s the review queue. A per-source well that hasn’t been matched to a master well yet sits there with a null, and that’s a row for a human to look at, not a silent failure that corrupts a join downstream.
Entity resolution is where the real work is
Everything above is plumbing. The hard part, the part the MDM vendors charge for, is deciding that the WellView idwell, the POD2 well key, and the accounting well number are the same physical well. That’s entity resolution, and no platform does it for you cleanly either. It sells you a UI to do it in.
The resolution runs in tiers, cheapest and most certain first.
Deterministic matching on the normalized API number handles the easy majority. Both sources carry a clean 14-digit API, they match, you’re done. This is why you normalize API format in every per-source dimension even though you’re not keying on it: it’s your best matching attribute, not your key.
-- Tier 1: exact match on normalized API, only where it's unambiguous in this source
UPDATE s
SET s.well_id = m.well_id
FROM dim_wellview_well s
JOIN dim_well m ON m.api_number_14 = s.api_number_14
WHERE s.well_id IS NULL
AND s.api_number_14 IS NOT NULL
AND NOT EXISTS ( -- skip anything with a duplicate API on either side
SELECT 1 FROM dim_wellview_well d
WHERE d.api_number_14 = s.api_number_14 AND d.wellview_well_sk <> s.wellview_well_sk
);
What’s left after Tier 1 is the interesting pile. Wells with no API. Wells where the API is present in one source and blank in another. Wells where two sources carry different APIs for what a human can see is obviously the same well. For these you fall back to fuzzy matching on well name plus a location tolerance (surface hole coordinates, or lease and section-township-range), and you score the candidates rather than auto-merging them.
Anything above a high confidence threshold auto-links. Anything in the ambiguous band goes to a review table with the candidate master well and the score, and a person makes the call once. That decision gets written back as a match rule so the same pair never comes up again. That review-and-remember loop is the entire product you’d otherwise be buying, and it’s a table plus a query plus an afternoon of someone’s attention each month, not a platform.
We wrote more about why this specific problem, the same well wearing different identities, is so persistent in upstream in the data quality post. The short version: the disagreements aren’t bugs, they’re the business changing while the systems didn’t change together.
How the master gets its canonical attributes
Once wells are linked, the master’s attributes come from the source you designated authoritative, not from a coalesce-and-pray. This is where primary_source_system earns its column. Well name comes from land or production, whichever your business decided owns naming. Directional survey comes from WellView. Cost center comes from accounting. Each attribute has an owning system, and the master pulls from that system’s per-source dimension.
That’s a governance decision, not a technical one, and it’s the same decision the land-and-production reconciliation forces you to make. No product can make it for you. What the pattern does is give the decision a place to live: a mapping of attribute to authoritative source, applied in one view, changeable in one place when the business changes its mind.
Why this beats a packaged MDM product for a mid-size operator
MDM platforms are built for the enterprise problem: thousands of users, dozens of domains, formal stewardship workflows, regulatory audit of the stewardship itself. That’s a real problem for a supermajor. It is not the problem a 200-well operator has.
For a mid-size operator the conformed-dimension approach wins on the things that actually matter to you.
It lives in the database you already run and back up. The master is tables and views in your SQL Server or PostgreSQL, versioned in git with the rest of your models, tested with the same dbt or check-based tests you already run. There’s no separate system to license, patch, secure, and staff. When we build this into a medallion warehouse, dim_well is just the conformed dimension the silver layer already needed.
The logic is legible. An engineer can read the match rules and the attribute-source map and understand exactly why a given well resolved the way it did. MDM survivorship rules configured through a vendor GUI are famously opaque, and opacity is expensive the day something resolves wrong during close.
And it’s honest about its limits, which a sales deck won’t be. This pattern does not magically clean your data. It gives you one place to resolve identity and a review queue for the cases a machine shouldn’t decide. It won’t fix a source system that emits garbage; wire a data contract at that boundary instead. And it is genuinely more manual than a platform for the ongoing stewardship, because you’re doing the review in SQL and a spreadsheet rather than a purpose-built UI. For a couple hundred wells and a handful of new ones a month, that manual cost is a rounding error next to a platform’s license and the integration project to feed it.
The wells stopped being resolved in every spreadsheet and BI tool separately and got resolved once, centrally. That’s the whole point of a master well table, and you can have it without buying anything. If the thing standing between you and one clean well list is a purchase order for a platform, the platform is not the thing you’re missing.