Data fundamentals

Lesson preparation and outcomes

Prerequisites: Lists, dictionaries and simple Python functions.

By the end, you can: name what one row represents before you count; choose a join and predict its row count; count people and applications separately; bind query values with placeholders; handle bad rows by a stated rule and record a number’s lineage.

Worked scenario and practice feedback

Two synthetic people have IDs a and b. Applications contains two rows for a and none for b. An inner join yields two rows for a; a left join also preserves b with missing application fields. Counting joined rows as people overcounts a. Aggregate by the intended entity.

Practice: one task, in the practice exercise below.

Data engineering supports many AI tasks. Loading it. Cleaning it. Joining it. Filtering it. Data preparation and validation often determine whether a model can do useful work; no fixed percentage applies to every system. We start with the two tools you’ll use for that: SQL and pandas.

Part 1 SQL: the 80% you'll use.

SQL is the language for talking to relational databases. Postgres, MySQL, SQLite, BigQuery, Snowflake, Redshift: all speak it. Learn it once, use it everywhere.

You need six verbs.

-- 1. SELECT — what columns to get back
SELECT name, rank, branch FROM veterans;

-- 2. WHERE — filter rows
SELECT name FROM veterans WHERE branch = 'Marines' AND years >= 4;

-- 3. ORDER BY — sort
SELECT name, years FROM veterans ORDER BY years DESC LIMIT 10;

-- 4. GROUP BY + aggregate — collapse rows
SELECT branch, COUNT(*) AS n, AVG(years) AS avg_years
FROM veterans
GROUP BY branch;

-- 5. JOIN — combine two tables
SELECT v.name, j.title
FROM veterans v
JOIN job_apps j ON v.id = j.veteran_id
WHERE j.status = 'offer';

-- 6. INSERT / UPDATE / DELETE — modify data
INSERT INTO veterans (name, branch) VALUES ('Operator A', 'Army');
UPDATE veterans SET rank = 'O-3' WHERE name = 'Operator A';
DELETE FROM veterans WHERE id = 42;

Those six verbs and three operators (=, AND, OR) cover most of what you do. The rest (subqueries, window functions, CTEs) you pick up as you need them.

One pattern worth learning by name

The JOIN is the single most useful operation in SQL. You’ll use it constantly. Mental model: you have two tables that share a column. You want one combined view.

-- "Show me every veteran and any job apps they have."
SELECT v.name, j.title, j.status
FROM veterans v
LEFT JOIN job_apps j ON v.id = j.veteran_id;

LEFT JOIN keeps every row from the left table even if it has no match on the right. INNER JOIN only keeps rows that match on both sides. RIGHT JOIN is the inverse of left and rarely used.

Count the right thing after a join

A join returns one row per match, not one row per person. Name what one row represents before you count.

SELECT COUNT(*)             AS joined_rows,
       COUNT(DISTINCT v.id) AS people,
       COUNT(j.veteran_id)  AS rows_with_an_app
FROM veterans v
LEFT JOIN job_apps j ON v.id = j.veteran_id;

COUNT(*) counts rows. COUNT(DISTINCT v.id) counts people. COUNT(j.veteran_id) skips rows where the left join found no application and filled in NULL. NULL means no value. It is not zero.

Bind values, do not build the string

Pass a value separately from the SQL text. Python’s sqlite3 uses ? as the placeholder.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE veterans (id TEXT, name TEXT, branch TEXT)")

branch = "Marines"  # could be typed by a user
rows = con.execute(
    "SELECT name FROM veterans WHERE branch = ?", (branch,)
).fetchall()

This is a parameterized query. The database receives the value as data, so a quote or semicolon inside it cannot change the query. Never join a value into the SQL with + or an f-string. Placeholders stand for values, not table or column names.

Part 2 pandas: the Python equivalent for in-memory data.

pandas provides in-memory table operations familiar from SQL. Use it when the working data and intermediate operations fit available memory. Row count alone is not a capacity rule: column count, string length, data types and temporary copies matter. Measure df.memory_usage(deep=True).sum() on a representative sample, preserve headroom, and profile the intended operations. Read selected columns, use appropriate types, or process independent chunks when needed. See the official scaling guidance; some workloads require a database or another execution engine.

import pandas as pd

# Load a CSV
df = pd.read_csv("veterans.csv")

# Look at it
df.head()           # first 5 rows
df.info()           # columns + types
df.describe()       # numeric summary
df.shape            # (rows, cols)

# Select columns
df[["name", "branch"]]

# Filter rows
df[df["branch"] == "Marines"]
df[(df["branch"] == "Marines") & (df["years"] >= 4)]

# Sort
df.sort_values("years", ascending=False).head(10)

# Group + aggregate
df.groupby("branch").agg(n=("name", "count"), avg_years=("years", "mean"))

# Join two DataFrames
merged = df.merge(jobs_df, left_on="id", right_on="veteran_id", how="left")

If you know SQL, pandas is an evening. If you don’t, pandas is harder than SQL because the syntax is denser. Either way, both repay the time.

Part 3 The file formats you'll see.

Format	Use when	Read with
CSV	Spreadsheets, hand-curated data, exports	pd.read_csv()
JSON / JSONL	API responses, structured logs	pd.read_json()
Parquet	Column-oriented analytics; compressed; choose from workload needs	pd.read_parquet()
SQL	Anything in a relational database	pd.read_sql()
Excel	When you have to. Convert to CSV if you can.	pd.read_excel()

Parquet is the format the data world uses for serious work. Smaller than CSV, faster to read, preserves types. Once you’re past prototype data, prefer parquet.

df.to_parquet("veterans.parquet")
df2 = pd.read_parquet("veterans.parquet")

Part 4 Cleaning data: the unglamorous half of the job.

Real data is messy. Names with whitespace. Dates as strings. Branch codes that should match but don’t. Two patterns worth burning in:

Strip and standardize

df["branch"] = df["branch"].str.strip().str.lower()
df["name"] = df["name"].str.title()

Parse dates

df["enlisted_at"] = pd.to_datetime(df["enlisted_at"], errors="coerce")
df["years"] = (pd.Timestamp.now() - df["enlisted_at"]).dt.days / 365

errors="coerce" turns a value that is not a real date, such as 2026-02-30, into NaT. Set those rows aside. Do not guess the day. SQLite’s date() does not reject it: it returns 2026-03-02 with no warning.

Handle missing values

df["branch"] = df["branch"].fillna("Unknown")   # assign back; inplace is deprecated under pandas' copy-on-write
df = df.dropna(subset=["enlisted_at"])           # dropna returns a new frame — assign it, don't discard it

Whitespace, types and null handling are useful initial diagnostic checks. If a query is returning nothing, check whitespace, types, and nulls first.

Record the lineage

Lineage is the trail from a number back to its source. Write down which file, which filter and which join produced it, so someone else can rerun it and get the same number. Keep the original file unchanged. Clean in a query or a copy.

Interface/reference limits

Use the workflow reference to distinguish model capability, deployment and harness controls. Provider snippets in this lesson are illustrative, have not been executed against an actual provider in this revision, and require current SDK/model capability verification before separately authorized use.