Telecom Network Connectivity Warehouse

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
L6

Problem

We're a telecom provider building a new data warehouse. We need to model our network infrastructure: towers, switches, fiber links, and the connections between them. We also need to track outages and capacity utilization. Can you design this?

One device goes down. The ripple keeps going.

Worked solution and explanation

What this really is

Strip the telecom costume and this is a graph-modeling problem in an incident-reporting hat. The real question: can you store a network as nodes and edges, not a tree, while keeping utilization at two grains so forensics and dashboards don't fight over one table? Anyone lists towers and switches. The trap is connections: model a neighbor as a parent_id on network_elements and you've built a tree that can't express a ring or a redundant path, so blast-radius queries silently return the wrong answer. The second trap is a mutable utilization_pct column on network_elements, which erases every reading the moment the next sample lands.

`connections` is the self-referential edge table; `outage_affected_elements` is the many-to-many junction whose grain is (`outage_id`, `element_id`); `utilization_daily` is a deterministic roll-up of `utilization_raw` at (`element_id`, `usage_date`) grain
  • regions: region_id BIGINT PK, region_name TEXT, country TEXT
  • network_elements: element_id BIGINT PK, element_type TEXT, region_id BIGINT FK, installed_at TIMESTAMP, capacity_gbps DECIMAL
  • connections: connection_id BIGINT PK, source_element_id BIGINT FK, target_element_id BIGINT FK, link_type TEXT, bandwidth_gbps DECIMAL
  • outages: outage_id BIGINT PK, started_at TIMESTAMP, ended_at TIMESTAMP, severity TEXT, root_cause TEXT
  • outage_affected_elements: outage_id BIGINT FK, element_id BIGINT FK, impact_pct DECIMAL
  • utilization_raw: sample_id BIGINT PK, element_id BIGINT FK, sampled_at TIMESTAMP, bytes_in BIGINT, bytes_out BIGINT
  • utilization_daily: usage_date DATE PK, element_id BIGINT FK, avg_utilization_pct DECIMAL, peak_utilization_pct DECIMAL
  • network_elements.region_id references regions.region_id
  • connections.source_element_id references network_elements.element_id
  • connections.target_element_id references network_elements.element_id
  • outage_affected_elements.outage_id references outages.outage_id
  • outage_affected_elements.element_id references network_elements.element_id
  • utilization_raw.element_id references network_elements.element_id
  • utilization_daily.element_id references network_elements.element_id

Step 1: Split nodes from edges

network_elements is the node table; connections is the edge table with source_element_id and target_element_id both keying element_id. link_type carries directionality, so a one-way fiber run and a bidirectional switch link stay distinguishable. This shape survives rings and dual-homed paths.

Step 2: Keep telemetry at two grains

The grain of utilization_raw is one row per (element_id, sampled_at); utilization_daily is one row per (element_id, usage_date). Both are additive facts and the daily table is fully rebuildable from raw, so a corrupt roll-up is a recompute, not data loss. Dashboards hit daily thousands of times a day; raw only during an investigation.

Step 3: Model the outage as an event, then bridge it

An outages row has a lifecycle (started_at, ended_at, severity, root_cause), and outage_affected_elements resolves the many-to-many between one incident and the elements it hit, with impact_pct as the edge attribute. Its composite grain (outage_id, element_id) makes weighted downtime per element computable, and network_elements.region_id conforms every incident to a region for one clean roll-up path.

Weighted downtime minutes per region per month
SELECT
    r.region_name,
    DATE_TRUNC('month', o.started_at) AS month,
    SUM(EXTRACT(EPOCH FROM (o.ended_at - o.started_at)) / 60 * oae.impact_pct / 100) AS weighted_downtime_minutes,
    COUNT(DISTINCT o.outage_id) AS incidents,
    AVG(ud.peak_utilization_pct) AS avg_peak_utilization
FROM outages o
JOIN outage_affected_elements oae ON oae.outage_id = o.outage_id
JOIN network_elements e ON e.element_id = oae.element_id
JOIN regions r ON r.region_id = e.region_id
LEFT JOIN utilization_daily ud
    ON ud.element_id = e.element_id
   AND ud.usage_date = o.started_at::date
WHERE o.started_at >= '2025-01-01'
GROUP BY r.region_name, month
ORDER BY weighted_downtime_minutes DESC

One join path from outages through the junction to regions, with utilization_daily left-joined so a missing sample never drops the incident

One relational warehouse
Topology, incidents and dual-grain telemetry share one schema, so blast-radius, capacity and forensics are all reachable from connections, utilization_daily and outage_affected_elements with plain joins. Cost: raw-sample storage, an ETL job for the roll-up, and a composite index on (element_id, sampled_at).
Graph store plus time-series store
Native traversal in a graph engine over connections, optimized ingestion in a time-series store for utilization_raw. Cost: two systems to run, cross-system stitching to tie an outage to its samples, and two query languages for one incident review.

Common follow-up questions