6 AI & Data Quality
Data quality work in traditional pipelines is often fragmented and labor-intensive, requiring separate rules, scripts, and tools for different problems such as missing values, invalid formats, duplicates, and negative numbers. The text contrasts this with an AI-based approach, where a single natural-language prompt can describe what clean, valid data should look like and a model can help identify and resolve a wider range of issues. This shifts data quality from hard-coded checks toward a more flexible, conversational workflow that can incorporate subject matter expertise without demanding deep coding knowledge.
The chapter then shows how Python and pandas can still handle basic validation and cleanup, but only when the engineer knows the exact problems in advance and codes each case explicitly. Examples include detecting nulls, spotting negative purchase amounts, removing duplicate rows, and dropping columns that are mostly empty. The AI-powered alternative uses the Chat Completions API to inspect data and return structured guidance, making it easier to catch inconsistencies that are harder to anticipate. The tradeoff is clear: Python is fast, local, and precise, while AI adds cost and latency but provides broader coverage and adaptability as rules evolve.
To make AI suitable for production data engineering, the text emphasizes structured outputs through data classes and response formatting. Pydantic BaseModel classes define the expected shape of the model’s response, so the API can return clean, parseable objects instead of fragile free-form text. The chapter also highlights using multiple messages to separate system instructions from user data, improving accuracy and scalability. Overall, it presents AI as a reusable data cleaning layer that can standardize dates, validate identifiers, truncate text, map categories, and other transformations while reducing manual parsing and maintenance overhead.
Traditional data quality pipelines often require multiple tools—like SQL, pandas, and scikit-learn—to handle tasks such as null detection, format enforcement, and rule-based validation. As shown in figure 6.1, each tool typically addresses a narrow part of the workflow. With AI, however, many of these tasks can be consolidated into a single interface. A well-prompted model can reason across diverse data quality issues, from detecting duplicates to mapping references, enabling a unified, intelligent path to clean data.
The data class acts as a contract between the prompt and the model’s response. The prompt defines what kind of output we want, the data class formalizes that structure, and response_format ensures the model returns data in a predictable, usable format. This makes it easy to extract structured results directly from the model without extra parsing.
Dataframe before transformations (top) and the dataframe after transformations (bottom). This is the output of the implementation of listing 6.5 and shows the type of cleaning we need to do in order to make our data legible and useful to downstream consumers.
Cleaned dataset after applying AI-generated standardization rules. The AI model returned structured outputs for date normalization, SKU validation, description truncation, name concatenation, and category mapping. Each transformation was handled in a single API call using a custom response schema, producing a clean, ready-to-use dataset with minimal parsing logic in the code.
Lab Answers
Refer to the Chapter 6 Lab Jupyter Notebook for full answers.
- You should follow the same pattern we used in Listing 6.5. To clean date formats, a custom function like this is used:
SKU format is enforced withregex:
To validate emails, use this regex and filter the DataFrame:
Create the full_name column using:
Description-based category mapping is done with a dictionary:
These techniques match the patterns introduced earlier in the chapter using pandas, map(), apply(), and filtering logic.
- This step uses the same techniques introduced earlier in the chapter—structured prompting, a BaseModel schema, and the response_format parameter. But once we move from toy data to real-world messiness, we hit a problem: the model doesn’t always return one row for every input.
In earlier listings (like 6.4 and 6.6), we passed the full dataset in a single API call:
This works fine if the model returns a perfectly aligned list for each field. But if even one row is skipped, you’ll get dreaded ValueErrors.
Instead of processing the whole DataFrame at once, we clean each record individually and rebuild a new dataset:
This avoids the entire row-alignment issue by guaranteeing each response matches exactly one input. Plus, using tqdm adds helpful feedback during processing.
The logic and structure of the API call hasn’t changed—but applying it one row at a time makes the system far more reliable, especially when cleaning dirty data in production environments.
Learn AI Data Engineering ebook for free
