Design the Company Data Platform
This prompt asks for one place where 3,000 engineers can find data, trust it, and query it safely. A Principal answer picks the few standards everything else depends on and moves 400 producer teams onto them without breaking a dashboard along the way.
1 · Requirements
The Prompt
Design the data platform for a company with 3,000 engineers and 400 services. Today each org runs its own pipelines into one of three warehouses, and every team builds its own PII masking. Cover ingestion, a lakehouse, governance and PII controls, self-serve analytics, and the path off the patchwork.
Functional
- Ingest events and database changes from 400 services through one path
- Store data once in an open lakehouse with bronze, silver, and gold layers
- Find any table with its owner, lineage, freshness, and PII tags
- Grant access by purpose, with masking applied in every query engine
- Delete a person's data everywhere on request
Non-Functional
- Events are queryable within 15 minutes of being emitted
- Breaking schema changes fail in CI before they ship
- Deletion requests complete within 30 days
- Compute spend falls about 30% once the three warehouses are retired
2 · Estimates
Drill This Phase First
Show the Answer and Estimates
Answer
About 10 TB a day
50B events at 1 KB is 50 TB of raw data a day. Compressed 5x, that is about 10 TB, or about 4 PB over 13 months of retention.
- Events
- About 50B a day from 400 services: about 580,000 a second on average and 1.5M at peak
- Volume
- About 1 KB an event is 50 TB a day raw, about 10 TB a day after 5x Parquet compression
- CDC
- Change logs from about 250 operational databases add about 2 TB a day
- Storage
- 13 months of events is about 4 PB on object storage, roughly $84,000 a month
- Compute
- $1.8M a month across three warehouses today. About 40% of tables are copies, so consolidation targets a 30% cut, about $540,000 a month
- Governance
- About 20,000 tables and 1,200 PII columns, with every new column classified within 24 hours
- Deletion
- About 30,000 requests a month, each purged from every table within 30 days
- Users
- About 2,500 weekly active analysts and engineers running 150,000 queries a day
3 · API
POST /v1/contracts { domain, event_name, schema, owner_team, pii_fields[] } → { contract_id, version }
POST /v1/contracts/:id/check { schema } → { compatible, breaking_changes[] } (runs in the producer's CI)
GET /v1/catalog/tables/:name → { owner, schema, tags[], lineage, freshness, quality }
POST /v1/access-requests { table, purpose, duration_days } → { request_id, status: pending }
POST /v1/deletions { subject_id } → { deletion_id, due_by }
GET /v1/deletions/:id → { tables_done, tables_pending, completed_at? }Data Model
contracts (SQL):
contract_id (PK) : uuid
domain : string
event_name : string
version : int
schema : json Avro or Protobuf
owner_team : string
compatibility : enum(backward|full)
catalog_columns (SQL, PK = table_name + column_name):
table_name : string
column_name : string
tags : list<string> pii.email, pii.phone, finance
classified_by : enum(scanner|owner)
access_grants (SQL):
principal : string
table_name : string
purpose : string
masking : enum(none|hash|redact)
expires_at : timestamp
events (lakehouse table, partition = event_date, domain):
event_id : string dedup key
subject_id : string encrypted with a per-subject key
payload : struct
ingested_at : timestamp4 · High-Level Design
5 · Deep Dives
The One-Way Doors
Two choices are expensive to reverse: the table format and the catalog. An open table format on object storage keeps one copy of the data readable by several engines, so the SQL engine, Spark, and the BI warehouse all stay replaceable. Pick the format and catalog first after a bake-off on real workloads, and treat every engine choice after that as reversible.
Data Contracts at the Source
Each producing team owns a versioned schema for every event it emits, registered with an owner and its PII fields. CI rejects breaking changes, so a renamed field fails the producer's build before it can break a downstream report. The platform team owns the pipes and the bronze layer; domain teams own the gold tables they publish as data products, each with a named owner and a freshness target.
PII by Tag and Policy
A scanner samples every new column within 24 hours and proposes tags that the owner confirms. Policies attach to tags, so one rule masks every email column in every engine, and access is granted by purpose with an expiry. Raw events encrypt subject identifiers with a per-person key, so deleting the key makes that person's history unreadable; curated tables take row deletes and compaction inside the 30 day window.
Retiring the Patchwork
Start by pointing the catalog at all three warehouses to inventory owners, usage, and duplicates. The top 50 dashboards carry about 80% of query volume, so migrate those first with dual runs and automated reconciliation of row counts and key metrics. Each warehouse gets a published sunset date once its consumers have moved. Buy the query engines, BI, and managed Kafka, and build only the contract, catalog, and policy layer that encodes how this company governs data.
6 · Wrap Up
The Short Version
Land everything once as open table format files on object storage, enforce schemas at the source with data contracts, and apply PII policy from catalog tags in every query engine. Migrate by consumer usage with dual runs, then retire each warehouse on a published date.