Phase 1: Foundations
Python, Git & SQL

You are not expected to memorize syntax. You are expected to type every example yourself, run it, and then break it on purpose to see what the error looks like. That last step matters more than people think — a data engineer's real job is reading error messages calmly and figuring out what they mean.

Every code example, every SQL query, and every mini-project in this book — across all three phases — uses the same small dataset: a fictional online store called BrightCart. Real data engineering work is never a random toy example per lesson; you follow one system end to end. This book does the same thing on purpose.

BrightCart sells electronics, home goods, apparel, and books. Its data has exactly the shape you'll meet in a real job: customers who sign up, products with prices, orders that get placed, and order line items — plus some orders that are still pending, cancelled, or refunded, because real businesses aren't tidy.

These files are provided alongside this book. Save them into one project folder — you'll load the .sql files into PostgreSQL in Chapter 5, and read the .csv files with Python starting in Chapter 2.

A carpenter doesn't start a project by learning to swing a hammer mid-job — they set up the workbench first. Data engineers do the same. Before you write a line of Python or SQL, you need a place to write it (an editor), a way to run it (an interpreter), a way to talk to your computer directly (a terminal), and a way to save your progress so nothing is ever lost (version control).

Git moves your work through three places. Understanding this diagram now will save you real confusion later — almost every Git error a beginner hits comes from not knowing which of these three states their file is in.

git init                        # start tracking this folder
git status                      # what changed since the last commit?
git add .                       # stage everything that changed
git commit -m "first commit"    # save a checkpoint
git push                        # upload to GitHub

A variable is a labeled box in your computer's memory. You put a value inside it, and you use the label to find that value again later. In Python you create one with a single equals sign — there's no need to declare a type up front, Python figures that out for you (this is called dynamic typing).

customer_id = "C001"
customer_name = "Ana Dela Cruz"
order_total = 792.34
is_paid = True

print(customer_name)   # Output: Ana Dela Cruz

Python is strict about what a variable name can look like, and the community is strict about what it *should* look like. Both matter — one crashes your program, the other makes your code readable to the next engineer (often future-you).

Every value in Python has a type, and the type decides what you're allowed to do with it — you can add two numbers, but adding a number to text will raise an error. These four types cover the vast majority of what you'll touch in this bootcamp.

order_id = 1001            # int
customer_city = "Manila"   # str
unit_price = 24.99         # float
is_paid = True              # bool

print(type(unit_price))    # <class 'float'>

This trips up nearly every beginner exactly once: Python's input() function always returns a string, even if the person types a number. If you try to do math on it directly, Python either crashes or does something you didn't expect.

qty = input("Quantity: ")   # user types: 3
total = qty * 10
# qty is the STRING "3", not the number 3
# Python repeats the text instead of doing math:
print(total)   # "333333333333333333333333333333"

qty = int(input("Quantity: "))   # convert to int immediately
total = qty * 10
print(total)   # 30

Operators are the symbols that combine values — arithmetic for math, comparisons for true/false questions, and logical operators for combining multiple conditions.

quantity = 2
unit_price = 24.99
line_total = quantity * unit_price
print(line_total)          # 49.98

is_big_order = line_total > 40 and quantity >= 2
print(is_big_order)        # True

Text data from the real world is almost never clean — extra spaces, inconsistent capitalization, stray characters. String methods are the tools you'll reach for constantly, both here and later when cleaning BrightCart's messier exports in Phase 2.

city = "  manila  "

print(city.strip())        # "manila"        (removes leading/trailing spaces)
print(city.strip().title())# "Manila"        (capitalizes each word)
print(city.upper())        # "  MANILA  "
print(city.lower())        # "  manila  "

name = "Ana" + " " + "Dela Cruz"
print(name)                 # "Ana Dela Cruz"   (+ joins/concatenates strings)

email = "ana.delacruz@brightmail.com"
print(email.split("@"))    # ['ana.delacruz', 'brightmail.com']

# f-strings: the modern way to build text with variables inside
print(f"{name} spent ${line_total}")

Conditionals let your code make decisions. Python checks each condition top to bottom and runs the first block that evaluates to True — everything else is skipped.

status = "paid"
amount = 792.34

if status == "cancelled":
    print("No action needed")
elif status == "paid" and amount >= 500:
    print("VIP order — flag for priority shipping")
elif status == "paid":
    print("Standard paid order")
else:
    print("Needs follow-up: " + status)

A for loop repeats code once per item in a collection — the workhorse of data engineering, since almost everything you process is a list of records. A while loop repeats until a condition becomes False, useful when you don't know in advance how many times you'll need to run.

order_amounts = [792.34, 59.99, 218.00, 38.97]

total = 0
for amount in order_amounts:
    total = total + amount

print(f"Total revenue: {total}")   # Total revenue: 1109.3

page = 1
has_more_pages = True

while has_more_pages:
    print(f"Fetching page {page}...")
    if page == 3:              # pretend the API told us this was the last page
        has_more_pages = False
    page += 1                  # same as: page = page + 1

A list holds multiple values in one variable, in order, accessed by position (starting at 0, not 1 — this catches everyone once). Lists are the closest thing Python has to a spreadsheet column, and you'll use them constantly before you meet pandas in Phase 2.

cities = ["Manila", "Cebu City", "Davao City", "Singapore"]

print(cities[0])          # "Manila"    <- index 0 is the FIRST item
print(cities[-1])         # "Singapore" <- -1 is the LAST item
print(len(cities))        # 4

cities.append("Tokyo")    # add to the end
print(cities)             # ['Manila', 'Cebu City', 'Davao City', 'Singapore', 'Tokyo']

philippine_cities = [c for c in cities if c != "Singapore" and c != "Tokyo"]
print(philippine_cities)  # ['Manila', 'Cebu City', 'Davao City']

A dictionary stores key–value pairs instead of positions — this is how you'll represent one row of a table before pandas does it for you automatically. If a list is a column, a dictionary is closer to a single record.

order = {
    "order_id": 1001,
    "customer": "Ana Dela Cruz",
    "amount": 792.34,
    "status": "paid"
}

print(order["customer"])       # "Ana Dela Cruz"
print(order.get("status"))     # "paid"
print(order.get("discount", 0))  # 0  <- .get() with a default avoids a crash if the key is missing

order["shipped"] = True        # add a new key
print(order.keys())            # dict_keys(['order_id', 'customer', 'amount', 'status', 'shipped'])

A list of dictionaries is exactly how you'll represent a whole table — and it's exactly the shape a JSON API response usually arrives in, which is why this pattern matters so much.

orders = [
    {"order_id": 1001, "customer": "Ana Dela Cruz", "amount": 792.34, "status": "paid"},
    {"order_id": 1002, "customer": "Ben Santos",    "amount": 59.99,  "status": "paid"},
    {"order_id": 1004, "customer": "Carlo Reyes",   "amount": 38.97,  "status": "pending"},
]

paid_total = 0
for o in orders:
    if o["status"] == "paid":
        paid_total += o["amount"]

print(f"Paid revenue: {paid_total}")   # Paid revenue: 852.33

Two more collection types worth recognizing, even if you'll reach for them less often than lists and dictionaries.

payment_methods = ["gcash", "credit_card", "gcash", "paypal", "gcash"]

unique_methods = set(payment_methods)
print(unique_methods)     # {'gcash', 'credit_card', 'paypal'}  -- duplicates gone

A function is a named, reusable block of code. Instead of retyping the same logic for every order, you write it once, give it a name, and call it as many times as you need with different inputs (called parameters or arguments).

def calculate_line_total(quantity, unit_price):
    return quantity * unit_price

# call it as many times as you need:
print(calculate_line_total(2, 24.99))   # 49.98
print(calculate_line_total(1, 129.00))  # 129.0

def classify_order(amount, threshold=500):
    """threshold has a DEFAULT value -- callers can override it or skip it"""
    if amount >= threshold:
        return "VIP"
    return "standard"

print(classify_order(792.34))         # "VIP"       (uses default threshold=500)
print(classify_order(300, 250))       # "VIP"       (override: threshold=250)

Real data is unpredictable — a field might be blank, a file might not exist, an API might time out. try / except lets your program catch an error and respond sensibly instead of crashing.

raw_quantities = ["2", "3", "N/A", "1"]

total_units = 0
for q in raw_quantities:
    try:
        total_units += int(q)
    except ValueError:
        print(f"Skipping bad value: {q!r}")

print(total_units)   # 6  (the 'N/A' was skipped, not crashed on)

Before pandas (Phase 2) makes this effortless, it's worth understanding what's actually happening: a CSV file is plain text with commas separating values, and Python's built-in csv module turns each line into a list — or, with DictReader, into a dictionary using the header row as keys.

import csv

with open("data/customers.csv", "r") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row["first_name"], row["city"])

# Ana Manila
# Ben Cebu City
# Carlo Davao City
# ...

import csv

vip_customers = [
    {"customer_id": 1, "customer_name": "Ana Dela Cruz", "total_spent": 792.34},
    {"customer_id": 12, "customer_name": "Liam Brooks", "total_spent": 476.00},
]

with open("data/vip_customers.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["customer_id", "customer_name", "total_spent"])
    writer.writeheader()
    writer.writerows(vip_customers)

JSON (JavaScript Object Notation) is the format almost every API speaks — and it maps directly onto Python dictionaries and lists, which is exactly why you learned those first. An API is just a website's URL that returns data instead of a webpage.

{
  "order_id": 1001,
  "customer": "Ana Dela Cruz",
  "items": [
    {"product": "Wireless Mouse", "quantity": 2},
    {"product": "Water Bottle", "quantity": 1}
  ],
  "status": "paid"
}

import json

with open("data/order_1001.json") as f:
    order = json.load(f)     # JSON text -> Python dict

print(order["customer"])            # "Ana Dela Cruz"
for item in order["items"]:
    print(item["product"], item["quantity"])

import requests

response = requests.get("https://api.example.com/orders", timeout=30)
response.raise_for_status()   # crash loudly if the request failed, instead of silently continuing

data = response.json()        # parses JSON straight into a Python dict/list
print(data[0]["customer"])

A relational database stores data in tables — grids of rows and columns, like a spreadsheet, except tables can be linked together by shared ID columns (that's the "relational" part). PostgreSQL, MySQL, and SQLite are all RDBMS (Relational Database Management Systems) — programs that store and let you query this kind of data.

Python variables live only while your script runs. A database persists — BrightCart's orders stay there whether your laptop is on or off, and multiple programs (or people) can read and write to it at the same time. That durability and shareability is exactly why production systems use databases instead of just CSV files.

Time to turn the .sql files from the Introduction into a real, queryable database. You'll run the schema file first (it creates empty tables) and then the seed file (it fills them with data).

$ psql -U postgres -d brightcart -f 01_schema.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX

$ psql -U postgres -d brightcart -f 02_seed_clean_data.sql
INSERT 0 16
INSERT 0 14
INSERT 0 45
INSERT 0 63

Every SQL query starts the same way: tell it what columns you want, from which table, which rows qualify, and how to sort the result. This one pattern answers most of the questions a business ever asks.

SELECT first_name, last_name, city
FROM customers
WHERE country = 'Philippines'
ORDER BY city ASC
LIMIT 5;

-- Every order that is still pending, most recent first
SELECT order_id, order_date, payment_method
FROM orders
WHERE status = 'pending'
ORDER BY order_date DESC;

Aggregate functions collapse many rows into one summary number — count, sum, average, min, max. GROUP BY is what lets you get one summary per category instead of one summary for the whole table.

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

-- Total revenue per payment method, but only for orders worth summarizing
SELECT payment_method,
       SUM(quantity * unit_price) AS revenue,
       COUNT(*) AS line_items
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'paid'
GROUP BY payment_method
HAVING SUM(quantity * unit_price) > 200
ORDER BY revenue DESC;

A primary key uniquely identifies each row in a table (customer_id in customers). A foreign key is a column in one table that points at a primary key in another (orders.customer_id points at customers.customer_id). This is the entire mechanism that lets SQL connect tables together.

A JOIN combines rows from two tables based on a matching column. Which rows survive when there isn't a match depends entirely on which join type you pick — this is the single most important SQL concept to get comfortable with.

A window function calculates something across a set of related rows without collapsing them into one row the way GROUP BY does — you keep every row, and get a ranking, running total, or comparison alongside it. This is the tool that makes "top N per group" and "running total" questions easy.

customers, orders, and order_items are OLTP tables (Online Transaction Processing) — designed so BrightCart's checkout page can insert one order quickly and safely. A dashboard asking "what were our total sales by category, by month?" has completely different needs — that's OLAP (Online Analytical Processing), and the design pattern for it is called a star schema.

A fact table holds the numbers you want to measure (sales_amount, quantity) plus foreign keys pointing out to dimension tables, which hold the descriptive context (who, what, when).

Phase 1 was about talking to data one script and one query at a time. Phase 2 is about making that repeatable — turning BrightCart's dirty_orders.csv into an automated pipeline that cleans itself, and moving your work from your laptop into the cloud.