Chapter 7: AI and Advanced Data Transformations
The chapter introduces advanced data transformations as a real-world data engineering need and presents AI as a conversational way to simplify varied transformation work.
After this chapter you will be able to
- Conversational AI Consolidation. Explain why AI is presented as a simplifying tool for advanced transformations: traditional approaches demand varied expertise, frequent context switching, and familiarity with many tools, libraries, and requirements, while one conversational interface brings those needs together.
- Fixed regex capture groups. Describe how the example regex uses capture groups to extract an allowed log level, a YYYY-MM-DD date, and an HH:MM:SS time, with spaces separating the parts. Explain why this precise pattern can need revision when log formats vary.
- AI extraction and message extension. Contrast fixed regex with natural-language instructions for AI extraction, then describe how to add the message after the timestamp: add a message field to the LogExtraction model, update the prompt, and rerun to check all four output values.
- AI log extraction safeguards. Explain plausible AI extraction failures—such as invented fields, incorrect but valid-looking patterns, inconsistent output formats, or overfitting to example log levels—and state that results should be checked against a known schema or test logs before production.
- Nested JSON flattening. Explain why the library JSON needs flattening for tabular analysis and describe what a Python extraction must do: traverse books, combine nested author details, make genres readable, and carry over library information.
- AI schema mapping. Describe how defining a target Python data class with Pydantic lets the model map nested book data into that schema instead of requiring custom parsing logic. State that shared library attributes, such as name and location, still need to be included in the payload.
- Identity across differing records. Explain that entity resolution identifies records referring to the same real-world entity despite differences in names, accounts, or other fields. Describe how signals across attributes can connect records that look unrelated at first.
- Prompt-based match reasoning. Explain how the AI approach uses natural-language instructions and a structured schema to compare multiple attributes and return a best match with both confidence and reasons. Describe why its moderate-confidence choice of John Smith was not definitive.
- Explicit matching prompt rules. Justify specifying field importance, authoritative sources, and treatment of ambiguity in an entity-resolution prompt. Explain the risks of leaving rules unstated or overloading the prompt, and the need to iterate and calibrate it.
- Reporting Date Representations. Describe how a transaction date is standardized and expanded into a date without a timestamp, timestamps in PST, EST, and GST, month and year, and a company-specific fiscal quarter whose Q1 runs from February through April.
- Business Due Dates and Balance Shares. Explain how payment terms such as NET30 or NET60 determine due dates using business days rather than calendar days, and how each transaction's balance is expressed as a percentage of the total outstanding balance for its account.
- Traditional and AI Transformation Tradeoffs. Contrast the traditional implementation's BDay import, custom fiscal-quarter and due-date functions, and separate account_totals object with the AI implementation's detailed system prompt, Pydantic response schema, per-transaction loop, and supplied context. State that the AI version still needs upfront per-account aggregation, while the two approaches produce identical, verifiable results in this exercise.
What it builds on
- (chapter intro) (chapter 6) · The preceding chapter's focus on using AI to address data quality challenges.
- Using AI to Implement Regex Patterns (chapter 4) · earlier use of AI to implement regex patterns
- Understanding Data Classes (chapter 6) · using data classes to define expected output fields
- Complex Example: Extracting Deeply Nested & Combined Fields (chapter 4) · Earlier work extracting deeply nested and combined JSON fields.
Section by section
Introduction
The chapter introduces advanced data transformations as a real-world data engineering need and presents AI as a conversational way to simplify varied transformation work.
- Advanced Transformation Context: Explain that the chapter moves from using AI for data quality to using it for advanced transformations, which help engineers handle complex data scenarios precisely.
Why it matters: This transition explains why the chapter moves beyond fixing data quality issues. - Conversational AI Consolidation: Explain why AI is presented as a simplifying tool for advanced transformations: traditional approaches demand varied expertise, frequent context switching, and familiarity with many tools, libraries, and requirements, while one conversational interface brings those needs together.
Why it matters: This is the chapter's reason for using AI across different transformation challenges.
Key terms: data quality, advanced data transformations, context switching, conversational interface, one size fits all
7.1 Complex Text Processing with Regular Expressions
This section shows how to turn messy log entries into structured data using either a precise regular expression or AI-assisted extraction, and why AI results must be validated.
- Structured value of log data: Explain why extracting fields from unstructured logs makes them useful for analysis, such as counting errors, identifying affected users, or investigating changes after a deployment.
Why it matters: Structured log fields let you query information that is difficult to use in a wall of text. - Fixed regex capture groups: Describe how the example regex uses capture groups to extract an allowed log level, a YYYY-MM-DD date, and an HH:MM:SS time, with spaces separating the parts. Explain why this precise pattern can need revision when log formats vary.
Why it matters: The pattern shows both how hand-written extraction works and why changing log formats make it difficult to maintain. - AI extraction and message extension: Contrast fixed regex with natural-language instructions for AI extraction, then describe how to add the message after the timestamp: add a message field to the LogExtraction model, update the prompt, and rerun to check all four output values.
Why it matters: The exercise connects a requested field to both the AI instruction and the structured output it must produce. - AI log extraction safeguards: Explain plausible AI extraction failures—such as invented fields, incorrect but valid-looking patterns, inconsistent output formats, or overfitting to example log levels—and state that results should be checked against a known schema or test logs before production.
Why it matters: AI can speed extraction while producing plausible output that breaks downstream automation or misrepresents the logs.
Key terms: log file, structured fields, query-ready data, regex, capture groups, log level, \s, AI-driven approach, conversational prompts, LogExtraction model
7.2 Handling Hierarchical and Nested Data Structures
This section compares hand-coded Python flattening with AI-assisted mapping of nested library JSON into structured book records.
- Nested JSON flattening: Explain why the library JSON needs flattening for tabular analysis and describe what a Python extraction must do: traverse books, combine nested author details, make genres readable, and carry over library information.
Why it matters: Flattening turns relationships stored in nested JSON into records that can be loaded into a DataFrame and queried. - AI schema mapping: Describe how defining a target Python data class with Pydantic lets the model map nested book data into that schema instead of requiring custom parsing logic. State that shared library attributes, such as name and location, still need to be included in the payload.
Why it matters: The distinction shows what the model can extract and what the surrounding code must still provide. - Schema change experiments: Describe how to test the approach by changing book structures, adding a static attribute such as library_type, or switching to movies with a new data class and prompt. Explain that the intended advantage is updating the target definition rather than rewriting extraction logic, while checking whether the existing harness still works.
Why it matters: Trying altered inputs tests whether the claimed flexibility holds for a real schema change.
Key terms: nested JSON, flatten, DataFrame, author, genres, Pydantic, data class, target schema, payload, shared attributes
7.3 Entity Resolution
This section compares hand-coded and AI-based entity resolution, then shows why reliable AI matching depends on carefully specified and calibrated prompts.
- Identity across differing records: Explain that entity resolution identifies records referring to the same real-world entity despite differences in names, accounts, or other fields. Describe how signals across attributes can connect records that look unrelated at first.
Why it matters: Recognizing one person across fragmented records makes a more complete customer view possible. - Limits of hand-coded matching: Describe how traditional matching requires programmed normalization, similarity measures, field choices, and interpretation of a score. Explain why a likely match from that approach can remain hard to justify or adapt when fields change.
Why it matters: A similarity score alone may not explain a decision or keep the matching logic useful as data changes. - Prompt-based match reasoning: Explain how the AI approach uses natural-language instructions and a structured schema to compare multiple attributes and return a best match with both confidence and reasons. Describe why its moderate-confidence choice of John Smith was not definitive.
Why it matters: An explained, qualified match is more useful than treating a score as certainty. - Explicit matching prompt rules: Justify specifying field importance, authoritative sources, and treatment of ambiguity in an entity-resolution prompt. Explain the risks of leaving rules unstated or overloading the prompt, and the need to iterate and calibrate it.
Why it matters: The prompt is the matching logic, so its design determines whether AI decisions fit the workflow.
Key terms: entity resolution, real-world entity, shared signals, string normalization, similarity scoring, heuristic rules, field weighting, fingerprints, AI-based approach, prompt
7.4 Time Series and Date-Time Transformations
The section transforms accounts-receivable transactions into reporting-ready dates, due dates, and balance measures, then compares traditional Python with an AI-assisted approach.
- Reporting Date Representations: Describe how a transaction date is standardized and expanded into a date without a timestamp, timestamps in PST, EST, and GST, month and year, and a company-specific fiscal quarter whose Q1 runs from February through April.
Why it matters: These fields make the same transaction usable across time zones and business reporting calendars. - Business Due Dates and Balance Shares: Explain how payment terms such as NET30 or NET60 determine due dates using business days rather than calendar days, and how each transaction's balance is expressed as a percentage of the total outstanding balance for its account.
Why it matters: Business-aware due dates and within-account balance shares support accounts-receivable aging and prioritization. - Traditional and AI Transformation Tradeoffs: Contrast the traditional implementation's BDay import, custom fiscal-quarter and due-date functions, and separate account_totals object with the AI implementation's detailed system prompt, Pydantic response schema, per-transaction loop, and supplied context. State that the AI version still needs upfront per-account aggregation, while the two approaches produce identical, verifiable results in this exercise.
Why it matters: The comparison shows what AI simplifies without hiding the context and preparation it still requires. - Extending the AI Output Contract: Describe how to add a timezone, change the fiscal calendar, or add a derived field by updating the TransformedTransaction Pydantic class and the system_prompt instructions. Compare that change with the timezone research, custom mapping, or column-integrity work a pure-Python change might require, without assuming AI flexibility is free of complexity.
Why it matters: Keeping the output schema and instructions aligned is essential when requirements change.
Key terms: transaction_date, PST, EST, GST, fiscal quarter, NET30, NET60, business days, account_totals, total outstanding balance
7.5 Lab
The lab asks readers to use Python and AI to clean, merge, and analyze three messy customer and transaction datasets.
7.6 Lab Answers
The lab answers show how AI can handle varied data transformations while making changing formats and business rules easier to accommodate.
Where this is used later
- Transforming News Data with AI (chapter 8) · Using AI for advanced and domain-aware transformations.
- The Transformation Prompt (chapter 8) · specifying the desired data in a transformation prompt
- The Transformation Pipeline (chapter 8) · checking AI-produced transformations within a pipeline
- Advanced Transformations (chapter 9) · Advanced transformations build on turning complex source data into usable fields.
Concepts explained in this chapter
- entity resolutionEntity resolution is the process of determining which records refer to the same real-world entity despite differences or errors in their attributes.
- output formatThe response_format parameter is an API request parameter that specifies the desired structure or format of a model response.
- regular expressionA regular expression is a pattern language for matching, extracting, or replacing text according to specified character and sequence rules.
- payment termsNet payment terms specify the number of days allowed between an invoice and its required payment date.
- percentage contributionA percentage contribution is the proportion of a specified total attributable to an item, account, or component, expressed as a percentage.
- data transformationData transformation is the process of changing data's structure, representation, or values to meet downstream requirements.
- prompt engineeringPrompt engineering is the deliberate design and refinement of inputs provided to an AI model to influence the quality, format, or behavior of its outputs.
- LogExtraction modelThe LogExtraction model is a structured data model representing extracted log type, date, time, and message fields.
- hallucinationAn AI hallucination is a confident but incorrect or misleading output produced by an artificial intelligence model.
- dataclassA data class is a class whose primary purpose is representing structured data, typically with declarative fields and automatically supplied data-oriented behavior.
- JSONJSON is a text-based data interchange format that represents structured values using objects, arrays, strings, numbers, booleans, and null.
- PydanticA Pydantic model is a structured data class defined with Pydantic fields and type annotations that can validate and serialize values.
- schemaA database schema is the formal organization of a database's tables, fields, relationships, constraints, and related definitions.
- timezone conversionTimezone conversion is the transformation of a timestamp from one timezone representation to another while preserving the represented instant.
- artificial intelligenceArtificial intelligence is the field of creating computational systems that perform tasks associated with human intelligence, such as reasoning, learning, perception, and decision-making.
- 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.
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.
