Courses › System Design › Question Breakdowns

Design an Enterprise Data Platform

Scoping an open-ended prompt, connectors and CDC, versioned datasets, DAG builds, lineage and permission propagation.

Design a data integration platform for a large enterprise — the kind of problem Palantir Foundry, Databricks or Snowflake solve: bring data from 500 different source systems (ERPs, databases, spreadsheets, sensors, APIs) into one place, clean and join it, keep it fresh, and let analysts and applications use it — with strict access control and lineage for every dataset. It's an open-ended question (Palantir often asks it as a "decomposition"), testing whether you can scope an ambiguous problem, design ingestion and transformation pipelines, and handle permissions and lineage properly.

Requirements

Functional

  • Connect to many source systems and ingest their data (batch and change streams)
  • Users define transformations (cleaning, joining, aggregating) that produce new datasets, which rebuild when inputs change
  • Every dataset is versioned and has lineage: which sources and code produced it
  • Analysts query datasets; applications consume them via APIs
  • Access control follows the data: if you can't see a source column, you can't see derived data built from it

Out of scope: BI dashboards, ML model training (a consumer of the platform), master data management workflows.

Non-functional

  • Scale: 500 sources, ~10k datasets, ~1 PB total; daily batch volume ~10 TB, some sources streaming
  • Freshness: most datasets hourly or daily; some operational ones within minutes
  • Reliability: failed sources or jobs don't corrupt downstream datasets; reruns are safe
  • Security and compliance: fine-grained permissions, audit logs, data residency
  • Self-service: many teams build pipelines without a central bottleneck

[!TIP] Scope out loud first This prompt is deliberately vague. Before drawing anything, ask: who are the users (analysts? operational apps?), what freshness do they need, how sensitive is the data, what's the first use case that must work in a month? Then propose a minimal end-to-end slice (one source → one cleaned dataset → one consumer) and grow it. Interviewers score this process as much as the architecture.

Capacity estimates

Core Entities

  • Source connector — type (JDBC, SFTP, REST, Kafka…), credentials reference, schedule, sync mode (full / incremental / CDC)
  • Dataset — name, schema, owner, storage location, versions (transactions), permissions/markings
  • Transform — code (SQL/Python/Spark), input datasets, output dataset, schedule or trigger
  • Build / job run — transform, input versions, output version, status, logs
  • Lineage edge — input dataset (version) → transform → output dataset (version)

API

POST /v1/sources        { "type": "postgres", "connection": "vault://erp-prod", "tables": ["orders"], "mode": "cdc" }
POST /v1/transforms     { "inputs": ["raw.orders", "raw.customers"], "output": "clean.orders_enriched", "code": "sql://..." }
POST /v1/builds         { "dataset": "clean.orders_enriched" }   → { "buildId": "b_9" }
GET  /v1/datasets/{name}/lineage?depth=3
GET  /v1/datasets/{name}/versions/{v}/query?sql=...

High-Level Design

src = client "500 Source Systems" @0,1
conn = service "Connectors /\nIngestion Agents" @1,1
raw = blob "Raw Zone\n(lake, versioned)" @2,1
orch = service "Orchestrator\n(DAG scheduler)" @2,0
compute = service "Compute\n(Spark / SQL engines)" @3,0
curated = blob "Curated Datasets\n(columnar tables)" @3,1
catalog = store "Catalog: schemas,\nlineage, permissions" @2,2
query = service "Query & API Layer" @4,1
users = client "Analysts / Apps" @4,2
src -> conn
conn -> raw
orch -> compute
compute -> raw
compute -> curated
conn --> catalog
compute --> catalog : lineage
users -> query
query -> catalog : check access
query -> curated
  • Connectors pull (or receive change streams from) each source and land data as-is in the raw zone — append-only and versioned.
  • The orchestrator runs transforms as a DAG: when an input dataset gets a new version, downstream builds are triggered.
  • Compute engines (Spark, SQL warehouses) read input versions and write a new version of the output in a columnar table format (Delta/Iceberg/Parquet).
  • The catalog records schemas, owners, permissions, and lineage for every build.
  • The query/API layer serves analysts and applications, enforcing permissions from the catalog.

Deep Dives

1. Ingestion from 500 different systems

  • Connector framework: each source type is a plugin with a common contract (discover schema, read full, read incremental). Hundreds of sources means configuration, not custom code, for the common types.
  • Sync modes: full snapshots for small tables; incremental by an updated-at column; CDC (reading the database's log, e.g. Debezium) for large or near-real-time tables.
  • Land data raw and immutable first (ELT, not ETL), so transforms can be fixed and re-run without re-extracting from fragile sources.
  • Agents running inside the customer's network push data out, so on-prem systems don't need inbound access.

2. Versioned datasets and safe rebuilds

Each build writes a new dataset version atomically (a table-format transaction: write files, then commit a new snapshot pointer). Readers always see a complete version; a failed build leaves the previous version in place. Builds record the exact input versions they read, which makes runs reproducible and lets you rebuild history or roll back.

3. Orchestration

Transforms form a DAG over datasets. Triggers are schedules or "inputs updated". The orchestrator:

  • Builds in dependency order, skipping branches whose inputs didn't change.
  • Retries transient failures; on hard failure, stops downstream builds and alerts the owner — don't propagate partial data.
  • Applies data quality checks (row counts, null rates, schema drift) as gates before publishing a version.

4. Lineage

Record an edge for every build: (input dataset@version) → transform@code-version → (output dataset@version). This answers "where did this number come from?", "what breaks if I change this column?" and "who used this sensitive source?". Column-level lineage (parsed from SQL) is the stronger version.

5. Permissions that follow the data

Source-level access isn't enough: a joined dataset can leak a sensitive column. Approaches:

  • Markings / classifications (e.g. PII, export-controlled) attached to sources propagate along lineage to every derived dataset; a user needs all markings on a dataset to read it.
  • Row- and column-level policies applied at query time (mask SSNs unless authorized).
  • Every read and export is audit-logged.

[!WARNING] Permissions can't be an afterthought If you design pipelines first and bolt on access control later, derived datasets end up readable by anyone with access to the output folder. Make propagation along lineage part of the core model from day one — it's the main differentiator interviewers look for here.

6. Freshness tiers

Most datasets are fine with hourly/daily batch. For operational use (e.g. live inventory), run streaming transforms (Kafka + Flink or incremental table updates) on the few datasets that need minutes of latency — don't force the whole platform into streaming.

What's expected at each level

  • Mid-level: connectors into a data lake, scheduled transforms, a warehouse/query layer.
  • Senior: raw immutable landing + ELT, CDC for large sources, atomic dataset versions, DAG orchestration with quality gates, basic lineage.
  • Staff: strong scoping of the ambiguous prompt, permission propagation along lineage, reproducible builds from input versions, freshness tiers, and the operating model for hundreds of teams and sources.