Pipelines & scenarios · Design & Scenarios

Design an end-to-end data pipeline

Harddesignend-to-endarchitecturemedallionCDCscenario

Interview question

Design an end-to-end data pipeline for an ecommerce company (orders, users, payments).

How to answer

The short answer

Interviewers want a clear path from sources to consumers, plus quality, orchestration, and failure thinking, not a random tool dump.

End-to-end architecture

How to talk through it (checklist)

  1. 1Requirements: freshness (hourly BI vs realtime fraud), volume, .
  2. 2Ingestion: for DB tables; scheduled API pull for Stripe; Kafka for events.
  3. 3Storage: raw bronze immutable; silver cleaned; gold marts (medallion).
  4. 4Transforms: Spark for heavy joins; dbt for warehouse SQL models/tests.
  5. 5Orchestration: Airflow with sensors, retries, .
  6. 6Quality: uniqueness on order_id, not-null amounts, reconciliation vs source counts.
  7. 7Serving: BI on gold; never point dashboards at raw.
  8. 8Ops: monitors on freshness, failed tasks, Kafka lag; idempotent loads.

Tiny grain examples

  • fct_orders: 1 row per order
  • fct_order_items: 1 row per line item
  • dim_user: SCD2 if city history matters

Interview tip: Draw sources → land → transform → serve, then spend time on , tests, and SLAs. Tools are secondary to the data path.