Telecom EDW data model
Data Vault 2.0 raw vault for telecom: hubs (subscriber/account/device/plan/network-element), links among them, a transactional CDR link, and satellites for subscriber, account, device, plan, and network-element history plus an account-plan effectivity satellite.
Telecommunications enterprise data warehouse as a Data Vault 2.0 raw vault: subscriber, account, device, plan, and network-element hubs; the relationships among them; a non-historized call-detail-record (CDR) link for high-volume usage events; and descriptive satellites — built to integrate CRM, billing, and network systems and ingest billions of immutable CDRs. 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
- 88
- columns
- 15
- relationships
- 4
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Non-historized CDR link for usage at scale
CDRs are immutable, extremely high volume, and need no history; a non-historized (insert-only) link carrying the usage measures ingests billions of events without satellite churn.
Subscriber, account, and device as separate hubs
A subscriber can change devices and move between accounts; independent hubs joined by links let those relationships evolve without rewriting the subscriber record.
Network element as a hub
Tying CDRs to a network-element hub enables network-performance and coverage analytics from the same vault that feeds billing.
Tables in the Telecom 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_subscriber
Grain: one row per unique hub_subscriber business key
| Column | Type | Keys |
|---|---|---|
| subscriber_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_device
Grain: one row per unique hub_device business key
Device by IMEI.
| Column | Type | Keys |
|---|---|---|
| imei | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_plan
Grain: one row per unique hub_plan business key
| Column | Type | Keys |
|---|---|---|
| plan_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_network_element
Grain: one row per unique hub_network_element business key
Network element / cell tower.
| Column | Type | Keys |
|---|---|---|
| cell_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_subscriber_account
Grain: one row per lnk_subscriber_account relationship
Subscribers on a billing account.
| Column | Type | Keys |
|---|---|---|
| subscriber_hk | binary | |
| account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_account_plan
Grain: one row per lnk_account_plan relationship
Rate plans subscribed on an account.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | |
| plan_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_subscriber_device
Grain: one row per lnk_subscriber_device relationship
Device a subscriber uses.
| Column | Type | Keys |
|---|---|---|
| subscriber_hk | binary | |
| device_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_cdr
Grain: one row per call-detail record (non-historized link)
Transactional (non-historized) CDR link — insert-only usage events.
| Column | Type | Keys |
|---|---|---|
| subscriber_hk | binary | |
| device_hk | binary | |
| network_element_hk | binary | |
| cdr_id | varchar | BK |
| event_ts | timestamp | |
| event_type | varchar | |
| duration_seconds | integer | |
| bytes_used | bigint | |
| rated_amount | decimal | |
| is_roaming | boolean | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_subscriber_details
Grain: one row per subscriber_hk per change
Subscriber descriptive history.
| Column | Type | Keys |
|---|---|---|
| subscriber_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone_number | varchar | PII |
| subscriber_status | varchar | |
| activation_date | date | |
| 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 | |
| account_status | varchar | |
| credit_limit | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_device_details
Grain: one row per device_hk per change
Device descriptive history.
| Column | Type | Keys |
|---|---|---|
| device_hk | binary | PK |
| load_date | timestamp | PK |
| manufacturer | varchar | |
| model | varchar | |
| os_version | varchar | |
| is_5g_capable | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_plan_details
Grain: one row per plan_hk per change
Rate-plan descriptive history.
| Column | Type | Keys |
|---|---|---|
| plan_hk | binary | PK |
| load_date | timestamp | PK |
| plan_name | varchar | |
| plan_type | varchar | |
| monthly_fee | decimal | |
| data_allowance_gb | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_network_element_details
Grain: one row per network_element_hk per change
Network-element descriptive history.
| Column | Type | Keys |
|---|---|---|
| network_element_hk | binary | PK |
| load_date | timestamp | PK |
| site_name | varchar | |
| technology | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_plan_effectivity
Grain: one row per account-plan relationship per effectivity change
Effectivity satellite tracking plan changes on an account.
| Column | Type | Keys |
|---|---|---|
| account_plan_hk | binary | PK |
| load_date | timestamp | PK |
| effective_from | date | |
| effective_to | date | |
| 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_subscriber_account.subscriber_hk → hub_subscriber (many-to-one)
- lnk_subscriber_account.account_hk → hub_account (many-to-one)
- lnk_account_plan.account_hk → hub_account (many-to-one)
- lnk_account_plan.plan_hk → hub_plan (many-to-one)
- lnk_subscriber_device.subscriber_hk → hub_subscriber (many-to-one)
- lnk_subscriber_device.device_hk → hub_device (many-to-one)
- lnk_cdr.subscriber_hk → hub_subscriber (many-to-one)
- lnk_cdr.device_hk → hub_device (many-to-one)
- lnk_cdr.network_element_hk → hub_network_element (many-to-one)
- sat_subscriber_details.subscriber_hk → hub_subscriber (many-to-one)
- sat_account_details.account_hk → hub_account (many-to-one)
- sat_device_details.device_hk → hub_device (many-to-one)
- sat_plan_details.plan_hk → hub_plan (many-to-one)
- sat_network_element_details.network_element_hk → hub_network_element (many-to-one)
- sat_account_plan_effectivity.account_plan_hk → lnk_account_plan (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.