Chapter 01 · Data, Databases, DBMSs, and SQL
Structured, Semi-Structured, and Unstructured Data
Classify real datasets by how explicitly their structure is represented, then work with relational rows, JSON events, metadata, and extracted features.
Learning outcomes
Data categories are not academic labels. They determine how a system validates records, discovers fields, compresses files, builds indexes, evolves schemas, and makes information queryable.
Classify common datasets as structured, semi-structured, or unstructured without relying only on the file extension.
Explain schema-on-write, schema-on-read, and why every useful dataset has some form of structure.
Compare relational tables, CSV, JSON, logs, documents, images, audio, and video as storage representations.
Store and inspect a small JSON event safely inside SQLite.
A spectrum of explicit structure
The three categories describe how directly a dataset exposes a predictable model. They form a spectrum rather than three perfectly separated boxes. A photograph is unstructured for SQL, but it still has pixels, dimensions, a file format, timestamps, and possibly EXIF metadata. A JSON object is semi-structured, yet an organization may enforce a strict JSON Schema that makes it nearly as governed as a relational row.
Structured data
Values follow a stable, explicit schema such as named table columns, declared types, keys, and constraints.
Semi-structured data
Records carry labels or nesting, but fields may be optional, repeated, differently typed, or changed between records.
Unstructured data
The primary meaning is not represented as a fixed set of database fields: prose, images, audio, video, scans, and similar content.
Metadata
Structured facts about any asset—owner, timestamp, checksum, language, dimensions, source, sensitivity, and retention class.
Ask two questions: Can I predict the fields before reading the record? and Can the system enforce those fields when data is written? The answers are more informative than the file extension.
Structured data
Structured data has a model known to the system before most records are written. In a relational database, the schema names columns, chooses data types, identifies keys, and declares constraints. The DBMS can reject an impossible state before it becomes durable.
| Column | Domain or type | Example rule |
|---|---|---|
order_id | positive integer | primary key; one value per order |
customer_id | integer | must reference an existing customer |
ordered_at | timestamp | required; stored in a defined time convention |
total_amount | exact decimal | must be greater than or equal to zero |
CSV is often called structured because it is tabular, but a CSV file usually does not carry strong types, keys, relationships, or constraints. Those rules live in a separate contract, import job, application, or human convention. A relational table therefore provides stronger executable structure than a comma-separated file with the same visible columns.
A rectangular file can still contain duplicate identifiers, mixed date formats, invalid codes, missing fields, and corrupted encodings. Structure makes validation possible; it does not guarantee that validation happened.
Semi-structured data
Semi-structured formats identify fields inside each record. JSON uses object keys and arrays; XML uses elements and attributes; many logs use key-value pairs; event systems commonly use a key, timestamp, headers, and a serialized payload. Records may evolve independently, which is valuable for integration but creates governance work.
{ "event_id": "evt-1042", "event_type": "course.lesson_opened", "occurred_at": "2026-08-05T00:20:31Z", "learner": { "learner_id": 17, "country": "DE" }, "properties": { "course": "sql-database-fundamentals", "lesson": 2, "device": "desktop" }} The event is understandable without a separate column header. It also allows nesting, optional properties, and arrays. Those benefits create questions that a relational schema normally answers explicitly:
- Is
learner_idalways an integer? - May
countrybe absent or null? - Which versions of the event are valid?
- Can producers add fields without breaking consumers?
- Who owns the contract and how is compatibility tested?
Avro, Protocol Buffers, and schema registries address some of these concerns by pairing flexible records with formal schemas. JSON Schema can validate JSON documents. The broader lesson is that semi-structured does not mean “without rules”; it means the rules are not necessarily fixed as database columns at write time.
Unstructured data and extracted structure
Text documents, PDFs, emails, images, audio, and video contain rich meaning, but a conventional SQL engine cannot directly interpret that meaning as rows and columns. Systems usually combine the original object with structured metadata and derived features.
The original asset remains authoritative while derived structure makes discovery and analysis possible.
For example, an image archive may store the binary objects in object storage, relational metadata in PostgreSQL, searchable labels in OpenSearch, and vector embeddings in a specialized index. Calling the image “unstructured” does not imply that the surrounding system lacks structure.
Schema-on-write and schema-on-read
| Approach | When interpretation is enforced | Strength | Risk |
|---|---|---|---|
| Schema-on-write | Before or during ingestion | Consistent records and earlier failure | Changes require coordination and migration |
| Schema-on-read | When a consumer queries or processes data | Fast ingestion and flexibility | Every consumer may interpret the same data differently |
| Hybrid | Core fields on write; flexible payload later | Stable identity plus extensibility | Governance can split between two models |
Modern data platforms frequently use a hybrid. An event envelope may require an identifier, type, timestamp, producer, and schema version, while the payload remains flexible. A data lake may accept raw files quickly but validate them before promoting them into curated tables.
A useful pipeline therefore distinguishes zones:
- Raw: preserve the source bytes and ingestion metadata.
- Validated: reject or quarantine records that violate the contract.
- Curated: standardize names, types, units, identifiers, and business meaning.
- Serving: organize data for a specific operational, analytical, search, or machine-learning workload.
Lab: keep JSON flexible but queryable
SQLite includes JSON functions in current builds. The following table preserves each original event payload as text, checks that the payload is valid JSON, and extracts selected fields when queried.
CREATE TABLE raw_events ( event_id INTEGER PRIMARY KEY, payload TEXT NOT NULL CHECK (json_valid(payload)), received_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP); INSERT INTO raw_events (payload)VALUES ('{ "event_type": "course.lesson_opened", "learner": {"learner_id": 17}, "properties": {"lesson": 2, "device": "desktop"}}'); SELECT event_id, json_extract(payload, '$.event_type') AS event_type, json_extract(payload, '$.learner.learner_id') AS learner_id, json_extract(payload, '$.properties.device') AS deviceFROM raw_events; This pattern is useful for learning, auditing, and low-volume ingestion. It is not a substitute for designing stable columns when fields are frequently filtered, joined, constrained, or indexed. A practical design often keeps the complete source payload and also promotes important fields into typed columns.
Promote a field to a normal column when it has stable meaning and participates in integrity rules, joins, filters, grouping, security policies, or indexes. Keep genuinely variable details in the payload.
Classification examples
| Dataset | Primary category | Reason |
|---|---|---|
| Customer table with constraints | Structured | Stable columns, types, identifiers, and integrity rules |
| CSV export of that table | Structured representation | Tabular shape, but rules must be supplied separately |
| JSON API response | Semi-structured | Named and nested fields with possible variation |
| Application log with free text | Mixed | Timestamp and level are structured; message body may not be |
| Scanned contract | Unstructured content | Meaning is embedded in the image until extracted |
| Parquet dataset | Structured | Columnar file stores an explicit schema and typed values |
Common mistakes
“JSON has no schema.”
Every producer and consumer assumes some shape. The schema may be implicit, versioned in code, described by JSON Schema, or governed through an event contract.
“Unstructured data cannot be searched.”
Search engines, parsers, OCR, speech recognition, computer vision, and embeddings derive indexes and features. The original content remains unstructured relative to ordinary SQL columns.
“Put everything into one JSON column.”
This avoids early design but can weaken constraints, portability, discoverability, and performance. Flexibility should be intentional, not a substitute for modeling.
“A data lake removes the need for schemas.”
It may delay enforcement, but reliable downstream use still requires contracts, types, ownership, quality tests, and evolution rules.
Checkpoint and practice
Concept check
- Why is a CSV file weaker than a constrained relational table even when both look rectangular?
- What makes JSON semi-structured rather than completely unstructured?
- Give one reason to preserve a raw payload after promoting fields into columns.
- When is schema-on-read useful, and what risk does it introduce?
Review the answers
CSV normally lacks embedded types, keys, relationships, and constraints. JSON labels and nests fields but may vary between records. Preserving the raw payload supports audit, reprocessing, and extraction of newly important fields. Schema-on-read enables flexible ingestion but can produce inconsistent interpretations across consumers.
Hands-on exercise
- Insert three event payloads with different optional fields.
- Query only events whose
deviceisdesktop. - Attempt to insert malformed JSON and inspect the constraint error.
- Add an
event_typecolumn, populate it from existing payloads, and compare the two query styles. - Write a one-paragraph contract for the fields every event must contain.
Summary and next lesson
Structured, semi-structured, and unstructured data differ mainly in how explicitly their model is represented and enforced. Useful systems surround every category with metadata, contracts, validation, and governance. The next lesson shifts from data shape to workload shape: operational transactions, analytical queries, hybrid systems, and continuously arriving streams.