Design an Enterprise Data Warehouse Solution — System Design Interview Practice

Interview prompt

Design an enterprise warehouse that ingests operational sources, transforms and governs trusted dimensional data, serves interactive BI, and supports reproducible historical corrections.

• Define raw/staged/curated layers, dimensional grain, slowly changing dimensions, data contracts, lineage, quality gates, and ownership.
• Separate batch/CDC ingestion from transformation, warehouse serving, semantic models, dashboards, and ad-hoc workloads.
• Handle late corrections, duplicate records, schema evolution, backfills, sensitive data, workload isolation, and cost governance.
• Explain reconciliation, disaster recovery, row/column security, observability, freshness, and degraded BI behavior.

Requirements and scale assumptions

• Ingest files, APIs, CDC, and operational extracts; validate, transform, conform dimensions/facts, and publish governed warehouse tables.
• Provide SQL, semantic metrics, dashboards, scheduled reports, lineage, data-quality status, freshness, and controlled exports.
• Support corrections/backfills, retention/deletion, access policies, failed-batch quarantine, snapshots, and restore testing.
• Meet p95 dashboard query latency under five seconds for governed common workloads and publish freshness metadata.
• Scale to petabytes, thousands of sources, and 10k concurrent BI users without a single hot key or unbounded synchronous work.
• Do not lose committed state; make retries and duplicate events safe.
• Degrade safely when downstream workers, caches, or external dependencies fail.
• 5PB warehouse, 1TB daily ingest, and 10k concurrent BI users
• Partition by the primary tenant, user, item, or geographic key and isolate hot partitions.
• Keep serving state bounded; retain raw events or durable records for replay and auditing.

• Peak scale: 5PB; 1TB/day; 10k BI users — Capacity assumption that drives partitioning and backpressure.
• Latency target: dashboard p95 < 5s; freshness visible — User-facing budget for the primary request or read path.
• Durable boundary: Committed before async — Validated source snapshots and published table manifests are authoritative; semantic models and dashboards are derived.
• Async boundary: At-least-once workers — Keep Synapse Analytics for warehousing, Data Lake Storage Gen2 for storage, Data Factory for ETL orchestration off the synchronous path.

Key entities

• SourcePartitionsourceId, partitionId, cursor, schemaVersion, watermark, status

  Replayable enterprise data warehouse solution source evidence and ingestion cursor.

• SchemaVersiondatasetId, version, compatibility, owner, effectiveAt, status

  Governed enterprise data warehouse solution contract used to validate producers and consumers.

• ProcessingRunrunId, inputWatermark, checkpoint, qualityStatus, codeVersion, status

  Checkpointed enterprise data warehouse solution processing attempt with quality and lineage metadata.

• AnalyticalDatasetdatasetId, partition, watermark, schemaVersion, qualityStatus, location

  Curated enterprise data warehouse solution serving partition with freshness and quality state.

Data flow

1. 1. Register sources and contractsThe enterprise data warehouse solution catalog records owners, schemas, compatibility rules, retention, lineage, and partitioning before data is accepted.
2. 2. Ingest with backpressureConnectors checkpoint enterprise data warehouse solution source cursors, validate schema and deduplication keys, and slow producers when downstream capacity is exhausted.
3. 3. Process event time with checkpointsStream or batch engines compute enterprise data warehouse solution transformations using watermarks, late-data policy, state checkpoints, and deterministic code versions.
4. 4. Publish quality-gated datasetsOnly enterprise data warehouse solution outputs that pass completeness, freshness, validity, and privacy checks become visible to analytical consumers.
5. 5. Serve, replay, and reconcileConsumers read bounded partitions with freshness metadata while operators replay failed enterprise data warehouse solution ranges and compare output checksums.

Deep dives and trade-offs

• Schema evolution and data qualityVersion enterprise data warehouse solution contracts and make compatibility rules explicit for every producer and consumer. Quarantine malformed partitions instead of poisoning the whole dataset. Track row counts, null rates, duplicates, distribution changes, and policy violations by partition.
• Watermarks, late data, and exactly-once effectsUse source cursors and event-time watermarks for enterprise data warehouse solution progress, not wall-clock assumptions. Make checkpoints, output keys, and sink commits retry-safe under at-least-once delivery. Document how late events revise windows, aggregates, or snapshots.
• Replay, lineage, and costKeep immutable enterprise data warehouse solution raw evidence and code or schema versions so failed outputs can be reproduced. Separate hot serving storage from cold retention and cap replay concurrency. Measure freshness, backlog, compute cost, storage growth, and quality-gate failure rate.
• Streaming versus batchUse streaming for freshness-critical enterprise data warehouse solution paths and batch for backfills, compaction, and expensive recomputation. Forcing every workload into streaming makes state, replay, and cost harder to operate.
• Raw retention versus curated-only storageRetain enough immutable raw evidence for replay, audit, and correction, then tier or expire it according to policy. Without raw evidence, a bad transformation can require an unreproducible emergency fix.
• Central warehouse versus domain-owned datasetsCentralize governance and discovery while letting domain owners own contracts and quality signals. A single team owning every transformation becomes a delivery bottleneck and hides data ownership.