Katkat
Back

Build a Transaction CSV Cleaning Pipeline

TeslaData Engineer · Technical Screen
MediumData EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

This problem tests your ability to design a robust, deterministic data cleaning pipeline that handles input parsing, data validation, and precision-safe numeric handling.

Problem Statement

You are tasked with building a clean function for a transaction pipeline. You receive raw transaction data in a CSV-like format where fields may be missing, poorly formatted, or contain inconsistent casing.

Your task is to implement process_transactions(raw_data: list[str]) -> list[dict] which performs the following operations:

  1. Parsing: Convert CSV rows into structured objects.
  2. Normalization: Trim whitespace, convert all currency symbols to a standard format, and ensure IDs are unique strings.
  3. Precision: Currency values must be handled with at least 2 decimal places of precision without floating-point errors (use strings or specialized numeric libraries if necessary).
  4. Validation: Drop rows where the transaction_id is missing or the amount is non-numeric.
  5. Audit: If a row is malformed or invalid, log it to a separate 'rejected' collection instead of crashing the pipeline.

Input format: "transaction_id,timestamp,amount,currency"

Example

Input:

raw_data = [
    "tx_001,2023-10-01,100.50,USD",
    "tx_002,2023-10-01,invalid,USD",
    ",2023-10-01,50.00,EUR",
    "tx_003,2023-10-01, 75.25 , EUR "
]

Output:

{
    "cleaned": [
        {"transaction_id": "tx_001", "timestamp": "2023-10-01", "amount": "100.50", "currency": "USD"},
        {"transaction_id": "tx_003", "timestamp": "2023-10-01", "amount": "75.25", "currency": "EUR"}
    ],
    "rejected": [
        "tx_002,2023-10-01,invalid,USD",
        ",2023-10-01,50.00,EUR"
    ]
}

Constraints

  • Use standard Python libraries.
  • Ensure the pipeline is deterministic (same input always produces same output).

Hints

Hint 1

Use Python's 'csv' module or string splitting methods to handle delimiters and potential quoted fields correctly.

Hint 2

Avoid floating-point math for currency; consider using the 'decimal' module or treating amounts as integer cents to avoid precision loss.

Hint 3

Think about how to keep your pipeline extensible, perhaps by using a list of validation functions that each row must pass.

Follow-up questions

  • How would you modify this pipeline to handle multi-gigabyte files that don't fit into memory?
  • If the 'currency' field contains inconsistent codes (e.g., 'US Dollar', 'USD', '$'), how would you implement a lookup mapping to normalize them?
  • How would you ensure data integrity if the pipeline is interrupted halfway through a large batch?