Design a company data platform

Every product team wants answers from data: which feature people use, why revenue dipped, what to recommend next. A data platform collects events from apps and changes from production databases, keeps them cheaply in one place, turns them into trustworthy tables, and serves dashboards, analysts and machine-learning jobs, without ever slowing down production.

1. The prompt

"Design the data platform for a large consumer company: collect app events and data from our production databases, and make it available for dashboards, ad-hoc analysis and machine learning."

What makes it hard:

• Many sources, one truth. Hundreds of databases and dozens of apps, each changing its schema on its own schedule.
• Correctness you can't see. A dropped event or a double-counted order doesn't crash anything; it just makes a dashboard quietly wrong.
• Cost grows with every question. Storage is cheap, but scanning petabytes for each query is not.

2. Frame the problem

3. What it must do

1. Ingest app events and database changes continuously, without load on production databases.
2. Store everything raw and cheaply, then transform it into clean, documented tables (daily active users, orders, revenue).
3. Serve SQL queries, dashboards and ML training jobs, plus a small set of minute-fresh metrics.

Not now: a feature store for real-time model serving, reverse ETL back into product tools, a data marketplace for external customers.

4. How well it must do it

5. Numbers that change the design

6. The nouns

• Event (from an app) and change (insert, update, delete from a database), each with a unique ID and an event time.
• Table in three layers: raw (as received), clean (deduplicated, typed), marts (business tables such as daily revenue).
• Job: a transformation from input tables to an output table, run by an orchestrator in dependency order.
• Schema and data contract: what a producer promises to send.

raw.app_events        event_id, event_type, user_id, event_time, received_at, payload (JSON)     partitioned by event_date
clean.app_events      event_id (unique), event_type, user_id, event_time, typed columns…        partitioned by event_date
raw.orders_changes    op (c | u | d), before, after, source_lsn, commit_time                    partitioned by commit_date
clean.orders          order_id, user_id, status, amount, currency, updated_at                   current state, merged from changes
mart.daily_revenue    date, country, currency, orders, revenue                                  small; dashboards read this

7. The contract

Producers and consumers both need interfaces:

POST /v1/events            [{event_id, event_type, user_id, event_time, properties}]   → 202   (batched by the SDK)
schema registry            event_type → versioned schema; incompatible changes are rejected at publish time
SQL endpoint               SELECT … FROM mart.daily_revenue WHERE date >= …           (dashboards, analysts)
table read API             read clean.app_events snapshot @ version N                    (ML training, reproducible)
job definition             inputs, output, SQL or code, schedule, owner, tests, freshness target

8. First working version

App SDKs send batched events to a collector; databases stream changes through CDC; both land in a log, then in object storage as raw tables. Nightly jobs build clean tables and marts; a SQL engine serves queries.

8.1 Getting data in

The collector only validates and appends to the log, so it stays fast and can't lose data in a burst. A CDC connector reads each database's replication log (the same stream a replica uses), so production sees no extra queries. A sink job writes the log into object storage every few minutes as columnar files.

8.2 Turning it into answers

An orchestrator runs transformation jobs in dependency order: clean tables first, then marts. Dashboards read the small marts; analysts can still query the big clean tables when they need detail.

9. Make it hold up

9.1 Batch, streaming, or both freshness

9.2 Files on object storage that behave like tables interactive queries

Object storage gives cheap, durable files but no transactions: a job that dies halfway leaves half-written output, and readers can see a mix. An open table format (Apache Iceberg, Delta Lake or Apache Hudi) adds a metadata layer: each commit writes new files, then atomically swaps a pointer to a new snapshot listing them.

• Atomic commits: readers see the old snapshot or the new one, never a half.
• Time travel: query the table as of snapshot N, so ML training and audits are reproducible.
• Small files: writing every few minutes creates thousands of tiny files, and opening files dominates query time. A background compaction job rewrites them into ~256 MB–1 GB files.
• Partitioning by event date (and clustering by common filters) lets a query for "last 7 days" skip the other 358 days entirely.

9.3 Change data capture without surprises no silent loss

• Snapshot, then stream: copy the table once at a known log position, then apply every change after that position, so nothing falls in the gap.
• Order per key: partition the log by primary key, so the changes to one order arrive in commit order.
• Deletes are changes too: keep them (as a delete marker) so the clean table can remove the row.
• Merge into current state: clean.orders applies changes with an upsert by key and keeps the latest by log position, so replaying the same changes gives the same table (idempotent).
• Schema changes flow through a schema registry; an added column is fine, a renamed one breaks consumers and needs a contract change (9.4).

9.4 Bad data and data contracts data quality

9.5 Late and duplicate events exactly once in curated tables

Mobile apps send events hours or days late (offline phones), and SDKs retry, so duplicates arrive. Partition by event time (when it happened), not arrival time; deduplicate on event_id when building clean.app_events; and re-run the last few days' partitions each night so late arrivals are included. Daily numbers can change slightly for a few days: say so on the dashboard, and freeze finance numbers only after the late window closes (Module 20).

9.6 Deleting personal data in files that never change privacy

• Keep personal data in few places: store a pseudonymous user ID in events, and emails or addresses only in a small, access-controlled user table.
• Delete by rewriting: table formats support row-level deletes (written as delete files and merged on read, then compacted away), so a weekly job removes the requested users' rows from every table that has them.
• Crypto-shredding for very large or scattered data: encrypt each user's personal fields with a per-user key and delete the key.
• Access control per column and row, enforced by the query engine, and an audit log of who read what (Module 26).

9.7 Query cost and noisy neighbours interactive queries

9.8 The whole design after the deep dives

10. Interviewer follow-ups

11. When things break

12. Wrap-up and the clock

Trade-offs we made:

• Batch by default, streaming only where minutes matter.
• Raw data kept so any table can be rebuilt, at the cost of storage.
• An open table format on object storage instead of one vendor's warehouse storage, for cost and engine choice.
• Daily numbers that may change for a few days, for complete late data.

What we'd watch: freshness per table · rows quarantined · CDC lag · files per partition · bytes scanned per team · dashboard query p95 · reconciliation gaps.

At 10×: more log partitions, more aggressive compaction and clustering, more marts in front of dashboards, and chargeback per team.

Common mistakes:

• Nightly SELECT * from production databases.
• Partitioning by arrival time, then wondering why late events land on the wrong day.
• Making every table real time.
• No raw layer, so a bug can't be fixed by replaying.
• Forgetting deletes, both CDC deletes and user deletion requests.

13. Try it, check yourself, read more

Query-cost lab. One year of app events (2 TB of raw JSON a day). A dashboard asks for the last 7 days, 3 of the 40 columns. Change how the table is stored and watch how much data the query has to read. Costs use an illustrative $5 per TB scanned.

Model: columnar files are ~6× smaller than JSON and a query reads only the columns it needs; each file costs ~20 ms to open and plan, spread over 200 parallel readers that each read 200 MB/s.