An upstream operator came to us running its entire business on more than twenty vendor systems, and not one of them agreed on what a well was.
WellView had its own well identity. ProdView had another. POD2, Bolo, ZDSCADA, ComboCurve, Enverus, iLandman, each carried its own key for the same physical hole in the ground. There was no common identifier that let you join production to accounting, or telemetry to a reserves forecast, without a human in the middle deciding which “Well 4-12H” in system A was the same as which “412H_ALLOC” in system B. Every cross-system number the business produced was reconstructed by hand, in a spreadsheet, by someone who happened to know the mapping in their head.
That is the problem this post is about, and what we built to fix it. It is a case study, not a how-to. The generalized pattern lives in the companion post, building a single master well table without an MDM platform. Here we want to show the actual shape of the thing we shipped.
Twenty systems, twenty definitions of a well
Start with the systems, because the sprawl is the whole problem.
Drilling and completions data lived in WellView. Daily and monthly production ran through ProdView and POD2. Accounting and the general ledger sat in Bolo, with invoices flowing through a separate AP system. Field telemetry came off ZDSCADA. Reserves and type curves lived in ComboCurve. Market and offset data came from Enverus. Land and lease obligations were tracked in iLandman. Hedging, GIS, and a handful of niche drilling and steering tools filled in the rest.
Each of these is a competent system inside its own boundary. That is not the complaint. The complaint is that each one models a well the way that vendor found convenient, and the operator inherited all twenty models at once. WellView keys wells one way. The accounting system keys them by a completely different well number that maps to a cost center, not a wellbore. SCADA identifies devices, not wells, and a single well might have three device IDs across its life. The API number, which everyone assumes is the universal key, is not one: it changes on recompletion, it is entered inconsistently, and it does not exist at all for some facilities you still need to account for.
So the “single source of truth” for any question that crossed two systems was whoever built the spreadsheet last. When that person left, the truth left with them.
Bronze first, one schema per source
We built the platform on a SQL Server foundation database using a medallion architecture. If you want the argument for why that pattern fits operational data specifically, we made it in the case for medallion architecture in operational data. The short version: preserve the raw signal, do the cleaning once, let consumers query conformed data without re-deriving anything.
The bronze layer got one schema per source system: bronze_wellview, bronze_prodview, bronze_pod2, bronze_bolo, and so on down the list. Each schema mirrors its source. No renaming, no unit conversion, no cleverness. WellView’s tables look like WellView inside bronze_wellview. This matters for a boring but load-bearing reason: when a vendor changes a schema or a load looks wrong, you reprocess exactly one source without touching the other nineteen. Bronze is the replay buffer. It is supposed to be boring, and this one was.
The rule we held to was that nothing got interpreted on the way into bronze. Interpretation is a decision, decisions belong downstream where they can be tested, and a bronze layer that quietly “helps” is a bronze layer you cannot trust for backfill.
Airflow running one container per load
Orchestration was Airflow, and every load task ran as its own purpose-built Docker container. One container knew how to pull WellView. A different one knew ProdView. A different one handled the ZDSCADA telemetry feed.
We do this deliberately, and we have opinions about it. Packing every source’s extraction logic into a shared Airflow codebase couples twenty vendor integrations to one deployment, so a dependency bump for one connector risks breaking the other nineteen. Containerizing per source keeps them independent: each load has its own dependencies, its own runtime, its own blast radius. The reasoning is in the case for decoupled Airflow. On an estate with this many heterogeneous sources, that decoupling is not a nicety. It is the difference between a platform you can extend one source at a time and one you are afraid to touch.
Infrastructure was Terraform. Secrets and credential injection ran through Vault, so no connector shipped with a hardcoded password and rotating a vendor credential did not mean editing a DAG.
Decorating rows at load time, not later
Here is the part most platforms defer and then never do.
Every row, as it landed, got decorated with metadata before it was written: which source system, which load run, when it arrived, and the resolved dimension keys tying it back to the conformed entities. Lineage was attached at write time, not reconstructed later by an archaeology project.
The instinct on most projects is to land raw data fast and clean it up “when we get to master data management.” You never get to it. The cleanup becomes a permanent backlog item, and in the meantime every consumer invents their own joins. By resolving identity and stamping lineage at load, the expensive master-data work happens once, in the pipeline, instead of being re-derived in every downstream report. When someone later asks why a production number looks off, the answer is a query against lineage columns, not a week of forensics.
One well id, resolved once
The core of the whole engagement was well identity.
We built a conformed well dimension for each source: dim_wellview_well, dim_prodview_well, dim_pod2_well, dim_bolo_well, and the rest. Each per-source dimension takes that system’s native well identity and shapes it into a common structure. Then all of them roll up into one system-agnostic master well table, dim_well, with a single surrogate well id that belongs to the platform and to no vendor.
Every row in the master carries primary_source_system and primary_source_key, so from any conformed well you can walk back to exactly how each source system named it. That is the piece that dissolves the spreadsheet problem. The mapping between “Well 4-12H” and “412H_ALLOC” and SCADA device 88213 stopped living in someone’s head and started living in a table, versioned, queryable, and owned.
We did not buy an MDM product to do this. A mid-size operator does not need a six-figure master-data platform to get one trustworthy well id. Conformed dimensions and disciplined lineage get you there, and they leave you with something your own engineers can read and maintain. The entity-resolution problem is genuinely hard, and we are not claiming it resolved itself. Some wells needed human adjudication, and a few edge cases (facilities with no API, wells that split or merged over their history) stayed on a review list. But the resolution happened once, centrally, and then held. The how-to companion, building a single master well table without an MDM platform, walks through the surrogate-key and entity-resolution decisions in detail. The related pain of tying land records to production is its own hard problem, covered in reconciling land and production data.
A silver KPI layer built with the analysts
With a real master well table underneath, the silver layer got useful in a hurry.
We built the KPI views (net operating income, production, cost, capex) collaboratively, starting from the SQL the client’s own analysts were already running inside individual systems. Those analysts knew what a correct NOI number looked like. What they had never had was a place to compute it across sources without stitching exports together by hand. We took their queries, pointed them at conformed data, and moved the logic into shared silver models that everyone inherited.
That collaboration mattered more than any tooling choice. The analysts trusted the numbers because the numbers came from their own definitions, now computed once against a well id they could rely on.
What the business could suddenly do
The outcome is the point of the whole story.
Once well identity was resolved centrally, business users could build on conformed data directly. Dashboards that used to require a week of manual reconciliation became a query against dim_well joined to the silver KPIs. Teams started standing up AI-enabled internal web apps on top of the platform, because the data underneath them was consistent enough to build against without babysitting it.
None of that was possible while every tool re-derived well identity on its own. What changed was not a slicker dashboard layer. It was that the hardest, most error-prone join in the entire business got solved one time, in one place, and then stopped being everyone’s problem. If you are staring at your own version of the twenty-system spreadsheet, that is the move: resolve identity once, centrally, and let everything downstream inherit it. That migration path, for operators earlier in the transition, is laid out in from spreadsheets to a real data stack.
Further Reading
- Building a Single Master Well Table Without an MDM Platform
- The Case for Medallion Architecture in Operational Data
- Keep Your Pipelines Portable: The Case for Decoupled Airflow
- Reconciling Land and Production Data: Why It’s the Hardest Problem in Upstream Data
- From Spreadsheets to a Real Data Stack: A Realistic Migration Path for Mid-Size Operators