Raw CSV Data Ingestion & Analytics Pipeline
Asynchronous Enrichment Pipeline
Uploads of real-world data are almost always broken. Emails are malformed, phone numbers are unformatted, company names are inconsistent, fields are missing, and the delimiter is occasionally wrong. Loading that into any system means hours of manual cleanup per batch — and the manual cleanup is invisible, unaudited and inconsistently applied. This pipeline automates the loop: upload, enrich, review the uncertain rows, export something clean.
Technology
The problem
Lead data arrives from vendors, events and scrapers as pipe-delimited fragments with malformed emails like john.doe@googlecom, phone numbers in any format imaginable, inconsistent casing and missing names. Cleaned by hand it costs hours per batch, produces inconsistent results, ships dirty records downstream, and leaves no record of what was changed or why. Automation is only safe here if a person can still overrule it — which means the pipeline has to know the difference between confident and uncertain.
Approach
- 01
Moved the work off the request thread immediately. The upload endpoint validates and enqueues, then returns a batch id. Everything expensive happens in a Celery worker, because the workload is I/O-bound on external model calls and would otherwise occupy a web worker for the length of the batch.
- 02
Made the model a component, not an oracle. Enrichment calls a chat completion constrained to a strict Pydantic schema, so the response is parsed and validated into typed fields rather than pattern-matched out of prose. An unexpected shape becomes a validation failure, not a corrupted record.
- 03
Isolated failures per row. Each record commits in its own transaction, so one malformed response cannot roll back a batch that has already processed hundreds of good rows. Anything that fails validation degrades to a zero-confidence record that gets flagged rather than discarded.
- 04
Designed review into the data model, not the interface. Every row carries a confidence score, and rows that score low or are structurally incomplete are flagged automatically and surfaced in a review drawer where an operator can correct and clear them.
- 05
Kept the model deterministic where possible. Temperature is pinned to zero with structured output requested explicitly, so the same input yields the same shape and repeatable behaviour — with a single retry path for models that reject the structured-output parameter.
- 06
Made the export a streaming operation. Cleaned batches download through a chunked generator rather than building an entire file in memory, so export cost does not scale with batch size in a way that exhausts the worker.
Architecture
- 01
Upload
A multipart CSV is posted to the API, a batch record is created in a pending state, and the request returns a batch id straight away.
- 02
Queue
The batch is dispatched to Celery over Redis as both broker and result backend, so the web process is free immediately.
- 03
Worker & enrichment
A worker processes rows individually. Each is normalised through a Pydantic schema with a bounded confidence score, calling a chat completion for structured output.
- 04
Per-row transaction
Each row commits independently. A parse or schema failure becomes a zero-confidence fallback that the pipeline flags for review instead of aborting the batch.
- 05
Human review
Low-confidence and structurally incomplete rows are surfaced in a review drawer. Operator corrections are restricted to whitelisted fields and clear the review flag.
- 06
Streaming export
Cleaned batches are generated and streamed in chunks as a CSV download, ready for import into a downstream system.
The model is constrained to a schema, the worker commits per row, and a person can overrule any result the pipeline is not confident about.
Key features
Challenges
Treating model output as untrusted input
A language model is not a database — it returns whatever shape it feels like returning. Wrapping the call in a strict Pydantic schema, clamping the confidence range and normalising the strings turns a response into a typed object or a clean failure, instead of a value that quietly propagates a bad email downstream.
Keeping one bad row from destroying a whole batch
A single transaction per batch means a late failure discards hours of completed work. Committing per row means the worst case is one flagged record, and the batch still finishes — which is the difference between a pipeline people trust and one they retry from scratch.
Knowing when not to trust the automation
Fully automatic cleaning is only acceptable if a person can review and overrule it. That requirement shaped the data model: a confidence score is a first-class field, and uncertainty is a state the interface is designed to surface, not an implementation detail to hide.
Keeping the client cheap to poll
The interface polls live progress, which is easy to make expensive. Polling a lightweight status endpoint, refetching the grid only when the processed count actually advances, and deriving throughput from the delta between polls kept the load proportional to real progress.
What I learned
Schema validation is the boundary that makes an LLM usable inside a system. The model proposes; the schema decides. Everything downstream of that boundary behaves like ordinary software, which is the only reason an automated pipeline is safe to hand to someone else to operate.
Scope & current state
Being precise about scope
- This is a backend and data-processing engineering project — asynchronous task architecture, validation layers, transactional integrity and streaming I/O. It is not a data-analysis or reporting product.
- The repository includes a sample dirty CSV used for local verification runs.