Data sources: databases, APIs and website data

Why this matters

An analysis is only as good as the data it starts from, and analysts rarely own that data. It sits in a database another team runs, behind an API a vendor controls, or on a website published for people rather than programs. Each source has its own way of being accessed, its own limits, and its own ways of quietly giving you less than you asked for.

The exam expects you to identify the right source for a need and to recognise the practical problems of pulling data from each. This lesson covers the sources that are systems: databases, APIs and websites. The next lesson covers files, logs and repositories.

The lesson

Querying a database you do not own, read-only

The most direct source of business data is the operational database behind an application. Analysts are usually given read-only access to it, or better, to a copy, and there are good reasons for both.

Read-only access protects the data: an analyst's account that cannot UPDATE or DELETE cannot damage the system of record by mistake. Querying a copy, such as a read replica or a nightly extract, protects the system's performance: a large summarising query against a busy transactional database can slow it for every customer using the application at that moment.

Two further habits apply when the database belongs to someone else. First, learn its meaning before trusting its structure. Column names are often historical: a column called status may hold codes that only the application team can explain, and a table called orders may include cancelled and test orders. The owners' documentation, or a conversation with them, is part of the data source. Second, record exactly what you queried and when, because a live database changes underneath you and the same query run tomorrow may return different numbers.

REST APIs: endpoints, parameters, authentication and rate limits

An API (application programming interface) lets one program request data from another. Most data APIs today are REST APIs: you send an HTTP request to a URL, called an endpoint, and receive a response, usually in JSON.

• The endpoint identifies what you want, such as /customers or /orders/12345.
• Parameters narrow the request, for example a date range or a region. They are usually added to the URL as a query string.
• The method says what the request does. Data collection almost always uses GET, which reads without changing anything.
• Authentication proves who you are. Common forms are an API key sent with each request and a token obtained through a sign-in process such as OAuth. Keys and tokens are credentials: they belong in a protected configuration, never in a shared script or a spreadsheet.
• Status codes report the outcome: 200 means success, 401 and 403 mean an authentication or permission problem, 404 means the resource does not exist, and 429 means you have sent too many requests.

That last code reflects rate limits: most APIs cap how many requests a client may make per minute or per day. A collection script has to respect the limit, typically by pausing between requests and retrying after a 429 rather than failing.

Pagination, and the data an API silently leaves out

APIs rarely return everything in one response. A request for all orders might return the first 100, with a marker saying how to fetch the next 100. This is pagination, and it is the most common reason an API extract is incomplete. A script that reads only the first page will produce a data set that looks complete, has the right columns and plausible values, and is missing most of its rows.

Pagination comes in several styles: a page number, an offset and limit, or a cursor that points to where the next page starts. Whichever is used, the check is the same: keep requesting until the API says there is nothing more, and compare your final count with any total the API reports.

APIs can omit data in other ways too. Some return only recent records unless a date range is given. Some leave out fields the caller's account is not allowed to see. Some sample large results rather than returning them all. Reading the API's documentation for these behaviours is part of using it correctly.

Website data: published downloads first, scraping last, and the terms that govern it

Much useful data is published on websites: statistics, prices, directories, public records. There are three ways to collect it, in order of preference.

The first is a published download or API. Many organisations that show data on web pages also offer it as a CSV file or through an API, and that version is more complete, more stable and explicitly permitted.

The second is a manual export from the site's own tools, such as a download button on a table.

The last is web scraping: a program that requests pages and extracts data from their HTML. Scraping is fragile, because any change to the page layout breaks the extraction, often without an error. It also raises questions that must be answered before, not after. The site's terms of service may forbid automated collection. Its robots.txt file states which parts automated clients should avoid. Personal data collected by scraping is still personal data, with the legal obligations covered in domain 5. And a scraper that sends requests too quickly can overload a site. Where scraping is appropriate, it should identify itself, go slowly, and collect only what is needed.

Recording where every data set came from and when

Every data set you use should carry a short record of its provenance: the source system, database or URL; the query, endpoint and parameters used; the date and time it was collected; who collected it; and any known limitations, such as "first 1,000 results only" or "excludes cancelled orders".

This record answers the question every analysis eventually faces: where did this number come from? It lets someone else, or you in six months, repeat the collection and get comparable data. It also explains differences: two reports built from the same source a week apart may disagree simply because the source changed in between. Provenance is the first link in data lineage, which domain 5 covers in full.