How I Mastered System Design Interviews

Main ideas and lessons

• System design interviews (data engineering) are harder because they test ambiguity navigation, not just knowledge.

  • Unlike DSA/coding problems with a single correct answer and a clear “destination,” data engineering system design has multiple valid architectures with different trade-offs.
  • Success depends on whether your reasoning holds up under constraint changes (volume, latency expectations, source reliability, etc.).

• Data engineering system design topics are broad, but the approach is consistent.

  • Topic examples mentioned: ingestion patterns, data modeling, batch vs streaming trade-offs, schema evolution, idempotency, backfills, and cost/scale.
  • The speaker claims all these can be handled using a single framework.

• A core framework (6 steps) for DE system design interviews

  • The video repeatedly emphasizes using this “map” to structure answers regardless of company/domain.

---

Methodology / step-by-step framework (6 steps)

1. Requirements gathering

• Understand the system before drawing boxes.
• Ask clarifying questions:
  • Who are the users/consumers?
    • Example: marketing analysts (batch-friendly) vs ML team / real-time ops (streaming needed).
  • What do they need?
    • Aggregated metrics vs raw events vs historical snapshots.
  • How do they access it?
    • SQL, BI tools (Tableau/Power BI), or REST APIs.
  • Latency requirements (latency SLA)
    • Example guidance:
      • If “~1 hour acceptable” → batch is likely sufficient
      • If “minute or less” → streaming likely required
  • Data volume
    • Determines compute/storage choices downstream (single-node vs distributed, and storage optimization).
• Output of this step
  • Clarified functional + non-functional constraints that drive architecture decisions.

---

2. Pipeline design

• Decide batch vs streaming first, based on requirements.

Batch processing (common for many pipelines)

• Used when data doesn’t need second-by-second freshness; cost > latency.
• Typical stack/pattern:
  • Spark for transformation
  • Airflow/Prefect/Dagster for orchestration
  • Write to layered storage: bronze → silver → gold
  • Serve via dashboards (e.g., Tableau)

Streaming processing

• Used for low latency use cases (real-time dashboards, fraud/event-driven systems; latency SLA under ~1 minute).
• Typical stack/pattern:
  • Kafka for ingestion/buffering and decoupling producers/consumers
  • Spark Structured Streaming or Flink for stream processing (enrich/filter)
  • Write results to low-latency serving stores like Redis/DynamoDB/Cassandra
• Example pattern:
  • event → Kafka → stream processor → serving layer → real-time app/dashboard

Lambda architecture (two parallel paths)

• Maintain:
  • Batch layer for historical computation
  • Speed/streaming layer for real-time updates
• Both feed a serving layer to combine “grounded current view”:
  • Real-time = current but noisy/no history
  • Batch = accurate historical baseline but stale

Kappa architecture (single streaming path)

• Use one pipeline; Kafka retains events as the historical store.
• Reprocessing is done by creating a new consumer group and replaying from earlier offsets.
• Limitation: if you must reprocess very long history (e.g., 6 months), Kafka retention may be too expensive and replay throughput may be slow.
• Conclusion claimed: many companies use a hybrid “lakehouse approach” (Kafka + S3/ADLS for history).

---

3. Data modeling

• Interviewers may push back beyond “just draw tables.”
• Four sub-areas emphasized:

1. Medallion architecture (bronze/silver/gold)

   • Bronze: raw, “store as-is,” no cleaning; reprocessable safety net.
   • Silver: cleaned + structured; fact/dimension model as trusted source.
   • Gold: pre-aggregated/joined datasets optimized for direct querying.

2. Star schema vs denormalization (one big table / OBT)

   • Star schema: separate fact and dimension tables; good for BI tools and maintainability; queries rely on joins.
   • Denormalized/OBT: pre-joined wide table for a known query pattern; faster reads; downside is expensive updates when dimensions change.
   • You must justify choice based on real-world update patterns and query needs.

3. Slowly Changing Dimensions (SCD)

   • SCD1: overwrite history; loses ability to answer historical “as-of” questions.
   • SCD2: keep history with validity ranges (e.g., valid_to); supports historical point-in-time analysis.
   • Example: free → premium subscription change.

4. Partitioning / optimization strategies

   • Default: partition by event date
   • If many queries filter by another high-cardinality attribute (e.g., region/country), consider it as a secondary partition key
   • Liquid clustering: reduce rigidity of fixed partitions; dynamically reorganize based on query patterns.

---

4. Storage and file formats

• Storage format impacts query cost due to how much data can be scanned.

• Key comparison:

  • Row-based vs columnar
    • Parquet (columnar): enables column pruning and skipping unrelated data; best for read-heavy analytics.
    • Avro (row-based, row-oriented): better suited for write-heavy/streaming event records.

• Open table formats (and why they matter vs raw files):

  • Problem with “dump Parquet to S3”
    • No ACID / transaction safety (concurrent writers can corrupt/overwrite silently)
    • No schema enforcement
    • Small-file problem (many tiny files cause overhead)
  • Delta Lake / Iceberg / similar (mentions Hoodie as well) provide:
    • ACID transactions
    • Time travel
    • Schema evolution
    • Schema management and compaction/optimization (e.g., optimize/compaction to reduce small files)

• Database type decision (relational vs non-relational):

  • Relational: structured data, clear relationships, need strong consistency, complex queries.
  • Non-relational: flexible/semi-structured/unstructured or rapidly evolving schemas; trade consistency for speed/availability.

---

5. Data quality and observability

• Data correctness can’t be assumed; production failures can silently create bad outputs.
• Three parts:

1. Data quality dimensions (checks should map to one of these)

   • Completeness: missing rows/fields
   • Accuracy: invalid values logically impossible (e.g., negative amounts, impossible times)
   • Consistency: agreement across systems/tables
   • Freshness: data meets SLA freshness (not just a schedule)
   • Uniqueness: no duplicates

2. Data contracts (“shift quality left”)

   • Formal agreement between producer and consumer:
     • schema, types, required fields, allowed values
   • Enforced before data enters the pipeline (e.g., schema registry / schema validation tools)

3. Pipeline-level observability

   • Monitor pipeline health:
     • whether DAG/job runs
     • failures
     • runtime/latency drift (e.g., 3 hours vs expected 40 minutes)
   • Use orchestration tool metrics and/or monitoring tools (mentions Airflow/Prefect/Dagster; also DataDog/CloudWatch).

---

6. Scalability, backfills, and data ops

• The “golden rule”: Idempotency

  • Running the same pipeline multiple times with same input should produce identical outputs (no duplicates/missing data).
  • Example technique: use merge/upsert for incremental loads instead of plain inserts.

• Backfills

  • Reprocess historical data when business logic changes or bugs are found.
  • If using ACID table formats (Delta/Iceberg), rerun only affected partitions and overwrite atomically.

• Schema evolution

  • Approach: flexible at bronze, strict at silver/gold
    • Bronze: schema-on-read; accept new fields (e.g., Delta “merge schema”).
    • Silver/gold: enforce a strict schema and explicit mapping/validation so dashboards/ML pipelines don’t break or get garbage.

---

Additional interview technique emphasized: “wide then zoom” + reasoning visibility

• Interviewers often start broad (design a user-activity pipeline) then zoom into specific framework steps/topics.
• The speaker claims the differentiator is:
  • Passing candidates: slow down, ask clarifying questions, and make reasoning explicit
  • Failing candidates: jump directly to tools/whiteboard, skip requirement gathering, and design only the “happy path”

---

Worked example included (real-time food delivery analytics pipeline)

Use case

• Design a real-time analytics pipeline (Uber Eats / Zomato / Swiggy)
• Consumers:
  • Restaurant partners: operational dashboard with live metrics
  • Executive/finance team: daily aggregated business reports

SLAs

• Restaurant dashboard: ≤ 2 minutes
• Executive dashboard: daily batch is fine

Events tracked (6 event types)

• order placed → restaurant confirmed → driver assigned → driver picked up → driver delivered
• order canceled

Back-of-the-envelope calculations (assumptions)

• 5M orders/day
• 6 event types → 30M events/day
• ~350–833 events/sec depending on peak hour estimate (dinner rush)
• event size ~1 KB
• Bronze retention ~2 years:
  • ~22 TB before compression
  • ~4–11 TB after parquet compression (2–5x)
• Kafka parallelism:
  • suggested 6–12 partitions; manageable with modest Kafka cluster.
• Serving layer (Redis):
  • store precomputed per-restaurant metrics rather than raw events
  • example: 50K active restaurants, ~10–15 fields each → fits in-memory comfortably.

Architecture chosen

• Lambda-style: two parallel paths from a single event stream (Kafka)
  • Streaming path:
    • Kafka → Spark streaming (partition by restaurant ID) → Redis serving for restaurant dashboards
  • Batch path:
    • Kafka events landed to S3/ADLS as raw parquet → bronze/silver/gold processing → daily reports

Data quality checks in the example

• Data contracts enforced at schema registry (stop malformed records early)
• Bronze checks:
  • event_type in valid set
  • order_id + event_type uniqueness (detect duplicates)
  • non-null customer_id, etc.
• Silver checks:
  • referential integrity (fact orders.restaurant_id exists in dim restaurants)
  • business rules (delivery duration bounds)
  • time consistency (picked_up after ordered; delivered after picked_up)
  • volume comparisons vs historical baselines
• Gold checks:
  • cancellation rate within 0–100
  • average delivery time positive/logically bounded

Ops/scalability in the example

• Backfill strategy when business logic changes (rerun for start/end date ranges)
• Ensure idempotency
• Handle schema evolution: bronze schema-on-read, strict silver/gold with merge schema enabled for additions

---

Speakers / sources featured

• Speaker: Afaq Ahmad (Solutions Architect at Databricks; previously Junior Principal Data Engineer)
• Sponsored learning platform: Educative (mentioned as video sponsor)

---

Referenced technologies/tools (not “speakers”)

• Apache Kafka, Apache Spark, Spark Structured Streaming, Flink
• Airflow, Prefect, Dagster, Mage
• Tableau, Power BI
• Delta Lake, Iceberg, Apache Hudi (HOODIE mentioned), Parquet, Avro
• Redis, DynamoDB, Cassandra
• Airflow/Datadog/CloudWatch (monitoring references)

---

Referenced companies/examples (as domains/use cases)

• Uber, DoorDash
• Uber Eats, Zomato, Swiggy
• Amazon.com (example for sessions/events)
• Spotify (example for data modeling concepts)