Data types, data structures and file formats

Why this matters

Before any analysis can start, data has to be read correctly, and "correctly" depends on three things: how the data is structured, what type each value is, and what format the file is in. Get one wrong and the errors are rarely loud. A postcode read as a number loses its leading zero. A date read as text sorts April before January. A file with commas inside its values splits one column into three.

The exam asks you to classify data, choose appropriate types, and recognise which file format suits a job. This lesson covers those three layers and the mistakes each one invites.

The lesson

Structured, semi-structured and unstructured data

Structured data fits a fixed schema of rows and columns, where every record has the same fields in the same order. A relational table and a tidy CSV file are structured. It is the easiest data to query and summarise, and most reporting runs on it.

Semi-structured data has an organising structure but not a rigid one. Each record is labelled with field names, and records can differ from each other. JSON and XML documents are the usual examples: one customer record may include a list of three addresses and another none at all. Log files are often semi-structured too, with a consistent pattern that has to be parsed out of each line. The course's survey export is semi-structured JSON, with scores nested inside each response.

Unstructured data has no predefined model: free text, emails, images, audio and video. Most of the data organisations hold is unstructured, and getting value from it means extracting structure first, for example by counting words in comments or classifying images. The survey's free-text comments are the unstructured part of an otherwise semi-structured file.

The classification matters because it decides the tools. Structured data goes straight into SQL; semi-structured data needs flattening; unstructured data needs processing such as text analysis before it can be counted at all.

Data types: integer, decimal, text, date, Boolean, and what a wrong one breaks

Every column should have one data type, and choosing it is a decision with consequences.

• Integer holds whole numbers, such as a quantity ordered.
• Decimal (or numeric, or floating point) holds numbers with fractions, such as a price. Exact decimal types are preferred for money, because floating-point types store some fractions only approximately.
• Text (string, character or varchar) holds letters, symbols and anything that should not be calculated with.
• Date and date-time types hold points in time, and let a system sort them chronologically and calculate the gap between two of them.
• Boolean holds true or false.

The common failures come from values that look like one type but are another. Identifiers that look numeric are the classic case: a phone number, account number or postcode stored as an integer loses its leading zeros and invites meaningless arithmetic, so identifiers belong in text columns even when they contain only digits. Numbers stored as text sort alphabetically, so "100" comes before "20", and they cannot be summed until converted. Dates stored as text sort by their characters, so "01/12/2025" and "02/01/2025" land in the wrong order, and different formats in the same column cannot be compared at all.

Categorical data, such as region or product category, is usually stored as text but behaves differently from free text: it has a limited set of allowed values, and a spelling variant creates a new, wrong category.

Delimited files, JSON and XML, and reading each correctly

The delimited text file is the most common format an analyst receives. A CSV (comma-separated values) file puts one record on each line and separates fields with commas; a TSV uses tabs. Delimited files are simple and universal, but they carry no types: everything is text until the reader decides otherwise. Two rules keep them readable. A field containing the delimiter must be wrapped in quotes, and the file's encoding, usually UTF-8, must be known, or accented characters turn into garbage. A header row naming the columns is not guaranteed, and a reader that assumes one will drop the first record.

JSON (JavaScript Object Notation) represents data as nested objects of name-value pairs and lists. It is the standard format of web APIs. Reading it correctly means deciding how to turn nesting into rows: the survey's scores, for example, become two columns, satisfaction and recommend.

XML (Extensible Markup Language) wraps data in named, nested tags. It is more verbose than JSON and still common in older systems, government data and document formats. Like JSON, it must be flattened for tabular analysis, and it may come with a schema file that states which elements are allowed.

Spreadsheet workbooks, such as XLSX files, are also common. They can hold several sheets, formulas and formatting, and they are a frequent source of problems: merged cells, totals typed into the data, and values that display differently from how they are stored.

Columnar and binary formats such as Parquet, and why analytics prefers them

Text formats store data row by row, which suits reading or writing one record at a time. Analytical queries usually do the opposite: they read one or two columns across millions of rows, such as summing a sales column.

Columnar formats store each column together. Parquet is the most widely used; ORC is similar. Their advantages for analysis are substantial. A query reads only the columns it needs. Values in one column are similar to each other, so they compress well, often to a fraction of the CSV size. And the file stores its own schema, so types are preserved rather than guessed.

The trade-off is that columnar files are binary: you cannot open them in a text editor, and they need a library or tool that understands the format. Avro is another binary format, row-based rather than columnar, often used for streaming data. As a rule of thumb, CSV and JSON are for exchange and for people, while Parquet is for storing analytical data at scale.

File extensions as clues, not guarantees

A file's extension (.csv, .json, .xlsx, .parquet) suggests its format, and tools use it to decide how to open the file. It is only a label. A file named .csv may use semicolons as its delimiter, as is common where the comma is the decimal separator. A .txt file may be tab-delimited data. A .json file may contain one JSON object per line rather than one large list, which is a different format known as JSON Lines. A spreadsheet renamed to .csv will not become one.

The reliable approach is to look before loading. Open the first few lines of a text file, check the delimiter, the header, the quoting and the encoding, and then tell the tool explicitly what to expect instead of trusting its guess.