Data Preparation and Transformation

Introduction

Business intelligence and data visualization are only as reliable as the data that supports them. A dashboard may be visually attractive and technically sophisticated, yet still produce misleading decisions if customer records are duplicated, dates are interpreted inconsistently, sales are missing, or measures from different systems are combined incorrectly. Data preparation is therefore not a preliminary clerical activity; it is a central analytical and governance process.

Data preparation converts raw, heterogeneous data into data that is sufficiently accurate, consistent, documented, and accessible for analysis. It normally includes acquiring data, profiling its condition, cleaning errors, integrating sources, transforming structures and values, and loading the result into an analytical destination. These activities are often organised as ETL—extract, transform, load—or ELT—extract, load, transform.

A professional data-preparation process must be repeatable. If an analyst manually edits a spreadsheet every month, the resulting report may be impossible to reproduce or audit. By contrast, a documented and automated pipeline can be rerun when new data arrives, tested when business rules change, and monitored for quality problems.

Learning Objectives

By the end of this chapter, you should be able to:

• explain the purpose and stages of data preparation;
• distinguish structured, semi-structured, and unstructured data;
• profile data using completeness, uniqueness, validity, consistency, and accuracy dimensions;
• identify and handle missing values, duplicates, outliers, and inconsistent formats;
• integrate data from multiple sources using keys and documented business rules;
• apply common transformations such as filtering, joining, aggregation, reshaping, and encoding;
• compare ETL and ELT architectures;
• design a reproducible and controlled preparation workflow; and
• evaluate ethical, governance, and operational risks in data preparation.

1. The Data-Preparation Lifecycle

A typical preparation lifecycle consists of the following stages:

1. Acquire data from operational databases, files, application programming interfaces (APIs), sensors, surveys, or external providers.
2. Profile the data to understand its structure, distributions, quality, and risks.
3. Clean errors, missing values, invalid records, duplicates, and inconsistent representations.
4. Integrate datasets by aligning definitions, keys, granularity, and formats.
5. Transform data into structures suitable for reporting, visualization, or modelling.
6. Validate outputs against technical and business rules.
7. Load and publish the prepared data to a warehouse, lakehouse, semantic model, or reporting platform.
8. Monitor and maintain the pipeline as sources and requirements change.

These stages are iterative rather than strictly linear. Profiling may reveal that a source cannot support the intended analysis. Validation may reveal that a transformation has changed the meaning of a measure. Monitoring may identify a new category or a sudden increase in missing values.

Figure: Data preparation is an iterative lifecycle with validation and feedback.

2. Understanding Data and Its Structure

Data may be structured, such as rows and columns in a relational database; semi-structured, such as JSON or XML containing tags and nested objects; or unstructured, such as free text, images, audio, and video. Business intelligence projects commonly combine all three.

A dataset has a schema, which describes fields, data types, relationships, and constraints. For example, an order table might contain OrderID, CustomerID, OrderDate, ProductID, Quantity, UnitPrice, and Currency. The grain of a table specifies what one row represents. A row may represent one order, one order line, one customer per month, or one store per day. Confusing these grains is a major source of incorrect totals.

A fact records an event or measurable activity, such as a sale. A dimension describes the context of that event, such as customer, product, location, or date. In a star schema, a fact table is connected to dimension tables through keys. A clear grain and well-defined keys are essential before joining or aggregating data.

Data types should reflect meaning, not merely appearance. A postal code may contain digits but should usually be stored as text because arithmetic is not meaningful and leading zeros matter. A currency amount should be numeric, while a date should be stored as a date rather than as a formatted text string.

3. Data Profiling and Quality Assessment

Data profiling is the systematic examination of data before transformation. It answers questions such as:

• What fields exist and what are their data types?
• How many records and distinct values are present?
• Which values are missing or invalid?
• Are identifiers unique?
• What ranges and distributions occur?
• Do relationships between tables hold?

Important data-quality dimensions include:

A completeness rate for a field can be expressed as:

where N is the total number of records and M is the number of records with missing values in the field. The formula assumes that each record should contain a value and that missingness has been defined consistently. If missing values are legitimate—for example, a customer may have no second telephone number—the field should not be treated as universally mandatory.

For example, if 1,920 of 2,000 customer records contain an email address, completeness is (2000 − 80) ÷ 2000 × 100% = 96%. A high completeness rate does not prove accuracy: a field could contain 96% invalid or placeholder values.

Profiling should also inspect frequency distributions, minimum and maximum values, date ranges, category spelling, null representations such as “N/A” or “unknown,” and suspicious constants such as the same date appearing for every record.

4. Cleaning Common Data Problems

4.1 Missing values

Missing data may arise because a value was not collected, was lost during transfer, is not applicable, or was deliberately suppressed. The appropriate response depends on its meaning and analytical use.

Common strategies include:

• Deletion: remove records or fields when missingness is limited and removal will not introduce bias.
• Imputation: replace missing values using a justified rule, such as a median for a skewed numerical variable or a category such as “Not provided.”
• Carry-forward or interpolation: useful for certain time-series contexts, but inappropriate when periods are independent.
• Flagging: retain the missing value and add an indicator showing that it was missing.
• Source correction: improve the collection process when the field is important.

Imputation changes the data and should be documented. A median replacement, for instance, reduces the apparent variation in a variable. Missingness may also be systematic: if rural customers are less likely to provide a postcode, deleting those records can distort geographic analysis.

4.2 Duplicates

Duplicates may be exact copies or partial duplicates with small differences. A reliable duplicate rule should be based on business identity, not simply identical rows. An order line may be identified by OrderID plus ProductID and LineNumber, whereas an OrderID alone may identify the whole order.

When duplicate records conflict, retain an audit trail and apply a documented survivorship rule—for example, prefer the latest verified record. Never remove records solely because they look similar without understanding the table grain.

4.3 Invalid and inconsistent values

Standardisation may include trimming whitespace, converting text to a consistent case, mapping abbreviations, validating email patterns, and converting dates with an explicit locale. The date “03/04/2025” means 3 April in some contexts and 4 March in others. ISO-style dates such as 2025-04-03 reduce ambiguity.

Units and currencies require special care. If sales are recorded in different currencies, conversion requires an exchange-rate date and a documented rate source. Adding dollar, euro, and yen amounts without conversion produces a meaningless total.

4.4 Outliers

An outlier is an observation that is unusually distant from other observations. It may be a valid exceptional event, a data-entry error, a unit error, or a fraud signal. Outliers should not be deleted automatically. First investigate their origin and business meaning.

A common exploratory rule uses the interquartile range:

Values below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR are often flagged for investigation. Here, Q₁ and Q₃ are the first and third quartiles. This is a screening convention, not proof that a value is erroneous. Its suitability depends on the distribution and purpose of the analysis.

5. Integration: Combining Sources Correctly

Integration combines data while preserving meaning. The principal technical issues are keys, granularity, schema matching, and conflicting definitions.

A primary key uniquely identifies a record in its table. A foreign key refers to a key in another table. A join should be based on a justified relationship. Joining customer data to order-line data on CustomerID may be valid, but joining on customer name is risky because names are not necessarily unique or stable.

Common joins include:

• Inner join: retains only matching records in both sources.
• Left join: retains every record from the left source and matching records from the right.
• Full outer join: retains records from both sources, including nonmatches.

A frequent error is a many-to-many join that multiplies rows. If a customer has three orders and two service cases, joining both tables directly on CustomerID can create six combinations. The safer approach is often to aggregate each table to customer level before joining.

Integration also requires schema matching. “Client,” “customer,” and “account” may or may not describe the same entity. “Revenue” may mean invoiced sales in one system and cash received in another. A data dictionary should document definitions, units, ownership, permitted values, and refresh frequency.

6. Transformation for Analysis and Visualization

Transformation changes data into a form suitable for a particular analytical purpose. Common transformations include:

• selecting relevant columns and filtering records;
• renaming fields with clear business labels;
• converting data types;
• deriving fields, such as gross sales = quantity × unit price;
• grouping dates into year, quarter, month, or week;
• aggregating transaction data to daily or monthly grain;
• pivoting long data into columns or unpivoting wide data into rows;
• encoding categories for analytical models;
• scaling numerical variables where required by a modelling method; and
• creating a calendar table for time-based reporting.

A derived measure must have a precise definition. For example, gross sales may be calculated as:

where i indexes valid transaction lines. This calculation assumes that quantity and unit price refer to the same product, currency, and unit of measure, and that discounts, taxes, returns, and cancellations are handled by explicit rules. Net sales may require subtracting discounts and returns; it should not be assumed to equal gross sales.

Long format stores one observation per row, such as Date, Product, and Sales. Wide format stores several measures or categories in separate columns. Long format is often convenient for reusable visualization and aggregation, while wide format may be required by particular tools or models. Reshaping should preserve the intended grain.

7. ETL and ELT Architectures

In ETL, data is extracted from sources, transformed in a processing layer, and then loaded into the target warehouse. This approach can ensure that only cleaned and structured data enters the warehouse and may be suitable where the target has limited processing capacity.

In ELT, raw data is extracted and loaded into a warehouse or lakehouse first; transformations are then executed in the target environment. ELT benefits from modern scalable storage and preserves raw data for reprocessing. It also requires strong access controls, storage management, and governance because unrefined data is retained.

Both architectures can be professional and reliable. The choice depends on volume, latency, security, platform capabilities, skills, and regulatory requirements.

8. Reproducibility, Testing, and Documentation

A reproducible pipeline produces the same result when given the same source snapshot, rules, and configuration. Good practice includes:

• storing transformation code in version control;
• separating raw, cleaned, and curated layers;
• parameterising dates, file locations, and environment settings;
• recording source files, extraction times, and row counts;
• avoiding undocumented manual edits;
• creating data dictionaries and lineage records;
• validating schemas before processing;
• testing uniqueness, referential integrity, permitted values, and totals; and
• logging failures and alerting responsible owners.

A useful validation framework contains several levels. Schema tests check field names and types. Quality tests check missingness and valid ranges. Relationship tests check foreign-key matches. Reconciliation tests compare totals with a trusted source. Business-rule tests check conditions such as “shipped date cannot precede order date.”

Data lineage records where data came from, which transformations were applied, and where it was published. This supports auditability, troubleshooting, and confidence in dashboards.

9. Applied Case: Retail Sales Reporting

This is an illustrative case. A regional retailer wants a monthly dashboard showing sales, returns, product performance, and store comparisons. Data comes from a point-of-sale database, an online sales export, a product spreadsheet, and a store master file.

Background and problem

The initial dashboard reports online and store sales separately. However, totals do not agree with finance. Product names differ between systems, dates use different formats, returns appear as positive sales, and some online orders contain no store identifier. The business needs a unified monthly view without double-counting transactions.

Analysis

The team first defines the target grain as one row per valid order line. It identifies OrderID plus LineNumber as the transaction key and documents that net sales equal gross sales less discounts and returned amounts. Profiling reveals 2.4% missing product codes in the online export, duplicate rows caused by repeated file delivery, and 7% of store records using outdated store codes.

Approach and solution

The team creates a raw layer that preserves each source exactly as received. It then:

1. removes repeated file deliveries using file identifiers and ingestion timestamps;
2. standardises dates to a single date type and records the source time zone;
3. maps old store codes to current codes using a controlled reference table;
4. matches products using product codes, with unresolved records placed in an exception table;
5. converts returns to negative quantities or negative amounts according to the agreed business rule;
6. unions store and online order lines after aligning their schemas;
7. joins product, store, and calendar dimensions using controlled keys;
8. aggregates validated lines to monthly store and product levels; and
9. reconciles monthly totals with finance before publishing the dashboard.

The pipeline includes tests for duplicate transaction keys, unmatched product codes, invalid dates, negative quantities, and reconciliation differences. Exceptions are not silently discarded; they are reported to data owners.

Outcome

Finance confirms that the dashboard total agrees with the approved ledger within the defined timing and scope. Managers can compare channels without double-counting. The unresolved-product report also identifies a weakness in the online product-catalogue process.

Learning points

The case demonstrates that transformation is not merely formatting. Defining grain, preserving lineage, resolving keys, handling returns, and reconciling results are necessary to protect the meaning of an analytical measure.

10. Governance, Ethics, and Contemporary Practice

Data preparation can create ethical harm even when the code is technically correct. Removing records may disproportionately exclude a demographic group. Imputing sensitive attributes may create misleading profiles. Combining datasets may reveal personal information that individuals did not expect to be linked.

Professional practice should apply data minimisation, purpose limitation, access control, retention rules, and appropriate anonymisation or pseudonymisation. Personal data should be handled according to applicable privacy law and organisational policy. Analysts should document lawful purpose, data ownership, consent requirements where relevant, and restrictions on onward use.

AI-assisted preparation tools can suggest joins, detect anomalies, classify text, or generate transformation code. They can improve productivity but may also infer incorrect relationships, expose confidential data to an external service, or produce undocumented logic. Human review, testing, provenance, and approved tool usage remain necessary. AI should assist rather than replace accountability.

Contemporary platforms increasingly combine data lakes, warehouses, streaming ingestion, semantic layers, and automated quality monitoring. These technologies do not eliminate fundamental problems. A scalable pipeline can distribute incorrect definitions at high speed. Sound modelling, governance, and validation therefore remain more important than platform branding.

Best-Practice Checklist

Before publishing a prepared dataset, confirm that:

• the purpose, owner, and grain are documented;
• sources and refresh schedules are known;
• data types, units, currencies, and time zones are explicit;
• missing values and outliers have been investigated;
• keys and joins have been tested;
• business definitions and calculated measures are documented;
• raw data and transformation history are preserved appropriately;
• quality tests and reconciliations have passed;
• privacy and access requirements have been reviewed; and
• the process can be rerun without undocumented manual intervention.

Chapter Summary

Data preparation converts raw data into reliable analytical data through acquisition, profiling, cleaning, integration, transformation, validation, publication, and monitoring. Profiling examines completeness, uniqueness, validity, consistency, accuracy, and timeliness. Cleaning requires informed treatment of missing values, duplicates, inconsistent formats, invalid values, and outliers. Integration depends on correct keys, compatible grain, schema matching, and clear business definitions. Transformations include filtering, joining, aggregation, reshaping, type conversion, and derived measures. ETL transforms before loading, whereas ELT loads raw data before transforming in the analytical platform. Reproducible pipelines use version control, lineage, testing, logging, and controlled exceptions. Ethical and governance considerations are essential because preparation choices can affect privacy, fairness, and decision quality.

Key Terms

Data preparation: The process of making data suitable for analysis and reporting.

ETL: Extract, transform, and load architecture.

ELT: Extract, load, and transform architecture.

Data profiling: Systematic examination of structure, values, distributions, and quality.

Schema: Description of fields, types, constraints, and relationships.

Grain: The business meaning represented by one row.

Primary key: Field or field combination that uniquely identifies a record.

Foreign key: Field that references a key in another table.

Completeness: The extent to which required values are present.

Validity: Conformity to permitted types, ranges, and rules.

Data lineage: Record of data origins and transformations.

Imputation: Replacement of missing values using a defined method.

Outlier: An unusually distant observation requiring investigation.

Inner join: Join retaining only matching records.

Left join: Join retaining all records from the left dataset.

Aggregation: Combining detailed records into summary values.

Long format: One observation or measurement per row.

Data dictionary: Documentation of fields, definitions, units, and rules.

Reconciliation: Comparison of a prepared result with a trusted total or source.

Semantic layer: Governed business definitions and relationships used consistently by analytical tools.

Review Questions

Short Answer Questions

1. What is the purpose of data profiling?
2. Distinguish ETL from ELT.
3. Why is table grain important when joining datasets?
4. Name three dimensions of data quality.
5. What is data lineage?

Long Answer Questions

1. Explain a complete data-preparation lifecycle and show why it is iterative.
2. Discuss alternative strategies for handling missing values and their limitations.
3. Explain how keys, grain, and schema matching affect data integration.
4. Compare ETL and ELT in terms of architecture, benefits, and risks.
5. Describe the controls required for a reproducible preparation pipeline.

Analytical / Application Questions

1. A transaction table contains 100,000 rows, but a join with a customer table produces 140,000 rows. Identify possible causes and propose diagnostic tests.
2. A sales dataset contains values in three currencies, inconsistent return signs, and duplicated online files. Design a preparation and validation approach before creating a dashboard.
3. A field has 96% completeness, but most populated values are the placeholder “unknown.” Evaluate whether the field is fit for analysis and recommend next steps.

Multiple Choice Questions with Answers

Question 1

Which activity is primarily concerned with examining distributions, missing values, and data types before transformation?

A. Data profiling

B. Data publishing

C. Dashboard formatting

D. Report scheduling

Correct Answer: A. Data profiling

Question 2

What does table grain describe?

A. The colour scheme of a table

B. What one row represents

C. The storage size of a table

D. The number of columns only

Correct Answer: B. What one row represents

Question 3

Which join retains every record from the left dataset and matching records from the right dataset?

A. Inner join

B. Cross join

C. Left join

D. Full deletion

Correct Answer: C. Left join

Question 4

If 80 of 2,000 records are missing a required value, what is the completeness rate?

A. 4%

B. 80%

C. 92%

D. 96%

Correct Answer: D. 96%

Question 5

Which statement about an outlier is correct?

A. It should always be deleted

B. It is always a data-entry error

C. It should be investigated because it may be valid or erroneous

D. It can never affect summary statistics

Correct Answer: C. It should be investigated because it may be valid or erroneous

Question 6

Which architecture loads raw data before transformations are performed in the analytical platform?

A. ETL

B. ELT

C. OLTP

D. Manual entry

Correct Answer: B. ELT

Question 7

Why can a many-to-many join produce inflated totals?

A. It converts numbers to text

B. It creates combinations of matching records

C. It removes all unmatched records

D. It automatically converts currencies

Correct Answer: B. It creates combinations of matching records

Question 8

Which item most directly supports reproducibility?

A. Undocumented spreadsheet edits

B. Version-controlled transformation code

C. Changing rules for each report

D. Deleting raw files after loading

Correct Answer: B. Version-controlled transformation code

Question 9

What is the main purpose of a data dictionary?

A. To define fields, meanings, units, and rules

B. To replace all quality tests

C. To encrypt every database table

D. To select dashboard colours

Correct Answer: A. To define fields, meanings, units, and rules

Question 10

Why should currency amounts not be added across currencies without preparation?

A. Currency values are always categorical

B. The resulting total has no consistent monetary unit

C. Currencies cannot be stored numerically

D. Exchange rates are irrelevant to reporting

Correct Answer: B. The resulting total has no consistent monetary unit