Energy & Utilities EDW data model
Data Vault 2.0 raw vault for utilities: hubs (customer/account/service_point/meter/asset), links (customer-account, account-service-point, service-point-meter, meter reading), and satellites (customer PII, account, service-point, meter, asset details + account-status effectivity). Hash keys and load metadata are generated by the DDL layer.
Energy & utilities enterprise data warehouse modeled as a Data Vault 2.0 raw vault: customer, account, service-point, meter, and asset hubs; customer-account, account-service-point, and service-point-meter links; a non-historized meter-reading link carrying kWh and demand; and descriptive (PII), effectivity, and status satellites — built to integrate CIS, MDM, AMI, and GIS with full historization for regulatory and GDPR auditability. 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
- 104
- columns
- 14
- relationships
- 6
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Databricks). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs isolate utility business keys
Customers, accounts, service points, and meters are mastered in different systems (CRM, CIS, MDM, AMI); isolating their keys in hubs lets each feed land independently and reconcile later — the integration agility a multi-source utility EDW depends on.
Non-historized meter-reading link
AMI interval reads are immutable time-series events, so the reading link is insert-only and carries kWh and demand directly with no satellite — keeping the highest-volume feed cheap to load.
Effectivity satellite on account status
Connect/suspend/disconnect transitions must be reconstructable for billing disputes and regulatory reporting; an effectivity satellite records the valid period of each account status for as-of queries.
Customer PII isolated in its own satellite
Splitting personal data into a dedicated, masked satellite makes GDPR right-to-erasure and access controls surgical without touching the high-volume operational structures.
Auditability drives Data Vault in utilities
Load_date + record_source on every row give queryable lineage for NERC-CIP and rate-case evidence without scraping source-system logs.
Tables in the Energy & Utilities 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_customer
Grain: one row per unique hub_customer business key
| Column | Type | Keys |
|---|---|---|
| customer_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_account
Grain: one row per unique hub_account business key
| Column | Type | Keys |
|---|---|---|
| account_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_service_point
Grain: one row per unique hub_service_point business key
| Column | Type | Keys |
|---|---|---|
| service_point_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_meter
Grain: one row per unique hub_meter business key
| Column | Type | Keys |
|---|---|---|
| meter_serial | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_asset
Grain: one row per unique hub_asset business key
| Column | Type | Keys |
|---|---|---|
| asset_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_customer_account
Grain: one row per lnk_customer_account relationship
Which customer holds which account.
| Column | Type | Keys |
|---|---|---|
| customer_hk | binary | |
| account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_account_service_point
Grain: one row per lnk_account_service_point relationship
Which service point an account is billed for.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | |
| service_point_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_service_point_meter
Grain: one row per lnk_service_point_meter relationship
Which meter is installed at a service point.
| Column | Type | Keys |
|---|---|---|
| service_point_hk | binary | |
| meter_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_meter_reading
Grain: one row per interval meter reading (non-historized link)
Transactional (non-historized) link — insert-only AMI interval reads.
| Column | Type | Keys |
|---|---|---|
| meter_hk | binary | |
| service_point_hk | binary | |
| reading_id | varchar | BK |
| reading_ts | timestamp | |
| register_type | varchar | |
| kwh | decimal | |
| kw_demand | decimal | |
| reading_quality | varchar | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_customer_details
Grain: one row per customer_hk per change
Customer descriptive history (PII; GDPR-governed).
| Column | Type | Keys |
|---|---|---|
| customer_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| national_id | varchar | PII |
| service_address | varchar | PII |
| customer_class | varchar | |
| communication_pref | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_details
Grain: one row per account_hk per change
Account descriptive history.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | PK |
| load_date | timestamp | PK |
| account_type | varchar | |
| billing_cycle | varchar | |
| currency | varchar | |
| open_date | date | |
| autopay_enrolled | boolean | |
| credit_limit | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_service_point_details
Grain: one row per service_point_hk per change
Service-point descriptive history.
| Column | Type | Keys |
|---|---|---|
| service_point_hk | binary | PK |
| load_date | timestamp | PK |
| commodity | varchar | |
| voltage_class | varchar | |
| connection_type | varchar | |
| premise_type | varchar | |
| grid_segment | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_meter_details
Grain: one row per meter_hk per change
Meter descriptive history.
| Column | Type | Keys |
|---|---|---|
| meter_hk | binary | PK |
| load_date | timestamp | PK |
| manufacturer | varchar | |
| model | varchar | |
| meter_type | varchar | |
| install_date | date | |
| number_of_dials | smallint | |
| multiplier | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_asset_details
Grain: one row per asset_hk per change
Network asset descriptive history.
| Column | Type | Keys |
|---|---|---|
| asset_hk | binary | PK |
| load_date | timestamp | PK |
| asset_name | varchar | |
| asset_class | varchar | |
| rated_capacity | decimal | |
| commission_date | date | |
| condition_rating | varchar | |
| ownership | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_status
Grain: one row per account per status effectivity change
Effectivity satellite tracking the valid period of each account status.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | PK |
| load_date | timestamp | PK |
| account_status | varchar | |
| dunning_stage | varchar | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_rate_schedule
Grain: one row per tariff rate schedule
| Column | Type | Keys |
|---|---|---|
| rate_code | varchar | BK |
| rate_name | varchar | |
| customer_class | varchar | |
| time_of_use_flag | boolean | |
| energy_charge_per_kwh | decimal |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_customer_account.customer_hk → hub_customer (many-to-one)
- lnk_customer_account.account_hk → hub_account (many-to-one)
- lnk_account_service_point.account_hk → hub_account (many-to-one)
- lnk_account_service_point.service_point_hk → hub_service_point (many-to-one)
- lnk_service_point_meter.service_point_hk → hub_service_point (many-to-one)
- lnk_service_point_meter.meter_hk → hub_meter (many-to-one)
- lnk_meter_reading.meter_hk → hub_meter (many-to-one)
- lnk_meter_reading.service_point_hk → hub_service_point (many-to-one)
- sat_customer_details.customer_hk → hub_customer (many-to-one)
- sat_account_details.account_hk → hub_account (many-to-one)
- sat_service_point_details.service_point_hk → hub_service_point (many-to-one)
- sat_meter_details.meter_hk → hub_meter (many-to-one)
- sat_asset_details.asset_hk → hub_asset (many-to-one)
- sat_account_status.account_hk → hub_account (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.