Load Daily JSONL LLM Chat Logs Into a Warehouse: Schema, Validation, Dedup, Idempotency

Every day, one JSONL file of LLM conversation logs lands in object storage, and the data must be loaded into a data warehouse for analysis. Each line of the file is one conversation with these fields: | Field | Meaning | |---|---| | `conversation_id` | Identifier of the conversation | | `user_id` | Identifier of the user | | `started_at` | When the conversation started | | `model_version` | Version of the model that served the conversation | | `messages` | Array of messages; each has `message_id`, `role`, `content`, `timestamp`, `tokens_in`, `tokens_out` and `latency_ms` | This was a take-home exercise with a one-week window and a suggested effort of about 1.5 hours, answered in pseudocode, followed by a 45-minute walkthrough with the hiring manager in which each design decision was questioned in depth. ### Clarifying Questions - When a conversation continues on a second day, does the second day's file repeat the whole conversation, or contain only the new messages? - Can the same `conversation_id` or `message_id` appear more than once within a file or across files, and if so, which copy should win? - Is `message_id` unique globally, or only within its conversation? - Which warehouse and orchestrator are available, and does the warehouse support `MERGE`? - Which analyses will use the data (for example usage per model version, latency, token cost), which determines grain and partitioning? - Does `content` contain personal data that must be masked or access-controlled? - How large is a typical daily file? ### Part 1 — Schema DDL Write the DDL for the warehouse tables that hold this data. ```hint Grain first Decide the grain of each table before choosing columns: one row per what? Then decide what uniquely identifies a row. ``` #### What This Part Should Cover - Table grain, keys and data types, including time zones - Partitioning or clustering for the expected queries - Where invalid records and load metadata go ### Part 2 — Parse, validate and deduplicate Write pseudocode that parses a file, validates records, and deduplicates them. It must cope with unexpected types, impossible values and malformed JSON. The walkthrough then asked two questions about this part: - "You are currently defaulting some missing values to 0. Why don't you just delete them? Why don't you keep them as NULL?" - "How would you do global dedup, that is, deduplicate against the entire table rather than only within the daily ingestion?" ```hint Row, field or file For each kind of problem, decide whether it should reject the whole line, reject one message, null out one field, or stop the whole run. ``` ```hint What zero means Ask what an average or a sum over a column would report if an unknown value were recorded as zero. ``` #### What This Part Should Cover - Handling of malformed lines, type mismatches and impossible values, with a quarantine for rejects - The missing-value policy and its effect on downstream aggregates - Deduplication within a file and across the whole table, and its cost as the table grows ### Part 3 — Daily job scheduling and idempotency Describe how the job is scheduled and how you make it idempotent. The walkthrough asked: "If a conversation spans two days, what would you do to keep the data pipeline idempotent?" ```hint Reruns in any order Consider rerunning yesterday's load after today's has already finished. What must not happen to the rows today's load wrote? ``` #### What This Part Should Cover - Triggering, parameterization by the file's logical date, and retries - Why rerunning a load produces the same table, including out-of-order reruns and backfills - Conversations and derived metrics that cross the day boundary ### Part 4 — Data skew on Spark "If the job runs on Spark and one worker node has a data skew issue, what would you do?" ```hint Find the key Start by finding which key or input is skewed and why, before choosing a fix. ``` #### What This Part Should Cover - How to confirm skew and find its cause - Remedies for skewed joins and aggregations, and their costs - Skew that comes from the input files themselves ### What a Strong Answer Covers - A schema whose keys make deduplication and idempotency possible - Explicit, defensible data-quality policies instead of silent defaults - Loads that are idempotent and independent of run order - Awareness of cost as the table grows: partition pruning and avoiding full-table scans - Operational concerns: monitoring data-quality rates, alerting and backfills ### Follow-up Questions - A new model version starts logging an extra field and changes `latency_ms` from an integer to a float. How does your pipeline react? - Analysts want a daily table of conversations per model version. How do you keep it correct when late data arrives? - A user asks for their data to be deleted. What has to change in your tables and pipeline? - How would you test this pipeline before the first production run?