4. Data Sources, Databases and Data Access

Introduction and Context

Data science begins before modelling. A predictive model, dashboard, or artificial-intelligence system is only as reliable as the data supplied to it and the processes used to obtain, interpret, protect, and maintain that data. Data may originate in operational applications, transaction systems, sensors, mobile devices, public repositories, documents, partner organisations, or user interactions. These sources differ in structure, quality, accessibility, ownership, update frequency, and legal status.

Data access is therefore not simply a technical act of downloading a file or writing a query. It is an architectural and governance activity involving source selection, schema interpretation, authentication, extraction, validation, transformation, storage, monitoring, and responsible use. A data scientist must understand both the analytical value of a source and the risks associated with relying on it.

This chapter examines files, application programming interfaces (APIs), relational databases, NoSQL systems, data warehouses, data lakes, and lakehouses. It also considers connectors, query tools, data formats, access patterns, security, governance, and emerging practices such as data contracts and automated metadata management.

Learning Objectives

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

• distinguish major types of data sources and storage platforms;
• interpret schemas, metadata, keys, constraints, and data types;
• select appropriate access methods for files, APIs, SQL systems, and NoSQL databases;
• explain the roles of warehouses, lakes, and lakehouses in analytical architectures;
• use connectors, queries, pagination, and incremental extraction appropriately;
• identify data-quality, security, privacy, performance, and governance risks;
• design a secure and reproducible data-access workflow; and
• evaluate data-access choices in professional and organisational settings.

The Data-Source Landscape

A data source is any system, repository, device, service, or process from which data can be obtained. Sources are commonly classified by structure. Structured data has a defined tabular organisation, as in a relational database. Semi-structured data contains organisational markers but does not require a fixed table, as in JSON or XML. Unstructured data includes text documents, images, audio, and video, although metadata can make such material partially discoverable.

Sources can also be classified by origin. Internal sources include enterprise resource planning systems, customer relationship management platforms, finance applications, and operational logs. External sources include government data, commercial APIs, research repositories, social platforms, and partner feeds. Another important distinction is between transactional and analytical sources. Transactional systems are designed to record current business events efficiently, whereas analytical systems are designed to support aggregation, historical analysis, and complex queries.

The value of a source depends on more than volume. Relevant dimensions include accuracy, completeness, consistency, timeliness, uniqueness, validity, lineage, accessibility, and representativeness. A large data set with unclear definitions or systematic bias may be less useful than a smaller, well-documented source.

Files as Data Sources

Files remain important because they are portable, inexpensive, and widely supported. Common formats include comma-separated values (CSV), delimited text, Excel workbooks, JSON, XML, Parquet, Avro, and images or documents. CSV is easy to inspect but has weaknesses: it does not formally preserve data types, relationships, constraints, or nested structures. A value such as 01-02-2025 may be interpreted differently by different applications. Parquet and similar columnar formats preserve richer schema information and are generally efficient for analytical workloads because queries can read only required columns.

Before loading a file, the analyst should inspect its encoding, delimiter, header, missing-value conventions, duplicate rows, date formats, units, and expected grain. Grain means the level represented by one row. For example, a sales table may represent one order, one order line, or one customer-day. Confusing these levels can produce double counting.

A robust file-ingestion process records the source location, acquisition time, file version, schema, and validation results. Raw files should normally be retained separately from transformed data so that processing can be reproduced. File names alone are not sufficient for version control; a file may be replaced without its name changing.

APIs and Service-Based Access

An API provides a programmatic interface to a service. Instead of directly reading a database, a client sends requests to defined endpoints and receives responses, frequently in JSON. APIs may support filtering, sorting, authentication, pagination, rate limits, and event-based access through webhooks.

A typical API workflow involves obtaining credentials, identifying the endpoint, constructing a request, handling status codes, parsing the response, validating the returned schema, and storing the result. Pagination is essential when a service limits the number of records per response. A client may use page numbers, offsets, cursors, or links supplied in the response. Incremental extraction is usually preferable to repeatedly downloading all records; it may use a last-updated timestamp, change token, or event identifier.

APIs are governed by contracts. The contract specifies endpoint behaviour, parameters, response fields, error meanings, and usage limits. However, APIs can change. Production pipelines should therefore log requests and responses where permitted, monitor schema changes, implement retries with backoff, and distinguish temporary failures from permanent errors. Credentials should be stored in a secrets manager rather than embedded in code or notebooks.

Relational Databases and SQL Access

A relational database organises data into tables containing rows and columns. Tables are related through keys. A primary key uniquely identifies a row, while a foreign key refers to a key in another table. Constraints can enforce uniqueness, valid relationships, and required values. The relational model encourages controlled structure and precise querying.

Structured Query Language (SQL) is used to define, retrieve, and manipulate relational data. A simple query may select columns from a table, filter rows with `WHERE`, aggregate records using `GROUP BY`, and combine tables using joins. An inner join returns matching records from both tables; a left join preserves every record from the left table, even when no match exists on the right. Join choice is therefore a semantic decision, not merely a syntactic one.

Normalization reduces duplication by separating related facts into appropriate tables. This supports transactional consistency, but highly normalized structures may require many joins for analysis. Analytical environments often use dimensional models, especially star schemas. A fact table records measurable events, while dimension tables describe customers, products, locations, dates, or other analytical perspectives. A clear grain and well-managed keys are essential to prevent incorrect aggregations.

Database access should use least privilege. An analyst may need read access to selected views rather than unrestricted access to production tables. Parameterised queries reduce injection risk. Connection pooling, indexes, query plans, partition pruning, and appropriate filters improve performance. Long-running analytical queries should not unnecessarily compete with operational transactions; replicas, extracts, or warehouses may be more suitable.

NoSQL Systems and Polyglot Storage

NoSQL is a broad category rather than a single data model. Document databases store records such as JSON-like documents; key-value stores retrieve values by keys; wide-column systems organise data for large-scale distributed access; graph databases represent entities and relationships as nodes and edges.

NoSQL systems are often selected when data is semi-structured, relationships are highly connected, write volume is substantial, or horizontal scaling is important. Their flexibility does not eliminate the need for design. Document structure, access patterns, indexing, consistency requirements, retention, and duplication must be considered. A document model may embed related information for efficient retrieval, while another design may reference separate documents to avoid excessive duplication.

Relational and NoSQL systems should not be treated as competitors in every situation. Organisations commonly use polyglot persistence, selecting different technologies for different workloads. A relational system may manage financial transactions, a document store may support product catalogues, a graph database may analyse fraud relationships, and a warehouse may serve enterprise reporting.

Warehouses, Lakes and Lakehouses

A data warehouse is a governed analytical repository containing integrated, often structured and historical data. It supports reporting, business intelligence, and complex queries. Data is commonly transformed before or during loading, and dimensions, measures, business definitions, and access permissions are managed carefully.

A data lake stores large volumes of data in its original or near-original form. It can accommodate structured, semi-structured, and unstructured data. This flexibility is useful for exploration, machine learning, and retaining raw evidence. Without catalogues, quality controls, ownership, and lifecycle policies, however, a lake can become a poorly organised collection sometimes described as a data swamp.

A lakehouse combines characteristics of lakes and warehouses. It generally retains inexpensive, flexible storage while adding table management, transactions, schema enforcement or evolution, governance, and analytical performance features. The architectural choice should reflect workload, scale, latency, regulatory requirements, skills, and total cost rather than fashion.

A useful layered pattern separates raw or bronze data, validated or silver data, and curated or gold data. The names vary across organisations, but the principle is to preserve source evidence, apply controlled transformations, and publish trusted products. Lineage should show how a dashboard or model depends on upstream data.

Schemas, Metadata and Data Contracts

A schema describes fields, types, relationships, constraints, and sometimes business meaning. Metadata describes data about data: its owner, source, update frequency, sensitivity, quality measures, retention period, and lineage. Technical metadata may state that a field is an integer, while business metadata explains that it represents net revenue in a specified currency and excludes tax.

A data contract is an explicit agreement between a producer and consumer about structure, semantics, quality, delivery, and change management. Contracts are valuable in distributed organisations because they reduce unexpected pipeline failures. Schema evolution must be managed deliberately. Adding a nullable field may be backward compatible, whereas renaming or changing the meaning of an existing field may break downstream users even if the technical type remains unchanged.

Data catalogues support discovery by allowing users to search assets, definitions, owners, classifications, and quality indicators. Catalogues are useful only when maintained. Automated profiling can assist, but human ownership remains important for ambiguous business meanings.

A Practical Data-Access Workflow

A professional workflow begins with a question and a data requirement, not with a tool. The analyst first defines the required entities, measures, time period, granularity, refresh rate, and acceptable quality. The next step is source assessment: ownership, authority, availability, cost, sensitivity, and legal conditions are examined.

Access is then configured using approved connectors, credentials, network controls, and permissions. The extraction process should be reproducible and incremental where possible. Raw data is validated for schema, row counts, duplicates, missingness, ranges, referential integrity, and unexpected changes. Transformations are documented and tested. Finally, the resulting data product is published with metadata, lineage, quality status, and an owner.

A useful control is the separation of extraction, transformation, and consumption. Extraction obtains source data without silently changing it. Transformation creates validated analytical structures. Consumption provides datasets, dashboards, notebooks, or model features to authorised users. This separation makes errors easier to locate and enables reprocessing.

Practical Applications

In retail, point-of-sale transactions may be extracted from a relational system, product attributes from a document database, and weather information from an API. These sources can be joined in a warehouse to forecast demand. In healthcare, clinical and administrative systems may be integrated for service planning, but access must respect confidentiality, purpose limitation, and strict audit requirements. In manufacturing, sensor streams may enter a lake or time-series platform, while maintenance records remain in an enterprise database. In financial services, transaction data, customer profiles, and external risk indicators may support fraud detection, subject to explainability and regulatory controls.

Connector libraries and platform tools reduce repetitive engineering. Examples include database drivers, ODBC or JDBC interfaces, cloud storage connectors, API clients, SQL workbenches, notebook environments, orchestration tools, and transformation frameworks. A connector does not guarantee correctness: type conversion, time zones, null handling, encoding, and transaction boundaries still require attention.

Illustrative Case Study: Integrating Data for Delivery Forecasting

Background

A fictional grocery-delivery company wants to predict late deliveries and improve staffing. Its order management system is relational, driver locations arrive through an API, store and product data are maintained in CSV files, and historical weather observations are obtained from an external service.

Situation and Problem

The operations team initially combines weekly exports manually. Analysts report different late-delivery rates because one report counts orders while another counts order lines. API extracts also miss records when pagination is handled incorrectly. The organisation needs a reliable, governed data product.

Analysis

The team defines the grain as one row per delivery order. It identifies the order identifier as the business key and documents the meaning of delivery time, promised time, cancellation status, and location. Source profiling reveals duplicate exports, inconsistent time zones, and missing driver identifiers. Weather data uses hourly observations, requiring a documented temporal matching rule.

Approach and Solution

A read-only database account extracts orders incrementally using a last-modified timestamp. API access uses cursor pagination, retry logic, rate-limit handling, and secret storage. Incoming files are retained in a raw storage area with acquisition metadata. Validation checks test uniqueness of order identifiers, permissible status values, timestamp ordering, and expected row-count changes.

A curated warehouse model contains a delivery fact table and dimensions for date, store, customer region, and driver. Weather observations are matched to the delivery location and scheduled time using a documented tolerance. Access is restricted: analysts receive de-identified customer data, while operational managers see aggregated performance. A catalogue records definitions, lineage, owners, and refresh status.

Outcome

The organisation obtains a consistent late-delivery measure and can compare performance by store, time period, weather condition, and staffing level. Failures become visible through pipeline alerts rather than being discovered in a presentation. The forecasting team can reproduce training data, while sensitive customer attributes remain controlled.

Learning Points

The principal improvement did not result from choosing a fashionable platform. It resulted from defining grain, documenting semantics, controlling access, handling incremental extraction correctly, validating inputs, and making lineage visible. Architecture and governance supported analytical usefulness rather than obstructing it.

Risks, Limitations and Common Mistakes

Common errors include treating CSV files as self-describing, assuming an API response contains all records, joining tables without checking grain, using `SELECT *` in production pipelines, ignoring time zones, overwriting raw data, and failing to distinguish null from zero. Another frequent mistake is extracting sensitive fields merely because they are available.

Performance problems arise from unfiltered queries, inefficient joins, missing indexes, repeated full loads, excessive small files, and querying operational databases during peak business periods. Reliability problems arise from undocumented manual steps, untested schema changes, absent retry policies, and pipelines that report success despite partial extraction.

No system is universally optimal. Warehouses can impose modelling and compute costs; lakes require disciplined management; APIs may be slow or unstable; NoSQL systems can complicate ad hoc analysis; and relational models may be difficult to change when requirements evolve. Decisions should therefore be evaluated against workload, consistency, latency, scale, skills, cost, and governance.

Security, Ethics and Governance

Secure data access begins with identity and access management. Least privilege, role-based access, multi-factor authentication, network segmentation, encryption in transit and at rest, secret rotation, and audit logging are standard controls. Service accounts should have defined owners and limited scopes. Credentials must not be placed in source code, shared notebooks, or unprotected configuration files.

Privacy requires more than removing names. Quasi-identifiers can enable re-identification when combined. Data minimisation, purpose limitation, retention controls, masking, tokenisation, aggregation, and controlled research environments may be required. Regulatory obligations differ by jurisdiction and sector, so professionals must consult applicable policies and laws rather than assume that public availability means unrestricted use.

Data governance assigns responsibility for ownership, definitions, quality, access, retention, and acceptable use. Ethical practice also requires attention to representativeness, consent, surveillance risks, discriminatory outcomes, and the consequences of inaccurate data. A technically successful extraction can still be professionally unacceptable.

Current Developments and Best Practices

Modern data platforms increasingly support automated schema checks, data observability, lineage capture, catalogues, policy enforcement, and infrastructure-as-code. Change-data-capture technologies can transfer database changes with lower latency than repeated full extraction. Event-driven architectures and streaming platforms support near-real-time use cases, although they introduce ordering, duplication, replay, and exactly-once-processing challenges.

AI assistants can help generate SQL, map schemas, document pipelines, and detect anomalies. Their output must be reviewed because generated queries may expose sensitive fields, misinterpret business definitions, or produce inefficient results. Retrieval and semantic layers are also being developed to help analytical and AI applications discover governed data, but trustworthy metadata remains essential.

Effective practice includes defining grain before joining, profiling every new source, preserving raw evidence, testing data contracts, using incremental extraction, parameterising queries, monitoring freshness and quality, documenting lineage, reviewing permissions regularly, and designing for failure. Data access should be reproducible, observable, secure, and understandable to someone other than its original author.

Chapter Summary

Data science depends on disciplined access to trustworthy data. Files provide portability but require explicit handling of schema and versioning. APIs provide service-based access governed by authentication, pagination, rate limits, and contracts. Relational databases offer structured modelling and SQL, while NoSQL systems support alternative access patterns and flexible or distributed data. Warehouses optimise governed analytics, lakes preserve diverse raw data, and lakehouses seek to combine flexibility with management and transactional capabilities.

Schemas, metadata, data contracts, lineage, and clearly defined grain allow analysts to interpret data correctly. A sound workflow defines requirements, assesses sources, uses secure connectors, extracts reproducibly, validates inputs, transforms transparently, and publishes governed data products. Security, privacy, ethics, and regulatory obligations are integral to data access. Current platforms increasingly automate monitoring and metadata management, but professional judgement remains indispensable.

Key Terms

Data source: A system, repository, device, or service from which data is obtained.

Schema: A description of data fields, types, relationships, and constraints.

Metadata: Information describing a dataset’s structure, meaning, origin, ownership, and use.

API: A programmatic interface through which software requests services or data.

Pagination: A method of retrieving a large API result in multiple responses.

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

Foreign key: A field that refers to a key in another table.

Relational database: A database that organises related data into tables.

NoSQL: A group of non-relational database models, including document and graph systems.

Data warehouse: A governed repository designed for integrated analytical workloads.

Data lake: Storage for large volumes of data in varied and often raw formats.

Lakehouse: An architecture combining flexible lake storage with warehouse-like management features.

Data grain: The level of detail represented by one record.

Data contract: An agreement defining a dataset’s structure, meaning, quality, and change rules.

Data lineage: The recorded path of data from origin through transformations to use.

Incremental extraction: Obtaining only new or changed records rather than reloading all data.

Least privilege: Granting only the access required for an authorised task.

Schema evolution: Changes to a dataset’s structure over time.

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

Polyglot persistence: Using different storage technologies for different workload requirements.

Review Questions

Short Answer Questions

1. Why is data grain important when joining and aggregating datasets?

2. Distinguish a data warehouse from a data lake.

3. What is the purpose of API pagination?

4. Define primary key and foreign key.

5. What does least-privilege access mean?

Long Answer Questions

1. Explain the stages of a professional data-access workflow, from requirement definition to publication.

2. Compare relational and NoSQL systems, including suitable use cases and design concerns.

3. Discuss the roles of schemas, metadata, data contracts, and lineage in reliable analytics.

4. Explain how warehouses, lakes, and lakehouses differ in structure, governance, and analytical use.

5. Describe the principal security, privacy, and governance controls required for organisational data access.

Analytical / Application Questions

1. A team joins an order-line table to a customer table and reports twice the expected revenue. Diagnose the likely causes and propose validation steps.

2. Design an extraction strategy for an API that returns 500 records per request, imposes rate limits, and provides a last-updated timestamp.

3. An organisation wants to store sensor data, financial transactions, and customer documents in one platform. Recommend an architecture and justify your choice using workload, governance, and access considerations.

22. Multiple Choice Questions with Answers

Question 1

A sales table contains one row for each order line. What is the main risk of joining it directly to a table containing one row per order and then summing order-level amounts?

A. The query will always fail because the tables have different column names

B. Order-level amounts may be duplicated for every line, causing overcounting

C. The database will automatically remove all duplicate values

D. The join will convert all numeric values into text

Correct Answer: B. Order-level amounts may be duplicated for every line, causing overcounting

Question 2

Which approach is most appropriate for reliably retrieving all records from a paginated API?

A. Read only the first response because it represents the complete dataset

B. Repeatedly request pages or cursors until the service indicates that no more records exist

C. Increase the number of columns requested in the first response

D. Disable authentication after the first request

Correct Answer: B. Repeatedly request pages or cursors until the service indicates that no more records exist

Question 3

Which characteristic most clearly distinguishes a data warehouse from a raw data lake?

A. A warehouse is generally designed for governed, integrated analytical querying

B. A warehouse can store only image files

C. A lake cannot contain structured data

D. A warehouse never contains historical data

Correct Answer: A. A warehouse is generally designed for governed, integrated analytical querying

Question 4

What is the principal purpose of a primary key in a relational table?

A. To encrypt every value in the table

B. To identify each row uniquely

C. To determine the physical location of a database server

D. To permit unrestricted access to the table

Correct Answer: B. To identify each row uniquely

Question 5

An API pipeline fails temporarily because the service is rate-limiting requests. Which response is most appropriate?

A. Retry immediately without delay indefinitely

B. Use controlled retries with backoff and respect the service limits

C. Delete all previously retrieved records

D. Store the credentials in the application log

Correct Answer: B. Use controlled retries with backoff and respect the service limits

Question 6

Which design best reflects the principle of least privilege?

A. Give every analyst administrator access to production systems

B. Provide an analyst read-only access to the approved views needed for the analysis

C. Share one unrestricted password across the team

D. Allow access to all sensitive fields because they might be useful later

Correct Answer: B. Provide an analyst read-only access to the approved views needed for the analysis

Question 7

What problem does a data contract primarily address?

A. It guarantees that a dataset contains no statistical bias

B. It defines expected structure, meaning, quality, and change arrangements between producers and consumers

C. It replaces the need for authentication

D. It converts every NoSQL database into a relational database

Correct Answer: B. It defines expected structure, meaning, quality, and change arrangements between producers and consumers

Question 8

Which storage choice is generally most suitable for preserving diverse raw files for later exploration and machine learning?

A. A data lake with appropriate cataloguing and governance

B. A highly restricted dimension table only

C. A primary-key constraint without data storage

D. A single API response held only in memory

Correct Answer: A. A data lake with appropriate cataloguing and governance

Question 9

Why should raw source data usually be retained separately from transformed data?

A. To make reproduction, auditing, and reprocessing possible

B. To prevent all future schema changes

C. To eliminate the need for data validation

D. To ensure that every query is slower

Correct Answer: A. To make reproduction, auditing, and reprocessing possible

Question 10

A team wants to use an AI assistant to generate SQL against sensitive customer data. What is the most responsible practice?

A. Allow unrestricted access because generated code is always safe

B. Review generated queries, restrict permissions, and prevent unnecessary exposure of sensitive fields

C. Disable all audit logging to improve speed

D. Copy production credentials into the assistant’s prompt

Correct Answer: B. Review generated queries, restrict permissions, and prevent unnecessary exposure of sensitive fields