The Vital Few

A medium 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: medium
Seniority: L5

Problem

We ingest about 2TB of raw application logs into S3 every day, where debug and info lines vastly outnumber the errors analysts actually care about, so the high-value error records need to live apart from the bulk firehose. Analysts follow a single request as it crosses services and stitch a user's activity together within a session, starting from either an ordinary log line or an error, so the request and session identifiers have to ride on both. Errors are grouped by a canonical category instead of raw message text, and error rates get sliced by the team that owns each service and its tier, so keep that ownership one hop away.

Two terabytes a day, and the lines that matter are a rounding error in the noise.

Worked solution and explanation

Why this problem exists in real interviews

This is cardinality skew hiding inside a schema design problem. The skill being probed: recognizing that a 1000:1 ratio between debug and error logs demands physical separation, and that request_id and session_id are the load-bearing correlation keys that let analysts jump between the two facts. A unified logs table scans the entire firehose on every error query. Get the correlation keys wrong and analysts cannot stitch a request across services or slice errors by the team that owns them.

Break down the requirements

Step 1: Split by cardinality

log_events absorbs the firehose; error_events is a narrower, higher-value fact. Analysts who care about errors never pay the cost of scanning debug logs.

Step 2: Dimensionalize services

services is a conformed dimension with team, tier, and criticality. Every fact FK points to it so that ownership is one join away.

Step 3: Classify errors via a dimension

error_types maps raw message patterns to canonical categories. error_events.error_type_id is the FK, so taxonomy changes do not rewrite the fact.

Step 4: Preserve correlation keys on both facts

request_id and session_id live on BOTH fact rows. That is the load-bearing detail: a trace must reconstruct from either the bulk log line or the error, so neither fact can drop the keys.

The solution

Below is one conceptually sound model. The split between log_events and error_events is the load-bearing decision: it is how a 2TB-per-day feed remains queryable. Each dimension sits on the one side of a one-to-many edge into the facts.

`request_id` on both facts enables cross-service tracing for a single user action

services: service_id INT PK, name TEXT, owning_team TEXT, tier TEXT
error_types: error_type_id INT PK, category TEXT, canonical_message TEXT, severity TEXT
sessions: session_id TEXT PK, user_id BIGINT, started_at TIMESTAMP, ended_at TIMESTAMP
log_events: log_event_id BIGINT PK, service_id INT FK, session_id TEXT FK, request_id TEXT, level TEXT, event_ts TIMESTAMP, raw_message TEXT
error_events: error_event_id BIGINT PK, service_id INT FK, error_type_id INT FK, session_id TEXT FK, request_id TEXT, event_ts TIMESTAMP, stack_trace TEXT

services references log_events (1:N)
sessions references log_events (1:N)
services references error_events (1:N)
error_types references error_events (1:N)
sessions references error_events (1:N)

The analysis pattern

Top error categories by service last hour

SELECT
    s.name AS service,
    et.category,
    COUNT(*) AS error_count
FROM error_events e
JOIN services s ON s.service_id = e.service_id
JOIN error_types et ON et.error_type_id = e.error_type_id
WHERE e.event_ts >= NOW() - INTERVAL '1 hour'
GROUP BY s.name, et.category
ORDER BY error_count DESC
LIMIT 20

Trade-offs and alternatives

Split fact tables: Hot error path separate from firehose. * Error queries are fast and narrow * Retention can differ per fact * Producers must classify at write time
Single logs table with level column: Unified logs table filtered by level. * One ingest path * Every error query scans everything * Retention is one-size-fits-all

Common follow-up questions

How would you enforce `request_id` propagation across 40 services? (Tests whether the candidate names a tracing context standard and upstream instrumentation.)
Debug logs must be kept 3 days, errors 90 days, audit 7 years. How is that enforced? (Tests per-fact partitioning and TTL policies.)
How do you detect a new error type the classifier has never seen? (Tests whether `error_type_id` is nullable with an unclassified bucket and a backfill path.)
At 2TB per day, where does the ingest bottleneck move, and how do you shard? (Tests scale awareness: batch vs streaming, partition keys, and hot-shard mitigation.)