esc
  • WorkflowsPages
  • Case studiesPages
  • Machine Learning FoundationsLearn
  • AI EngineeringLearn
  • AI Guardrails & Red TeamingLearn
  • UI/UXLearn
  • Design SystemsLearn
  • Production System DesignLearn
  • Book a callContact
  • Download ResumeContact
Let’s talk

I’ll build the agent for it.

Machine Learning Foundations / Beginner / 01.41

Databases: SQL and NoSQL

Most company data lives in a database. Learn how tables, keys and SQL work, what NoSQL adds, and how to pull a clean table for a model without falling into the usual traps.

14 min readFree

What a database is, and why a model team cares

In the last lesson we met databases as one data source among many. Now we open the box. Picture a school office with a wall of filing cabinets. Each drawer holds one kind of record: students, classes, marks. A clerk knows exactly where to look, and the rules say nobody may file a mark for a student who does not exist. A database is that office, run by software.

IBM describes a relational database as 'a type of database that organizes data into rows and columns, which collectively form a table where the data points are related to each other.' So when a team says 'we have the data', they often mean 'it is in the database', and your first job is to get it out in a shape a model can learn from.

Key words

Table
A grid of data about one kind of thing, such as customers. Think of one spreadsheet sheet.
Row
One item in a table, such as one customer. In machine learning a row is usually one example.
Column
One kind of fact about every item, such as a city. In machine learning a column is usually a feature or the label.
SQL
Short for structured query language: the language used to ask a relational database for data. A query is one question.
Primary key
A column whose value names exactly one row, like a student number. PostgreSQL's manual says it is 'a unique identifier for rows in the table' and that its values must be 'both unique and not null'.
Foreign key
A column that points to a row in another table, like an order that carries the number of the customer who made it. PostgreSQL says the values 'must match the values appearing in some row of another table'.
NULL
The database's way of saying 'no value here'. It is not zero and not an empty word. It is a blank.

Relational databases and SQL

Why split data into several tables at all? Because it avoids copying. IBM explains that a customer table 'should only log unique records of customer information versus duplicating this information for multiple transactions.' Her address lives once in a customers table, and each order only carries her customer number. Data is 'typically structured across multiple tables, which can be joined together via a primary key or a foreign key', IBM adds.

The cost is that to see a whole picture you must put the pieces back together. That is what SQL is for. Python ships with a small relational database engine inside it, called SQLite, and its docs say it is 'a lightweight disk-based database that doesn’t require a separate server process'. You can pass the text ":memory:" to it to 'create an SQLite database existing only in memory', so nothing is left on your computer afterwards. That makes it perfect for learning.

Our shop has two tables. customers has an id (the primary key), a name and a city. orders has its own id, a customer_id (the foreign key that points back to a customer), the date it was placed and the total. Chloe has not ordered yet, and her city is blank on purpose.

Asking questions with SELECT and WHERE

The block below builds the database, then asks the simplest kind of question. SELECT says which columns you want, FROM says which table, and WHERE keeps only the rows that pass a test. Here we want the name and city of customers whose id is 2 or less. Each row comes back as a Python tuple, a fixed little list in round brackets. Later blocks continue this same script, so db stays open.

Python
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    placed_on TEXT,
    total REAL
);
INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Ben', 'Leeds'), (3, 'Chloe', NULL);
INSERT INTO orders VALUES
    (1, 1, '2026-01-05', 40.0),
    (2, 1, '2026-02-10', 25.5),
    (3, 2, '2026-01-20', 60.0),
    (4, 1, '2026-03-01', 30.0);
""")
for row in db.execute("SELECT name, city FROM customers WHERE id <= 2"):
    print(row)

Output

('Asha', 'Pune')
('Ben', 'Leeds')

Putting tables back together with JOIN

A join glues two tables side by side. PostgreSQL's tutorial says joins 'combine rows from one table with rows from a second table, with an expression specifying which rows are to be paired.' Our expression is: the order's customer_id equals the customer's id. SQLite's docs explain the idea underneath: every join starts from the 'cartesian product', which means every row of one table paired with every row of the other, and the ON test then keeps only the rows for which the expression 'evaluates to true'.

Python
query = """
SELECT customers.name, orders.placed_on, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id
"""
for row in db.execute(query):
    print(row)

Output

('Asha', '2026-01-05', 40.0)
('Asha', '2026-02-10', 25.5)
('Ben', '2026-01-20', 60.0)
('Asha', '2026-03-01', 30.0)

Each order now carries the customer's name. Asha shows up three times, once per order. That is right for a list of orders, and it will matter soon. This plain (inner) join drops rows with no partner: PostgreSQL says it 'ignores the unmatched rows', so Chloe, who has no orders, is missing. A left outer join keeps her: 'empty (null) values are substituted for the right-table columns' when nothing matches.

Summing up with GROUP BY

Models rarely want one row per order. They want one row per customer, with numbers that summarize the orders. For that SQL has aggregate functions. PostgreSQL defines them: 'An aggregate function computes a single result from multiple input rows', and it lists count, sum, avg (average), max and min. GROUP BY says which rows to bundle together before the function runs. Here we bundle by customer.

Python
query = """
SELECT customers.name, count(orders.id), sum(orders.total)
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.id
"""
for row in db.execute(query):
    print(row)

Output

('Asha', 3, 95.5)
('Ben', 1, 60.0)
('Chloe', 0, None)

We used a left join, so Chloe is still here. Her count is 0, and her total is None, which is how Python shows a database NULL. WHERE works on single rows before the bundling, and a related word, HAVING, works on the bundles after it: PostgreSQL says 'WHERE selects input rows before groups and aggregates are computed', whereas 'HAVING selects group rows after groups and aggregates are computed.'

NoSQL: when the table is the wrong shape

Not all data fits neatly in tables. Think of a customer profile on a music app. One person has a favourite playlist, another has three linked accounts, a third has neither. Forcing every profile into the same columns leaves many blanks. NoSQL databases were built for cases like this.

IBM says NoSQL, 'also referred to as "not only SQL" or "non-SQL"', is 'an approach to database design that enables the storage and querying of data outside the traditional structures found in relational databases.' It adds that because this design 'does not require a schema', it offers 'rapid scalability to manage large and typically unstructured data sets'. A schema is the plan that fixes in advance which tables and columns exist.

IBM describes four main families. Here they are, with the daily-life picture for each.

Key words

Key-value
IBM calls it 'typically considered the simplest form of NoSQL databases': a dictionary of keys and values, such as a shopping cart ID as the key and the items in that cart as the value. It is commonly used for caching and for user session information. IBM warns it is 'not ideal when you need to pull multiple records at a time'. Redis and Memcached are examples. Picture a coat check: hand over a ticket, get one coat.
Document
Data is stored as documents, 'typically stored in JSON, XML, or BSON formats'. IBM says schemas 'do not need to match across documents', and names content management systems and user profiles as common uses. MongoDB is an example. Picture a folder per person, where each folder can hold different papers.
Wide-column
Information is stored in columns, so users can 'access only the specific columns they need'. IBM says it is a more complex system to manage and is 'not recommended for use for newer teams and projects'. Apache Cassandra and HBase are examples. Picture a huge ledger where you read down just one column.
Graph
Data is stored 'as nodes, edges and properties'. Any object, place or person can be a node, and an edge defines the relationship between two nodes. Neo4j is an example. Picture a map of friendships: dots for people, lines for who knows whom.

MongoDB's manual says: 'A document is the basic unit of data in MongoDB.' Documents are 'composed of field-and-value pairs', and a value can be 'other documents, arrays, and arrays of documents'. The _id field is 'reserved for use as a primary key', so even here every record has a name tag.

Here are two stand-in documents as plain Python dictionaries, with different fields. A model needs the same columns for every row, so we flatten them by choosing a default for each missing field.

Python
profiles = [
    {"_id": 1, "name": "Asha", "likes": ["tea", "maps"]},
    {"_id": 2, "name": "Ben", "plan": "free"},
]
for p in profiles:
    print(p["_id"], p["name"], p.get("plan", "none"), len(p.get("likes", [])))

Output

1 Asha none 2
2 Ben free 0

Quick check

A shop wants to store each visitor's shopping cart under the visitor's session ID and fetch it very fast. Which family does IBM describe as a good fit?

Pulling data out for a model

A model cannot read a database directly. It needs one flat table: one row per example, one column per feature, plus the label. So the job is always the same: decide what one row means, then write a query that produces exactly that. Let us walk through it with the shop. The goal is made up for this lesson: predict whether a customer will buy again.

  1. 1

    1. Decide what one row is

    We want one row per customer. That choice matters more than any other, because every later step must keep it true.

  2. 2

    2. Pick a cutoff date

    A prediction is made at one moment in time, for example 15 February 2026. Before it is the past. On or after it is the future.

  3. 3

    3. Build features from the past only

    Count each customer's orders that were placed before the cutoff. Use a left join so customers with no orders still appear, with a count of 0.

  4. 4

    4. Build the label from the future

    The label is whether the customer placed an order on or after the cutoff. This is the answer the model will try to predict.

  5. 5

    5. Check one row per customer

    Count the rows. If the table has more rows than customers, a join has multiplied them, and something is wrong.

Steps 2 to 4 look like this. The question mark in the query is a placeholder. The Python docs say: 'Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks.' It is a good habit even in a toy script.

Python
cutoff = "2026-02-15"
features = db.execute(
    """
    SELECT customers.id, customers.name, count(orders.id)
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id AND orders.placed_on < ?
    GROUP BY customers.id
    """,
    (cutoff,),
).fetchall()
bought_after = {
    r[0]
    for r in db.execute("SELECT customer_id FROM orders WHERE placed_on >= ?", (cutoff,))
}
for cid, name, orders_before in features:
    print(name, "orders before cutoff:", orders_before, "| bought after:", cid in bought_after)

Output

Asha orders before cutoff: 2 | bought after: True
Ben orders before cutoff: 1 | bought after: False
Chloe orders before cutoff: 0 | bought after: False

Only Asha bought after the cutoff. She has three orders in total, but the model sees only the two before it, because that is all anyone would know on 15 February.

Three traps in database data

Database data looks tidy, so people trust it too fast. These three traps cause most of the damage, and none of them throws an error.

  • Joins that multiply rows

    A join pairs every matching row, so a customer with three orders becomes three rows. Count rows before and after every join.

  • Rows from the future

    A feature that uses information from after the prediction moment makes the model look brilliant in testing and useless in real life.

  • Blank (NULL) values

    A blank is neither zero nor false. It needs a decision from you, and the database will not make it for you.

Trap 1: rows that multiply

Remember that Asha appeared three times in the join. That is fine for a list of orders, but it is a disaster if your model is supposed to have one row per customer. Asha would be counted three times, and she would pull the model's attention toward herself. If copies of Asha land on both sides of a train and test split, the test stops being a fair exam. The check in step 5 catches it.

Python
(n_customers,) = db.execute("SELECT count(*) FROM customers").fetchone()
joined = db.execute(
    "SELECT customers.name FROM customers JOIN orders ON orders.customer_id = customers.id"
).fetchall()
print("customers:", n_customers)
print("rows after join:", len(joined))
print("Asha appears:", [r[0] for r in joined].count("Asha"), "times")

Output

customers: 3
rows after join: 4
Asha appears: 3 times

Three customers became four rows, and Asha is in three of them. Chloe vanished entirely, because an inner join drops customers without orders. The fix is the pattern from the GROUP BY block: group first, so there is one row per customer, then join. Whenever your row count changes after a join, stop and find out why.

Trap 2: rows from the future

scikit-learn's documentation defines data leakage like this: 'Data leakage occurs when information that would not be available at prediction time is used when building the model.' It says this 'results in overly optimistic performance estimates', and thus 'poorer performance when the model is used on actually novel data'.

A database makes this mistake easy, because it holds everything, including the future. Imagine a feature called total_orders that counts all of Asha's orders, with no date test. On 15 February nobody knows about her March order, yet the feature includes it, so it has already peeked at the answer. That is why the cutoff date appears in the join, not just in the label. The same page advises: 'Always split the data into train and test subsets first, particularly before any preprocessing steps.'

Tip

For every column you pull, ask: 'On the day we make the prediction, would the database already hold this value?' If not, leave it out.

Trap 3: blanks

NULL behaves in surprising ways. PostgreSQL's manual says: 'Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example, 7 = NULL yields null.' So asking WHERE city = NULL finds nothing, because 'unknown' is never true. SQL has a separate test, IS NULL, for that job. SQLite's docs add two facts about counting: count(X) counts 'the number of times that X is not NULL', and avg() returns 'the average value of all non-NULL' values. So blanks quietly drop out of totals and averages.

Python
print(db.execute("SELECT count(*), count(city) FROM customers").fetchone())
print(db.execute("SELECT name FROM customers WHERE city = NULL").fetchall())
print(db.execute("SELECT name FROM customers WHERE city IS NULL").fetchall())
cities = [row[0] if row[0] is not None else "unknown" for row in db.execute("SELECT city FROM customers")]
print(cities)

Output

(3, 2)
[]
[('Chloe',)]
['Pune', 'Leeds', 'unknown']

There are 3 customers but only 2 cities. The = NULL test returns an empty list, IS NULL finds Chloe, and last we turned the blank into the word unknown. That is a decision, not a fact: a blank city could mean the customer never gave it, or the system lost it. PostgreSQL advises that 'in most database designs the majority of columns should be marked not null', which is why our name column has a NOT NULL rule.

Quick check

Your query gives 3 customers but count(city) returns 2. What is the most likely reason?

What to remember

A database is a data source with rules. Relational databases keep data in linked tables and answer SQL questions, and NoSQL databases trade fixed tables for flexible shapes such as documents, key-value pairs, wide columns or graphs. Either way, a model needs one flat table, so you choose what a row means, pick a cutoff date, join carefully and decide what to do with blanks.

Next: the internet as a data source. There the data is not in one tidy database but spread across web pages and services, so the questions about where it came from and whether we may use it come back.

Sources

How is this lesson?

Last updated on 16 November 2026

Learn with me

Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.