Manufacturing EDW data model
Data Vault 2.0 raw vault for manufacturing: hubs (product/work_order/machine/plant/material), links (wo-product, wo-machine, machine-plant, material consumption), and satellites (product, work-order status, machine, plant, material details + production output). Hash keys and load metadata are generated by the DDL layer.
Discrete-manufacturing enterprise data warehouse modeled as a Data Vault 2.0 raw vault: product, work-order, machine, plant, and material hubs; work-order-to-product / machine and machine-to-plant links; a non-historized material-consumption link carrying quantity and cost; and descriptive plus effectivity satellites — built to integrate MES, ERP, and SCADA with full historization for genealogy, recall, and quality traceability. 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
- 103
- columns
- 14
- relationships
- 1
- 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 manufacturing business keys
Product codes, work-order numbers, and machine ids arrive from different systems (PLM, MES, SCADA) at different times; isolating them in hubs lets each source attach independently without reshaping the model — the core agility Data Vault gives a manufacturing EDW.
Non-historized material-consumption link
Material issues against a work order are immutable shop-floor events, so the consumption link is insert-only and carries quantity, scrap, and cost directly with no satellite.
Production output split into its own satellite
OEE and yield readings change far faster than machine master data; a separate high-rate-of-change satellite on the wo-machine link keeps history compact and loads efficient.
Effectivity satellite on work-order status
Work orders move through release/in-progress/complete states; an effectivity satellite records the valid period of each status for as-of WIP and throughput reporting.
Traceability drives Data Vault in manufacturing
Load_date + record_source on every row give a queryable genealogy and audit trail for recalls and IATF-16949 / ISO-9001 quality lineage without scraping MES logs.
Tables in the Manufacturing 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_product
Grain: one row per unique hub_product business key
| Column | Type | Keys |
|---|---|---|
| product_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_work_order
Grain: one row per unique hub_work_order business key
| Column | Type | Keys |
|---|---|---|
| work_order_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_machine
Grain: one row per unique hub_machine business key
| Column | Type | Keys |
|---|---|---|
| machine_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_plant
Grain: one row per unique hub_plant business key
| Column | Type | Keys |
|---|---|---|
| plant_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_material
Grain: one row per unique hub_material business key
| Column | Type | Keys |
|---|---|---|
| material_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_wo_product
Grain: one row per lnk_wo_product relationship
Which product a work order produces.
| Column | Type | Keys |
|---|---|---|
| work_order_hk | binary | |
| product_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_wo_machine
Grain: one row per lnk_wo_machine relationship
Which machine a work order is executed on.
| Column | Type | Keys |
|---|---|---|
| work_order_hk | binary | |
| machine_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_machine_plant
Grain: one row per lnk_machine_plant relationship
Which plant a machine is installed in.
| Column | Type | Keys |
|---|---|---|
| machine_hk | binary | |
| plant_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_wo_material_consumption
Grain: one row per material issue against a work order (non-historized link)
Transactional (non-historized) link — insert-only material consumption events.
| Column | Type | Keys |
|---|---|---|
| work_order_hk | binary | |
| material_hk | binary | |
| consumption_id | varchar | BK |
| consumption_ts | timestamp | |
| uom_code | varchar | |
| quantity_consumed | decimal | |
| scrap_quantity | decimal | |
| material_cost | decimal | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_product_details
Grain: one row per product_hk per change
Product descriptive history.
| Column | Type | Keys |
|---|---|---|
| product_hk | binary | PK |
| load_date | timestamp | PK |
| product_name | varchar | |
| product_family | varchar | |
| product_category | varchar | |
| revision | varchar | |
| base_uom | varchar | |
| standard_cost | decimal | |
| lifecycle_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_work_order_status
Grain: one row per work order per status effectivity change
Effectivity satellite tracking the valid period of each work-order status.
| Column | Type | Keys |
|---|---|---|
| work_order_hk | binary | PK |
| load_date | timestamp | PK |
| order_status | varchar | |
| priority | varchar | |
| planned_quantity | decimal | |
| completed_quantity | decimal | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_machine_details
Grain: one row per machine_hk per change
Machine descriptive history.
| Column | Type | Keys |
|---|---|---|
| machine_hk | binary | PK |
| load_date | timestamp | PK |
| machine_name | varchar | |
| machine_type | varchar | |
| manufacturer | varchar | |
| model_number | varchar | |
| serial_number | varchar | |
| install_date | date | |
| rated_capacity | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_plant_details
Grain: one row per plant_hk per change
Plant descriptive history.
| Column | Type | Keys |
|---|---|---|
| plant_hk | binary | PK |
| load_date | timestamp | PK |
| plant_name | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| plant_manager | varchar | PII |
| shift_pattern | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_material_details
Grain: one row per material_hk per change
Material descriptive history.
| Column | Type | Keys |
|---|---|---|
| material_hk | binary | PK |
| load_date | timestamp | PK |
| material_name | varchar | |
| material_group | varchar | |
| material_type | varchar | |
| base_uom | varchar | |
| standard_price | decimal | |
| hazardous_flag | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_production_output
Grain: one row per work-order/machine run per output reading (high-rate-of-change satellite)
Production output satellite on the work-order/machine link.
| Column | Type | Keys |
|---|---|---|
| wo_machine_hk | binary | PK |
| load_date | timestamp | PK |
| good_quantity | decimal | |
| reject_quantity | decimal | |
| cycle_time_seconds | decimal | |
| downtime_minutes | decimal | |
| oee_pct | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_uom
Grain: one row per unit of measure
| Column | Type | Keys |
|---|---|---|
| uom_code | varchar | BK |
| uom_name | varchar | |
| dimension | varchar | |
| conversion_factor | decimal |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_wo_product.work_order_hk → hub_work_order (many-to-one)
- lnk_wo_product.product_hk → hub_product (many-to-one)
- lnk_wo_machine.work_order_hk → hub_work_order (many-to-one)
- lnk_wo_machine.machine_hk → hub_machine (many-to-one)
- lnk_machine_plant.machine_hk → hub_machine (many-to-one)
- lnk_machine_plant.plant_hk → hub_plant (many-to-one)
- lnk_wo_material_consumption.work_order_hk → hub_work_order (many-to-one)
- lnk_wo_material_consumption.material_hk → hub_material (many-to-one)
- sat_product_details.product_hk → hub_product (many-to-one)
- sat_work_order_status.work_order_hk → hub_work_order (many-to-one)
- sat_machine_details.machine_hk → hub_machine (many-to-one)
- sat_plant_details.plant_hk → hub_plant (many-to-one)
- sat_material_details.material_hk → hub_material (many-to-one)
- sat_production_output.wo_machine_hk → lnk_wo_machine (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.