Data Architecture, Preparation and Governance

Introduction

Business analytics depends less on sophisticated algorithms than on the quality, accessibility, meaning and control of the data supplied to them. A sales forecast cannot be trusted if transactions are duplicated, customer identities are inconsistent, dates use different time zones, or the source system changes a field without notice. Data architecture provides the structures through which data is collected, stored, integrated and made available. Data preparation converts raw records into analytically usable data. Data governance establishes the policies, responsibilities and controls that preserve data quality, security, meaning and accountability.

For a business analyst, these subjects are not merely technical concerns. They affect revenue reporting, regulatory compliance, customer experience, operational efficiency and managerial trust. A well-designed analytical solution therefore treats data as an organizational asset with a defined lifecycle, ownership model and evidence trail.

Learning Objectives

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

1. Explain the principal components of modern data architecture.
2. Distinguish operational databases, analytical databases, data warehouses, data lakes and lakehouses.
3. Acquire data through SQL, APIs, files and cloud platforms.
4. Apply systematic techniques for cleaning, integrating and transforming structured and unstructured data.
5. Assess data quality using measurable dimensions and indicators.
6. Explain metadata, data lineage, stewardship, ownership and master-data management.
7. Design a reproducible data-preparation workflow.
8. Evaluate governance, privacy, security, ethical and regulatory risks.

1. Data Architecture as an Analytical Foundation

Data architecture is the planned arrangement of data sources, storage technologies, integration processes, access mechanisms, metadata and controls. It answers four practical questions:

• Where does data originate?
• How is it moved and transformed?
• Where is it stored and in what form?
• Who may use it, for what purpose, and with what controls?

A simplified architecture contains five layers:

1. Source layer: enterprise resource planning systems, customer relationship management systems, point-of-sale terminals, websites, sensors, spreadsheets, documents and external providers.
2. Ingestion and integration layer: batch uploads, application programming interfaces (APIs), extract-transform-load (ETL), extract-load-transform (ELT), message queues and change-data-capture processes.
3. Storage layer: relational databases, data warehouses, object storage, data lakes and lakehouses.
4. Transformation and semantic layer: validated tables, business definitions, feature stores, analytical models and reporting views.
5. Consumption and control layer: dashboards, statistical software, machine-learning systems, governance catalogues, access controls and audit logs.

The layers are conceptually distinct, although commercial platforms may combine several of them. Separating them improves maintainability: a change in a source system should not silently invalidate every report downstream.

Operational and analytical workloads

Operational systems are optimized for frequent, small transactions. A normalized relational database may store a customer once and link that customer to many orders. Analytical systems are optimized for reading and aggregating large volumes of historical data. They often use denormalized dimensional structures, columnar storage and parallel processing.

A useful distinction is between online transaction processing (OLTP) and online analytical processing (OLAP):

The distinction is not absolute. Modern cloud systems can support both patterns, but workload requirements remain important when choosing a design.

2. Storage Models and Architectural Choices

Relational databases

Relational databases represent data in tables consisting of rows and columns. A primary key uniquely identifies a row; a foreign key references a key in another table. Constraints can enforce uniqueness, valid ranges and referential integrity.

Normalization reduces unnecessary repetition. For example, customer information should generally be stored in a customer table rather than repeated in every order row. Excessive normalization, however, can require many joins for analytical queries. Analytical warehouses commonly use a star schema, consisting of a central fact table linked to descriptive dimension tables.

A sales fact table might contain date key, product key, store key, quantity and sales value. Dimension tables may describe products, stores and calendar periods. The grain must be explicit: for example, “one row per product per order line.” Ambiguous grain is a major source of incorrect aggregation.

Data warehouses, lakes and lakehouses

A data warehouse stores curated, structured data for reporting and analysis. Its data generally has defined schemas, quality controls and business-friendly definitions.

A data lake stores large volumes of raw or lightly processed data, including structured tables, JSON records, text, images and audio. Its flexibility is useful when future analytical uses are not yet known, but weak controls can turn a lake into a poorly documented “data swamp.”

A lakehouse combines low-cost, flexible storage with warehouse-like management, including transactional consistency, schema enforcement and analytical performance. The appropriate choice depends on cost, latency, governance, skills, regulatory requirements and the diversity of data types—not on fashionable terminology.

Batch and streaming architectures

Batch processing moves data at scheduled intervals, such as hourly or nightly. It is suitable for payroll, daily financial reporting and many historical analyses. Streaming processes events continuously or near real time, which is useful for fraud detection, equipment monitoring and website personalization.

Streaming does not eliminate the need for data quality. It introduces additional concerns, including event ordering, duplicate messages, late-arriving events, temporary service failures and exactly-once or at-least-once delivery semantics.

3. Acquiring and Integrating Data

SQL acquisition

SQL is used to select, filter, join, aggregate and transform relational data. For example:

SELECT product_id,
       SUM(quantity * unit_price) AS revenue
FROM order_lines
WHERE order_date >= '2025-01-01'
GROUP BY product_id;

This query assumes that quantity and unit_price are numeric, that each row represents an order line, and that revenue is defined as quantity multiplied by unit price before discounts and taxes. A professional analyst must verify those assumptions rather than infer them from column names.

Joins require particular care. An inner join retains only matching records. A left join retains every row from the left table and matching rows from the right table. If the right table contains several matching rows, the join can multiply records. Analysts should therefore check the cardinality of relationships and compare row counts before and after joins.

APIs and external data

An API provides a controlled method for one system to request data from another. Common API practices include authentication tokens, pagination, rate limits, versioning, status codes and JSON responses. A robust ingestion process should record:

• endpoint and request parameters;
• extraction timestamp and time zone;
• API version;
• response status;
• page or cursor information;
• raw response location;
• retry and failure details.

The raw response should usually be retained before transformation. This supports auditability and allows the process to be rerun when transformation logic changes.

Files and unstructured data

CSV files require validation of delimiters, quotation marks, character encoding, headers, decimal conventions and missing-value symbols. Spreadsheet files may contain formulas, merged cells, multiple tables and manually overwritten values.

Unstructured data includes emails, contracts, social-media text, images, audio and video. Preparation may involve optical character recognition, language detection, tokenization, entity extraction, classification or embedding generation. These transformations can introduce errors and should preserve links to the original material. An extracted customer name is not equivalent to a verified customer identifier.

Integration and entity resolution

Integration combines data from different sources. The same customer might appear as “A. Khan,” “Amir Khan,” and “CUST-1048.” Entity resolution attempts to determine whether records refer to the same real-world entity. Deterministic matching uses exact identifiers; probabilistic matching uses similarities in names, addresses, telephone numbers or other attributes.

Matching rules should be tested against labelled examples. False matches can merge two people, while missed matches can fragment one person into multiple records. In sensitive contexts, automated matching should support human review rather than operate without oversight.

4. Data Preparation: From Raw Records to Analytical Data

Data preparation is an iterative process rather than a single cleaning operation. A defensible workflow is:

1. Define the analytical question and unit of analysis.
2. Inventory sources and inspect schemas.
3. Preserve immutable raw data.
4. Profile completeness, uniqueness, distributions and relationships.
5. Standardize formats and reference values.
6. Validate business rules.
7. Resolve duplicates and integrate entities.
8. Transform data into analytical structures.
9. Test outputs and document decisions.
10. Publish a versioned dataset for approved users.

Missing values

A missing value may mean unknown, not applicable, not collected, withheld or not yet available. These meanings should not be collapsed into one code without justification. Common treatments include deletion, substitution with a constant, statistical imputation, model-based imputation or an explicit “unknown” category.

The correct treatment depends on the analytical purpose and the mechanism producing missingness. For example, a missing income value may be systematically associated with respondents who decline to answer. Replacing every missing value with zero would create a false economic meaning.

Duplicates and inconsistent formats

Duplicates may be exact copies or near duplicates. A duplicate transaction can overstate revenue; a duplicate customer can distort retention rates. Deduplication requires a business rule identifying which record is authoritative, such as the latest verified update or the source with the highest reliability.

Standardization includes consistent date formats, currency units, capitalization, country codes and categorical labels. Converting currencies requires an exchange-rate date and a stated convention. A value of 100 may mean dollars, euros, units or cents; numbers without metadata are not analytically complete.

Outliers and invalid values

An outlier is an observation unusually distant from other observations. It may represent fraud, a data-entry error, a rare but valid event or a change in population. It should not be removed solely because it is inconvenient.

Validation rules can identify impossible or suspicious records, such as a negative quantity where returns are stored separately, a delivery date earlier than an order date, or a customer age of 250. Rules should distinguish hard errors from warnings requiring investigation.

Text preparation

Text preparation can include lowercasing, punctuation handling, tokenization, language identification, stop-word treatment and named-entity recognition. These choices affect results. Removing negation words such as “not” may damage sentiment analysis. Stemming can improve matching but reduce interpretability. Text containing personal or confidential information requires access control and potentially redaction before external processing.

5. Measuring Data Quality

Data quality is fitness for a defined purpose. A dataset is not simply “good” or “bad”; it may be adequate for one use and unsuitable for another. Important dimensions include:

For a field with n records, of which n_present are populated, a simple completeness rate is:

This measure assumes that every record should contain the field. If missingness is valid for some records, the denominator should instead represent records for which the field is required.

If n_invalid records fail a defined validity rule, the invalidity rate is:

For example, if 48 of 1,200 required email addresses are missing, completeness is (1,200 − 48) ÷ 1,200 × 100% = 96%. The calculation does not establish accuracy: a syntactically valid but fictitious email address may still be wrong.

Quality thresholds should be linked to business risk. A 98% completeness rate may be unacceptable for regulatory reporting but adequate for exploratory market research. Data-quality dashboards should show trends, affected owners, severity and remediation status rather than a single undifferentiated score.

6. Metadata, Lineage and Reproducible Workflows

Metadata is data about data. Technical metadata describes data types, table structures and constraints. Business metadata explains meanings, definitions, owners and permitted uses. Operational metadata records refresh time, processing status and quality results.

A data catalogue makes metadata searchable. A business glossary standardizes terms such as “active customer,” “net sales” and “churn.” Definitions must specify scope and calculation. For example, “active customer” might mean a customer with at least one completed purchase in the preceding 12 months, excluding cancelled orders.

Data lineage records the origin and transformations of data. It may answer: Which source fields contribute to this dashboard metric? Which reports depend on a changed column? What transformations were applied to a personally identifiable field? Lineage is essential for impact analysis, audit and troubleshooting.

A reproducible workflow produces the same result—or a documented, explainable result—when run again on the same version of the inputs. Good practice includes:

• version-controlled SQL, notebooks and configuration files;
• fixed or recorded software and package versions;
• immutable raw inputs;
• parameterized workflows;
• automated tests;
• documented dependencies;
• separate development, testing and production environments;
• logged execution times and outcomes.

A notebook containing manually edited cells is not automatically reproducible. Reproducibility is a property of the whole process, not merely of the analytical code.

7. Governance, Ownership and Master Data

Data governance is the system of decision rights, policies, standards, roles and controls used to manage data. It should enable responsible use rather than simply restrict access.

Typical roles include:

• Data owner: accountable for a domain, policy decisions, access approval and acceptable quality.
• Data steward: manages definitions, quality rules, issue resolution and day-to-day coordination.
• Data custodian: operates storage, backup, security and technical controls.
• Data producer: creates or captures data in a source process.
• Data consumer: uses data for an authorized business purpose.
• Privacy or security officer: advises on legal, ethical and protection requirements.

Accountability should be assigned to named roles, not to an abstract “IT department.” A responsibility matrix can clarify who is responsible for performing an activity, accountable for its outcome, consulted about decisions and informed of results.

Master-data management

Master data describes core entities shared across processes, such as customers, products, suppliers, employees and locations. Master-data management (MDM) establishes authoritative records, identifiers, hierarchies, matching rules and change controls.

A product may have different codes in manufacturing, sales and finance. MDM can provide a durable enterprise identifier while retaining source-system identifiers for traceability. It should also manage effective dates: a product category change should not rewrite historical reports unless the organization explicitly chooses a restatement policy.

Reference data—such as country codes, tax categories and currency codes—is narrower than master data but equally important. Controlled reference lists prevent free-text variation and support consistent reporting.

8. Security, Privacy and Ethical Governance

Governance must address confidentiality, integrity and availability. Access should follow least privilege: users receive only the permissions required for their roles. Other controls include encryption in transit and at rest, multi-factor authentication, logging, backup, retention schedules and tested recovery procedures.

Personal data should be collected for a specified purpose, minimized where possible, retained only as long as justified and protected against unauthorized disclosure. De-identification reduces risk but is not automatically irreversible; combinations of quasi-identifiers can sometimes re-identify individuals.

Ethical governance also concerns fairness and explainability. If a data-quality problem disproportionately affects a protected group, an automated decision may reproduce that disadvantage. Analysts should examine representativeness, document limitations and provide escalation routes for harmful outcomes. Generative AI and automated extraction tools require controls for confidential inputs, hallucinated values, copyright, prompt logging and human review.

Legal requirements vary by jurisdiction and sector. Therefore, organizations should obtain appropriate legal advice and map data practices to applicable privacy, records, consumer-protection and industry regulations. Compliance is a minimum standard, not a substitute for ethical judgment.

9. An Illustrative Case: Retail Customer and Sales Integration

Background

A fictional retailer, Northstar Home, operates stores, an online shop and a loyalty programme. Executives observe that the online dashboard reports 8% more customers than the loyalty system and that regional sales totals differ between finance and marketing reports.

Problem

Three systems use different customer identifiers. Store transactions contain abbreviated product codes, online orders include returns as negative quantities, and the marketing database stores multiple loyalty records for some households. Dates are recorded in local time, while the warehouse uses coordinated universal time (UTC). No catalogue identifies which definition of “customer” each report uses.

Analysis

The analytics team first defines the grain of a sales fact as one completed order line, with returns represented by a separate transaction type. It profiles nulls, duplicate identifiers, date ranges and product-code coverage. It then compares totals before and after each join to identify row multiplication. A lineage review shows that finance excludes cancelled orders while marketing does not.

Approach and solution

The team creates a governed product master with an enterprise product identifier and source crosswalks. Customer records are matched using verified loyalty identifiers where available, followed by reviewed probabilistic matching for unresolved cases. Raw extracts are retained in cloud object storage, while validated sales facts and dimensions are published in a warehouse.

A business glossary defines “net sales” as completed sales less approved returns, excluding tax and cancelled orders. Time-zone conversion uses the store’s location and records both local event time and UTC. Automated tests check primary-key uniqueness, valid product references, accepted transaction types, and reconciliation to finance totals.

Outcome

The apparent customer discrepancy falls substantially after duplicate household records are separated from individual customers. Regional totals become reconcilable because reports use the same return and cancellation rules. Remaining exceptions are displayed in a quality dashboard rather than silently discarded.

Learning points

The case demonstrates that integration is not achieved merely by joining tables. It requires agreed definitions, entity governance, explicit grain, temporal rules, quality testing and lineage. Technical modernization without business ownership would not have solved the reporting conflict.

10. Implementation Guidance and Limitations

A practical implementation should begin with high-value data domains and measurable business problems. Organizations should avoid attempting to catalogue every field before delivering any benefit. At the same time, rapid delivery must not bypass security, retention or ownership decisions.

Common failure modes include:

• building dashboards before defining metrics;
• copying data into many uncontrolled spreadsheets;
• treating null, zero and “not applicable” as identical;
• joining on names without assessing duplicates;
• overwriting raw data;
• relying on manual notebook steps;
• measuring quality without assigning remediation ownership;
• assuming cloud storage automatically provides governance;
• removing outliers without investigating their business meaning.

Cloud platforms can provide elastic storage, managed databases, orchestration, catalogues and access policies. They also create risks involving misconfigured permissions, uncontrolled consumption costs, cross-border processing and vendor dependence. Platform features should support, not replace, architecture principles and governance accountability.

Best-practice checklist

Before publishing an analytical dataset, confirm that:

1. The purpose, population and grain are documented.
2. Sources and extraction times are recorded.
3. Raw inputs are preserved appropriately.
4. Keys, joins and cardinality have been tested.
5. Missing, duplicate, invalid and exceptional records are handled explicitly.
6. Business definitions and units are documented.
7. Quality results and unresolved limitations are visible.
8. Ownership, access and retention are established.
9. Code, configuration and dependencies are version controlled.
10. Lineage supports impact analysis and audit.

Chapter Summary

Data architecture organizes sources, integration processes, storage, analytical transformations and controlled consumption. OLTP systems support operational transactions, whereas analytical platforms support integrated historical analysis. Data lakes offer flexible storage, warehouses offer curated analytical structures, and lakehouses seek to combine both capabilities.

Reliable preparation requires a defined analytical grain, preserved raw data, profiling, validation, standardization, entity resolution and documented transformations. Data quality includes completeness, validity, accuracy, consistency, uniqueness, timeliness and integrity; each dimension must be assessed against a defined purpose. Metadata, catalogues, glossaries and lineage make data understandable and auditable.

Governance assigns decision rights and responsibilities through owners, stewards, custodians and consumers. Master-data management provides consistent identities and hierarchies for shared entities. Security, privacy, ethical review and reproducibility are integral to professional analytics. The central principle is that trustworthy analysis is produced by a controlled data lifecycle, not by an algorithm alone.

Key Terms

Data architecture: The planned structure of data sources, storage, integration, access and controls.

OLTP: Online transaction processing for operational record keeping.

OLAP: Online analytical processing for analysis and aggregation.

Data warehouse: Curated, structured storage designed for analytical use.

Data lake: Flexible storage for raw or lightly processed data in many formats.

Lakehouse: An architecture combining flexible lake storage with managed analytical capabilities.

ETL: Extract, transform and load processing.

ELT: Extract, load and transform processing, commonly used with scalable analytical platforms.

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

Foreign key: A field referencing a key in another table.

Grain: The precise meaning of one row in a dataset.

Entity resolution: The process of determining which records refer to the same real-world entity.

Metadata: Information describing data, its meaning, structure and use.

Data lineage: A record of data origins, transformations and downstream dependencies.

Data steward: A person responsible for operational data definitions, quality and coordination.

Master data: Shared core entities such as customers, products and suppliers.

Data quality: Fitness of data for a specified purpose.

Reproducibility: The ability to rerun a documented process and obtain an explainable result.

Data governance: Policies, roles, standards and controls for responsible data management.

Least privilege: Providing only the access necessary for an authorized role.

Review Questions

Short Answer Questions

1. What is the difference between an OLTP system and an OLAP system?
2. Why must the grain of a fact table be documented?
3. Name four dimensions of data quality.
4. What is the purpose of data lineage?
5. Distinguish a data owner from a data custodian.

Long Answer Questions

1. Compare data warehouses, data lakes and lakehouses, including their principal advantages and risks.
2. Explain a complete workflow for preparing raw transactional data for business analysis.
3. Discuss how metadata and a business glossary improve analytical consistency.
4. Explain the role of master-data management in integrating customer and product information.
5. Evaluate the security, privacy and ethical controls required when using cloud-based analytical data.

Analytical / Application Questions

1. A dataset contains 4,000 records, of which 120 lack a required value. Calculate its completeness rate and explain what the result does and does not prove.
2. After joining an order table to a customer table, the number of rows increases by 18%. Identify possible causes and design checks to determine whether the increase is legitimate.
3. Design a governance and reproducibility plan for a dashboard combining API data, spreadsheets and a cloud warehouse. Specify ownership, metadata, quality tests, lineage and access controls.

Multiple Choice Questions with Answers

Question 1

Which characteristic most directly distinguishes an OLTP system from an OLAP system?

A. OLTP systems record operational transactions, while OLAP systems support analytical queries.

B. OLTP systems store only unstructured data, while OLAP systems store only structured data.

C. OLTP systems never use relational tables, while OLAP systems always do.

D. OLTP systems require cloud computing, while OLAP systems require on-premises computing.

Correct Answer: A. OLTP systems record operational transactions, while OLAP systems support analytical queries.

Question 2

A table contains 2,500 records, and 75 required values are missing. What is the completeness rate?

A. 3%

B. 75%

C. 97%

D. 99.7%

Correct Answer: C. 97%

Question 3

Why should the grain of a fact table be explicitly documented?

A. To determine the color scheme of dashboards

B. To prevent incorrect aggregation and clarify what each row represents

C. To eliminate the need for primary keys

D. To ensure that all data is unstructured

Correct Answer: B. To prevent incorrect aggregation and clarify what each row represents

Question 4

Which practice best supports reproducibility?

A. Manually editing the final spreadsheet after every run

B. Deleting raw inputs after transformation

C. Version-controlling code, configurations and input versions

D. Allowing each analyst to use undocumented business definitions

Correct Answer: C. Version-controlling code, configurations and input versions

Question 5

What is the principal purpose of master-data management?

A. To replace all operational systems with spreadsheets

B. To establish consistent identities and definitions for shared entities

C. To remove all historical records

D. To encrypt every field regardless of sensitivity

Correct Answer: B. To establish consistent identities and definitions for shared entities

Question 6

A left join unexpectedly doubles the number of order rows. What is the most likely explanation?

A. The database has no storage capacity

B. Several customer rows match one order row

C. The query contains a valid aggregation

D. The source data is necessarily complete

Correct Answer: B. Several customer rows match one order row

Question 7

Which item is business metadata?

A. The physical data type of a column

B. The server's processor model

C. The definition of “active customer” used in a report

D. The number of bytes in a database file

Correct Answer: C. The definition of “active customer” used in a report

Question 8

Which statement about missing values is most accurate?

A. Every missing value should be replaced with zero

B. Missingness may represent different conditions and requires contextual treatment

C. Missing values always indicate inaccurate records

D. Deleting all rows with missing values is universally safest

Correct Answer: B. Missingness may represent different conditions and requires contextual treatment

Question 9

Which role is primarily accountable for policy decisions and acceptable quality within a data domain?

A. Data owner

B. Data consumer

C. Database end user

D. API client

Correct Answer: A. Data owner

Question 10

Which control best reflects the principle of least privilege?

A. Giving every employee administrator access

B. Allowing access only to data required for an authorized role

C. Publishing all raw personal data internally

D. Removing all access logs

Correct Answer: B. Allowing access only to data required for an authorized role