Mining EDW data model
Data Vault 2.0 raw vault for mining: hubs (mine_site/equipment/material/shipment/operator), links (site-equipment, site-material, production, material-shipment), and satellites (site, equipment, material grade, shipment, operator details + an equipment-status effectivity satellite). Hash keys and load metadata are generated by the DDL layer.
Mining enterprise data warehouse modeled as a Data Vault 2.0 raw vault: mine-site, equipment, material, shipment, and operator hubs; site-equipment / site-material links; a non-historized production link carrying tonnes and ore grade; a material-shipment link; and descriptive, grade, and effectivity satellites — built to integrate fleet management, LIMS, dispatch, and logistics with full historization for grade reconciliation, royalty, and ESG 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.
- 15
- tables
- 97
- columns
- 15
- relationships
- 3
- 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 mining business keys
Sites, equipment, material, and shipments are mastered in separate fleet, LIMS, and logistics systems; isolating their keys in hubs lets each feed land independently and supports the frequent equipment re-deployment typical across mine sites.
Non-historized production link
Production events (tonnes mined, grade) are immutable measurements, so the production link is insert-only and carries tonnage and grade directly with no satellite — keeping high-frequency dispatch data cheap to load.
Grade isolated from material master
Assay grade arrives from the lab on a different cadence and source than material master data; a separate satellite lets grade revisions and re-assays historize independently for reconciliation.
Effectivity satellite on equipment status
Haul trucks and excavators move between available / in-use / maintenance states constantly; an effectivity satellite records the valid period of each status for as-of utilization and availability KPIs.
Auditability drives Data Vault in mining
Load_date + record_source on every row give queryable lineage for grade-reconciliation audits, royalty calculations, and GISTM / ISO-14001 ESG reporting without scraping dispatch logs.
Tables in the Mining 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_mine_site
Grain: one row per unique hub_mine_site business key
| Column | Type | Keys |
|---|---|---|
| site_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_equipment
Grain: one row per unique hub_equipment business key
| Column | Type | Keys |
|---|---|---|
| equipment_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_material
Grain: one row per unique hub_material business key
| Column | Type | Keys |
|---|---|---|
| material_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_shipment
Grain: one row per unique hub_shipment business key
| Column | Type | Keys |
|---|---|---|
| shipment_number | 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_site_equipment
Grain: one row per lnk_site_equipment relationship
Which equipment is assigned to which mine site.
| Column | Type | Keys |
|---|---|---|
| mine_site_hk | binary | |
| equipment_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_site_material
Grain: one row per lnk_site_material relationship
Which material is mined at which site.
| Column | Type | Keys |
|---|---|---|
| mine_site_hk | binary | |
| material_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_production
Grain: one row per production event (non-historized link)
Transactional (non-historized) link — insert-only production tonnage and grade.
| Column | Type | Keys |
|---|---|---|
| mine_site_hk | binary | |
| material_hk | binary | |
| production_id | varchar | BK |
| production_ts | timestamp | |
| equipment_hk | binary | |
| tonnes | decimal | |
| ore_grade | decimal | |
| strip_ratio | decimal | |
| load_date | timestamp | |
| record_source | varchar |
lnk_material_shipment
Grain: one row per lnk_material_shipment relationship
Which material is loaded onto which shipment.
| Column | Type | Keys |
|---|---|---|
| material_hk | binary | |
| shipment_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_site_details
Grain: one row per mine_site_hk per change
Mine-site descriptive history.
| Column | Type | Keys |
|---|---|---|
| mine_site_hk | binary | PK |
| load_date | timestamp | PK |
| site_name | varchar | |
| mine_type | varchar | |
| commodity | varchar | |
| country | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| permit_number | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_equipment_details
Grain: one row per equipment_hk per change
Equipment descriptive history.
| Column | Type | Keys |
|---|---|---|
| equipment_hk | binary | PK |
| load_date | timestamp | PK |
| equipment_name | varchar | |
| equipment_type | varchar | |
| manufacturer | varchar | |
| model | varchar | |
| rated_payload_tonnes | decimal | |
| commission_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_material_grade
Grain: one row per material_hk per change
Material grade / assay descriptive history.
| Column | Type | Keys |
|---|---|---|
| material_hk | binary | PK |
| load_date | timestamp | PK |
| material_name | varchar | |
| commodity | varchar | |
| head_grade | decimal | |
| moisture_pct | decimal | |
| contaminant_pct | decimal | |
| assay_method | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_shipment_details
Grain: one row per shipment_hk per change
Shipment descriptive history.
| Column | Type | Keys |
|---|---|---|
| shipment_hk | binary | PK |
| load_date | timestamp | PK |
| carrier | varchar | |
| transport_mode | varchar | |
| destination_port | varchar | |
| net_tonnes | decimal | |
| freight_cost | decimal | |
| dispatch_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_operator_details
Grain: one row per operator_hk per change
Operator descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| operator_hk | binary | PK |
| load_date | timestamp | PK |
| operator_name | varchar | PII |
| employee_number | varchar | PII |
| crew | varchar | |
| certification_level | varchar | |
| contact_email | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_equipment_status
Grain: one row per equipment per status effectivity change
Effectivity satellite tracking the valid period of each equipment status.
| Column | Type | Keys |
|---|---|---|
| equipment_hk | binary | PK |
| load_date | timestamp | PK |
| operational_status | varchar | |
| location_zone | varchar | |
| engine_hours | decimal | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_site_equipment.mine_site_hk → hub_mine_site (many-to-one)
- lnk_site_equipment.equipment_hk → hub_equipment (many-to-one)
- lnk_site_material.mine_site_hk → hub_mine_site (many-to-one)
- lnk_site_material.material_hk → hub_material (many-to-one)
- lnk_production.mine_site_hk → hub_mine_site (many-to-one)
- lnk_production.material_hk → hub_material (many-to-one)
- lnk_production.equipment_hk → hub_equipment (many-to-one)
- lnk_material_shipment.material_hk → hub_material (many-to-one)
- lnk_material_shipment.shipment_hk → hub_shipment (many-to-one)
- sat_site_details.mine_site_hk → hub_mine_site (many-to-one)
- sat_equipment_details.equipment_hk → hub_equipment (many-to-one)
- sat_material_grade.material_hk → hub_material (many-to-one)
- sat_shipment_details.shipment_hk → hub_shipment (many-to-one)
- sat_operator_details.operator_hk → hub_operator (many-to-one)
- sat_equipment_status.equipment_hk → hub_equipment (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.