Katkat
Back

Design a 25,000-CSV ETL pipeline

NatooraData Analyst · Technical Screen
MediumData AnalystTechnical ScreenSystem Design

Last updated

This question assesses the ability to design a robust, scalable ETL architecture capable of processing large volumes of semi-structured data while ensuring data integrity and operational reliability.

Scenario

Your company receives 25,000 individual CSV files daily from various suppliers. These files contain inconsistent headers, varying date formats, and occasional malformed rows. Your task is to design an automated ETL pipeline that ingests these files into a centralized data warehouse (e.g., BigQuery or Snowflake) for downstream analytics.

Requirements

  1. Functional Requirements:

    • Ingest raw files from a cloud storage bucket (e.g., S3).
    • Standardize schemas across different suppliers.
    • Handle errors: identify and isolate bad rows without crashing the pipeline.
    • Ensure the process is idempotent (running the same file twice results in no duplicate data).

  2. Non-Functional Requirements:

    • Scalability: The system must process all 25,000 files within a 4-hour window.
    • Observability: Provide a mechanism to track the status of each file (Pending, Success, Failed).
    • Data Quality: Implement a mechanism to alert data engineering teams when a file fails validation.

Deliverables

  • Provide a high-level architectural diagram or description of the components.
  • Define the storage/processing layers used.
  • Explain how you would handle schema evolution (e.g., a supplier adds a new column).
  • Describe the strategy for dealing with malformed rows (e.g., logging, dead-letter queues).

Hints

Hint 1

Consider using an orchestration tool like Airflow or Prefect to manage the lifecycle of each file ingestion task.

Hint 2

Decouple the ingestion layer from the transformation layer using a staging area where raw data remains immutable.

Hint 3

Implement a metadata database to track file state and checksums to ensure idempotency and prevent duplicate processing.

Follow-up questions

  • How would you handle a scenario where a schema change breaks downstream dashboard reporting?
  • If the pipeline latency increases significantly, what metrics would you examine to identify the bottleneck?
  • How would you implement a backfill process if a supplier resends a month's worth of data?