Data Sources, Databases, and Analytics Technology

Introduction

Business analytics begins before modeling, visualization, or decision-making: it begins with obtaining trustworthy data. Organizations generate data through sales transactions, customer interactions, operational processes, surveys, websites, mobile applications, sensors, public records, and third-party providers. These sources differ in structure, ownership, quality, update frequency, legal status, and analytical value.

A business analyst therefore needs more than the ability to write queries or create dashboards. The analyst must understand where data originates, how it moves through an organization, how it is stored, how it can be integrated, and how its limitations affect conclusions. This chapter introduces the principal data sources and technologies used in modern analytics, including relational databases, SQL, data warehouses, data lakes, cloud platforms, ETL and ELT, application programming interfaces, and contemporary lakehouse architectures.

Learning Objectives

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

1. Classify common internal and external data sources.
2. Evaluate data sources for relevance, quality, timeliness, accessibility, and legality.
3. Explain relational database concepts and write basic SQL queries.
4. Distinguish operational databases, data warehouses, data lakes, and lakehouses.
5. Explain ETL, ELT, batch processing, and streaming architectures.
6. Describe the role of cloud platforms, APIs, and web data collection.
7. Identify governance, privacy, security, and ethical risks in data acquisition.
8. Recommend an appropriate analytics architecture for a business problem.

1. The Data Supply Chain

The data supply chain is the sequence through which data is created, captured, transported, stored, transformed, analyzed, and used. A simplified supply chain is:

Business activity → source system → ingestion → storage → transformation → analysis → decision or action

For example, when a customer purchases a product, a point-of-sale system records the transaction. An ingestion process copies the transaction to an analytical platform. Transformation standardizes product codes and joins the transaction with customer and store information. Analysts then calculate sales, margins, and customer trends. The resulting insight may support inventory replenishment or a marketing campaign.

Errors can enter at every stage. A source system may contain duplicate customers, an API may omit records, a transformation may incorrectly join tables, or a dashboard may use an inappropriate definition of revenue. Data analytics is consequently a socio-technical process: technology, organizational procedures, human judgment, and governance all influence the result.

Important dimensions of data quality

Data quality is not absolute. A data set may be sufficiently accurate for strategic trend analysis but unsuitable for real-time fraud prevention. Quality must therefore be evaluated against a specific purpose.

2. Major Data Sources

2.1 Operational systems

Operational systems support day-to-day business activities. Examples include enterprise resource planning systems, customer relationship management platforms, point-of-sale systems, human-resource systems, banking systems, and supply-chain applications. They typically record events such as orders, payments, shipments, returns, employee changes, and service interactions.

Operational data is often detailed and valuable because it is generated as part of real business processes. However, it is designed primarily for transaction processing rather than complex analysis. Heavy analytical queries can slow the system that employees and customers depend on. Operational databases may also use normalized structures that reduce duplication but require several joins for reporting.

Common extraction methods include scheduled exports, database replication, change-data capture, and vendor-provided connectors. Analysts must understand whether an extraction is a complete snapshot or contains only changes since the previous extraction.

2.2 Surveys and experiments

Surveys collect responses directly from individuals or organizations. They can measure attitudes, satisfaction, intentions, awareness, and experiences that are not captured in transaction systems. Survey design affects data quality substantially. Leading questions, ambiguous wording, poor response options, and nonresponse can create bias.

A population is the complete group about which conclusions are intended. A sample is the subset that provides observations. If the sample is not representative, increasing its size does not necessarily remove bias. For example, a customer survey distributed only through a mobile application may underrepresent customers who rarely use the application.

Experiments deliberately vary an intervention and compare outcomes. Random assignment, when feasible, helps distinguish the effect of the intervention from pre-existing differences. Ethical review, informed consent, and protection of participants remain essential.

2.3 APIs

An application programming interface (API) is a defined method through which one software system requests data or services from another. A typical web API uses an HTTP request and returns data in JSON or XML. Requests commonly contain an endpoint, parameters, authentication credentials, and sometimes a time range or page number.

APIs are useful because they provide structured, repeatable access. Their limitations include rate limits, changing specifications, incomplete historical coverage, downtime, authentication requirements, and restrictions on redistribution. A robust ingestion process records the request time, endpoint, parameters, response status, and source version.

Pagination deserves special attention. If an API returns only 100 records per request, a complete extraction requires repeated requests until no additional page exists. The process should also handle retries, duplicate records, and partial failures.

2.4 Web sources and web scraping

Public web pages can provide prices, product descriptions, reviews, job advertisements, news, and public announcements. Web scraping programmatically retrieves and parses page content. It differs from using an API because page layouts are primarily designed for human viewing and may change without notice.

Scraping must respect applicable law, terms of service, copyright, robots-exclusion instructions, access controls, and privacy expectations. It should not bypass authentication or impose unreasonable traffic. When a legitimate API is available, it is generally preferable to scraping pages.

2.5 External data providers

External providers sell or license demographic, geographic, economic, market, weather, industry, and consumer data. Such data can enrich internal records, but the buyer must evaluate documentation, licensing, collection methods, geographic coverage, update frequency, and liability for errors. A provider’s reputation does not guarantee that its data is appropriate for a particular decision.

2.6 Machine and sensor data

Internet-of-Things devices, industrial equipment, vehicles, and applications produce event streams. These data may arrive at high volume and velocity. Important design questions include event time versus processing time, device identification, missing transmissions, calibration, and retention. Sensor readings are not automatically facts about the physical world; they are measurements produced by instruments with error and operating assumptions.

3. Relational Databases and Data Modeling

A relational database stores data in tables. A table consists of rows, representing records, and columns, representing attributes. A primary key uniquely identifies a row. A foreign key references a key in another table and represents a relationship.

Consider a retail model with the following tables:

• Customer(CustomerID, Name, Region)
• Product(ProductID, ProductName, Category, UnitPrice)
• Order(OrderID, CustomerID, OrderDate)
• OrderLine(OrderID, ProductID, Quantity, UnitPrice)

CustomerID in Order is a foreign key to Customer. OrderLine resolves the many-to-many relationship between orders and products: an order can contain many products, and a product can appear in many orders.

Normalization

Normalization organizes data to reduce unnecessary duplication and update anomalies. In a normalized design, customer information is stored once rather than repeated in every order. If a customer changes region, one customer record is updated.

Normalization supports transaction integrity, but analytical models often intentionally introduce some redundancy. A dimensional warehouse commonly contains a central fact table connected to dimension tables. This can make business reporting easier and improve query performance. The appropriate design depends on workload, governance, and performance requirements.

Schema-on-write and schema-on-read

In schema-on-write systems, data must conform to a defined structure before it is stored for use. This promotes consistency but can slow the incorporation of new data. In schema-on-read systems, data may be stored in a relatively raw form and interpreted when accessed. This increases flexibility but places greater responsibility on analysts and governance processes.

4. SQL for Analytical Work

Structured Query Language (SQL) is used to define, retrieve, manipulate, and control data in relational systems. A basic query is:

SELECT CustomerID, Region
FROM Customer
WHERE Region = 'West';

The SELECT clause identifies columns, FROM identifies the table, and WHERE filters rows.

An aggregate query can summarize sales:

SELECT ProductID,
       SUM(Quantity * UnitPrice) AS SalesValue
FROM OrderLine
GROUP BY ProductID
ORDER BY SalesValue DESC;

Here, each line’s value is calculated as quantity multiplied by unit price, and values are summed by product. This calculation assumes that UnitPrice is expressed in a common currency and represents the relevant sales price. If discounts, taxes, refunds, or multiple currencies exist, the query must incorporate those business rules.

A join combines related tables:

SELECT o.OrderID, c.Region, o.OrderDate
FROM Order AS o
JOIN Customer AS c
  ON o.CustomerID = c.CustomerID;

An inner join returns rows with matching keys in both tables. A left join returns every row from the left table and matching rows from the right table; unmatched right-side values become NULL. Analysts must choose joins deliberately because an incorrect join can duplicate measures or silently exclude records.

A useful analytical query may use common table expressions:

WITH customer_sales AS (
    SELECT o.CustomerID,
           SUM(ol.Quantity * ol.UnitPrice) AS SalesValue
    FROM Order AS o
    JOIN OrderLine AS ol ON o.OrderID = ol.OrderID
    GROUP BY o.CustomerID
)
SELECT CustomerID, SalesValue
FROM customer_sales
WHERE SalesValue >= 1000;

SQL results should be validated. Analysts should check row counts before and after joins, identify nulls, compare totals with a trusted source, and test unusual cases such as returns and cancelled orders.

5. Analytical Storage Technologies

Operational databases

Operational databases prioritize fast inserts, updates, and reliable transactions. They are appropriate for applications such as order entry and account management. They are usually not the best location for organization-wide historical analysis.

Data warehouses

A data warehouse is a curated repository designed for reporting and analysis. It integrates data from multiple sources, preserves historical information, and applies standardized definitions. Warehouses often organize data into fact tables and dimension tables. A sales fact table might contain quantity, revenue, cost, date key, product key, and store key. Dimensions provide descriptive context.

Warehouses generally emphasize governed, structured data. They are appropriate when consistent metrics, controlled access, and reliable reporting are priorities.

Data lakes

A data lake stores large amounts of structured, semi-structured, and unstructured data, often in relatively raw form. Files may include relational extracts, JSON events, text, images, audio, and sensor records. Lakes are flexible and can preserve source data for future uses, but unmanaged lakes can become “data swamps,” in which data is difficult to find, interpret, or trust.

A functional lake requires cataloging, ownership, security, metadata, retention policies, quality checks, and clear zones such as raw, refined, and curated data.

Lakehouses

A lakehouse combines low-cost, flexible storage associated with lakes with management features traditionally associated with warehouses. It typically provides transaction support, schema management, versioning, and governance over files or object storage. The term describes an architectural approach rather than a single universal product. Its value depends on implementation quality and organizational requirements.

Cloud platforms

Cloud analytics platforms provide on-demand computing, managed storage, security services, databases, orchestration, and machine-learning capabilities. They can scale resources according to workload and reduce the need to operate physical infrastructure. However, cloud adoption does not eliminate architecture decisions. Organizations must manage costs, identity permissions, data location, vendor dependence, resilience, and contractual obligations.

6. ETL, ELT, and Data Pipelines

ETL means extract, transform, load. Data is extracted from source systems, transformed in a processing environment, and loaded into the target repository. Traditional ETL is useful when the target requires strict structure or when sensitive data must be transformed before storage.

ELT means extract, load, transform. Data is loaded into a scalable target first and transformed there. ELT is common in cloud environments because the target platform can provide substantial processing capacity. Raw data can be retained for auditing and future transformation.

A reliable pipeline usually includes:

1. Source identification and ownership.
2. Extraction or ingestion.
3. Validation of format, completeness, and keys.
4. Storage of raw or staged data.
5. Transformation and standardization.
6. Quality testing and reconciliation.
7. Publication to analytical tables or semantic models.
8. Monitoring, logging, alerting, and documentation.

Batch processing handles data at scheduled intervals, such as hourly or nightly. Streaming processing handles events continuously or in small intervals. Streaming is valuable for fraud alerts, operational monitoring, and recommendations, but it introduces complexity involving event ordering, late arrivals, duplicate events, and recovery.

A pipeline should be idempotent where possible: rerunning the same input should not create unintended duplicates or inconsistent results. This can be supported by stable event identifiers, checkpoints, merge operations, and controlled reprocessing.

7. A Modern Analytics Architecture

A contemporary architecture often includes source systems, ingestion services, object storage or a warehouse, transformation tools, a semantic layer, dashboards, and machine-learning services. The semantic layer defines reusable business measures such as net sales, active customer, or on-time delivery. Without shared definitions, different departments may produce conflicting results from the same underlying data.

Metadata is equally important. Technical metadata describes table names, data types, and update times. Business metadata explains meaning, ownership, and approved use. Lineage metadata records how a dashboard field was derived from source fields. Together, these features improve discovery, reproducibility, and auditability.

Data architecture should be selected according to requirements rather than fashion. Key questions include:

• How quickly must data become available?
• How large and varied are the data sources?
• Which data must be retained, and for how long?
• What reliability and recovery levels are required?
• Who may access sensitive fields?
• What analytical workloads are expected?
• What skills and budget are available?

8. Illustrative Case: Retail Inventory Analytics

Background

A regional retailer operates stores, an online channel, a warehouse, and a loyalty program. Managers report that stockouts are increasing, but the organization cannot agree on the cause. Store sales are stored in a point-of-sale database, online orders in an e-commerce platform, inventory movements in a warehouse system, and supplier delivery information in spreadsheets.

Problem

The retailer needs a daily view of available inventory, expected replenishment, sales velocity, and stockout risk. Directly querying operational systems is slow and produces inconsistent definitions of “available stock.”

Analysis and approach

The analytics team first documents source owners and key entities. It identifies product, store, supplier, order, shipment, and inventory-event keys. A scheduled extraction captures point-of-sale transactions, online orders, and warehouse movements. Supplier spreadsheets are uploaded to a controlled landing area with validation rules for product codes and dates.

The team adopts an ELT design. Raw extracts are retained for traceability. Transformations standardize product identifiers, convert timestamps to a common time zone, distinguish sales from returns, and reconcile inventory movements. A curated fact table records inventory events, while dimensions describe products, stores, suppliers, and dates.

A daily dashboard reports opening inventory, receipts, sales, returns, adjustments, and closing inventory. The team defines closing inventory conceptually as the prior balance plus accepted increases minus accepted decreases; the exact implementation depends on the organization’s event model and adjustment policy. Reconciliation tests compare calculated balances with warehouse-system balances and flag unexplained differences.

Outcome

Managers discover that many apparent stockouts are caused not by insufficient supply but by delayed receiving records and inconsistent product codes. After correcting the integration process, the retailer can distinguish genuine shortages from data-recording problems. Replenishment decisions improve, while unresolved discrepancies remain visible rather than being hidden in a dashboard.

Learning points

1. Architecture cannot compensate for undefined business concepts.
2. Key management is central to integration.
3. Raw data retention supports investigation and auditability.
4. Data-quality monitoring should be part of the pipeline, not an afterthought.
5. A dashboard is only as reliable as its source definitions and transformations.

9. Governance, Privacy, Security, and Ethics

Data acquisition must comply with applicable privacy and data-protection law, contractual terms, sector regulation, and organizational policy. Important principles commonly include purpose limitation, data minimization, transparency, accuracy, retention control, and accountability. The precise legal requirements depend on jurisdiction and context; organizations should obtain qualified legal and compliance advice.

Personally identifiable information may include names, contact details, identification numbers, precise location, online identifiers, or combinations of attributes that enable identification. Sensitive information may receive stronger protection. Access should follow least privilege: users receive only the permissions required for legitimate work. Encryption, authentication, logging, segregation of duties, and secure deletion reduce risk but do not eliminate it.

Analysts should question whether data was collected fairly and whether its secondary use is justified. Combining data sets can create new privacy risks even when individual sources appear harmless. De-identification is not always permanent protection, especially when records can be linked with other data.

Ethical analytics also requires attention to exclusion and bias. A data source may systematically underrepresent certain populations. Automated decisions based on such data can reproduce or amplify disadvantage. Documentation should identify intended use, limitations, affected groups, and review procedures.

10. Best Practices and Common Failure Modes

Effective practice includes:

• Define business terms before building pipelines.
• Assign data owners and stewards.
• Preserve source provenance and transformation lineage.
• Use stable keys and document join logic.
• Validate completeness, uniqueness, validity, and reconciliation totals.
• Separate development, testing, and production environments.
• Monitor pipeline freshness, failures, volume changes, and schema changes.
• Apply least-privilege access and protect sensitive fields.
• Retain raw data where lawful and useful for audit or reprocessing.
• Document assumptions, exclusions, and known limitations.

Common failures include building dashboards directly on uncontrolled spreadsheets, treating missing values as zeros, joining tables without checking cardinality, ignoring time zones, overwriting historical values without explanation, and assuming a cloud service automatically provides governance. Another failure is collecting data simply because it is available. Data has value only when its meaning, quality, legal basis, and analytical purpose are understood.

Contemporary Developments

Modern analytics increasingly combines governed self-service, real-time processing, data contracts, observability, and machine learning. Data contracts specify expected schemas, meanings, quality rules, and responsibilities between producers and consumers. Data observability tools monitor freshness, volume, distribution, and lineage. Generative AI can assist with SQL drafting, metadata search, and documentation, but generated queries require human review for correctness, security, and business meaning.

The continuing direction is toward architectures that make data both accessible and controlled. The central challenge is not merely storing more data; it is creating trustworthy, interpretable, secure, and appropriately available data for decisions.

Chapter Summary

Business data comes from operational systems, surveys, experiments, APIs, websites, external providers, and connected devices. Each source has different strengths, limitations, quality risks, and governance requirements. Relational databases organize structured records using tables, keys, and relationships; SQL supports filtering, joining, aggregation, and validation.

Operational databases support transactions, whereas warehouses support governed analysis. Data lakes provide flexible storage for varied data, and lakehouses seek to combine flexibility with management and transaction features. ETL transforms data before loading; ELT loads data before transformation. Batch and streaming pipelines address different timeliness and complexity requirements.

Reliable analytics requires architecture, metadata, lineage, quality controls, security, privacy protection, and clear business definitions. Technology choices should follow analytical requirements rather than fashion. Analysts must treat data acquisition and preparation as essential parts of analytical reasoning, not as purely technical preliminaries.

Key Terms

API: A defined interface through which software systems exchange data or services.

Batch processing: Processing data at scheduled or accumulated intervals.

Data lake: A repository for large volumes of structured, semi-structured, and unstructured data.

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

Data warehouse: A curated repository optimized for reporting and analysis.

Database: An organized system for storing and managing data.

ELT: Extract, load, transform; data is transformed after loading into the target platform.

ETL: Extract, transform, load; data is transformed before loading into the target platform.

Fact table: A table containing measurable events or quantities in a dimensional model.

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

Lakehouse: An architecture combining lake-style flexible storage with warehouse-style management capabilities.

Metadata: Data describing the structure, meaning, ownership, or history of other data.

Normalization: Organizing relational data to reduce duplication and update anomalies.

Operational system: A system designed to support routine business transactions.

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

Schema: The structure and rules describing data elements and relationships.

Streaming processing: Processing events continuously or near continuously as they arrive.

Web scraping: Programmatically collecting and parsing information from web pages.

Review Questions

Short Answer Questions

1. What is the difference between an operational database and a data warehouse?
2. Define a primary key and a foreign key.
3. What is an API, and why is it useful for data acquisition?
4. Distinguish ETL from ELT.
5. Name four dimensions of data quality.

Long Answer Questions

1. Explain how data moves through a typical analytics supply chain, identifying possible points of failure.
2. Compare data warehouses, data lakes, and lakehouses in terms of structure, flexibility, governance, and use cases.
3. Explain how normalization supports transaction systems and why analytical models may intentionally use denormalization.
4. Describe the controls required for a reliable analytical data pipeline.
5. Discuss the privacy, ethical, and security issues involved in combining internal customer data with external data.

Analytical / Application Questions

1. A company joins an order table to an order-line table and finds that total revenue has doubled. Diagnose the likely cause and propose validation steps.
2. Design a high-level architecture for a fraud-monitoring application that receives transaction events continuously and also requires historical analysis.
3. An organization wants to scrape competitor prices from public websites. Evaluate the technical, legal, ethical, and business risks and recommend an appropriate approach.

Multiple Choice Questions with Answers

Question 1

Which source is primarily designed to support routine business transactions?

A. Operational database

B. Data warehouse

C. Data lake

D. Metadata catalog

Correct Answer: A. Operational database

Question 2

What does a foreign key represent in a relational database?

A. A calculated measure

B. A reference to a key in another table

C. A duplicate-free text field

D. A database password

Correct Answer: B. A reference to a key in another table

Question 3

Which SQL clause filters individual rows before aggregation?

A. ORDER BY

B. GROUP BY

C. WHERE

D. SELECT

Correct Answer: C. WHERE

Question 4

What is the principal characteristic of ELT?

A. Data is deleted after extraction

B. Data is transformed before extraction

C. Data is loaded before transformation

D. Data is never transformed

Correct Answer: C. Data is loaded before transformation

Question 5

Which technology is generally most suitable for continuous fraud alerts?

A. Streaming processing

B. Annual batch processing

C. Static spreadsheet storage

D. Offline archival storage

Correct Answer: A. Streaming processing

Question 6

A data lake becomes difficult to use when it lacks cataloging, ownership, and quality controls. What is this condition commonly called?

A. Normalization

B. A data swamp

C. Replication

D. Pagination

Correct Answer: B. A data swamp

Question 7

Which join returns every row from the left table and matching rows from the right table?

A. Inner join

B. Cross join

C. Left join

D. Self join

Correct Answer: C. Left join

Question 8

Why should an analyst check join cardinality before summing a measure?

A. Joins can duplicate records and inflate totals

B. Joins always remove null values

C. Cardinality changes the currency

D. Joins prevent aggregation

Correct Answer: A. Joins can duplicate records and inflate totals

Question 9

Which principle gives users only the access needed for legitimate work?

A. Schema-on-read

B. Least privilege

C. Denormalization

D. Pagination

Correct Answer: B. Least privilege

Question 10

Which feature records where a dashboard field originated and how it was transformed?

A. Data lineage

B. Rate limiting

C. Primary indexing

D. Web caching

Correct Answer: A. Data lineage