Chapter 9: Data Cleaning and Transformation Pipelines in Practice
The chapter introduces a capstone that brings earlier AI-assisted ETL work into a coordinated pipeline for continuously arriving event data.
After this chapter you will be able to
- Continuous event data challenge. Describe how the capstone moves from small, neatly scoped datasets and simple batches to noisy, semi-structured or unstructured event data that arrives continuously. Explain that data quality, transformation, and lifecycle practices must work in that setting.
- Coordinated pipeline roadmap. Explain that the chapter introduces Apache Airflow task-based orchestration alongside an event-driven mindset to coordinate familiar steps such as parsing, quality rules, and AI enrichment. State that the pipeline may run on a schedule or in response to new events, while most code is still run in a Jupyter notebook.
- Purpose of data orchestration. Explain how orchestration turns separate scripts into a pipeline that defines, sequences, and monitors tasks as data moves, transforms, and reaches its destination.
- Workflows as Python DAGs. Explain that Airflow defines workflows as version-controlled Python DAGs rather than ad hoc scripts or GUI schedules, allowing teams to build, test, deploy, and monitor pipelines using software-development practices.
- Limits of time-based scheduling. Explain how a scheduled ETL pipeline can appear reliable when every extract finishes on time, yet produce stale or inaccurate results when an extract arrives late or runs long, or a transform fails or retries.
- Dependency-aware ETL orchestration. Distinguish orchestration from scheduling alone: ETL tasks should run in response to dependencies and pipeline state, after preceding steps have completed with fresh, valid data, rather than simply because a clock reaches a set time.
- Task-based pipeline decomposition. Describe breaking a pipeline into meaningful Extract, Transform, or Load tasks rather than treating it as one large script. Explain how a notebook cell that performs a distinct operation could become a task.
- DAG dependency execution. Explain that explicit dependencies connect tasks into a directed acyclic graph (DAG), where a task runs only after its upstream dependencies succeed. Distinguish this from relying on separate clock-based schedules to coordinate steps.
- Event-driven architecture fit. Explain why continuously arriving individual actions or messages call for an event-driven architecture rather than waiting for flat files, batch extracts, or simple API calls.
- Event records and payloads. Describe an event as a record of something that happened at a particular time, commonly carried as a small, structured JSON payload describing an action and its context.
- Shared event structure contract. Explain why event producers and consumers agree on a relatively rigid structure, including field names, types, and required attributes, so downstream systems can understand and react to each self-contained message.
- Producer topic consumer flow. Trace how a producer emits events to a named topic and how multiple consumers subscribe to that topic to process events as they arrive.
- Topic organization and ETL parallels. Explain how topics group related events by business purpose, and relate producers, topics, and consumers to continuous extraction, transport or staging, and transformation and loading.
- Pitch events and structured data. Explain that each row of the play-by-play file represents one pitch described in an announcer’s natural language, and that the pipeline aims to turn those descriptions into a consistent schema for baseball analysis.
- Pitch-level tabular schema. Explain why each pitch event should become one structured row with consistent columns, and identify fields that the event descriptions may support, such as inning, batter, pitcher, pitch type, velocity, and count.
- AI-assisted schema proposal. Describe how to prompt an AI model with several sample events and intended analyses to propose a compact schema, including field names, types, descriptions, and required status. Explain that it should flag ambiguities and follow-up research, while a human decides which inferable fields to retain.
- Canonical pitch identifiers. Explain why each Game 7 pitch row gets both a sequential play_id and a text-based play_hash before extraction: they let derived features and tables join back to the original pitch text.
- Typed pitch extraction workflow. Trace how the section defines a Pydantic schema and extraction prompt, applies the model to each play-by-play row, collects the structured results in a DataFrame, and merges them into the original data.
- Focused extraction for tricky fields. Explain why a prompt requesting many attributes can misclassify nuanced fields, and how a separate class and prompt for a field such as pitch type can improve reliability at the cost of more time and money.
- Need for post-extraction standardization. Explain why apparently accurate extraction still needs a data quality step: the same player or pitch type can appear under different names or abbreviations, making grouping, joins, and analysis unreliable.
- Canonical reference tables. Describe how canonical reference tables provide the normalized values against which extracted pitchers, batters, and pitch types are reconciled, including pitcher IDs and full names and pitch-type abbreviations and descriptions.
- AI pitcher name mapping. Trace how the section defines a Pydantic mapping model, builds a prompt using the pitcher reference table, and applies AI across extracted pitcher names to return a standardized pitcher ID, full name, and confidence score; state that the notebook repeats the pattern for batters and pitch types.
- Narrative features for pitches. Explain how the pipeline uses each pitch’s narrative to identify key moments and assign an excitement score from 1 to 10, then writes those features onto the enriched dataset for analysis.
- Structured baseball judgment prompt. Describe how a Pydantic response model and a baseball-specific system prompt constrain the AI’s output, using a role, explicit fields and types, decision rules or examples, and instructions to return matching JSON.
- Limits of subjective scoring. Explain why key-moment labels and excitement ratings should be treated as useful features rather than ground truth, and describe the section’s suggested ways to check and stabilize them.
- Game excitement arc. Describe how the section visualizes Game 7 by plotting excitement against play number, marking key moments, and adding a smoothed trend line.
- AI features in familiar workflows. Explain that AI-generated semantic features from plain-language commentary become columns in an analysis-ready table that can be used with ordinary pipelines, joins, and visualizations.
What it builds on
- Understanding ETL and ELT (chapter 8) · The earlier introduction to ETL and ELT lifecycle thinking.
- Transforming News Data with AI (chapter 8) · Earlier AI-assisted transformation work that the chapter brings into a larger pipeline.
- The Transformation Pipeline (chapter 8) · Earlier work on an AI-assisted transformation pipeline.
- The AI Data Engineering Code Harness (chapter 8) · The AI data engineering code harness that can run a focused extraction pass.
- Role-Playing, Domain Priming, Prompt Chaining and Beyond (chapter 3) · Role-playing and domain priming provide prompting techniques used here.
- Understanding Data Classes (chapter 6) · Data classes provide earlier context for defining structured responses.
Section by section
Introduction
The chapter introduces a capstone that brings earlier AI-assisted ETL work into a coordinated pipeline for continuously arriving event data.
- Continuous event data challenge: Describe how the capstone moves from small, neatly scoped datasets and simple batches to noisy, semi-structured or unstructured event data that arrives continuously. Explain that data quality, transformation, and lifecycle practices must work in that setting.
Why it matters: This shift explains why the chapter moves beyond individual cleaning and transformation examples. - Coordinated pipeline roadmap: Explain that the chapter introduces Apache Airflow task-based orchestration alongside an event-driven mindset to coordinate familiar steps such as parsing, quality rules, and AI enrichment. State that the pipeline may run on a schedule or in response to new events, while most code is still run in a Jupyter notebook.
Why it matters: It connects familiar ETL steps to the more automated pipeline approach the chapter introduces.
Key terms: event data, data quality checks, transformation logic, lifecycle thinking, Apache Airflow, task based orchestration, event driven, AI powered enrichment, Jupyter notebook
9.1 Data Orchestration
This section introduces orchestration as the way to make data pipelines repeatable and observable, while placing AI inside pipeline tasks rather than using it to design the orchestration.
- Purpose of data orchestration: Explain how orchestration turns separate scripts into a pipeline that defines, sequences, and monitors tasks as data moves, transforms, and reaches its destination.
Why it matters: Orchestration makes a data flow repeatable and observable rather than a collection of handoffs. - AI tasks and project scope: Distinguish embedding previously learned AI techniques in individual tasks from asking AI to design the orchestration itself. Describe the provided Airflow sports-analytics DAG as an example to explore, while noting that setting up and maintaining orchestration infrastructure is outside the book’s scope.
Why it matters: This boundary clarifies what the chapter demonstrates and what operational work remains for production.
Key terms: data orchestration, pipeline, tasks, repeatable, observable, Apache Airflow, DAG, sports analytics, individual tasks
9.1.1 Apache Airflow
Introduces Apache Airflow as a way to orchestrate scheduled data pipelines by defining workflows as Python code.
- Workflows as Python DAGs: Explain that Airflow defines workflows as version-controlled Python DAGs rather than ad hoc scripts or GUI schedules, allowing teams to build, test, deploy, and monitor pipelines using software-development practices.
Why it matters: Treating pipelines as code makes them easier to reproduce, debug, and combine. - Airflow’s orchestration role: Describe Airflow as a battle-tested engine for managing scheduled workflows at scale, supported by operators, extensibility, and integrations with Kubernetes and cloud services.
Why it matters: This explains why many data teams use Airflow to coordinate complex pipelines.
Key terms: workflows as code, Python DAGs, Directed Acyclic Graphs, version-controlled, operators, extensibility, Kubernetes, scheduled workflows
9.1.2 Beyond Scheduling
The section uses an ETL pipeline to show why fixed schedules can produce stale or inaccurate results and why orchestration must account for task dependencies and pipeline state.
- Limits of time-based scheduling: Explain how a scheduled ETL pipeline can appear reliable when every extract finishes on time, yet produce stale or inaccurate results when an extract arrives late or runs long, or a transform fails or retries.
Why it matters: A job starting on time does not mean the data it needs is ready. - Dependency-aware ETL orchestration: Distinguish orchestration from scheduling alone: ETL tasks should run in response to dependencies and pipeline state, after preceding steps have completed with fresh, valid data, rather than simply because a clock reaches a set time.
Why it matters: Dependencies make the ETL steps operate as a coordinated pipeline rather than isolated timed jobs.
Key terms: time based schedules, late arriving data, long running extracts, stale and inaccurate data products, orchestration, dependencies, state of the pipeline, fresh, valid data
9.1.3 Task Framework
This section explains how to turn a pipeline into focused tasks connected by dependencies, then use an orchestrator to run them reliably.
- Task-based pipeline decomposition: Describe breaking a pipeline into meaningful Extract, Transform, or Load tasks rather than treating it as one large script. Explain how a notebook cell that performs a distinct operation could become a task.
Why it matters: Focused tasks make pipeline logic easier to reuse, maintain, and debug. - DAG dependency execution: Explain that explicit dependencies connect tasks into a directed acyclic graph (DAG), where a task runs only after its upstream dependencies succeed. Distinguish this from relying on separate clock-based schedules to coordinate steps.
Why it matters: Dependencies let execution follow the state of the work rather than just the clock. - Airflow orchestration benefits: Describe how Apache Airflow lets engineers define tasks and their relationships, then manages execution order, retries, and monitoring. Explain why small, focused tasks make errors easier to isolate and pipelines easier to reuse and maintain.
Why it matters: This connects the task model to the practical work of operating a production pipeline.
Key terms: task framework, tasks, Extract, Transform, Load, Jupyter, dependencies, directed acyclic graph, DAG, upstream dependencies
9.2 Event Driven Architecture
This section introduces when event-driven architecture fits a data pipeline and how AI can participate as events arrive.
- Event-driven architecture fit: Explain why continuously arriving individual actions or messages call for an event-driven architecture rather than waiting for flat files, batch extracts, or simple API calls.
Why it matters: It helps you recognize when a pipeline should process data as it arrives instead of waiting for a scheduled extract. - AI participation across event scales: Describe how AI can take part in an event pipeline, from enriching one message to scoring thousands of events per second.
Why it matters: AI's role in an event pipeline need not be limited to either one-off messages or large streams.
Key terms: event driven architecture, events, streaming, batch extracts, enriching a single message, scoring thousands of events per second
9.2.1 What are events?
This section defines events as time-stamped messages and explains why producers and consumers need an agreed-upon event structure.
- Event records and payloads: Describe an event as a record of something that happened at a particular time, commonly carried as a small, structured JSON payload describing an action and its context.
Why it matters: Recognizing what an event contains makes it easier to follow how downstream systems use it. - Shared event structure contract: Explain why event producers and consumers agree on a relatively rigid structure, including field names, types, and required attributes, so downstream systems can understand and react to each self-contained message.
Why it matters: A shared contract lets different services build pipelines and applications around the same events.
Key terms: event, payload, JSON object, producers, consumers, contract, event type, unique identifier, timestamp
9.2.2 Pub/Sub and Beyond
Introduces the publish/subscribe pattern for event-driven pipelines and relates its parts to ETL.
- Producer topic consumer flow: Trace how a producer emits events to a named topic and how multiple consumers subscribe to that topic to process events as they arrive.
Why it matters: This flow is the basic pattern behind the event-driven pipelines introduced here. - Topic organization and ETL parallels: Explain how topics group related events by business purpose, and relate producers, topics, and consumers to continuous extraction, transport or staging, and transformation and loading.
Why it matters: The ETL analogy connects streaming events to familiar data-engineering work.
Key terms: producer, event, topic, consumer, publish and subscribe, business defined shape, ETL, continuous extracts, transport and staging layer, transformation and load
9.3 Pipelines in Practice
This section introduces a pipeline that turns natural-language descriptions of individual baseball pitches into structured data for analysis, using event and orchestration concepts.
- Pitch events and structured data: Explain that each row of the play-by-play file represents one pitch described in an announcer’s natural language, and that the pipeline aims to turn those descriptions into a consistent schema for baseball analysis.
Why it matters: Recognizing each pitch as an event clarifies what the pipeline processes and why it needs to structure the text. - Pitch pipeline workflow: Describe the proposed workflow as pitch events passing through extraction, validation, and transformation tasks, with most work done in a notebook and an Apache Airflow version showing how the same logic can be orchestrated as a DAG.
Why it matters: The notebook and Airflow versions connect the hands-on exercise to a production-style event pipeline.
Key terms: event, pitch, play by play commentary, consistent schema, extraction, validation, transformation, notebook, Apache Airflow, DAG
9.3.1 Inspecting the Data & Inferring the Schema
The section shows how to inspect natural-language pitch events and use AI to propose a reliable, human-reviewed schema before extracting data.
- Pitch-level tabular schema: Explain why each pitch event should become one structured row with consistent columns, and identify fields that the event descriptions may support, such as inning, batter, pitcher, pitch type, velocity, and count.
Why it matters: A consistent pitch-level schema makes the events possible to filter, group, and analyze. - AI-assisted schema proposal: Describe how to prompt an AI model with several sample events and intended analyses to propose a compact schema, including field names, types, descriptions, and required status. Explain that it should flag ambiguities and follow-up research, while a human decides which inferable fields to retain.
Why it matters: AI can surface useful fields and edge cases without taking responsibility for the final data design. - Schema prompt experimentation: Describe trying different numbers of sample events, levels of requested detail, and AI models or providers, then comparing the clarity, structure, and quality of their schema proposals. State that model responses can vary even with the same input.
Why it matters: Comparing outputs helps you choose a useful AI tool and prompt for data-design work, not just coding.
Key terms: pitch event, one row per pitch, target schema, schema, snake_case, required, vagueness, follow-up research, non-deterministic, non coding work
9.3.2 Extracting the basics
This section builds a pitch-by-pitch AI extraction workflow with stable IDs, a typed schema, and a prompt, then explains when to isolate difficult fields in separate extraction passes.
- Canonical pitch identifiers: Explain why each Game 7 pitch row gets both a sequential play_id and a text-based play_hash before extraction: they let derived features and tables join back to the original pitch text.
Why it matters: Stable identifiers keep AI-generated features connected to the pitch events they describe. - Typed pitch extraction workflow: Trace how the section defines a Pydantic schema and extraction prompt, applies the model to each play-by-play row, collects the structured results in a DataFrame, and merges them into the original data.
Why it matters: This workflow turns free-text pitch descriptions into consistent, typed data for later analysis. - Focused extraction for tricky fields: Explain why a prompt requesting many attributes can misclassify nuanced fields, and how a separate class and prompt for a field such as pitch type can improve reliability at the cost of more time and money.
Why it matters: Isolating error-prone fields can prevent costly mistakes from propagating through a production pipeline.
Key terms: Game 7, play_id, play_hash, canonical IDs, Pydantic model, schema, system message, structured output, DataFrame, overloaded extraction prompts
9.3.3 Data Quality Transformations
This section standardizes inconsistently extracted baseball data by matching names and pitch types to canonical reference values before analysis.
- Need for post-extraction standardization: Explain why apparently accurate extraction still needs a data quality step: the same player or pitch type can appear under different names or abbreviations, making grouping, joins, and analysis unreliable.
Why it matters: Consistent values are necessary for trustworthy analysis of the extracted events. - Canonical reference tables: Describe how canonical reference tables provide the normalized values against which extracted pitchers, batters, and pitch types are reconciled, including pitcher IDs and full names and pitch-type abbreviations and descriptions.
Why it matters: Reference values give the transformation a consistent target rather than letting each extracted spelling stand. - AI pitcher name mapping: Trace how the section defines a Pydantic mapping model, builds a prompt using the pitcher reference table, and applies AI across extracted pitcher names to return a standardized pitcher ID, full name, and confidence score; state that the notebook repeats the pattern for batters and pitch types.
Why it matters: This turns the canonical references into standardized fields on the pitch events.
Key terms: post-processing, standardize, pitcher_name, canonical reference tables, truth tables, pitcher IDs, pitch type abbreviations, Python dictionaries, Pydantic model, prompt
9.3.4 Advanced Transformations
This section shows how to add AI-generated key-moment labels and excitement scores to structured pitch data, then explains how to prompt for and validate those subjective features.
- Narrative features for pitches: Explain how the pipeline uses each pitch’s narrative to identify key moments and assign an excitement score from 1 to 10, then writes those features onto the enriched dataset for analysis.
Why it matters: These features let you analyze the importance and drama of plays, not just the factual pitch data. - Structured baseball judgment prompt: Describe how a Pydantic response model and a baseball-specific system prompt constrain the AI’s output, using a role, explicit fields and types, decision rules or examples, and instructions to return matching JSON.
Why it matters: A defined schema and clear criteria make the model’s judgments easier to process and less prone to unsupported or malformed answers. - Limits of subjective scoring: Explain why key-moment labels and excitement ratings should be treated as useful features rather than ground truth, and describe the section’s suggested ways to check and stabilize them.
Why it matters: Unvalidated opinion-based scores can make downstream analysis look more certain than it is.
Key terms: key moment, excitement score, enriched dataset, semantic schema, Pydantic model, system prompt, role assignment, instruction anchoring, JSON object, subjective AI scoring
9.3.5 Analysis
The section uses AI-generated excitement and key-moment features to visualize a baseball game and shows how those features make new analyses possible with familiar data tools.
- Game excitement arc: Describe how the section visualizes Game 7 by plotting excitement against play number, marking key moments, and adding a smoothed trend line.
Why it matters: The chart turns enriched play-by-play data into a view of how the game's tension changed. - AI features in familiar workflows: Explain that AI-generated semantic features from plain-language commentary become columns in an analysis-ready table that can be used with ordinary pipelines, joins, and visualizations.
Why it matters: AI can help data engineers create useful subject-matter signals, not just move existing data. - Chart scope and variability: Distinguish the full-notebook chart from the small test run, and explain why changes to the AI model, prompt, or settings may slightly alter the excitement arc.
Why it matters: The example demonstrates an analytical capability rather than a chart whose individual scores must always match exactly.
Key terms: excitement arc, excitement scores, key moments, smoothed trend line, AI engineered features, semantic features, analysis ready table, plain language commentary, full notebook, test notebook
9.4 Lab
The lab asks readers to run the complete Game 7 AI pipeline and use its enriched data to compare pitcher and batter performance.
9.5 Lab Answers
Where this is used later
- Orchestrating the pipeline (chapter 12) · Provides a way to think about organizing pipeline steps for orchestration.
Concepts explained in this chapter
- pitch typeA pitch type is a baseball classification describing how a particular pitch is thrown and moves, such as a curveball.
- schemaA database schema is the formal organization of a database's tables, fields, relationships, constraints, and related definitions.
- pitch velocityThe mean speed of a pitcher's pitches measured in miles per hour.
- excitement scoreAn excitement score is a numerical rating representing the perceived dramatic intensity of a baseball play on a specified scale.
- PydanticA Pydantic model is a structured data class defined with Pydantic fields and type annotations that can validate and serialize values.
- visualizationData visualization is the graphical representation of data to reveal patterns, relationships, trends, or comparisons.
- output formatThe response_format parameter is an API request parameter that specifies the desired structure or format of a model response.
- data transformationData transformation is the process of changing data's structure, representation, or values to meet downstream requirements.
- data frameA pandas DataFrame is a two-dimensional labeled tabular data structure in the pandas library.
- structured extractionAI-powered extraction is the use of an artificial intelligence model to identify and retrieve requested fields from source content based on field descriptions rather than fixed location rules.
- unstructured dataUnstructured data is information that lacks a consistently organized schema for its content and fields.
- data contractA data contract is a formal specification of the structure, fields, types, and validity requirements that data exchanged between processing components must satisfy.
- edge caseAn edge case is an unusual, extreme, or boundary condition that can expose limitations or failures in a system or process.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.
