System Design
53 questionsEnd-to-end pipeline design, data lake architecture, CDC, real-time systems, and performance optimization. The questions that separate Senior from Lead engineers.
Design a Daily ETL Pipeline for a Data Warehouse
Design an end-to-end daily ETL pipeline that ingests data from 15+ source systems (RDBMS, APIs, flat files) into a central data warehouse. Daily volume: ~500GB raw data. Requirements: - Data must be available by 6 AM for business reports - Handle schema changes gracefully - Support backfill for historical corrections - Monitor and alert on failures - Maintain data lineage
Design an Idempotent Data Pipeline
Your pipeline occasionally gets re-triggered due to Airflow retries, duplicate events, or manual reruns. Design the pipeline so that running it multiple times produces the same result. Scenario: An e-commerce order pipeline processes ~10M orders/day. Orders can arrive late or be corrected. The pipeline runs hourly.
Design a Slowly Changing Dimension (SCD) Pipeline
Design a pipeline to maintain customer dimension data with full history tracking (SCD Type 2). Customers can change their address, phone, name, etc. Requirements: - Track all historical changes - Queries should easily get "current" state or "state as of date X" - Handle late-arriving changes - ~5M customer records, ~50K changes/day
Design a Data Quality Framework
Design a reusable data quality framework for 100+ pipelines. Quality checks should be configurable, run automatically, and gate data promotion from staging to production. Requirements: - Support rule types: null checks, uniqueness, range, referential integrity, freshness, volume anomaly - Configurable per table/column (YAML or similar) - Block bad data from reaching production - Historical quality metrics for trend analysis - Integrate with Airflow
Design a Backfill Strategy for a Large Pipeline
A critical business logic change requires reprocessing 2 years of historical data (730 days, ~50TB total). The pipeline currently processes ~70GB/day and takes 2 hours. You can't disrupt daily runs. Design a backfill strategy that: - Doesn't break production SLAs - Is cost-efficient - Handles failures gracefully (can resume, not restart) - Validates correctness before cutover
Design a File Format Strategy
Your data lake has a mix of CSV, JSON, Parquet, and ORC files. Query performance is poor and storage costs are high. Design a file format and partitioning strategy. Data: 50+ tables, mix of fact tables (100M+ rows/day) and dimension tables (10K-5M rows). Queried by analysts (Athena/Presto) and Spark jobs.
Design Airflow DAG for Complex Dependencies
Design an Airflow DAG structure for a pipeline with these requirements: - 5 independent source extracts (can run in parallel) - 3 staging transforms that depend on specific sources - 1 final aggregation that depends on all staging transforms - External dependency: wait for a partner's file on S3 - SLA: all done by 6 AM - Different retry policies per task - Send alerts at different severity levels
Design a Real-Time Event Processing System
Design a real-time system that processes user clickstream events (~100K events/sec) for: 1. Real-time dashboards (last 5 min aggregation) 2. User session analysis (sessionize events) 3. Real-time anomaly detection (sudden traffic spikes) 4. Sink processed data to a data lake for batch analytics Requirements: - End-to-end latency < 30 seconds - Exactly-once processing semantics - Handle late arrivals (up to 1 hour) - Auto-scale with traffic
Design a Kafka Topic Strategy
Your company has 30+ microservices producing events. Currently everyone creates topics ad-hoc, leading to: - 500+ topics with inconsistent naming - Some topics with 3 partitions, others with 200 - No schema enforcement - Retention varies from 1 hour to 30 days Design a topic governance strategy.
Exactly-Once Processing in a Streaming Pipeline
Explain how you would achieve exactly-once processing semantics in a pipeline: Kafka → Stream Processor → Database + Kafka output topic Transactions involve debits and credits. Even one duplicate or lost event means money is wrong.
Design a Real-Time Feature Store
Design a feature store that serves ML features with: - Batch features: updated daily (user demographics, 30-day purchase history) - Real-time features: updated within seconds (items in cart, last click) - Serving latency: < 10ms at p99 - 50K+ feature lookups/sec - Point-in-time correctness for training data
Design a Stream-to-Lake Ingestion Pipeline
Design a pipeline that continuously ingests events from Kafka into a data lake (S3) in a queryable format. Requirements: - ~50K events/sec, JSON format - Must be queryable via Athena/Presto within 15 minutes of arrival - Exactly-once delivery to S3 - Handle schema evolution - Optimize for query performance (not just dump files)
Design a Real-Time Alerting System for Data Anomalies
Design a system that detects data anomalies in real-time across 50+ data pipelines: - Volume anomalies (sudden drop/spike in row counts) - Freshness alerts (data not arriving on time) - Value anomalies (unexpected nulls, out-of-range values) - Schema drift detection Alert must fire within 5 minutes of anomaly.
Design a Data Lake Architecture
Design a data lake architecture for a mid-size company (~500 employees, 50+ data sources) migrating from on-prem data warehouse to cloud. Requirements: - Support structured, semi-structured, and unstructured data - Self-service analytics for data analysts - Governed access with PII protection - Cost-optimized storage - Support both batch and real-time workloads
Design a Partition Strategy for a Petabyte-Scale Table
You have a clickstream table with 2 billion rows/day, queried in multiple patterns: - "All events for user X in last 7 days" - "All events on page Y yesterday" - "Hourly aggregates for last 30 days" Current: partitioned by date only. Queries scanning full partitions are slow. Design a better strategy.
Delta Lake vs Iceberg vs Hudi — When to Use What
Your team is choosing a lakehouse table format. Compare Delta Lake, Apache Iceberg, and Apache Hudi. When would you pick each one?
Design a Data Mesh Architecture
Your centralized data team is a bottleneck — 50+ data requests in the backlog. Business domains complain about slow delivery. The CTO wants to explore "Data Mesh." Design the architecture and organizational changes needed.
Handling Schema Evolution in a Data Lake
Your data lake has 200+ Iceberg tables. Sources change schemas frequently — new columns, renamed columns, type changes. Design a schema evolution strategy that doesn't break downstream consumers.
Design a Multi-Tenant Data Lake
Design a data lake that serves multiple business units (tenants) with: - Data isolation: Tenant A cannot access Tenant B's data - Shared infrastructure: common platform, don't duplicate everything - Cross-tenant analytics: some aggregated data is shared - Cost allocation: charge each tenant for their usage - Compliance: some tenants have stricter data residency requirements
Design a CDC Pipeline (Database to Data Lake)
Design a Change Data Capture pipeline that replicates 20+ PostgreSQL tables to a data lake in near real-time. Requirements: - Capture inserts, updates, and deletes - < 5 minute latency - Handle schema changes - Initial full load + ongoing incremental - Don't impact source DB performance
CDC vs Timestamp-Based Incremental — Tradeoffs
Compare log-based CDC (Debezium) vs timestamp-based incremental extraction (WHERE updated_at > last_run). When would you choose each?
Design an Event Sourcing System
Design an event-sourced order management system where every state change is captured as an immutable event. Support: - Reconstruct order state at any point in time - Audit trail for compliance - Real-time projections for the API layer - Event replay for bug fixes or new features
Handle CDC with Out-of-Order Events
Your CDC pipeline processes events from Kafka, but events sometimes arrive out of order (network delays, partition reassignment, multi-region replication). An UPDATE might arrive before the corresponding INSERT. Design a system that handles out-of-order CDC events correctly.
Design a Multi-Database CDC Consolidation Pipeline
Your company has 8 microservices, each with its own database (mix of PostgreSQL, MySQL, and MongoDB). Design a pipeline that consolidates data from all services into a unified data lake for analytics. Challenges: - Different schemas for similar entities (each service has its own "user" table) - Different databases (relational + document) - Different update frequencies (some high-volume, some low) - Need a unified customer 360 view
Optimize a Slow Spark Job
A Spark job that joins 3 tables and aggregates results has slowed from 30 minutes to 4 hours over the past 6 months. Data has grown from 50GB to 500GB. You need to diagnose and fix it. Walk through your debugging and optimization process.
Design for 10x Data Growth
Your data platform currently handles 1TB/day. The business expects 10TB/day within 12 months due to new product launches. Design a scaling strategy that doesn't require a complete rewrite. Current stack: Airflow + Spark on EMR + S3 + Redshift + Kafka.
Optimize Redshift Query Performance
Analysts complain that their Redshift queries are slow. A typical query joins 3 fact tables (~500M rows each) with dimension tables, filters by date range, and aggregates. Query time: 5-10 minutes. Target: under 30 seconds. Diagnose and optimize.
Small Files Problem — Diagnosis and Fix
Your Spark jobs are progressively getting slower. Investigation reveals that a key table has 2 million small Parquet files (average 500KB each) instead of fewer large files. What caused this? How do you fix it? How do you prevent it?
Design a Cost-Optimized Data Platform on AWS
Your AWS data platform costs $80K/month. The CFO wants it under $40K without sacrificing SLAs. Current breakdown: - EMR (Spark): $30K (always-on clusters) - Redshift: $25K (ra3.4xlarge × 6 nodes) - S3: $8K (2PB across tiers) - Kafka (MSK): $10K (6 brokers) - Other (Glue, Lambda, etc.): $7K Find the savings.
Design a Data Pipeline Observability Platform
With 200+ pipelines running daily, failures are frequent and debugging takes hours. Design an observability platform that provides: - End-to-end pipeline visibility - Proactive alerting (before SLA breach, not after) - Root cause analysis - Data lineage (what tables does this pipeline affect?) - Historical performance trends
CAP Theorem — Pick Two?
Explain the CAP theorem. Can you really "pick two"? Where do systems like Kafka, Cassandra, PostgreSQL, DynamoDB, and Redshift fall on the CAP spectrum?
ACID vs BASE — When to Choose What
Explain ACID and BASE properties. When would you choose a BASE system over ACID for a data engineering workload?
OLAP vs OLTP — Architecture Deep Dive
Explain the differences between OLAP and OLTP systems. Why can't you just use PostgreSQL for everything? How does this affect your data platform design?
Sharding Strategies
Your database has grown to 5TB and single-node performance is degrading. Explain sharding strategies, how to choose a shard key, and how to handle resharding.
Partitioning vs Sharding vs Bucketing
These three terms are often confused. Explain the differences between partitioning, sharding, and bucketing — with examples from databases, data lakes, and Kafka.
Replication Strategies
Explain different replication strategies in distributed systems. How do Kafka, PostgreSQL, and Cassandra each handle replication?
Consistent Hashing
Explain consistent hashing. Why is it better than simple modular hashing for distributed systems? Where is it used in data engineering?
Load Balancing Strategies
Explain load balancing strategies. When would you use each? How does load balancing apply to data engineering (not just web servers)?
Caching Strategies
Explain caching strategies (write-through, write-behind, cache-aside). When would you use caching in a data pipeline? What are the pitfalls?
Backpressure & Rate Limiting in Data Pipelines
Your Kafka consumer processes 50K events/sec but producers suddenly spike to 200K/sec. The consumer falls behind, memory grows, and eventually crashes. How do you handle this?
Star Schema vs Snowflake vs One Big Table (OBT)
Compare star schema, snowflake schema, and the One Big Table (OBT) approach. When would you use each? What's the modern trend?
Normalization vs Denormalization for Analytics
When should you normalize and when should you denormalize? How does the answer change between OLTP and OLAP systems?
Data Serialization — Avro vs Protobuf vs JSON vs Parquet
Compare Avro, Protobuf, JSON, and Parquet. When do you use each in a data platform?
Idempotency in Data Pipelines
What does idempotency mean in data pipelines? Why is it the most important property? How do you achieve it?
Event-Driven Architecture — CQRS & Event Sourcing
Explain event-driven architecture patterns: Pub/Sub, Event Sourcing, CQRS, and Choreography vs Orchestration. When do you use each?
Batch vs Streaming vs Lambda vs Kappa Architecture
Compare batch, streaming, Lambda, and Kappa architectures. Which would you recommend for a new data platform?
Data Lakehouse Architecture
What is a Data Lakehouse? How does it combine the benefits of data lakes and data warehouses? What makes it possible now but not 5 years ago?
Data Mesh vs Data Fabric
Compare Data Mesh and Data Fabric. These terms are often confused — what's the actual difference and when would you recommend each?
Microservices vs Monolith — Data Perspective
From a data engineering perspective, what changes when an organization moves from a monolith to microservices? What problems does it create for analytics?
ETL vs ELT — When and Why
What's the difference between ETL and ELT? Why has the industry shifted toward ELT? When is ETL still the right choice?
Consensus & Leader Election
Explain consensus in distributed systems. How do Raft and Paxos work at a high level? Why does Kafka need ZooKeeper (or KRaft)?
SLA, SLO, SLI & Error Budgets
Define SLA, SLO, and SLI. How do you apply these concepts to data pipelines? What's an error budget and how does it affect engineering decisions?
Slowly Changing Dimensions — All Types
Explain SCD Types 0 through 6. When do you use each? How do you implement Type 2 in practice?