Oil & Gas Upstream EDW data model
Data Vault 2.0 raw vault for upstream oil & gas: hubs (well/field/lease/facility/operator), links (well-field, well-lease, well-facility, production), and satellites (well, field, facility, operator, well-status details + a lease-terms effectivity satellite). Hash keys and load metadata are generated by the DDL layer.
Upstream oil & gas enterprise data warehouse modeled as a Data Vault 2.0 raw vault: well, field, lease, facility, and operator hubs; well-field / well-lease / well-facility links; a non-historized well-production link carrying oil bbl and gas mcf; and descriptive, effectivity, and status satellites — built to integrate SCADA, production accounting, land, and partner systems with full historization for joint-venture accounting and regulatory volume reporting. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 99
- columns
- 14
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs isolate upstream business keys
Wells (API numbers), leases, and operators are mastered in separate land, SCADA, and partner systems; isolating their keys in hubs lets each source attach independently — essential when acreage and operatorship change through deals.
Non-historized well-production link
Allocated daily production volumes are immutable accounting facts, so the production link is insert-only and carries oil, gas, and water volumes directly with no satellite.
Effectivity satellite on lease terms
Royalty rates and net-revenue interests change with assignments and amendments; an effectivity satellite records the valid period of each term set for as-of revenue-distribution calculations.
Well status split into its own satellite
Operational status (producing / shut-in) changes far more frequently than well master data; a separate high-rate-of-change satellite keeps history compact and loads efficient.
Auditability drives Data Vault in oil & gas
Load_date + record_source on every row provide queryable lineage for joint-venture audits and EPA-GHGRP / regulatory volume reporting without scraping production-accounting logs.
Tables in the Oil & Gas Upstream EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (5)
hub_well
Grain: one row per unique hub_well business key
| Column | Type | Keys |
|---|---|---|
| api_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_field
Grain: one row per unique hub_field business key
| Column | Type | Keys |
|---|---|---|
| field_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_lease
Grain: one row per unique hub_lease business key
| Column | Type | Keys |
|---|---|---|
| lease_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_facility
Grain: one row per unique hub_facility business key
| Column | Type | Keys |
|---|---|---|
| facility_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_operator
Grain: one row per unique hub_operator business key
| Column | Type | Keys |
|---|---|---|
| operator_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_well_field
Grain: one row per lnk_well_field relationship
Which producing field a well belongs to.
| Column | Type | Keys |
|---|---|---|
| well_hk | binary | |
| field_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_well_lease
Grain: one row per lnk_well_lease relationship
Which lease a well is drilled under.
| Column | Type | Keys |
|---|---|---|
| well_hk | binary | |
| lease_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_well_facility
Grain: one row per lnk_well_facility relationship
Which surface facility a well flows into.
| Column | Type | Keys |
|---|---|---|
| well_hk | binary | |
| facility_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_well_production
Grain: one row per well per production day (non-historized link)
Transactional (non-historized) link — insert-only allocated production volumes.
| Column | Type | Keys |
|---|---|---|
| well_hk | binary | |
| facility_hk | binary | |
| production_id | varchar | BK |
| production_date | date | |
| oil_bbl | decimal | |
| gas_mcf | decimal | |
| water_bbl | decimal | |
| downtime_hours | decimal | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_well_details
Grain: one row per well_hk per change
Well descriptive history.
| Column | Type | Keys |
|---|---|---|
| well_hk | binary | PK |
| load_date | timestamp | PK |
| well_name | varchar | |
| well_type | varchar | |
| spud_date | date | |
| total_depth_ft | decimal | |
| surface_latitude | decimal | |
| surface_longitude | decimal | |
| completion_type | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_field_details
Grain: one row per field_hk per change
Field descriptive history.
| Column | Type | Keys |
|---|---|---|
| field_hk | binary | PK |
| load_date | timestamp | PK |
| field_name | varchar | |
| basin | varchar | |
| reservoir | varchar | |
| discovery_year | smallint | |
| estimated_reserves_mmboe | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_lease_terms
Grain: one row per lease per terms effectivity change
Effectivity satellite tracking the valid period of each lease term set.
| Column | Type | Keys |
|---|---|---|
| lease_hk | binary | PK |
| load_date | timestamp | PK |
| lessor_name | varchar | PII |
| royalty_rate | decimal | |
| net_revenue_interest | decimal | |
| primary_term_years | smallint | |
| bonus_payment | decimal | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_facility_details
Grain: one row per facility_hk per change
Surface-facility descriptive history.
| Column | Type | Keys |
|---|---|---|
| facility_hk | binary | PK |
| load_date | timestamp | PK |
| facility_name | varchar | |
| facility_type | varchar | |
| processing_capacity | decimal | |
| commission_date | date | |
| operatorship | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_operator_details
Grain: one row per operator_hk per change
Operator/partner descriptive history.
| Column | Type | Keys |
|---|---|---|
| operator_hk | binary | PK |
| load_date | timestamp | PK |
| operator_name | varchar | |
| operator_type | varchar | |
| working_interest | decimal | |
| contact_email | varchar | PII |
| regulatory_id | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_well_status
Grain: one row per well per status change (high-rate-of-change satellite)
Well operational status satellite.
| Column | Type | Keys |
|---|---|---|
| well_hk | binary | PK |
| load_date | timestamp | PK |
| well_status | varchar | |
| artificial_lift | varchar | |
| choke_size | decimal | |
| last_test_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_product_type
Grain: one row per produced product type
| Column | Type | Keys |
|---|---|---|
| product_code | varchar | BK |
| product_name | varchar | |
| phase | varchar | |
| standard_uom | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_well_field.well_hk → hub_well (many-to-one)
- lnk_well_field.field_hk → hub_field (many-to-one)
- lnk_well_lease.well_hk → hub_well (many-to-one)
- lnk_well_lease.lease_hk → hub_lease (many-to-one)
- lnk_well_facility.well_hk → hub_well (many-to-one)
- lnk_well_facility.facility_hk → hub_facility (many-to-one)
- lnk_well_production.well_hk → hub_well (many-to-one)
- lnk_well_production.facility_hk → hub_facility (many-to-one)
- sat_well_details.well_hk → hub_well (many-to-one)
- sat_field_details.field_hk → hub_field (many-to-one)
- sat_lease_terms.lease_hk → hub_lease (many-to-one)
- sat_facility_details.facility_hk → hub_facility (many-to-one)
- sat_operator_details.operator_hk → hub_operator (many-to-one)
- sat_well_status.well_hk → hub_well (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.