LearnTopic guide

Databases & Data Modeling

How data is structured, stored, and kept consistent, and how to model it when requirements and source systems are messy.

Topic overview

Almost every AI system reads from or writes to a database, and Forward Deployed Engineers spend much of their time mapping a client's data into something clean and reliable. Good data modelling makes everything downstream easier, from retrieval to reporting. This page covers the fundamentals interviewers expect.

Core concepts

Relational model: data lives in tables of rows and columns. A primary key identifies each row; a foreign key links a row to another table, which keeps relationships explicit and consistent.

Normalization: organizing tables so each fact is stored once, which avoids update anomalies and duplicate data. The usual target is third normal form.

Denormalization: deliberately duplicating data to make reads faster or simpler, for example in reporting tables. It trades storage and write complexity for speed.

Joins: combine rows from related tables. Understanding inner, left, and anti-joins, and how join keys affect row counts, prevents the classic bug of accidentally multiplying rows.

Indexes: a separate structure, commonly a B-tree, that lets the database find rows without scanning the whole table. They speed up reads and slow down writes, and they only help if the query can use them.

Transactions and ACID: a transaction groups changes so they all succeed or all fail. ACID stands for atomicity, consistency, isolation, and durability. Isolation levels trade strictness against concurrency and decide which anomalies, such as dirty or phantom reads, can occur.

SQL vs NoSQL: relational databases suit structured data with relationships and strong consistency. Document, key-value, and wide-column stores suit flexible schemas and horizontal scale. Choose by access patterns and consistency needs, not fashion.

Vector databases: store embeddings and answer nearest-neighbour queries, which is the retrieval step in RAG. They are often an index on top of, or beside, your main database, not a replacement for it.

ETL and ELT: moving data between systems. ETL transforms before loading; ELT loads raw data first and transforms inside the warehouse. Make loads idempotent so re-running them does not create duplicates.

Modeling messy source data: client data often has missing keys, inconsistent formats, and shifting definitions. Keep a raw layer, clean into a stable schema, document assumptions, and validate data quality with explicit checks.

Why it matters

Poorly modelled data causes wrong answers that look right: duplicated customers, joins that double-count, and slow queries that time out in production. For AI work, the quality and structure of the data you retrieve limits the quality of every answer.

Interview relevance

Expect questions such as: design a schema for a described product, explain normalization and when you would break it, what an index does and when it does not help, what ACID means, how you would choose between SQL and NoSQL, and how you would integrate a client's legacy data. Forward Deployed roles add scenarios about unclear source data, changing requirements, and safely migrating data.