← All components

DataView

The database. Derived in part from PPDM 3.9, then evolved - an integrated file catalogue and geometry the map reads directly.

DataView — the database

Component documentation · Data Wrangler v4

Database: DataView_Demo · Server: SQL Server 2022 Express · 7 August 2026

Introduction: DataWrangler 4.0 is a Petroleum Data Management platform consisting of 7 modules.

1 · Dataview database – a customized database schema derived from PPDM 3.9 that is more adaptable and flexible for today’s messy data environment. Dataview is much easier to load data into it and to customize it. It carries the provenance of every record so that the file used to load it is identified and can be accessed. DataWrangler can handle multiple schemas like a modified schema for the Gulf of Mexico or the Federation where each state and federal agencies data was loaded into separate schemas then common fields were federated so a single map could be displayed.

2. File Catalog – an extension of the dataview schema to include tables for the inventory of files on a shared drive as-well-as the cataloging and the extraction of tables of relevant data contained in those documents (Strucured Files: LAS, DLIS, LIS, SEGY, p190 and Esri SHAPEFILE and Unstructured Files: Word, Excel and PDF), The data can optionally be loaded to the main dataview schema or another database.

3. Data Assistance – is where flat data files can be bulk loaded into Dataview. The data is loaded to staging tables, the mapping of tables and columns is presented for review, then the data is normalized and validated, foreign keys are presented for resolution when a foreign key has been violated, when the mapping and foreign keys have been resolved the data is promoted to dataview tables.

The mapping of the tables is kept as a fingerprint and a synonym file is built. The result is that a file format has been seen before the file moves through the system with a few clicks. The same applies a directory of csv of excel files. You can bulk load headers, stratigraphy, log summaries, directional surveys, completions, production, etc. all at the same time if those files formats have been loaded before. So updating the database with data coming in from the field and a provider is virtually automatic.

4. Mapping – DataWrangler was designed to be fully integrated with a mapping front end. The map has a drop down query tool and an AI query tool to ask more complex questions. The mapping tool provides access to scout tickets for wells, access to all documents related to a well like a final well report, LAS files etc. for wells selected on the map. No need to write complex SQL queries. The export tool allows the user to export the data in a variety of formats, scout tickets, csv, multipage excel, petrel, Geographfix and OSDU. The Mapping tool has viewers for Log data (LAS, LIS, DLIS), Seismic data (SEGY) and standard desktop formats.

Note;Several available mapping tools were tried (MapBox and QGIS). They were too slow and not flexible enough so we developed the mapping tool to fit our needs. What other mapping tool can handle 3.9 million federated wells and link to documents in the file catalog.

5. Document Assistant – The document assistant is a tool to train the system to recognize tables in a document by their shape. Which means the Recognizer extracts a table of data and if it has seen that table before the File Catalog will pass it along to the database. The user can inspect a set of files to see if the Recognizer identifies the shape of the able and has correctly mapped all the columns. If not you can correct or map columns that were missed. These corrections go into the knowledge database to be used in the future. So you can see how the system grows over time as new documents are mapped and the vocabulary grows with E&P terminology. The technology could be used for any type of document.

1 · What DataView is

DataView is the customized database at the centre of Data Wrangler v4, together with the integrated Mapping tool for exploring it. It is a derivative of PPDM 3.9 — not a subset, not a rename, and not a conformant implementation. It borrows some of PPDM’s vocabulary and shape where those earn their keep, and departs from them deliberately to make the database generally more usable and adaptive to a mapping front end.

The job is different in a specific way. PPDM 3.9 is a destination model: it assumes data arrives already identified, coded and validated, and gives it a rigorous place to live. Data Wrangler’s value is the messy upstream part — working out what a folder of SEG-Y, LAS, PDF and shapefile files actually is, which well or survey it belongs to, and whether it can be trusted. DataView is shaped for that arrival problem, and for putting the result on a map. It can feed a PPDM 3.9 model if required, or stand on its own. It is adapted to receiving data directly extracted from scanned documents on a shared drive, or bulk loading from flat files (CSV, Excel) or structured data files (LAS, LIS, DLIS, SEG-Y, P190, shapefiles).

The other three components all terminate here. The Data Assistant loads structured data into dv_* tables (LAS, LIS, DLIS, SEG-Y, P190, shapefiles). The File Catalog captures document contents into cat_* staging tables and promotes them into dv_*. The Document Assistant recognises tables by shape and lands them through the same door. dv_* is the final database, not a staging layer — everything upstream exists to fill it.

Multiple schemas are a design feature, not accumulated clutter. dataview is the model; file_catalog is the capture mirror and inventory; stg is per-source bulk staging. The dataview_gom database exists because BOEM models the Gulf differently enough that forcing it into the common schema would damage both. The dataview_federation database contains 3.9 million wells from all producing state and federal agencies, and dataview_federation.v_well unions the sources that share common ground. Letting a source keep its own shape and federating on the fields that genuinely correspond is a deliberate position — the alternative, forcing every source into one rigid schema, is how detail gets discarded on the way in.

3 · What it inherits from PPDM 3.9

Dataview borrows some of the tables PPDM 3.9 but others were modified and created from scratch to be more practical and useful for a map driven database.

Table names. dv_well, dv_well_log, dv_well_core, dv_well_dir_srvy_hdr/_sta, dv_well_formation_top, dv_business_associate, dv_seis_set, dv_seis_line, dv_strat_interval are PPDM table names with a dv_ prefix. When the loader was pointed at a real PPDM 3.9 instance, twelve tables matched by name alone with no mapping at all.

Audit columns. Nearly every dv_* table carries PPDM’s audit block: active_ind, row_created_by, row_created_date, row_changed_by, row_changed_date, source, remark. Promote fills these automatically rather than requiring each loader to remember them.

Reference-table pattern. Coded values resolve to dv_r_* tables keyed on the code itself, exactly as PPDM keys its r_* tables — dv_r_source, dv_r_uom, dv_r_well_status, dv_r_well_type, dv_r_depth_datum, each with the same short_name / long_name / remark / audit shape.

Entity resolution. Operators resolve to a ba_id on dv_business_associate rather than being stored as a name string — the PPDM discipline that stops “CHEVRON USA”, “Chevron U.S.A. Inc” and “CHEVRON USA INC.” from becoming three operators.

FK-derived load order. The loaders read the live foreign-key catalog and derive insert order topologically. That is PPDM thinking: the model’s own constraints, not a hand-maintained list, decide what loads first.

4 · Where it departs, and why

4.1 A narrower reference vocabulary — and it is not finished

DataView carries five dv_r_* reference tables today, holding 310 codes between them: 204 units of measure, 51 well types, 35 sources, 18 well statuses, 2 depth datums. The domains that are seeded are seeded properly.

This is a current state, not a design ceiling. The intent is the full set — the Standards Manager already seeds from 19 PPDM 3.9 reference CSVs, and the remaining fourteen domains are outstanding work rather than a deliberate omission. Anyone reading the schema today would reasonably infer a philosophical position about light coding; there isn’t one.

Two consequences follow from the present state and are worth knowing while it lasts. Coded values outside those five domains are carried as free text. And promote’s reference guard — which holds a row whose coded value isn’t registered, rather than rejecting it — only applies to tables named dv_r_*, so most coded columns are currently unguarded because there is nothing to guard them against. Seeding the remaining reference tables tightens both at once, with no code change.

4.2 Geography is first-class

Eight tables carry a geography column:

Stock PPDM 3.9 has no spatial type. It carries coordinates as numeric attributes and leaves geometry to whatever sits on top. DataView stores the geometry itself, in SRID 4326, so what the map draws is what the database holds — a line on screen is a row you can query, not a rendering artifact.

dv_well keeps both: surface_latitude / surface_longitude as numeric(15,10) and geog. The numerics are what documents and loaders supply; geog is derived from them at promote time. That split matters — it means a coordinate can be examined and corrected as a number before it becomes geometry.

dv_seis_set.geog holds polygons only. A LINESTRING in the footprint column once broke the entire seismic map layer, because the page shares one error handler across the footprint and line loops. A geometry-type mismatch in a shared column is a rendering contract, not a cosmetic detail.

4.3 Spatial indexing by H3

dv_well carries h3_r4, h3_r5, h3_r6, h3_r7 and h3_coord_hash. Four resolutions of hexagonal grid cell, precomputed. This is not PPDM at all — it exists so the map can aggregate 3.9 million well headers at continental zoom without touching geometry, by grouping on a string. The h3_coord_hash lets a backfill find rows whose coordinates changed without recomputing every cell.

4.4 Selected denormalisation, for the map

dv_well carries operator_ba_id and operator_name; field_id and field_name. PPDM would keep only the identifiers and join for the labels.

The reason is the map. A popup that names the operator should not require a join to dv_business_associate for every visible well, and a filter on operator should not either. The identifier remains authoritative; the name is a carried label.

The same reasoning explains uwi appearing directly on almost every detail table — casing, cores, tops, surveys, logs, stimulations, perforations, DSTs. PPDM reaches several of these through intermediate parents. DataView carries the well key on the child so “everything known about this well” is one predicate rather than a join chain.

The one exception is production, and it is worth knowing: dv_prod_volume has no uwi. It keys on prod_entity_id, with the well link on dv_prod_entity. Any query counting production by well must join through the entity.

4.5 Geography as columns, not as area rows

dv_well holds country, province_state, county, area and protraction_area as columns. PPDM models each of these as a row in area plus a row in well_area linking it to the well, with area_type as the discriminator — so one DataView well row becomes three area rows and three well_area rows on the way into PPDM.

This is the clearest case where the derivative genuinely diverges, and it is why promoting to PPDM needs a fan-out mapping rather than a column map.

4.6 Permissive by construction, governed by code

dv_well has 52 columns. Four are NOT NULL: uwi (the primary key, char(14)), active_ind, row_created_by, row_created_date. Everything else is nullable.

That is deliberate and it mirrors PPDM’s own permissiveness — but DataView then puts the discipline in code rather than constraints. REQUIRE_WELL_COORDS = True in promote means a well without a surface coordinate is held in the mirror, not lost and not promoted. Seismic surveys pass a mappable gate — a usable outline or a complete bounding box — or they wait.

The governing principle: the catalog records what you have; dv_* records what you can use. Un-georeferenced data does not get promoted. A confident wrong coordinate is worse than none, because it plots and nobody checks it.

4.7 Provenance is a column, not a convention

Around forty dv_* tables carry INVENTORY_ID — the SHA-1 of the source file’s path, joining every row back to the exact document it came from in GLOBAL_FILE_CATALOG. PPDM has no equivalent; it models sources and documents as entities to be related, not as a column stamped on every row.

Alongside it, source records how a row arrived, against the dv_r_source vocabulary: SHAPE for the recogniser, CATALOG for the File Catalog’s own extractors, BULK_LOADER for tabular loads, LAS, SEGY, SYNTH and so on. A single GROUP BY source answers “what did the document pipeline contribute over the structured loaders” — a question that would otherwise need an audit trail.

INVENTORY_ID is also load-bearing for correctness, not just lineage: promote’s idempotency is delete the rows carrying this file’s ID, then insert. Re-processing a document replaces its contribution exactly, without touching anything from any other file.

Two things the derivative deliberately did not inherit. PPDM’s PPDM_GUID and ROW_QUALITY appear on no dv_* table, and that is on purpose: DataView is a derivative, not a PPDM implementation. Carrying PPDM’s own row-identity and row-quality machinery would imply a conformance the database does not claim and does not need — the identity that matters here is the UWI and the INVENTORY_ID, and quality is expressed by whether a row cleared the gates into dv_* at all.

4.8 One version, on purpose

PPDM expects multiple source rows per entity, discriminated by active_ind and effective/expiry dates — three vendors’ opinions of one well’s total depth coexisting, each attributed, none authoritative.

DataView carries one. A single row per well, updated in place, with promote filling NULLs and never clobbering an existing value. The reason is to eliminate ambiguity: a user asking for a well’s total depth gets a number, not a set of candidate numbers with a resolution policy attached. For companies too small to employ a data manager — the people this is built for — a model that requires someone to adjudicate between versions has moved the problem rather than solved it.

The trade is real and worth stating: DataView cannot represent disagreement between sources. What it can do is record where its single answer came from, which is what INVENTORY_ID and source are for. Disagreement is resolved at the door rather than stored.

4.9 A master well header reference, shipped with the product

This is the piece with no PPDM counterpart at all, and the one a customer notices first.

DataView ships with a master well header database of 3,888,078 wells — call it 3.9 million — compiled from state and federal agencies. A customer starts with a populated reference rather than an empty schema — and more importantly, the pipeline uses it as a working reference, not as sample data. It does two jobs:

It finds missing UWIs in documents. A scout ticket written before an API number was assigned, or a report that names a well without identifying it, arrives with no key. The enrich stage joins what the document did state — well name, operator, field, county, state, and where available total depth and spud date — against the master, and resolves the UWI when the match is unique and corroborated. A shared well name alone never auto-fills; the corroborating attributes are what make it safe.

It backfills missing header attributes. A document that identifies a well but states little else gets the rest filled from the master. In a recent run over 1,055 files this contributed 974 attribute fills and 74 sets of coordinates that no document supplied. Those coordinates matter more than they look: REQUIRE_WELL_COORDS means a well without them is held out of dv_* and off the map, so backfilling a coordinate is the difference between a document’s contents landing and waiting.

This is why the reference is a database rather than a lookup file. Both jobs are set-based joins against millions of rows, executed server-side, and both run on every pipeline pass.

5 · How the shape serves the mapping front end

Geometry lives in the database. The map layers issue SELECT geog.STAsText(); they don’t compute. A well path drawn on screen was computed once, server-side, and stored — so the same geometry is available to any query, export or report without re-deriving it.

The coordinate gate is what makes the map trustworthy. Because nothing reaches dv_well without a surface coordinate, “every well in dv_well” and “every well the map can draw” are the same set. There is no silent population of invisible rows.

H3 makes scale tractable. Aggregating on h3_r4 at continental zoom and dropping to points as the user zooms in is a string GROUP BY, not a spatial operation.

Well paths are filtered by usefulness, not just validity. Paths below a minimum closure — roughly 500 ft — are not computed, because a near-vertical well is a dot at map scale and a drawn line implies a shape nobody can see. Those wells still appear as points from dv_well. Nothing is lost; a misleading rendering is avoided.

Refusal is a feature. A well with no directional survey gets no path. Absent geometry is not straight geometry, and an invented curve on a map gets believed.

6 · How the shape serves the File Catalog

Documents are catalogued before a well header exists. A scout ticket arrives with tops, casing and completion details for a well that may not be in dv_well yet. The mirror exists to hold that.

cat_* is dv_* with the constraints removed. build_catalog_mirror.py reads each dv_* table’s real columns from INFORMATION_SCHEMA and generates file_catalog.cat_<name> with:

every column, same name, same type, but always nullable and with no foreign keys — capture is tolerant and parentless

provenance: CAT_ROW_ID, INVENTORY_ID, SOURCE_PATH, PROMOTED, PROMOTED_AT, CAPTURED_AT

a UWI helper column if the target has none, so promotion can always gate on “does dv_well have this UWI yet”

indexes on (UWI, PROMOTED) and INVENTORY_ID

Because the mirror is generated from the model, the two cannot drift in shape. That is what makes the next part work.

Promote moves rows by column-name intersection. It doesn’t carry a hand-written column map. It takes the shared column names between cat_X and dv_X, minus the provenance set, and builds the INSERT from that. Add a column to dv_*, rebuild the mirror, and it flows — no mapping to maintain.

Promote moves rather than copies. Rows are inserted into dv_* and deleted from cat_*. The move is the cleanup; there is no flag to reconcile. cat_* is transient by design, which is also why anything wanting a durable read (such as the PPDM hop) must source from dv_*.

Parents first, from the FK graph. Order is derived topologically, with one synthetic edge added: every mirror depends on dv_well, even where no declared FK exists, so a brand-new well’s header lands before its details.

Held, not lost. A row whose coded value isn’t in its reference table, or whose well has no coordinates, stays in the mirror and is reported. Nothing is discarded for failing a gate.

7 · Keeping those three lists honest

MIRROR_TABLES in build_catalog_mirror.py used to answer two different questions at once — which mirrors exist, and which tables promote’s generic loop walks. Nothing checked either against the database, and the module’s own comment records the cost: casing sat with 148 rows staged, 0 promoted, and no error reported at all.

Cross-checking the allowlist against the live schema and against promote_catalog.py found two more instances. cat_reservoir existed with nothing walking it and no dv_reservoir to promote into. cat_well_perforation was named in the allowlist with no mirror table, so perforations had nowhere to land. Both were invisible for the same reason: three lists that must agree, and no check that they did.

That is now closed rather than patched:

The two questions are separate. MIRROR_TABLES is the generic-promote set; DEDICATED_MIRRORS names the mirrors that must exist but are moved by a dedicated promoter (cat_field, cat_land_tract, cat_boundary, cat_pipeline, cat_log_curve). A table in both would have its rows moved twice.

check_mirror_registry.py compares the allowlist, the mirror tables and the promoters, and additionally checks for column drift — because promote moves rows by column-name intersection, a column added to dv_* after its mirror was built is not an error, it is a column that silently never carries data. That check found eight columns missing from cat_field, including the field’s own coordinates.

It runs as a selftest invariant, so the lists cannot drift again without the test saying so.

One finding survived the fix, and it is worth recording as a decision rather than a defect. With cat_well_perforation present, three subsequent runs over the full corpus promoted zero perforations, and dv_field took zero rows. The mirror was never the blocker: no shape in the petroleum pack emits perforation or field rows. Creating the mirror was necessary and not sufficient. If the source documents contain perforation and reservoir sections, those shapes need writing; if they do not, the mirrors should go. Either way it is now a visible choice instead of a silent zero.

8 · Relationship to PPDM 3.9 as a target

DataView does not replace a PPDM database; it feeds one. The customer’s PPDM 3.9 instance stays the curated store, and a single promote step copies dataview.dv_* into it.

What is proven: dv_well → PPDM well moves on 30 shared columns with no reference seeding required, because PPDM’s well has exactly one NOT NULL column — UWI — and every coded column is nullable, so unresolved codes simply stay empty rather than blocking the insert.

What is not automatic: the child tables. dv_well was modelled closely on PPDM WELL, but the detail tables were modelled loosely, so column names diverge even where the table plainly corresponds. Measured coverage across child pairs runs 12–31%, which is the honest scope of the mapping work. PPDM also decomposes several DataView tables — core samples into description, analysis and remark tables; mud logs into sample, property and resistivity — so some mappings are one-to-many.

9 · Positions and outstanding work

Five things a reader will ask about, and where each stands.

The reference vocabulary is unfinished. Five of nineteen PPDM reference domains are seeded. This is outstanding work, not a design position, and the Standards Manager already has the remaining CSVs.

Need to work on this.

Perforations and reservoirs have mirrors and no shapes. Whether they get shapes depends on whether the source documents carry those sections — a question about the corpus, answered by looking at it.

We are getting perforations now with shapes. We don’t have a reservoir table. Can we make one.

PPDM’s PPDM_GUID and ROW_QUALITY are deliberately absent. DataView is a derivative, not a PPDM implementation, and carrying them would imply a conformance it does not claim.

Multiple schemas are deliberate, and the master well header reference is a shipped capability rather than sample data — see 4.9.

A single version is a choice with a reason: eliminating ambiguity for users who have nobody to adjudicate between competing versions. The cost — DataView cannot represent disagreement between sources — is accepted, and provenance is what carries the answer to “where did this come from” instead.