Design a Conversation Log Ingestion Pipeline

Design a daily data pipeline to ingest conversation logs for analytical purposes.

Data Source

Every day, a JSONL file is delivered to object storage. Each file is associated with a load_date. Each line in the file represents a single conversation record with the following top-level fields:

• conversation_id
• user_id
• started_at
• model_version
• messages (an array of message objects)

Each message object within the messages array contains these fields:

• message_id
• role
• content
• timestamp
• tokens_in
• tokens_out
• latency_ms

Deliverables

Your design should be presented as pseudo-code, focusing on the architectural approach rather than production-ready code. Address the following aspects:

1. Schema Definition: Define the warehouse schemas or typed column lists for the target tables where the conversation data will be stored.
2. Parsing and Validation: Detail the process for parsing and validating each JSONL record.
3. Error Handling: Explain how to handle various data quality issues, including:
   • Malformed JSON lines
   • Missing fields
   • Unexpected data types
   • Impossible numeric values
   • Partially invalid records
4. Deduplication: Describe strategies for deduplicating records, both within a single daily load and across the entire historical warehouse table.
5. Scheduling and Idempotency: Outline how the daily job would be scheduled and designed to be idempotent.
6. Cross-Day Conversations: Propose a method for handling conversations whose messages span more than one calendar day.
7. Missing Numeric Values: Discuss the approach for missing numeric values (e.g., default to 0, store as NULL, or delete the record).
8. Spark Data Skew: If the pipeline runs on Spark, explain how you would diagnose and mitigate data skew on a worker.

Interview Context

The design will be discussed during a 45-minute live walkthrough with a hiring manager. Ensure your design is clear and allows for a thorough explanation of architectural tradeoffs.