0

Data platform with ingestion and governed access

by Frontier Interview· about 3 hours ago· ⑂ 0 remixes

Company: Anthropic
Difficulty: Medium

Problem statement

Design a system that ingests large-scale data, processes it, and serves stakeholders with different access needs and sensitivities.

Requirements

A company-wide platform that takes data in from many sources, processes it into trustworthy tables, and serves it to people and systems with very different access rights: analysts, finance, research teams training models, and automated pipelines. Some data is sensitive (personal data, customer content, financials). Settle first: batch, streaming, or both (both: product events arrive continuously, finance data daily)? Who are the consumers (SQL users, ML training jobs, dashboards)? What regulations apply (assume privacy law with deletion rights, and customer contracts that limit how their data may be used)?

Functional requirements

  • Ingest from operational databases (change data capture), event streams, third-party APIs and file drops.
  • Store raw data unchanged, then build cleaned and modeled tables with scheduled and streaming jobs.
  • Serve SQL queries, dashboards and bulk reads for ML.
  • A catalog: what tables exist, who owns them, their schema, freshness, quality and lineage.
  • Governed access: data classified by sensitivity; access granted by role and purpose; column masking and row filters; every access audited.
  • Delete a person's data everywhere on request, within a deadline (say 30 days).

Non-functional requirements

  • Freshness: streaming tables within 5 minutes; daily tables by 06:00.
  • Correctness: no silent data loss or duplication; bad data stopped before consumers see it.
  • Security: least privilege by default, sensitive data never readable without an explicit grant, and a full audit trail.
  • Scale with the company without a central team in every pipeline (self-service with guardrails).

Back-of-the-envelope estimation

  • Events: 500k/s average at ~1 KB = 500 MB/s ≈ 43 TB/day raw. Columnar compression (~5×) gives ~9 TB/day stored, ~3 PB/year before derived tables (assume 1.5× more for those).
  • Database change capture: 300 source databases, ~2 TB/day of changes.
  • Consumers: 2,000 people, ~50k interactive queries/day (peaks of ~20/s), 1,500 scheduled pipelines, ML jobs that each scan 10–200 TB.
  • Object storage cost for ~5 PB at ~$20/TB-month ≈ $100k/month; compute dominates, so the design must make queries prune data well.

API design

Users and services touch the platform through a few interfaces, all going through the same identity and policy checks:

SQL:      SELECT ... FROM catalog.schema.table      (query engine, JDBC/HTTP)
Bulk:     POST /v1/reads {table, columns, filter, purpose} → {read_session, file_splits}
Ingest:   POST /v1/sources {type: cdc|stream|api|files, connection_ref, target_table, schedule, owner}
Catalog:  GET /v1/tables/{name} → {schema, owner, classification, freshness, quality, lineage}
Access:   POST /v1/access-requests {principal, table, columns, purpose, duration}
          → approval by the data owner (and privacy for restricted data); grants expire
Privacy:  POST /v1/deletions {subject_id} → {deletion_id, status}

The bulk read path is how ML jobs read large tables without going through a SQL engine, while still getting the same filters and masks: the policy service hands out a read session whose file splits already exclude what the caller cannot see.

Data model

Storage layout: an open table format (Apache Iceberg or Delta) on object storage. It gives snapshots, schema evolution, row-level deletes and time travel on top of Parquet files.

Zone Contents Who reads
Raw exactly what arrived, append-only, partitioned by arrival date platform jobs only
Clean typed, deduplicated, personal data tokenized pipeline owners, approved users
Modeled business tables (orders, sessions, revenue) with tests analysts, dashboards, ML

Catalog and policy tables (a relational database):

  • tables(name, owner_team, zone, classification, retention_days, schema_version)
  • columns(table, column, tags): tags such as pii.email, pii.user_id, customer_content, financial.
  • grants(principal, resource, purpose, granted_by, expires_at)
  • policies(tag, rule): for example, pii.email → masked unless the grant's purpose is support.
  • lineage(upstream_table, downstream_table, job_id)
  • audit(ts, principal, action, resource, columns, rows_returned, purpose, query_id): append-only, in its own restricted store.

High-level design

Build it one requirement at a time. Each drawing adds to the one before; the shaded boxes are what that step adds.

1) Ingest into a raw zone

Change capture streams database changes into the event log; product events go there directly. Connectors land everything in the raw zone, untouched, with the source offset recorded so a replay can rebuild any day.

{"type":"excalidraw","elements":[
 {"type":"rectangle","id":"src","x":0,"y":0,"width":180,"height":70,"label":{"text":"Sources"}},
 {"type":"rectangle","id":"log","x":340,"y":0,"width":180,"height":70,"label":{"text":"Event log"}},
 {"type":"rectangle","id":"conn","x":680,"y":0,"width":180,"height":70,"label":{"text":"Connectors"}},
 {"type":"ellipse","id":"raw","x":1020,"y":-5,"width":200,"height":80,"label":{"text":"Raw zone"}},
 {"type":"arrow","id":"a1","x":180,"y":35,"width":160,"height":0,"start":{"id":"src"},"end":{"id":"log"},"label":{"text":"CDC"}},
 {"type":"arrow","id":"a2","x":520,"y":35,"width":160,"height":0,"start":{"id":"log"},"end":{"id":"conn"}},
 {"type":"arrow","id":"a3","x":860,"y":35,"width":160,"height":0,"start":{"id":"conn"},"end":{"id":"raw"},"label":{"text":"append"}}
]}

2) Process into clean and modeled tables

An orchestrator runs batch and streaming jobs that read raw tables and write clean and modeled ones. Every job declares its inputs and outputs (that is the lineage) and its quality checks; a failed check blocks publishing the new snapshot.

{"type":"excalidraw","elements":[
 {"type":"text","id":"legend","x":0,"y":-60,"text":"Shaded: added in this step","fontSize":16},
 {"type":"rectangle","id":"src","x":0,"y":0,"width":180,"height":70,"label":{"text":"Sources"}},
 {"type":"rectangle","id":"log","x":340,"y":0,"width":180,"height":70,"label":{"text":"Event log"}},
 {"type":"rectangle","id":"conn","x":680,"y":0,"width":180,"height":70,"label":{"text":"Connectors"}},
 {"type":"ellipse","id":"raw","x":1020,"y":-5,"width":200,"height":80,"label":{"text":"Raw zone"}},
 {"type":"rectangle","id":"jobs","x":1030,"y":160,"width":180,"height":70,"label":{"text":"Pipelines +\nquality checks"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"rectangle","id":"orch","x":680,"y":160,"width":180,"height":70,"label":{"text":"Orchestrator"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"ellipse","id":"curated","x":1020,"y":320,"width":200,"height":80,"label":{"text":"Clean +\nmodeled"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"arrow","id":"a1","x":180,"y":35,"width":160,"height":0,"start":{"id":"src"},"end":{"id":"log"},"label":{"text":"CDC"}},
 {"type":"arrow","id":"a2","x":520,"y":35,"width":160,"height":0,"start":{"id":"log"},"end":{"id":"conn"}},
 {"type":"arrow","id":"a3","x":860,"y":35,"width":160,"height":0,"start":{"id":"conn"},"end":{"id":"raw"},"label":{"text":"append"}},
 {"type":"arrow","id":"b1","x":1120,"y":75,"width":1,"height":85,"start":{"id":"raw"},"end":{"id":"jobs"}},
 {"type":"arrow","id":"b2","x":860,"y":195,"width":170,"height":0,"start":{"id":"orch"},"end":{"id":"jobs"},"label":{"text":"schedule"}},
 {"type":"arrow","id":"b3","x":1120,"y":230,"width":1,"height":90,"start":{"id":"jobs"},"end":{"id":"curated"}}
]}

3) Serve queries and bulk reads, with a catalog

A distributed SQL engine and a bulk-read service sit over the same tables. The catalog holds schemas, owners, freshness and lineage, and is where people find data.

{"type":"excalidraw","elements":[
 {"type":"text","id":"legend","x":0,"y":-60,"text":"Shaded: added in this step","fontSize":16},
 {"type":"rectangle","id":"src","x":0,"y":0,"width":180,"height":70,"label":{"text":"Sources"}},
 {"type":"rectangle","id":"log","x":340,"y":0,"width":180,"height":70,"label":{"text":"Event log"}},
 {"type":"rectangle","id":"conn","x":680,"y":0,"width":180,"height":70,"label":{"text":"Connectors"}},
 {"type":"ellipse","id":"raw","x":1020,"y":-5,"width":200,"height":80,"label":{"text":"Raw zone"}},
 {"type":"rectangle","id":"jobs","x":1030,"y":160,"width":180,"height":70,"label":{"text":"Pipelines +\nquality checks"}},
 {"type":"rectangle","id":"orch","x":680,"y":160,"width":180,"height":70,"label":{"text":"Orchestrator"}},
 {"type":"ellipse","id":"curated","x":1020,"y":320,"width":200,"height":80,"label":{"text":"Clean +\nmodeled"}},
 {"type":"rectangle","id":"query","x":680,"y":325,"width":180,"height":70,"label":{"text":"SQL engine +\nbulk reads"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"rectangle","id":"users","x":340,"y":325,"width":180,"height":70,"label":{"text":"Analysts, BI,\nML jobs"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"ellipse","id":"catalog","x":670,"y":480,"width":200,"height":80,"label":{"text":"Catalog +\nlineage"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"arrow","id":"a1","x":180,"y":35,"width":160,"height":0,"start":{"id":"src"},"end":{"id":"log"},"label":{"text":"CDC"}},
 {"type":"arrow","id":"a2","x":520,"y":35,"width":160,"height":0,"start":{"id":"log"},"end":{"id":"conn"}},
 {"type":"arrow","id":"a3","x":860,"y":35,"width":160,"height":0,"start":{"id":"conn"},"end":{"id":"raw"},"label":{"text":"append"}},
 {"type":"arrow","id":"b1","x":1120,"y":75,"width":1,"height":85,"start":{"id":"raw"},"end":{"id":"jobs"}},
 {"type":"arrow","id":"b2","x":860,"y":195,"width":170,"height":0,"start":{"id":"orch"},"end":{"id":"jobs"},"label":{"text":"schedule"}},
 {"type":"arrow","id":"b3","x":1120,"y":230,"width":1,"height":90,"start":{"id":"jobs"},"end":{"id":"curated"}},
 {"type":"arrow","id":"c1","x":1020,"y":360,"width":-160,"height":0,"start":{"id":"curated"},"end":{"id":"query"},"label":{"text":"scan"}},
 {"type":"arrow","id":"c2","x":520,"y":360,"width":160,"height":0,"start":{"id":"users"},"end":{"id":"query"},"label":{"text":"SQL"}},
 {"type":"arrow","id":"c3","x":770,"y":395,"width":1,"height":85,"start":{"id":"query"},"end":{"id":"catalog"}}
]}

4) Govern access

Columns carry sensitivity tags, set at ingestion (by the source owner and by a classifier that scans samples for emails, phone numbers and keys). The policy service decides, per query, which tables, columns and rows the principal may see for the stated purpose. The SQL engine and the bulk-read service enforce it by rewriting the plan (masks and row filters). Every decision is written to the audit log.

{"type":"excalidraw","elements":[
 {"type":"text","id":"legend","x":0,"y":-60,"text":"Shaded: added in this step","fontSize":16},
 {"type":"rectangle","id":"src","x":0,"y":0,"width":180,"height":70,"label":{"text":"Sources"}},
 {"type":"rectangle","id":"log","x":340,"y":0,"width":180,"height":70,"label":{"text":"Event log"}},
 {"type":"rectangle","id":"conn","x":680,"y":0,"width":180,"height":70,"label":{"text":"Connectors"}},
 {"type":"ellipse","id":"raw","x":1020,"y":-5,"width":200,"height":80,"label":{"text":"Raw zone"}},
 {"type":"rectangle","id":"jobs","x":1030,"y":160,"width":180,"height":70,"label":{"text":"Pipelines +\nquality checks"}},
 {"type":"rectangle","id":"orch","x":680,"y":160,"width":180,"height":70,"label":{"text":"Orchestrator"}},
 {"type":"ellipse","id":"curated","x":1020,"y":320,"width":200,"height":80,"label":{"text":"Clean +\nmodeled"}},
 {"type":"rectangle","id":"query","x":680,"y":325,"width":180,"height":70,"label":{"text":"SQL engine +\nbulk reads"}},
 {"type":"rectangle","id":"users","x":340,"y":325,"width":180,"height":70,"label":{"text":"Analysts, BI,\nML jobs"}},
 {"type":"ellipse","id":"catalog","x":670,"y":480,"width":200,"height":80,"label":{"text":"Catalog +\nlineage"}},
 {"type":"rectangle","id":"policy","x":340,"y":485,"width":180,"height":70,"label":{"text":"Policy service"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"ellipse","id":"audit","x":330,"y":640,"width":200,"height":80,"label":{"text":"Audit log"},"backgroundColor":"#d0ebff","fillStyle":"solid"},
 {"type":"arrow","id":"a1","x":180,"y":35,"width":160,"height":0,"start":{"id":"src"},"end":{"id":"log"},"label":{"text":"CDC"}},
 {"type":"arrow","id":"a2","x":520,"y":35,"width":160,"height":0,"start":{"id":"log"},"end":{"id":"conn"}},
 {"type":"arrow","id":"a3","x":860,"y":35,"width":160,"height":0,"start":{"id":"conn"},"end":{"id":"raw"},"label":{"text":"append"}},
 {"type":"arrow","id":"b1","x":1120,"y":75,"width":1,"height":85,"start":{"id":"raw"},"end":{"id":"jobs"}},
 {"type":"arrow","id":"b2","x":860,"y":195,"width":170,"height":0,"start":{"id":"orch"},"end":{"id":"jobs"},"label":{"text":"schedule"}},
 {"type":"arrow","id":"b3","x":1120,"y":230,"width":1,"height":90,"start":{"id":"jobs"},"end":{"id":"curated"}},
 {"type":"arrow","id":"c1","x":1020,"y":360,"width":-160,"height":0,"start":{"id":"curated"},"end":{"id":"query"},"label":{"text":"scan"}},
 {"type":"arrow","id":"c2","x":520,"y":360,"width":160,"height":0,"start":{"id":"users"},"end":{"id":"query"},"label":{"text":"SQL"}},
 {"type":"arrow","id":"c3","x":770,"y":395,"width":1,"height":85,"start":{"id":"query"},"end":{"id":"catalog"}},
 {"type":"arrow","id":"d1","x":700,"y":395,"width":-200,"height":90,"start":{"id":"query"},"end":{"id":"policy"},"label":{"text":"authorize"}},
 {"type":"arrow","id":"d2","x":430,"y":555,"width":1,"height":85,"start":{"id":"policy"},"end":{"id":"audit"}}
]}

Final design

{"type":"excalidraw","elements":[
 {"type":"rectangle","id":"src","x":0,"y":0,"width":180,"height":70,"label":{"text":"Sources"}},
 {"type":"rectangle","id":"log","x":340,"y":0,"width":180,"height":70,"label":{"text":"Event log"}},
 {"type":"rectangle","id":"conn","x":680,"y":0,"width":180,"height":70,"label":{"text":"Connectors"}},
 {"type":"ellipse","id":"raw","x":1020,"y":-5,"width":200,"height":80,"label":{"text":"Raw zone"}},
 {"type":"rectangle","id":"jobs","x":1030,"y":160,"width":180,"height":70,"label":{"text":"Pipelines +\nquality checks"}},
 {"type":"rectangle","id":"orch","x":680,"y":160,"width":180,"height":70,"label":{"text":"Orchestrator"}},
 {"type":"ellipse","id":"curated","x":1020,"y":320,"width":200,"height":80,"label":{"text":"Clean +\nmodeled"}},
 {"type":"rectangle","id":"query","x":680,"y":325,"width":180,"height":70,"label":{"text":"SQL engine +\nbulk reads"}},
 {"type":"rectangle","id":"users","x":340,"y":325,"width":180,"height":70,"label":{"text":"Analysts, BI,\nML jobs"}},
 {"type":"ellipse","id":"catalog","x":670,"y":480,"width":200,"height":80,"label":{"text":"Catalog +\nlineage"}},
 {"type":"rectangle","id":"policy","x":340,"y":485,"width":180,"height":70,"label":{"text":"Policy service"}},
 {"type":"ellipse","id":"audit","x":330,"y":640,"width":200,"height":80,"label":{"text":"Audit log"}},
 {"type":"arrow","id":"a1","x":180,"y":35,"width":160,"height":0,"start":{"id":"src"},"end":{"id":"log"},"label":{"text":"CDC"}},
 {"type":"arrow","id":"a2","x":520,"y":35,"width":160,"height":0,"start":{"id":"log"},"end":{"id":"conn"}},
 {"type":"arrow","id":"a3","x":860,"y":35,"width":160,"height":0,"start":{"id":"conn"},"end":{"id":"raw"},"label":{"text":"append"}},
 {"type":"arrow","id":"b1","x":1120,"y":75,"width":1,"height":85,"start":{"id":"raw"},"end":{"id":"jobs"}},
 {"type":"arrow","id":"b2","x":860,"y":195,"width":170,"height":0,"start":{"id":"orch"},"end":{"id":"jobs"},"label":{"text":"schedule"}},
 {"type":"arrow","id":"b3","x":1120,"y":230,"width":1,"height":90,"start":{"id":"jobs"},"end":{"id":"curated"}},
 {"type":"arrow","id":"c1","x":1020,"y":360,"width":-160,"height":0,"start":{"id":"curated"},"end":{"id":"query"},"label":{"text":"scan"}},
 {"type":"arrow","id":"c2","x":520,"y":360,"width":160,"height":0,"start":{"id":"users"},"end":{"id":"query"},"label":{"text":"SQL"}},
 {"type":"arrow","id":"c3","x":770,"y":395,"width":1,"height":85,"start":{"id":"query"},"end":{"id":"catalog"}},
 {"type":"arrow","id":"d1","x":700,"y":395,"width":-200,"height":90,"start":{"id":"query"},"end":{"id":"policy"},"label":{"text":"authorize"}},
 {"type":"arrow","id":"d2","x":430,"y":555,"width":1,"height":85,"start":{"id":"policy"},"end":{"id":"audit"}}
]}

Deep dives

Exactly-once ingestion without distributed transactions. Change capture and the event log give at-least-once delivery. Connectors write to the raw zone in table-format commits that record the source offsets in the commit's metadata; on restart, a connector reads the last committed offsets and resumes from there, so a crash between writing files and committing simply leaves orphan files that a cleanup job removes. Clean tables deduplicate on a natural key (event_id, or primary key plus change sequence number for CDC) with a merge, so a replayed batch is harmless.

Data quality as a gate, not a dashboard. Each modeled table declares checks: row count within ±30% of the 7-day median, null rate per column under a limit, uniqueness of keys, referential integrity, freshness. Jobs write to a staging snapshot, run the checks, and publish (an atomic snapshot swap in the table format) only if they pass. Consumers keep reading yesterday's good snapshot instead of today's broken one, and the owner is paged. Quality status shows in the catalog next to the table.

How access is decided. Attribute-based rather than per-table grants, because per-table grants do not scale to 50k tables:

  • Every column has tags; every principal has roles and team; every request has a purpose (analytics, support, finance, model training).
  • A policy is a rule over those: pii.* columns are masked (hash or null) unless the grant's purpose is in the tag's allowed list; customer_content is readable for model training only from tenants whose contract allows it (a row filter on tenant_id, from a contracts table); financial requires the finance role.
  • Grants are requested through the platform, approved by the data owner (plus privacy review for restricted tags), and expire (90 days by default). Break-glass access exists for incidents, needs a ticket, and pages the security team.
  • Enforcement happens in the engines, which rewrite the query plan: a masked column becomes sha256(salt || email), a row filter becomes an added WHERE. Raw files are not readable by people at all: object-store permissions allow only the engines' service identities, so nobody bypasses the policy by reading Parquet files directly.

Personal data: tokenize early. At ingestion, direct identifiers (email, name, phone) are replaced in clean tables by a stable token from a token vault; the mapping lives in a separate, tightly controlled store. Analysts join on tokens and never see the values. Re-identification needs the vault, which needs its own grant and is audited.

Deletion in an immutable lake. A deletion request for subject S: look up S's token; find every table holding that token's columns through the catalog tags and lineage; issue row-level deletes (the table format writes delete files, so queries stop returning the rows at once); compaction physically rewrites affected files within the 30-day window; old snapshots that still contain the rows expire by then too (snapshot retention ≤ 30 days on tables with personal data). Deleting the token-vault entry makes any copy left in a backup unlinkable. Each step is recorded so the deletion can be proven.

Lineage drives impact and governance. Because every job declares inputs and outputs, the catalog knows that revenue_daily depends on orders_clean, which depends on the orders database. Tags propagate downstream automatically (a column derived from an email is still pii), a schema change to a source can warn every downstream owner before it lands, and the deletion workflow knows where the data went.

Trade-offs

  • Lakehouse on object storage keeps storage cheap, open and shared by every engine; a managed warehouse is simpler to operate and faster for small interactive queries. Many teams use both: lake as the source of truth, warehouse for curated marts.
  • Attribute-based policies scale and stay consistent, but they need accurate tags; invest in classifiers and in making the owner's tagging part of onboarding a source.
  • Tokenizing early makes most analytics safe by default but makes debugging harder and adds a vault to protect.
  • Quality gates stop bad data from spreading but delay data when a check is too strict; checks need owners and tuning like alerts.
  • Short snapshot retention on personal data meets deletion deadlines but limits time travel for debugging on exactly those tables.

Interviewer expectations

Mid-level (L4)

  • Lays out ingestion, raw storage, processing jobs and a query layer.
  • Distinguishes batch and streaming ingestion and gives rough volumes.
  • Mentions role-based access and an audit log.

Senior (L5)

  • Designs replayable, deduplicated ingestion with offsets committed alongside data, and quality checks that gate publishing.
  • Builds governed access on tags, purposes and expiring grants, enforced inside the query engines, with raw files closed to people.
  • Explains the catalog and lineage and why they matter for discovery and impact analysis.

Staff (L6+)

  • Handles deletion rights in an immutable lake end to end (tokens, row-level deletes, compaction, snapshot expiry, proof).
  • Makes the platform self-service: onboarding a source sets owner, tags and checks; policies scale without per-table grants.
  • Weighs lakehouse against warehouse, cost against query speed, and safety defaults against analyst productivity, with numbers.

Sign in to comment, vote or remix.