9 Data Cleaning and Transformation Pipelines in Practice

The chapter brings together the core ideas of AI-assisted cleaning, transformation, and loading into a more realistic workflow built around noisy, continuously arriving event data. Using a World Series play-by-play dataset as the example, it shows how raw natural-language records can be turned into structured, analysis-ready rows through a pipeline that handles schema inference, extraction, validation, and enrichment. The emphasis is on moving from small, tidy exercises to production-like thinking, where data quality and transformation logic must work reliably on messy inputs.

A major theme is orchestration: instead of relying on time-based scheduling alone, the chapter explains how task-based pipelines and directed acyclic graphs help manage dependencies, retries, and monitoring. Apache Airflow is presented as a practical orchestration tool for defining workflows as code, making pipelines more reproducible and maintainable. The text also introduces event-driven architecture and pub/sub concepts, showing how producers, topics, and consumers resemble ETL stages but better fit streaming and near-real-time scenarios.

The practical pipeline starts by assigning stable identifiers to each pitch event, then uses a structured prompt and Pydantic schema to extract fields such as inning, count, pitcher, batter, pitch type, velocity, and result. It then standardizes inconsistent names and pitch labels with canonical reference tables, and applies a second AI pass to derive higher-level features like whether a play is a key moment and how exciting it is. The resulting enriched dataset supports familiar analytics and visualizations, such as pitcher workload summaries, batter contact patterns, and an excitement arc across the game, while also highlighting the need to manage ambiguity, subjectivity, and model variability carefully.

Ideal time based scheduling where extracts from sources A, B, and C, the transform, and the load all complete on time.

Real world time based scheduling with late arriving data, long running extracts, and transform errors that lead to stale downstream data products.

Sources A, B, and C feed a single Transform task that flows into a Load task, forming a simple DAG where dependencies control execution, which can run ephemerally or be started on a schedule, such as every fifteen minutes, once an hour, or via an external trigger.

A simple publish and subscribe pattern where a producer writes events to a topic, and multiple consumers subscribe to that topic to read and process the events independently.

Excitement arc for World Series Game 7, plotting pitch by pitch excitement scores with red markers highlighting key moments and a dashed line showing the smoothed excitement trend across the game.

Excitement arc for World Series Game 7, plotting pitch by pitch excitement scores with red markers highlighting key moments and a dashed line showing the smoothed excitement trend across the game.

Result of aggregating pitcher performance.

Result of visualizing aggregated pitcher performance.

Result of visualizing aggregated batter performance on a table format.

Result of visualizing aggregated batter performance on a bar charts.

FAQ