Data Foundations and Exploratory Analysis

Introduction

Machine learning systems are only as reliable as the data on which they are developed. In business, data may be stored in relational databases, spreadsheets, application logs, documents, images, customer messages, sensor streams, or third-party platforms. Before selecting an algorithm, an analyst must establish what the data represents, how it was generated, whether it is sufficiently complete and accurate, and whether it is appropriate for the decision under consideration.

Data foundations therefore connect business questions with trustworthy analytical evidence. This chapter develops a practical framework for acquiring, integrating, querying, cleaning, sampling, and exploring structured and unstructured data using databases, SQL, Python, and visualization. Particular attention is given to missingness, bias, outliers, representativeness, and the ethical consequences of poor data practices.

Learning Objectives

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

1. Distinguish structured, semi-structured, and unstructured business data.
2. Describe relational database concepts and write basic SQL queries.
3. Integrate data from multiple sources while preserving meaning and traceability.
4. Use Python and visualization for exploratory data analysis (EDA).
5. Assess data quality, missingness, sampling, bias, outliers, and representativeness.
6. Explain how data limitations affect machine-learning models and business decisions.

1. The Role of Data in Business Analytics

A business dataset is not simply a collection of values. Each value has a meaning, a unit, a time reference, a source, and a process that generated it. For example, “revenue” might mean invoiced revenue, cash received, or revenue net of returns. “Customer” might mean an account, a household, or an individual user. Ambiguous definitions create analytical error even when the data contains no missing values.

A useful data-to-decision chain is:

Business question → data-generating process → data acquisition → integration → quality assessment → exploration → modeling → decision and monitoring

EDA is not a cosmetic step before modeling. It is an investigative process used to discover structure, identify errors, formulate hypotheses, and decide whether modeling is appropriate. An analyst should ask:

• What population does the dataset describe?
• What is the unit of observation: customer, transaction, product, or day?
• What period and geographic area are covered?
• Which variables are inputs, outcomes, identifiers, and administrative fields?
• Could any variable contain information unavailable at the time of prediction?

The last question addresses data leakage. Leakage occurs when information from the future or from the outcome-generation process enters a predictor. For example, using a “collection status” field to predict whether an invoice will become overdue may produce excellent historical accuracy but no useful advance warning.

2. Types and Structures of Business Data

Structured data

Structured data follows a defined schema, commonly represented as rows and columns. Examples include sales transactions, employee records, inventory tables, and loan applications. Structured data is well suited to SQL and statistical analysis.

Semi-structured data

Semi-structured data has organizational markers but not a rigid tabular schema. JSON documents, XML files, application logs, and event streams are common examples. A JSON transaction may contain different attributes for different types of products, requiring transformation before analysis.

Unstructured data

Unstructured data lacks a predefined tabular organization. Examples include email text, customer reviews, call recordings, photographs, video, and scanned documents. Such data may be converted into analytical features using natural-language processing, speech recognition, optical character recognition, or computer vision. Conversion can introduce additional uncertainty and should be documented.

Measurement scales

The measurement scale affects the operations that are meaningful:

A postal code may look numeric but is nominal. Treating it as a continuous quantity would create meaningless averages and distances.

3. Relational Databases and SQL

A relational database stores data in tables linked through keys. A primary key uniquely identifies each row. A foreign key refers to a key in another table and establishes a relationship. A schema specifies tables, columns, data types, constraints, and relationships.

Suppose a retailer has the following tables:

• customers(customer_id, segment, region)
• orders(order_id, customer_id, order_date, status)
• order_lines(order_id, product_id, quantity, unit_price)
• products(product_id, category)

The grain of orders is one row per order, while the grain of order_lines is one row per product within an order. Joining them without understanding grain can duplicate order-level values.

A basic query is:

SELECT region, COUNT(*) AS customer_count
FROM customers
GROUP BY region;

Filtering and aggregation can be combined:

SELECT c.region,
       SUM(ol.quantity * ol.unit_price) AS sales_value
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
JOIN order_lines AS ol ON o.order_id = ol.order_id
WHERE o.status = 'Completed'
GROUP BY c.region
ORDER BY sales_value DESC;

The expression quantity × unit_price calculates line value. The query assumes that completed order lines represent recognized sales and that unit prices are expressed in the same currency. If discounts, taxes, refunds, or returns exist, the business definition of sales must be clarified.

A LEFT JOIN preserves all rows from the left table, including customers with no orders; an INNER JOIN retains only matching rows. Selecting the wrong join can silently exclude relevant cases. Analysts should inspect row counts before and after joins and test whether keys are unique where they are expected to be unique.

Data integrity controls

Common database constraints include:

• NOT NULL: a value must be present.
• UNIQUE: duplicate values are prohibited.
• CHECK: values must satisfy a condition, such as quantity ≥ 0.
• Referential integrity: a foreign key must refer to an existing record.

Constraints prevent some errors, but they cannot guarantee semantic correctness. A date can be valid syntactically while being impossible operationally, and a customer can be assigned to the wrong region.

4. Acquiring and Integrating Data

Data integration combines sources into an analytical dataset. A defensible integration process includes:

1. Define the analytical unit. Decide whether one row represents a customer, order, customer-month, or another entity.
2. Create a data dictionary. Record variable name, meaning, type, unit, allowed values, source, and update frequency.
3. Profile each source separately. Examine row counts, keys, ranges, missingness, and time coverage.
4. Standardize formats. Align dates, currencies, units, category labels, and character encoding.
5. Resolve entities. Match records that refer to the same customer or product using reliable identifiers or documented entity-resolution rules.
6. Join at the correct grain. Aggregate before joining when necessary to avoid multiplying records.
7. Validate the result. Reconcile totals, inspect unmatched records, and compare key distributions before and after integration.
8. Record lineage. Preserve the source, transformation, person or system responsible, and execution date.

A major risk is many-to-many joining. If a customer appears three times in one table and four times in another, a direct join can create up to twelve rows for that customer. This is not necessarily a technical error; it is usually a mismatch between table grains. Aggregation or a bridge table may be required.

For unstructured data, integration also involves metadata. A review dataset should preserve review ID, timestamp, product, language, source, and consent status alongside the text. Images should retain capture time, device information where appropriate, and labeling provenance.

5. Python for Data Preparation and EDA

Python libraries commonly used in business analytics include pandas for tabular data, numpy for numerical operations, matplotlib and seaborn for visualization, and database connectors such as sqlalchemy.

A typical workflow is:

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
print(orders.shape)
print(orders.dtypes)
print(orders.isna().mean().sort_values(ascending=False))
print(orders.describe(include="all"))

The statement orders.isna().mean() calculates the proportion of missing values in each column when Boolean values are averaged, because True is treated as 1 and False as 0. The result is a rate between 0 and 1, not a count.

Useful initial checks include:

orders["order_id"].duplicated().sum()
orders["quantity"].value_counts(dropna=False)
orders["order_date"].min(), orders["order_date"].max()

Code should be reproducible, readable, and tested. Avoid silently converting errors to missing values. For example, a failed date conversion should be counted and investigated rather than discarded without documentation.

6. Exploratory Data Analysis

EDA combines numerical summaries, visualizations, domain knowledge, and questioning. It is iterative: an unusual plot may lead to a query, a revised data definition, and a new plot.

Measures of center and spread

For observations x₁, x₂, …, x_n, the arithmetic mean is:

Here, n is the number of observed values. The mean is sensitive to extreme values and is appropriate when the arithmetic average is meaningful.

The sample variance is:

The sample standard deviation is s = √s². The denominator n − 1 is used when estimating population variability from a sample under standard random-sampling assumptions. If describing the entire finite population rather than estimating from a sample, a denominator of n may be used.

The median is the middle ordered value and is more robust to skew. The interquartile range is:

where Q₁ and Q₃ are the 25th and 75th percentiles. Percentile definitions can differ slightly between software packages, so the method should be recorded when precision matters.

Worked example: skewed transaction values

Consider transaction values of 10, 12, 13, 15, and 100 monetary units. The mean is:

x̄ = (10 + 12 + 13 + 15 + 100)/5 = 30.

The median is 13. The mean is much larger because of the 100-unit transaction. Reporting only the mean could misrepresent a typical transaction; both center and distribution shape should be examined.

Visual exploration

• Histogram: displays the distribution of a quantitative variable; bin width can affect interpretation.
• Box plot: shows median, quartiles, and possible extreme observations.
• Bar chart: compares counts or summaries across categories; bars should normally begin at zero when length encodes magnitude.
• Scatter plot: examines association between two quantitative variables.
• Line chart: displays change over an ordered time axis; it should not imply continuity where observations are unrelated.
• Heat map: displays a matrix such as correlations or missingness patterns.

Correlation measures linear association, not causation. Pearson’s sample correlation is:

It assumes paired observations and is sensitive to outliers. A high correlation may result from a common time trend, reverse causality, confounding, or leakage.

7. Data Quality Assessment

Data quality is multidimensional:

Quality must be evaluated relative to purpose. A daily inventory report may tolerate small delays, while fraud detection may not. A missing email address may not matter for demand forecasting but may prevent customer communication.

A quality report should include counts, rates, thresholds, examples, responsible owners, and remediation decisions. Deleting problematic records is not automatically cleaning; it can change the population and introduce bias.

8. Missing Data and Missingness Mechanisms

Missing data may be represented as NULL, blank text, special codes such as −99, or an absent JSON field. These representations should be standardized while preserving the original source value.

Three conceptual mechanisms are important:

• MCAR (missing completely at random): missingness is unrelated to observed and unobserved values.
• MAR (missing at random conditional on observed data): missingness depends on observed variables after conditioning.
• MNAR (missing not at random): missingness depends on the unobserved value itself or other unobserved factors.

These are assumptions about the missingness process, not labels that can usually be proven from the observed dataset alone.

Possible responses include:

• investigate why values are missing;
• retain a missingness indicator when the fact of nonresponse may be informative;
• use complete-case analysis only when its assumptions and impact are acceptable;
• impute using domain-appropriate methods;
• report uncertainty and compare results under alternative treatments.

Mean imputation is simple but reduces variation and can distort relationships. More advanced methods, such as multiple imputation, should reflect the analysis model and be validated. Imputation must be performed within training data during model development to avoid leakage from validation or test data.

9. Sampling, Bias, and Representativeness

A population is the group about which conclusions are intended. A sample is the observed subset. The sampling frame is the operational list from which the sample is drawn. These are not always identical. A customer-app sample may exclude customers who do not use smartphones.

Common sampling approaches include:

• Simple random sampling: every eligible unit has an equal known probability of selection.
• Systematic sampling: select every kth unit after a random start; periodic ordering can create bias.
• Stratified sampling: sample within groups, often to ensure adequate representation.
• Cluster sampling: sample groups such as stores or regions, then observe units within them.
• Convenience sampling: select accessible units; it is often fast but weak for generalization.

The standard error of a sample mean under simple random sampling is approximately:

where s is sample standard deviation and n is sample size. This formula assumes an appropriate random sample and independence, or an adjustment for the sampling design. Increasing sample size reduces random sampling error but does not eliminate systematic bias.

Important biases include:

• Coverage bias: some population members cannot be selected.
• Nonresponse bias: respondents differ from nonrespondents.
• Selection bias: inclusion depends on characteristics related to the outcome.
• Survivorship bias: analysis excludes entities that exited or failed.
• Measurement bias: a variable is systematically recorded inaccurately.
• Temporal bias: the data period does not represent current conditions.

Representativeness should be assessed by comparing observed distributions with reliable population benchmarks across relevant dimensions such as region, age, customer tenure, device, or income. Weighting may help when selection probabilities are known or estimable, but weights can increase variance and cannot repair poor measurement or missing entire groups.

10. Outliers and Anomalies

An outlier is an observation unusually distant from the rest of a distribution. It may be a data-entry error, a legitimate rare event, fraud, a new business segment, or a change in measurement. It should not be removed merely because it is inconvenient.

A common screening rule identifies observations below:

or above:

This is a descriptive rule for flagging potential outliers, not a universal deletion rule. It is less suitable for heavily skewed or multimodal data without contextual interpretation.

Responses include correcting confirmed errors, retaining legitimate observations, transforming variables, using robust summaries, capping values with justification, or modeling anomalies separately. A log transformation such as log(1 + x) can reduce right skew when x ≥ 0, but it changes interpretation and does not solve data-quality problems.

11. Illustrative Case: Retail Demand Data

Background

A fictional retailer wants to predict weekly product demand and allocate inventory across 120 stores. The analytical team receives sales transactions, product information, store attributes, promotions, and online search data.

Problem

The initial dataset appears to contain 2.4 million sales rows. However, the team discovers that online and store transactions use different product identifiers, several stores report sales in local currencies, and weeks with zero sales are absent rather than explicitly recorded.

Analysis

The team defines the analytical unit as one product-store-week. It creates a complete product-store-week calendar for the study period, then left-joins observed sales so that structural zeros can be distinguished from missing reports. It maps identifiers through a governed product table and converts currencies using the organization’s approved accounting convention. SQL aggregation occurs before joining promotions to prevent line-level duplication.

EDA shows that a small number of promotional transactions are extremely large. Investigation reveals legitimate bulk purchases rather than errors. They are retained, but the team reports median demand and upper percentiles in addition to the mean. Missing promotion fields are concentrated in smaller stores, indicating possible coverage bias.

The team compares store-level demand distributions with the full store population. Because rural stores are underrepresented in the online-search data, search activity is not treated as a universally available predictor. Instead, the model includes a missingness indicator and is evaluated separately by store type, subject to governance approval.

Approach and outcome

The team documents definitions, data lineage, quality exceptions, and validation checks. A time-based validation design is used so that future weeks are not used to predict past weeks. Performance is reported overall and by store segment. Inventory managers receive both forecasts and quality warnings rather than a single unexplained score.

Learning points

The largest gains came not from a more complex algorithm but from defining the correct grain, representing zero demand, preventing join duplication, and examining coverage. Data preparation changed the business meaning of the modeling problem.

12. Governance, Ethics, and Professional Practice

Data work must respect privacy, security, fairness, and applicable law. Analysts should collect only data necessary for a legitimate purpose, control access, protect sensitive fields, and define retention periods. Personal data should not be copied into notebooks or shared files unnecessarily.

Important practices include:

• documenting consent and lawful use where applicable;
• applying role-based access and encryption;
• separating identifiers from analytical features when possible;
• evaluating disparate missingness and selection across groups;
• avoiding proxy variables that reproduce protected characteristics unfairly;
• preserving audit trails and reproducible code;
• explaining limitations to decision-makers.

An apparently neutral variable can create unequal effects. For example, historical complaint volume may reflect unequal access to complaint channels rather than true service quality. Governance therefore requires examining how data was produced, not merely how it is formatted.

13. Contemporary Developments and Best Practices

Modern data platforms increasingly combine warehouses, lakehouses, streaming systems, document stores, and feature repositories. Automated data-quality checks can monitor schema changes, freshness, distribution shifts, and failed pipelines. Generative AI can assist with code explanation, metadata extraction, or text classification, but generated code and labels require human validation, privacy review, and tests.

A practical checklist is:

1. Define the decision, population, unit, outcome, and time horizon.
2. Inventory sources and document ownership and lineage.
3. Profile types, keys, ranges, duplicates, missingness, and time coverage.
4. Validate joins and reconcile totals.
5. Visualize distributions and relationships before modeling.
6. Investigate missingness, outliers, and subgroup coverage.
7. Separate training, validation, and test data temporally when appropriate.
8. Record every transformation and assumption.
9. Evaluate data quality and model performance by relevant subgroups.
10. Monitor the data after deployment for drift and changing definitions.

Chapter Summary

Reliable machine learning begins with a clear understanding of data and its generating process. Structured, semi-structured, and unstructured data require different acquisition and preparation strategies. Relational databases provide schemas, keys, constraints, and SQL operations, but analysts must understand table grain to avoid duplication and unintended exclusion. Python supports reproducible profiling and EDA, while summaries and visualizations reveal skew, relationships, missingness, and anomalies.

Data quality includes accuracy, completeness, consistency, validity, uniqueness, timeliness, and provenance. Missingness mechanisms affect the suitability of deletion or imputation. Sampling error decreases with larger appropriate samples, but larger samples do not eliminate systematic bias. Outlier rules are screening devices rather than automatic deletion instructions. Representativeness must be assessed against the intended population and across relevant subgroups. Finally, privacy, fairness, governance, documentation, and monitoring are integral to professional analytics rather than optional additions.

Key Terms

Data-generating process: The operational process that produces observed data.

Structured data: Data organized in a fixed schema of rows and columns.

Semi-structured data: Data with organizational markers but a flexible schema.

Unstructured data: Data without a predefined tabular structure.

Primary key: A field or combination of fields that uniquely identifies a row.

Foreign key: A field that references a key in another table.

Data grain: The level represented by one row in a dataset.

Data lineage: A record of data origin, transformations, and movement.

Exploratory data analysis: Iterative investigation using summaries, visualizations, and domain knowledge.

Data leakage: Use of information unavailable at prediction time or derived from the outcome.

Missingness: The absence of an expected data value.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random.

Sampling frame: The operational list from which a sample is selected.

Representativeness: The degree to which observed data reflects the intended population.

Outlier: An observation unusually distant from other observations.

Interquartile range: The difference between the third and first quartiles.

Data bias: Systematic distortion arising from selection, measurement, coverage, or other processes.

Data quality: Fitness of data for a specified purpose across dimensions such as accuracy and completeness.

Review Questions

5 Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why must an analyst identify the grain of each table before joining tables?
3. Name three dimensions of data quality.
4. Distinguish MCAR from MNAR missingness.
5. Why does a larger sample not necessarily remove bias?

5 Long Answer Questions

1. Explain how SQL joins, aggregation, and table grain can affect reported sales.
2. Describe a complete EDA workflow for a customer transaction dataset.
3. Compare structured, semi-structured, and unstructured data and give one business example of each.
4. Explain how outliers should be investigated and why automatic deletion is risky.
5. Discuss the relationship between data lineage, privacy, fairness, and responsible machine learning.

3 Analytical / Application Questions

1. A customer dataset has missing income values concentrated among younger customers. What analyses would you perform, what missingness mechanisms might be plausible, and how would you assess the effect of alternative treatments?
2. A direct join between order headers and order lines doubles reported revenue. Diagnose the likely cause and propose a corrected analytical design.
3. An online survey contains many responses from urban customers but few from rural customers. Explain how coverage, nonresponse, weighting, subgroup evaluation, and business conclusions should be addressed.

Multiple Choice Questions with Answers

Question 1

Which field is most appropriate as a primary key in an orders table?

A. Customer region

B. Order ID that uniquely identifies each order

C. Order status

D. Unit price

Correct Answer: B. Order ID that uniquely identifies each order

Question 2

What is the main risk of joining two tables without checking their grain?

A. All dates become invalid

B. The database automatically deletes duplicates

C. Rows and measures may be unintentionally multiplied

D. Missing values are always imputed

Correct Answer: C. Rows and measures may be unintentionally multiplied

Question 3

Which visualization is generally most suitable for examining the distribution of a quantitative variable?

A. Histogram

B. Relational schema

C. Pie chart of database keys

D. SQL constraint

Correct Answer: A. Histogram

Question 4

For values 10, 12, 13, 15, and 100, which statement is correct?

A. The mean is 13 and median is 30

B. The mean and median are both 30

C. The mean is 30 and median is 13

D. The mean is 100 and median is 10

Correct Answer: C. The mean is 30 and median is 13

Question 5

Which situation best illustrates data leakage?

A. Using customer region to summarize sales

B. Using a future collection-status field to predict overdue invoices

C. Plotting a histogram of transaction values

D. Checking duplicate order IDs

Correct Answer: B. Using a future collection-status field to predict overdue invoices

Question 6

What does the interquartile range equal?

A. Q₁ + Q₃

B. Q₃ − Q₁

C. Mean ÷ median

D. Maximum − minimum only

Correct Answer: B. Q₃ − Q₁

Question 7

Which sampling method deliberately samples within defined groups to ensure their inclusion?

A. Convenience sampling

B. Cluster sampling only

C. Stratified sampling

D. Systematic sampling without a frame

Correct Answer: C. Stratified sampling

Question 8

Why can mean imputation be problematic?

A. It always increases variance

B. It can reduce variation and distort relationships

C. It guarantees unbiased estimates

D. It converts nominal variables into ratio variables

Correct Answer: B. It can reduce variation and distort relationships

Question 9

An observation above Q₃ + 1.5 × IQR should initially be treated as:

A. A confirmed error

B. A potential outlier requiring investigation

C. A missing value

D. A population mean

Correct Answer: B. A potential outlier requiring investigation

Question 10

Which statement about sample size is most accurate?

A. Increasing sample size eliminates systematic bias

B. Increasing sample size generally reduces random sampling error under suitable assumptions

C. Sample size has no relation to uncertainty

D. A convenience sample becomes representative automatically when large

Correct Answer: B. Increasing sample size generally reduces random sampling error under suitable assumptions