7. Data Management and Data Engineering Foundations

Introduction and Context

Data science depends not only on algorithms and statistical reasoning but also on the quality, accessibility, structure, and governance of the data supplied to those algorithms. A predictive model trained on incomplete, duplicated, poorly defined, or incorrectly joined data may produce results that appear sophisticated while being operationally useless. Data management and data engineering provide the foundations through which raw observations become reliable analytical datasets.

Data management concerns the policies, practices, architecture, and controls used to collect, store, describe, protect, integrate, and use data. Data engineering focuses more specifically on designing and operating the pipelines and platforms that move and transform data. The two disciplines overlap substantially. In a modern organization, data engineers, database administrators, analysts, data scientists, security specialists, and business owners must work together because data quality is a shared organizational responsibility.

The distinction between operational and analytical data is central. Operational systems support transactions such as purchases, payments, bookings, or patient registrations. Analytical systems reorganize data so that users can identify patterns, compare performance, construct forecasts, and support decisions. The movement from operational records to analytical assets is not merely a technical transfer. It involves interpretation, data modeling, validation, metadata management, security, and governance.

This chapter examines relational and NoSQL databases, SQL, schemas and metadata, ETL and ELT, data warehouses, data lakes, and lakehouses. It also considers architectural choices, practical pipelines, quality controls, governance, and emerging engineering practices relevant to postgraduate study in artificial intelligence and machine learning.

Learning Objectives

After studying this chapter, students should be able to:

1. Distinguish operational, analytical, relational, and non-relational data systems.

2. Explain schemas, keys, constraints, normalization, denormalization, and metadata.

3. Construct and interpret SQL queries for analytical purposes.

4. Compare ETL and ELT and select an appropriate approach for a data workload.

5. Explain the purposes and trade-offs of warehouses, lakes, and lakehouses.

6. Design a basic data pipeline that includes ingestion, transformation, validation, storage, and monitoring.

7. Evaluate data quality, governance, security, and ethical risks.

8. Apply data engineering concepts to an organizational case involving analytics or machine learning.

From Records to Reliable Data Products

A data value has meaning only within a context. The value “42” might represent age, quantity, temperature, or an error code. Data engineering therefore begins with understanding the business or scientific process that generated the data. A reliable analytical dataset should have clearly defined fields, stable identifiers, known units, documented transformations, appropriate granularity, and evidence that quality checks have been performed.

Data is commonly described through a lifecycle: creation, capture, storage, processing, use, sharing, archival, and deletion. At each stage, risks arise. Data may be captured with missing values, stored without adequate access controls, processed using undocumented rules, shared beyond its intended purpose, or retained longer than necessary. A lifecycle perspective prevents organizations from treating storage as the only data-management concern.

Two concepts are particularly important. A data asset is a reusable collection of data, such as a customer table, a feature store, or a sales dashboard dataset. A data product is a data asset designed for identifiable users and purposes, with defined ownership, quality expectations, documentation, and service commitments. A machine-learning feature table, for example, is more than a collection of columns: it requires definitions, refresh schedules, point-in-time correctness, and controls against leakage.

Relational Foundations: Tables, Relationships, and Constraints

The relational model represents data as relations, usually implemented as tables. A table consists of rows, representing records or tuples, and columns, representing attributes. A relational design separates entities such as Customer, Product, Order, and Payment, then represents relationships among them through keys.

A primary key uniquely identifies each row. A foreign key references a key in another table and expresses a relationship. For example, `customer_id` may be the primary key of a Customer table and a foreign key in an Order table. A composite key uses multiple columns when no single attribute uniquely identifies a record, such as `(order_id, product_id)` in an order-line table.

Constraints protect structural integrity. A `NOT NULL` constraint requires a value; a `UNIQUE` constraint prevents duplicates; a `CHECK` constraint restricts permitted values; and a foreign-key constraint prevents references to nonexistent records. Constraints are valuable because they detect errors near their source rather than allowing them to spread into reports and models.

Normalization organizes relational data to reduce unnecessary duplication and update anomalies. In a normalized design, customer information is stored once and referenced by orders. If the same address is copied into thousands of order rows, changes may produce inconsistent versions. Normalization improves consistency, although excessive normalization can require many joins and may reduce analytical query performance. Analytical systems often use deliberate denormalization, such as star schemas, to make common queries simpler and faster.

A star schema contains a central fact table surrounded by dimension tables. A sales fact table might contain date, product, store, quantity, revenue, and foreign keys, while dimensions describe products, stores, and dates. The grain of a fact table—the exact meaning of one row—must be declared explicitly. “One row per order” and “one row per item in an order” are different grains; confusing them can cause double counting.

NoSQL Systems and Polyglot Persistence

NoSQL refers to a family of non-relational approaches rather than one single technology. Document databases store semi-structured records, often in JSON-like form. Key-value stores associate a key with an opaque value and are useful for caching and high-speed lookups. Wide-column stores organize data by column families and are designed for distributed workloads. Graph databases represent nodes and relationships and are useful where connections are central, such as recommendation networks or fraud analysis.

NoSQL systems are attractive when data structures change frequently, when very large distributed workloads are required, or when access patterns do not fit relational joins. A document representing a customer may contain nested addresses and preferences, reducing the need for joins. However, denormalization can create duplicated data and complicated update logic. Some systems provide limited transaction support compared with traditional relational databases, although the boundary between relational and NoSQL capabilities has become less rigid.

The choice should follow workload requirements rather than fashion. Relevant questions include: What are the transaction guarantees? What query patterns dominate? How rapidly does the data grow? Is horizontal distribution required? How important are joins, consistency, latency, and schema flexibility? Polyglot persistence uses more than one data technology within an architecture, assigning each system a suitable role. It increases flexibility but also increases operational complexity, synchronization requirements, and governance demands.

Schemas, Metadata, and Meaning

A schema defines the structure, types, relationships, and permitted values of data. A database schema may specify that `transaction_date` is a date, `amount` is a decimal, and `account_id` is a key. A schema can be enforced before data is accepted, as in many relational systems, or inferred and applied during reading, as is common in some data-lake environments.

Schema-on-write validates and organizes data before storage. It supports consistency and predictable queries but may slow ingestion when source formats change. Schema-on-read stores data in a relatively raw form and applies structure when it is used. This offers flexibility but shifts responsibility to downstream users and can result in inconsistent interpretations.

Metadata is “data about data.” Structural metadata describes columns, types, and relationships. Business metadata explains terms such as “active customer” or “net revenue.” Technical metadata records pipeline versions, refresh times, source systems, and processing status. Operational metadata includes failure rates, latency, and record counts. Provenance or lineage records where data came from and how it was transformed.

A data catalogue makes metadata discoverable. A strong catalogue does not merely list tables; it identifies owners, definitions, sensitivity classifications, quality indicators, lineage, and approved uses. Metadata is essential for reproducibility. Without it, analysts may unknowingly combine incompatible measures, use stale extracts, or interpret missing values incorrectly.

SQL as a Language for Data Work

SQL supports data definition, manipulation, access control, and querying. A basic analytical query selects columns from a table and filters rows:

```sql

SELECT product_id, SUM(revenue) AS total_revenue

FROM sales

WHERE sale_date >= DATE '2025-01-01'

GROUP BY product_id

ORDER BY total_revenue DESC;

```

`WHERE` filters individual rows before aggregation, whereas `HAVING` filters groups after aggregation. Joins combine tables through related keys. An inner join returns matching records; a left join preserves all rows from the left table even when no match exists. The choice of join affects both interpretation and row counts.

Window functions calculate values across related rows without collapsing them. For example, `RANK() OVER (PARTITION BY region ORDER BY revenue DESC)` ranks products within each region. Common table expressions improve readability by naming intermediate results. Views provide reusable query logic, while materialized views store query results for faster access.

SQL is declarative: the user specifies what result is required rather than every physical execution step. Database optimizers decide how to execute queries using indexes, partitions, join strategies, and statistics. Nevertheless, users must understand data volume, cardinality, null behavior, and duplicate-producing joins. A technically valid query can still be analytically wrong.

ETL, ELT, and Pipeline Design

A data pipeline moves data from sources to destinations while applying defined transformations. ETL means extract, transform, and load. Data is extracted from operational systems, transformed in an intermediate processing environment, and loaded into a target warehouse. ELT means extract, load, and transform. Data is first loaded into a scalable target platform and transformed there.

ETL is useful when data must be cleaned or filtered before entering a constrained target, or when sensitive information should not be copied in raw form. ELT is attractive when the target platform has substantial computational capacity and when retaining raw data supports reprocessing, auditing, and changing analytical requirements. Modern systems often combine the two approaches.

A robust pipeline normally includes:

• source extraction and authentication;
• ingestion through batch files, APIs, streams, or change-data capture;
• raw, staging, and curated storage zones;
• transformations such as type conversion, deduplication, joining, and standardization;
• validation of schema, completeness, uniqueness, and business rules;
• publication to analytical tables, dashboards, or machine-learning stores;
• orchestration, logging, alerting, lineage, and recovery.

Batch processing handles data periodically, such as nightly sales aggregation. Stream processing handles events continuously or with low latency, such as fraud alerts. Exactly-once processing is difficult in distributed systems; practical designs often use idempotency, checkpoints, unique event identifiers, and replayable inputs. An idempotent pipeline can be run repeatedly without producing incorrect duplicates.

Orchestration tools schedule dependencies and track execution. A pipeline should make failures visible, isolate bad records where appropriate, and support backfills when logic changes. Testing should cover both code and data. Data tests may check that a key is unique, a date is within an expected range, a column is not unexpectedly null, or a total reconciles with a source system.

Warehouses, Lakes, and Lakehouses

A data warehouse is a curated repository optimized for structured analytical queries. Warehouses generally emphasize governed schemas, performance, access control, and consistent business definitions. They commonly store dimensional models and support business intelligence, reporting, and aggregate analysis.

A data lake stores data in its original or lightly processed form, often using economical object storage. It can accommodate structured, semi-structured, and unstructured data, including logs, documents, images, and event streams. Its flexibility is valuable for exploration and machine learning, but an unmanaged lake can become a “data swamp”: difficult to search, poorly documented, duplicated, and unreliable.

A lakehouse attempts to combine the flexibility and cost characteristics of a lake with warehouse-like transactions, governance, schema management, and analytical performance. It commonly uses open storage formats and a metadata or transaction layer to support reliable tables. The architectural label is less important than the capabilities actually provided: versioning, quality controls, concurrent access, lineage, and reproducible processing.

The correct choice depends on use case. A regulated financial report may require highly curated warehouse tables. Exploratory natural-language processing may require a lake for documents and text. A machine-learning platform may need both raw object storage and governed feature tables. Architecture should be evaluated according to latency, volume, variety, consistency, cost, skills, security, and expected reuse.

A Practical Data Engineering Framework

A useful design sequence begins with the decision or product requirement. Engineers should identify who will use the data, what questions must be answered, how current the data must be, and what level of accuracy is acceptable. Next, they identify sources and define the target grain, entities, measures, and dimensions.

The next step is to establish contracts. A data contract specifies expected fields, types, meanings, ownership, freshness, and allowable changes between producers and consumers. Contracts reduce surprises when an upstream application changes a field or removes an event. They should be supported by automated checks rather than treated only as documentation.

The pipeline is then designed with separation between raw, intermediate, and trusted layers. Raw data supports auditability and reprocessing. Intermediate data contains standardized or joined structures. Trusted data is published for a known purpose and has quality evidence. Transformations should be version-controlled, peer-reviewed, and reproducible.

Finally, the system must be operated. Monitoring should cover freshness, volume, schema changes, distribution shifts, pipeline duration, failure rates, and business-level reconciliations. Observability is stronger when it links technical metrics to user impact. For example, a successful pipeline run does not guarantee that a dashboard is correct if the source has stopped sending transactions.

Illustrative Case Study: Retail Demand Intelligence

Background

The following is an illustrative case of a mid-sized retailer, Northstar Retail, operating stores and an online channel. The company wants to forecast product demand and reduce stock-outs. Data exists in a point-of-sale database, an e-commerce platform, supplier spreadsheets, and a customer-support system.

Situation and Problem

Analysts report conflicting sales totals. The online system identifies products with text codes, while stores use numeric codes. Returns are recorded separately and are sometimes included in sales reports. Supplier files arrive with changing column names and inconsistent units. The machine-learning team has built a demand model, but its accuracy varies sharply across products.

Analysis

The first problem is semantic rather than algorithmic. The organization has not defined whether “sales” means gross transactions, completed purchases, or net sales after returns. The second problem is grain: store records are at line-item level, while an online extract is summarized by order. The third problem is identity resolution because product identifiers differ across systems. Finally, delayed supplier files create a freshness issue.

Approach and Solution

The team establishes a product master with a governed product key and maintains mappings from each source identifier. It defines a sales fact table at one row per product per order line, with explicit treatment of cancellations and returns. Dimensions are created for product, store, channel, date, and promotion.

Raw source files and event extracts are retained in a lake for audit and reprocessing. Validated, conformed data is loaded into a warehouse using an ELT process. SQL tests check key uniqueness, nonnegative or appropriately signed quantities, valid dates, reconciliation with source totals, and duplicate events. A data catalogue documents the definition of net sales and identifies owners.

For machine learning, features are generated using only information available before the forecast time. The pipeline records feature timestamps to prevent future information from leaking into training data. Supplier freshness is monitored, and forecasts are flagged when critical inputs are late. The warehouse serves reporting, while the lake retains semi-structured supplier and event data.

Outcome

The organization now has one governed definition of sales, traceable transformations, and a stable product identity. Model evaluation becomes more credible because training and scoring data share consistent definitions. When a supplier file changes, an automated schema check detects the change before it silently corrupts forecasts.

Learning Points

The case demonstrates that data engineering is not simply data movement. It requires business definitions, grain control, identity management, quality testing, lineage, and close coordination between analysts and engineers. Better models often result first from better data foundations rather than more complex algorithms.

Quality, Governance, Security, and Ethics

Data quality has several dimensions. Accuracy concerns correspondence with reality; completeness concerns missing required values; consistency concerns agreement across systems; timeliness concerns availability when needed; validity concerns conformance to permitted formats or rules; uniqueness concerns the absence of unintended duplicates. Quality is contextual: a one-hour delay may be unacceptable for fraud detection but acceptable for an annual report.

Governance assigns decision rights and accountability. It includes ownership, stewardship, standards, classification, retention, access policies, issue management, and audit processes. Data should be classified according to sensitivity, such as public, internal, confidential, or highly restricted. Access should follow least privilege, with authentication, authorization, encryption, monitoring, and appropriate separation of duties.

Privacy requires purpose limitation, data minimization, retention controls, and careful handling of personal or sensitive information. De-identification reduces risk but does not guarantee anonymity, especially when datasets can be linked. Organizations must comply with applicable privacy, sectoral, and records-management requirements. Ethical practice also requires assessing whether collection and use are proportionate, whether affected individuals could reasonably expect the use, and whether errors may disadvantage particular groups.

Common mistakes include treating null as zero, joining tables without checking cardinality, using mutable business labels as keys, overwriting raw data, ignoring time zones, failing to document units, and allowing dashboards to use untested extracts. Another serious mistake is training models on data that would not have been available at prediction time. Data leakage produces falsely optimistic evaluation and may cause harmful operational decisions.

Contemporary Developments

Modern data platforms increasingly support real-time ingestion, data contracts, automated metadata capture, and infrastructure managed through code. Data mesh emphasizes domain ownership and data products, although it does not remove the need for common standards and platform capabilities. Data fabric refers to an integrated approach using metadata, automation, and connectivity across distributed environments.

Data observability tools monitor freshness, volume, schema, distributions, and lineage. Data quality is also becoming more closely connected to machine-learning operations through feature stores, model monitoring, and training-data versioning. Vector databases support retrieval applications involving embeddings, but they still require sound governance, provenance, access control, and evaluation of source content.

Artificial intelligence can assist with schema matching, anomaly detection, documentation, and query generation. Such automation should remain subject to human review. Generated transformations may encode incorrect assumptions, and automated data classification can mislabel sensitive information. The engineering principle remains unchanged: automation should increase evidence and control, not conceal uncertainty.

Best Practices for Reliable Analytical Data

A professional data team should define business terms before building metrics, declare the grain of every analytical table, preserve raw inputs when lawful and necessary, and use stable identifiers. Pipelines should be modular, version-controlled, observable, testable, and restartable. Quality checks should include both technical assertions and business reconciliations.

Documentation must be treated as part of the product. Every important dataset should identify its purpose, owner, source, refresh frequency, schema, known limitations, sensitivity, lineage, and approved uses. Engineers should design for failure, including late data, duplicated events, malformed records, changed schemas, partial outages, and replay requirements.

Most importantly, data architecture should be proportionate to the decision it supports. A small, well-defined dataset with clear ownership can be more valuable than a large platform with weak semantics. Reliable data is produced through a combination of technology, disciplined process, domain knowledge, and accountability.

Chapter Summary

Data management and data engineering transform operational records and diverse source material into reliable, governed analytical assets. Relational systems provide tables, keys, constraints, and strong support for structured transactions and queries, while NoSQL systems support varied data models and distributed access patterns. Schemas define structure, and metadata supplies the meanings, lineage, ownership, and operational context needed for responsible use.

ETL transforms before loading, whereas ELT loads before transforming; both can be appropriate depending on workload, governance, and platform capabilities. Warehouses emphasize curated analytical data, lakes provide flexible storage for varied formats, and lakehouses seek to combine flexibility with transactional and governance features. Effective pipelines use explicit grain, stable identities, validation, orchestration, monitoring, and recovery mechanisms.

Quality, privacy, security, and governance are inseparable from technical design. The Northstar Retail case illustrates how definitions, keys, time-awareness, and testing can improve both reporting and machine learning. Emerging practices such as data contracts, observability, data products, feature stores, and AI-assisted engineering extend these foundations but do not replace professional judgment.

Key Terms

Relational database: A database that represents data in related tables governed by a formal relational model.

Primary key: An attribute or set of attributes that uniquely identifies a row.

Foreign key: An attribute that references a key in another table to represent a relationship.

Normalization: The organization of data to reduce redundancy and update anomalies.

Denormalization: The deliberate duplication or restructuring of data to improve access or analytical performance.

NoSQL: A broad family of non-relational database approaches, including document, key-value, wide-column, and graph systems.

Schema: A definition of data structure, types, relationships, and constraints.

Metadata: Information describing the structure, meaning, origin, use, or operation of data.

Data lineage: A record of data origins and transformations through a system.

Data warehouse: A governed repository optimized for structured analytical querying.

Data lake: Flexible storage for raw or lightly processed structured and unstructured data.

Lakehouse: An architecture combining lake-style storage flexibility with warehouse-like management and analytical capabilities.

ETL: Extract, transform, and load; a process that transforms data before loading it into a target.

ELT: Extract, load, and transform; a process that transforms data after loading it into a target platform.

Data pipeline: A sequence of automated activities that ingests, transforms, validates, and delivers data.

Data contract: An agreement specifying expected data fields, meanings, quality, ownership, and change rules.

Data grain: The precise meaning represented by one row in a table.

Data quality: The degree to which data is accurate, complete, consistent, timely, valid, and unique for its purpose.

Data governance: The assignment of policies, responsibilities, standards, and controls for managing data.

Data leakage: The unintended use of information in model development that would not be available at prediction time.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?

2. Define schema-on-write and schema-on-read.

3. What is metadata, and why is lineage important?

4. Distinguish ETL from ELT.

5. What does the grain of a fact table mean?

Long Answer Questions

1. Explain normalization, denormalization, and the role of star schemas in analytical systems.

2. Compare relational databases with document, key-value, wide-column, and graph databases.

3. Describe the architecture and operating requirements of a reliable data pipeline.

4. Compare data warehouses, data lakes, and lakehouses, including their principal trade-offs.

5. Explain how data quality, governance, security, and privacy influence analytical reliability.

Analytical / Application Questions

1. A company joins an order table to a product table and its reported revenue doubles. Diagnose the likely causes and propose validation steps.

2. Design an ingestion and transformation approach for sensor data that must support both real-time alerts and historical machine-learning analysis.

3. An organization wants to predict customer churn using account closure dates and service interactions. Identify possible leakage risks and explain how you would construct a point-in-time-correct dataset.

22. Multiple Choice Questions with Answers

Question 1

A fact table contains one row for each product on an order. Which statement correctly describes its grain?

A. One row per customer

B. One row per order

C. One row per product per order line

D. One row per product category

Correct Answer: C. One row per product per order line

Question 2

Which situation most strongly supports using ELT rather than traditional ETL?

A. The target platform has scalable compute and transformations can be governed there

B. Sensitive data must be filtered before entering the target

C. The target cannot execute transformations

D. The source has no extractable data

Correct Answer: A. The target platform has scalable compute and transformations can be governed there

Question 3

What is the main purpose of a foreign-key constraint?

A. To encrypt a column

B. To prevent all duplicate values in a database

C. To ensure references correspond to existing records

D. To determine query execution speed

Correct Answer: C. To ensure references correspond to existing records

Question 4

A left join is used between customers and purchases. What does it preserve?

A. Only customers who made purchases

B. All customers, including those without purchases

C. Only purchases with valid customer records

D. All rows from both tables regardless of matching

Correct Answer: B. All customers, including those without purchases

Question 5

Which example is operational metadata?

A. The business definition of net revenue

B. The owner of a customer dataset

C. The refresh delay and pipeline failure rate

D. The legal basis for collecting names

Correct Answer: C. The refresh delay and pipeline failure rate

Question 6

Why can a data lake become a data swamp?

A. It stores only highly structured data

B. It cannot store raw data

C. It lacks documentation, governance, discoverability, and quality controls

D. It requires every record to be normalized

Correct Answer: C. It lacks documentation, governance, discoverability, and quality controls

Question 7

Which design best prevents duplicate effects when a pipeline is rerun?

A. Removing all source identifiers

B. Using idempotent processing and stable event identifiers

C. Appending every output without validation

D. Deleting the raw input after each run

Correct Answer: B. Using idempotent processing and stable event identifiers

Question 8

A demand model uses a promotion recorded after the forecast date. What problem does this create?

A. Normalization

B. Data leakage

C. Referential integrity

D. Schema inference

Correct Answer: B. Data leakage

Question 9

Which architecture is most appropriate for curated financial reporting with stable definitions and structured queries?

A. A governed data warehouse

B. An uncatalogued object-storage folder

C. A cache-only key-value store

D. A graph database without tabular models

Correct Answer: A. A governed data warehouse

Question 10

Which practice most directly improves the interpretability and reuse of an analytical dataset?

A. Removing all metadata to reduce storage

B. Documenting definitions, grain, lineage, owner, freshness, and limitations

C. Allowing every user to modify source data

D. Replacing stable keys with descriptive labels

Correct Answer: B. Documenting definitions, grain, lineage, owner, freshness, and limitations