Data sources: files, logs and repositories

Why this matters

Not every data source is a system you can query. A great deal of analysis starts from a file someone emailed, a log a server has been writing for months, or a data set downloaded from a public portal. These sources are easy to obtain and easy to misjudge: a file arrives looking finished, with none of the context that would tell you what is missing from it.

The exam asks you to identify sources by type and to judge whether a source is fit for a purpose. This lesson covers files, logs and repositories, the difference between primary and secondary data, and a short set of questions that decide whether to trust a source at all.

The lesson

Spreadsheets and flat files as sources, and their typical faults

A flat file is a single table in a file, usually delimited text such as CSV. A spreadsheet is a workbook of sheets that may contain data, formulas, charts and notes together. Both are the most common way data moves between people, and both arrive with predictable faults.

Spreadsheets built for people to read are often laid out for the eye rather than for analysis: a title in the first rows, merged cells across headings, blank rows between sections, and subtotal rows mixed in with the data. Loading one naively counts the subtotals as records and doubles the totals. Values may display differently from how they are stored, such as a number formatted as a percentage or a long number shown in scientific notation. Spreadsheet software may also have converted values on import: long numeric identifiers lose precision, and some text that resembles a date is silently turned into one.

Flat files have their own common faults: an unknown delimiter or encoding, a missing header row, quoted fields containing commas or line breaks, and manually edited rows where someone fixed a value by hand with no record of what it was. The raw customer file in this course's practice data has several of these faults, including padded names and dates in two formats.

The safe way to use any file is to treat it as a snapshot from an unknown process: inspect it before loading, keep the original untouched, and write down what it appears to contain and what it does not.

Log files: timestamps, levels and fields, and parsing them into rows

A log is a record a system writes as it runs: one line per event, in time order. Web servers log every request, applications log errors and warnings, and operating systems log sign-ins and service changes. Logs are among the richest sources of behavioural data, and they are semi-structured: each line follows a pattern, but the pattern has to be parsed before the data becomes columns.

Most log lines contain some common parts:

• a timestamp, often with a time zone, which must be parsed as a date-time rather than left as text;
• a severity level in application logs, such as DEBUG, INFO, WARN, ERROR;
• a source, such as the host, service or client address;
• the event itself, sometimes with structured fields and sometimes as free text.

The course's web server log uses the widely used Common Log Format: client address, timestamp, the request line in quotes, the HTTP status code and the response size. Parsing it means splitting each line into those fields, for example with a regular expression, which a later lesson covers. Once parsed, a log answers questions such as how many requests failed with a 500 error per hour, or which pages are requested most.

Logs bring their own traps. Log rotation splits one period across several files, so an analysis of one file can miss data. Time zones differ between systems. And logging levels change what is recorded: a system logging only errors says nothing about how often things went right.

Data repositories, open data portals and data marketplaces

A data repository is a managed store of data sets for others to find and use. Inside organisations, repositories include shared data folders, data catalogues and the warehouse itself. Outside, three kinds matter.

Open data portals are run by governments and public bodies to publish data such as census figures, transport statistics and public spending. The data is usually free and licensed for reuse, and often well documented.

Research and public data repositories hold data sets published alongside studies or collected for public use, often with a citation and a licence.

Data marketplaces and vendors sell or license data, such as demographic, financial or location data, to enrich an organisation's own.

Whatever the repository, the data set's licence governs what you may do with it: some permit any use, some require attribution, and some forbid commercial use. Its documentation, sometimes called a data dictionary or metadata, tells you what each field means and how the data was collected, and using a data set without reading it is a common source of misinterpretation.

Primary against secondary data, and internal against external sources

Primary data is collected first-hand for the purpose at hand: a survey you design, an experiment you run, an interview you conduct. You control how it is collected and know its limitations, but it costs time and money to gather.

Secondary data was collected by someone else for another purpose and is reused: a government statistic, a vendor's data set, another department's report. It is cheaper and often larger, but it may not measure exactly what you need, and its definitions and collection methods are someone else's choices.

A separate distinction is internal against external. Internal data comes from the organisation's own systems: sales, customers, operations. External data comes from outside: market data, public statistics, weather, social media. Much valuable analysis combines the two, such as internal sales joined to external weather data to explain seasonal demand, and the join between them is only as good as the shared field they are matched on.

Judging a source: freshness, completeness and who maintains it

Before relying on any source, ask a short set of questions.

• Freshness: how recent is it, and how often is it updated? Last year's price list is a poor basis for this month's margins.
• Completeness: does it cover the whole population and period you need, or only part of it? A file of "all customers" exported with a filter still applied is a partial file with a misleading name.
• Accuracy and method: how was it collected, and what could have gone wrong? A survey of volunteers measures volunteers.
• Maintenance: who owns it, and will it still exist and mean the same thing next quarter? A spreadsheet maintained by one person who has left is a risk.
• Consistency: do its definitions match the other sources you will combine it with? Two sources may both have a "customer" count and mean different things by it.

The answers do not have to be perfect for a source to be useful. They have to be known, and written into the provenance record, so the limits of the source become the stated limits of the analysis.