Construction EDW data model
Data Vault 2.0 raw vault for construction: hubs (project/contractor/site/contract/work_package), links (project-contractor, project-site, contract-project, work-package cost), and satellites (project, contractor, site, financials details + contract-terms and work-package-progress effectivity satellites). Hash keys and load metadata are generated by the DDL layer.
Construction enterprise data warehouse modeled as a Data Vault 2.0 raw vault: project, contractor, site, contract, and work-package hubs; project-contractor / project-site / contract-project links; a non-historized work-package-cost link carrying committed and actual cost; and descriptive, effectivity (contract terms, work-package progress), and earned-value financials satellites — built to integrate ERP, project controls, field, and contract-management systems with full historization for cost control and change-order audit. 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
- 102
- 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 construction business keys
Projects, contracts, contractors, and sites are mastered in separate ERP, contract-management, and project-controls systems; isolating their keys in hubs lets each feed land independently as scope, subcontracts, and change orders evolve over a multi-year build.
Non-historized work-package-cost link
Cost postings against a work package are immutable accounting events, so the cost link is insert-only and carries committed and actual cost directly with no satellite.
Effectivity satellites on contract terms and progress
Change orders revise contract terms and work-package progress moves through states over time; effectivity satellites record the valid period of each version for as-of cost-to-complete and claims defense.
Financials split into its own satellite
Earned-value metrics (EAC, CPI, SPI) refresh every reporting cycle, far faster than project master data; a separate high-rate-of-change satellite keeps cost history compact and loads efficient.
Auditability drives Data Vault in construction
Load_date + record_source on every row give queryable lineage for change-order audits, progress-claim disputes, and ISO-19650 / SOX cost reporting without scraping ERP and project-controls logs.
Tables in the Construction 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_project
Grain: one row per unique hub_project business key
| Column | Type | Keys |
|---|---|---|
| project_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_contractor
Grain: one row per unique hub_contractor business key
| Column | Type | Keys |
|---|---|---|
| contractor_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_site
Grain: one row per unique hub_site business key
| Column | Type | Keys |
|---|---|---|
| site_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_contract
Grain: one row per unique hub_contract business key
| Column | Type | Keys |
|---|---|---|
| contract_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_work_package
Grain: one row per unique hub_work_package business key
| Column | Type | Keys |
|---|---|---|
| work_package_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_project_contractor
Grain: one row per lnk_project_contractor relationship
Which contractor is engaged on which project.
| Column | Type | Keys |
|---|---|---|
| project_hk | binary | |
| contractor_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_project_site
Grain: one row per lnk_project_site relationship
Which site a project is executed at.
| Column | Type | Keys |
|---|---|---|
| project_hk | binary | |
| site_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_contract_project
Grain: one row per lnk_contract_project relationship
Which project a contract governs.
| Column | Type | Keys |
|---|---|---|
| contract_hk | binary | |
| project_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_work_package_cost
Grain: one row per work-package cost posting (non-historized link)
Transactional (non-historized) link — insert-only cost-posting events.
| Column | Type | Keys |
|---|---|---|
| work_package_hk | binary | |
| project_hk | binary | |
| cost_event_id | varchar | BK |
| cost_ts | timestamp | |
| cost_type | varchar | |
| committed_cost | decimal | |
| actual_cost | decimal | |
| quantity | decimal | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_project_details
Grain: one row per project_hk per change
Project descriptive history.
| Column | Type | Keys |
|---|---|---|
| project_hk | binary | PK |
| load_date | timestamp | PK |
| project_name | varchar | |
| project_type | varchar | |
| project_manager | varchar | PII |
| planned_start | date | |
| planned_finish | date | |
| contract_value | decimal | |
| currency | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_contractor_details
Grain: one row per contractor_hk per change
Contractor descriptive history.
| Column | Type | Keys |
|---|---|---|
| contractor_hk | binary | PK |
| load_date | timestamp | PK |
| contractor_name | varchar | |
| trade | varchar | |
| license_number | varchar | |
| prequalification_status | varchar | |
| safety_rating | varchar | |
| contact_email | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_site_details
Grain: one row per site_hk per change
Site descriptive history.
| Column | Type | Keys |
|---|---|---|
| site_hk | binary | PK |
| load_date | timestamp | PK |
| site_name | varchar | |
| address | varchar | |
| city | varchar | |
| country | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| zoning_type | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_contract_terms
Grain: one row per contract per terms effectivity change
Effectivity satellite tracking the valid period of each contract term set.
| Column | Type | Keys |
|---|---|---|
| contract_hk | binary | PK |
| load_date | timestamp | PK |
| contract_type | varchar | |
| contract_amount | decimal | |
| retention_pct | decimal | |
| payment_terms | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_work_package_progress
Grain: one row per work package per progress effectivity change
Effectivity satellite tracking the valid period of each work-package progress state.
| Column | Type | Keys |
|---|---|---|
| work_package_hk | binary | PK |
| load_date | timestamp | PK |
| work_package_name | varchar | |
| pct_complete | decimal | |
| planned_quantity | decimal | |
| earned_quantity | decimal | |
| status | varchar | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_project_financials
Grain: one row per project per financials reporting cycle (high-rate-of-change satellite)
Project earned-value financials satellite split out for its reporting cadence.
| Column | Type | Keys |
|---|---|---|
| project_hk | binary | PK |
| load_date | timestamp | PK |
| budget_at_completion | decimal | |
| estimate_at_completion | decimal | |
| earned_value | decimal | |
| actual_cost_to_date | decimal | |
| cost_performance_index | decimal | |
| schedule_performance_index | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_project_contractor.project_hk → hub_project (many-to-one)
- lnk_project_contractor.contractor_hk → hub_contractor (many-to-one)
- lnk_project_site.project_hk → hub_project (many-to-one)
- lnk_project_site.site_hk → hub_site (many-to-one)
- lnk_contract_project.contract_hk → hub_contract (many-to-one)
- lnk_contract_project.project_hk → hub_project (many-to-one)
- lnk_work_package_cost.work_package_hk → hub_work_package (many-to-one)
- lnk_work_package_cost.project_hk → hub_project (many-to-one)
- sat_project_details.project_hk → hub_project (many-to-one)
- sat_contractor_details.contractor_hk → hub_contractor (many-to-one)
- sat_site_details.site_hk → hub_site (many-to-one)
- sat_contract_terms.contract_hk → hub_contract (many-to-one)
- sat_work_package_progress.work_package_hk → hub_work_package (many-to-one)
- sat_project_financials.project_hk → hub_project (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.