Design an LLM-Assisted Pipeline to Clean and Import Schema-Less Client CSV Files

In a 45-minute client-scenario interview you advise a customer that sells a SaaS product for demographic and payroll analytics. Its clients send data as CSV files, and today a six-person operations team cleans every file by hand and then imports it into the product's database. Design a system that automates this cleaning and import. What the customer tells you: - Each file has between 100 and 50,000 rows. - There is no common input schema. Headers may carry no meaning (for example columns named A, B and C), and some fields must be restructured, such as a full name that has to be split into separate name columns. - The output is a fixed table of about 40 columns. - Volume is growing to more than 10,000 files per month, and processing currently costs about \$17 per file. - Batch processing is acceptable: a turnaround of a few hours to a few days is fine. - The customer's priorities are cost per file, accuracy, and exception handling. Walk through how the system turns an incoming file into rows of the 40-column table, where a language model fits in, how the cost per file behaves as files get larger and more numerous, how you know the output is correct, and what happens to the files and rows the automation cannot handle. ```hint What grows with the rows Compare a 100-row file and a 50,000-row file from the same client, and ask which parts of the cleaning work actually differ between them. ``` ```hint Catching mistakes before the database Think about how a wrong column mapping or a malformed value would be noticed before it reaches the customer's table, and who resolves it. ``` ### Clarifying Questions - What makes up the current \$17 per file: mostly operations staff time, or also tooling and infrastructure? - Do clients send files in the same layout month after month, and roughly how many distinct clients and layouts are there? - Is a partial import acceptable, loading the valid rows and holding the rest, or must each file load completely or not at all? - Are there historical files together with the operations team's cleaned output that could serve as ground truth? - Which validation rules does the 40-column target define: required fields, types, allowed values, rules that span several columns? - May client payroll and demographic data be sent to an external model provider, and under what restrictions? ### What a Strong Answer Covers - How the cost per file scales with the number of rows and the number of files, compared with sending every row through a model - Deterministic parsing and profiling, and a sample that represents every format present in the whole file - A reviewable transformation that ordinary code applies to all rows, and reuse of approved transformations for repeat layouts - Validation at file, column and row level, and a way to measure accuracy against ground truth before and after launch - Exception handling: confidence-based routing to the operations team, row quarantine, and feeding corrections back into the transformation - Protection of sensitive payroll data, and the metrics used to run the system: cost, accuracy, exception rate and turnaround ### Follow-up Questions - Two exports were appended into one file, and the date format changes halfway through. How does your design notice? - How would you show the operations team that automated imports are at least as accurate as their manual work before turning them on? - A file has no header row and two columns that both look like dates. How does the system decide which is which? - How do you keep the language model from seeing more personal payroll data than it needs?