Choose your target sector early
Sector: ______________
Typical sources: ______________
Typical questions the business asks: ______________
Project I will build that mirrors it: ______________
Tools it uses (from job posts): ______________

-- Turning raw app events into a daily active users table
CREATE TABLE daily_active_users AS
SELECT DATE(event_time) AS activity_date,
       COUNT(DISTINCT user_id) AS active_users
FROM app_events
WHERE event_name IN ('login', 'page_view', 'purchase')
GROUP BY DATE(event_time);

-- Keep only the latest record per customer
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY updated_at DESC) AS rn
  FROM customers_raw
)
SELECT * FROM ranked WHERE rn = 1;

import sqlite3
import pandas as pd

con = sqlite3.connect("warehouse.db")
con.execute("CREATE TABLE IF NOT EXISTS orders (order_id INTEGER PRIMARY KEY, amount REAL, updated_at TEXT)")
last = con.execute("SELECT COALESCE(MAX(updated_at), '1970-01-01') FROM orders").fetchone()[0]

new = pd.read_csv("orders_export.csv")
new = new[new["updated_at"] > last]
for r in new.itertuples(index=False):
    con.execute("INSERT OR REPLACE INTO orders VALUES (?, ?, ?)",
                (r.order_id, r.amount, r.updated_at))
con.commit()
print(len(new), "new or changed rows loaded")

# Apache Airflow 2.x
from datetime import datetime
from airflow import DAG
from airflow.operators.python import PythonOperator

def extract():
    print("pull data from API")

def load():
    print("load into warehouse")

with DAG("daily_orders", start_date=datetime(2026, 11, 1),
         schedule="@daily", catchup=False) as dag:
    t1 = PythonOperator(task_id="extract", python_callable=extract)
    t2 = PythonOperator(task_id="load", python_callable=load)
    t1 >> t2

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("orders").getOrCreate()
df = spark.read.csv("orders/*.csv", header=True, inferSchema=True)
daily = (df.groupBy(F.to_date("order_time").alias("day"))
           .agg(F.sum("amount").alias("revenue"),
                F.countDistinct("customer_id").alias("customers")))
daily.orderBy("day").show(5)

Data Engineering Learning Plan
Phase 1: SQL in depth (joins, windows, CTEs)
Phase 2: Python for data (files, APIs, pandas, logging)
Phase 3: Data modelling (star schema)
Phase 4: One cloud warehouse
Phase 5: Orchestration with Airflow
Phase 6: Spark basics
Phase 7: Capstone pipeline + interview prep

Capstone: End-to-end pipeline
Source: public API or open dataset updated regularly
Steps:
  1. Extract with Python (with retries and logging)
  2. Land raw data as files
  3. Load incrementally into a warehouse or Postgres
  4. Model into fact and dimension tables with SQL
  5. Data quality checks (nulls, duplicates, ranges)
  6. Schedule with Airflow
  7. A simple dashboard on top
README: architecture diagram, how to run, design choices

Data Quality Checks
☐ Row count vs previous run within expected range
☐ Primary key unique
☐ No nulls in required columns
☐ Values within valid ranges
☐ Freshness: latest timestamp within SLA
☐ Alerts on failure