Data Engineer Roadmap 2026: From Zero to Job-Ready (Step-by-Step)

Before diving into data engineering, you need a solid grasp of a few core CS concepts.

How the Internet Works

• IP Address: A unique numerical label assigned to each device on a network
• DNS: Translates human-readable domain names (google.com) to IP addresses
• HTTP/HTTPS: Protocols for transferring data between clients and servers
• TCP/IP: The foundational communication protocols of the internet

Client-Server Architecture

• Client: Makes requests (browser, Python script, mobile app)
• Server: Processes requests and sends responses (web server, database server, API server)
• Request/Response Cycle: Client sends a request → Server processes it → Server returns a response

File Systems

• Directories/Folders: Hierarchical organization of files
• Paths: Absolute (/home/user/data) vs Relative (./data)
• File Extensions: .csv, .json, .parquet, .sql — you'll use all of these
• Permissions: Read, Write, Execute (important for scripts and data files)

Why This Matters: Data pipelines pull data from APIs (HTTP), move files across systems (file I/O), and connect to databases (client-server). These fundamentals appear everywhere.

The command line is your primary tool as a data engineer. Get comfortable with these essentials.

Navigation

pwd          # Print working directory
ls           # List files and directories
ls -la       # List all files with details
cd /path     # Change directory
cd ..        # Go up one level
cd ~         # Go to home directory

File Operations

cat file.txt       # Display file contents
head -n 10 file.csv  # First 10 lines
tail -n 10 file.csv  # Last 10 lines
wc -l file.csv     # Count lines
cp source dest     # Copy file
mv source dest     # Move/rename file
mkdir dirname      # Create directory
rm file.txt        # Delete file

Searching & Filtering

grep 'pattern' file.txt    # Search for text
find . -name '*.csv'       # Find files by name
| (pipe)                   # Chain commands
> output.txt               # Redirect output to file

Process Management

ps aux         # List running processes
top            # Monitor system resources
kill PID       # Stop a process
Ctrl+C         # Cancel running command

Tip: Practice by navigating your file system, creating directories, and manipulating text files. You'll use these commands daily.

VS Code is the most popular editor for data engineers. Install these extensions to boost your productivity.

Must-Have Extensions

• Python (Microsoft): Linting, debugging, IntelliSense for Python
• Pylance: Fast Python language server with type checking
• SQLTools: Run SQL queries directly from VS Code
• Docker: Manage containers and images
• GitLens: Enhanced Git integration
• YAML: Syntax highlighting for config files
• Rainbow CSV: Color-coded CSV viewing

Key Shortcuts

Settings Tips

• Enable Auto Save (File > Auto Save)
• Set Python interpreter (Cmd+Shift+P → "Python: Select Interpreter")
• Use the integrated terminal for running scripts

Tip: Learn keyboard shortcuts early — they compound over time.

Data engineering is the foundation of every data-driven organization. Here's what the role involves.

The Role

Data engineers build and maintain the infrastructure that allows data to flow from sources to consumers (analysts, data scientists, ML models, dashboards).

Core Responsibilities

1. Build Data Pipelines: Automate the movement of data from source systems to storage
2. Design Data Models: Structure data for efficient querying and analysis
3. Ensure Data Quality: Validate, clean, and monitor data reliability
4. Manage Infrastructure: Set up databases, cloud services, orchestration tools
5. Optimize Performance: Make queries and pipelines fast and cost-effective

A Typical Day

• Monitor overnight pipeline runs for failures
• Debug a broken data pipeline
• Write SQL transformations for a new dashboard
• Review a teammate's pull request
• Set up a new data source integration
• Optimize a slow query

Career Path

Junior DE → Mid-level DE → Senior DE → Staff/Principal DE or Data Architect

Average salaries range from $85K (junior) to $180K+ (senior/staff) in the US.

The bottom line: If you enjoy building systems, automating workflows, and solving puzzles with data, data engineering is for you.

SQL is the language of data. Every data engineer uses it daily.

SELECT Basics

-- Select specific columns
SELECT first_name, last_name, email
FROM customers;

-- Select all columns
SELECT * FROM orders;

Filtering with WHERE

SELECT * FROM orders
WHERE status = 'completed'
  AND total_amount > 100;

-- Common operators: =, !=, <, >, <=, >=, BETWEEN, IN, LIKE, IS NULL
SELECT * FROM products
WHERE category IN ('electronics', 'books')
  AND price BETWEEN 10 AND 50;

Sorting with ORDER BY

SELECT * FROM customers
ORDER BY created_at DESC;  -- newest first

SELECT * FROM products
ORDER BY category ASC, price DESC;

Limiting Results

-- Top 10 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10;

-- Pagination: skip 20, get next 10
SELECT * FROM orders
ORDER BY id
LIMIT 10 OFFSET 20;

Practice: Try SQLBolt or Mode's interactive tutorial — writing SQL is the fastest way to learn.

JOINs combine rows from two or more tables based on a related column.

INNER JOIN

Returns only matching rows from both tables.

SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right. Non-matching right rows are NULL.

-- All customers, even those without orders
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN

Returns all rows from the right table, plus matching rows from the left.

SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where there's no match.

SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

CROSS JOIN

Returns the Cartesian product (every combination of rows).

SELECT * FROM sizes CROSS JOIN colors;

Key Rule: Always think about which table is "left" and which is "right". LEFT JOIN is the most common in practice.

GROUP BY lets you summarize data by categories — essential for analytics and reporting.

Aggregate Functions

GROUP BY Examples

-- Orders per customer
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- Revenue by category
SELECT category, SUM(price * quantity) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY category
ORDER BY revenue DESC;

Filtering Groups with HAVING

WHERE filters rows before grouping. HAVING filters after grouping.

-- Customers with more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Execution Order

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Common Mistake: Using a non-aggregated column in SELECT without including it in GROUP BY.

Subqueries and CTEs let you break complex queries into manageable pieces.

Subqueries

A query nested inside another query.

-- Customers who placed orders above average
SELECT name FROM customers
WHERE id IN (
  SELECT customer_id FROM orders
  WHERE total > (SELECT AVG(total) FROM orders)
);

CTEs (Common Table Expressions)

Named temporary result sets using WITH. Much more readable.

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY 1
),
revenue_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  ROUND((revenue - prev_revenue) / prev_revenue * 100, 1) AS growth_pct
FROM revenue_growth
ORDER BY month;

When to Use Each

Best Practice: Prefer CTEs over subqueries for readability. They're the standard in analytics engineering (dbt).

Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY.

Syntax

function_name() OVER (
  PARTITION BY column    -- optional: group rows
  ORDER BY column        -- optional: define order
  ROWS BETWEEN ...       -- optional: define frame
)

Ranking Functions

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

• ROW_NUMBER(): Unique sequential number (1, 2, 3)
• RANK(): Gaps after ties (1, 1, 3)
• DENSE_RANK(): No gaps after ties (1, 1, 2)

Offset Functions

SELECT
  month, revenue,
  LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;

Aggregate Windows

SELECT
  order_date, total,
  SUM(total) OVER (ORDER BY order_date) AS running_total,
  AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM orders;

Interview Favorite: Window functions are the #1 topic in data engineering SQL interviews. Practice them extensively.

Python is the primary programming language for data engineering.

Installation

1. Download from python.org or use brew install python (Mac)
2. Verify: python3 --version
3. Set up a virtual environment:

python3 -m venv .venv
source .venv/bin/activate  # Mac/Linux
.venv\Scripts\activate     # Windows
pip install --upgrade pip

Core Syntax

# Variables and types
name = "pipeline"        # str
row_count = 1000         # int
success_rate = 0.95      # float
is_active = True         # bool

# Conditionals
if row_count > 0:
    print(f"Processed {row_count} rows")
elif row_count == 0:
    print("No data")
else:
    print("Error: negative count")

# Loops
for table in ["users", "orders", "products"]:
    print(f"Processing {table}")

# Functions
def extract_data(url: str, timeout: int = 30) -> dict:
    """Extract data from an API endpoint."""
    response = requests.get(url, timeout=timeout)
    return response.json()

# F-strings (formatted strings)
print(f"Loaded {row_count:,} rows in {elapsed:.2f}s")

Package Management

pip install pandas requests sqlalchemy
pip freeze > requirements.txt
pip install -r requirements.txt

Tip: You don't need to master all of Python. Focus on functions, loops, file I/O, and working with dictionaries/lists.

These four data structures cover 90% of what you'll use in data engineering.

Lists (ordered, mutable)

tables = ["users", "orders", "products"]
tables.append("reviews")

# List comprehension (use this a lot!)
csv_files = [f for f in files if f.endswith('.csv')]
row_counts = [len(df) for df in dataframes]

Dictionaries (key-value pairs)

config = {
    "host": "localhost",
    "port": 5432,
    "database": "analytics"
}
print(config["host"])  # localhost
print(config.get("timeout", 30))  # 30 (default)

# Dict comprehension
table_counts = {t: get_count(t) for t in tables}

Tuples (ordered, immutable)

coordinates = (40.7128, -74.0060)
db_config = ("localhost", 5432, "analytics")

# Unpacking
host, port, db = db_config

Sets (unique values, unordered)

source_columns = {"id", "name", "email", "name"}
# {"id", "name", "email"} — duplicates removed

target_columns = {"id", "name", "phone"}
missing = target_columns - source_columns  # {"phone"}
extra = source_columns - target_columns    # {"email"}

Data Engineering Pattern: API responses are dicts, query results are lists of dicts/tuples, deduplication uses sets.

Data engineers constantly read, write, and convert between file formats.

CSV

import csv

# Reading
with open('data.csv', 'r') as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row['name'], row['email'])

# Writing
with open('output.csv', 'w', newline='') as f:
    writer = csv.DictWriter(f, fieldnames=['id', 'name'])
    writer.writeheader()
    writer.writerows(data)

JSON

import json

# Reading
with open('config.json', 'r') as f:
    data = json.load(f)

# Writing
with open('output.json', 'w') as f:
    json.dump(data, f, indent=2)

# String conversion
json_str = json.dumps(data)
data = json.loads(json_str)

Parquet (columnar format — fast and compressed)

import pandas as pd

# Reading
df = pd.read_parquet('data.parquet')

# Writing
df.to_parquet('output.parquet', index=False)

# With PyArrow directly
import pyarrow.parquet as pq
table = pq.read_table('data.parquet')

Format Comparison

Rule of Thumb: Use CSV for simple exchange, JSON for API/config data, Parquet for analytical workloads.

Most data sources expose REST APIs. Learning to consume them is a critical skill.

Making Requests

import requests

# GET request
response = requests.get('https://api.example.com/users')
response.raise_for_status()  # Raise error on 4xx/5xx
data = response.json()

# With parameters
params = {'page': 1, 'per_page': 100}
response = requests.get('https://api.example.com/users', params=params)

# With authentication
headers = {'Authorization': 'Bearer YOUR_TOKEN'}
response = requests.get(url, headers=headers)

Pagination Pattern

def fetch_all_pages(base_url: str) -> list:
    all_data = []
    page = 1
    while True:
        response = requests.get(base_url, params={'page': page, 'per_page': 100})
        response.raise_for_status()
        data = response.json()
        if not data:
            break
        all_data.extend(data)
        page += 1
    return all_data

Rate Limiting

import time

for endpoint in endpoints:
    response = requests.get(endpoint)
    time.sleep(0.5)  # Respect rate limits

HTTP Status Codes

• 200: Success
• 201: Created
• 400: Bad request
• 401: Unauthorized
• 403: Forbidden
• 404: Not found
• 429: Too many requests (rate limited)
• 500: Server error

Real-World Tip: Always add error handling, timeouts, and retries when calling APIs in production pipelines.

Production pipelines must handle errors gracefully and log everything.

Exception Handling

try:
    response = requests.get(url, timeout=30)
    response.raise_for_status()
    data = response.json()
except requests.exceptions.Timeout:
    logger.error(f"Request timed out: {url}")
    raise
except requests.exceptions.HTTPError as e:
    logger.error(f"HTTP error {e.response.status_code}: {url}")
    raise
except Exception as e:
    logger.error(f"Unexpected error: {e}")
    raise

Logging Setup

import logging

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

# Usage
logger.info(f"Starting extraction from {source}")
logger.warning(f"Retrying after {delay}s")
logger.error(f"Failed to load {table}: {error}")

Retry Pattern

import time

def retry(func, max_retries=3, delay=1):
    for attempt in range(max_retries):
        try:
            return func()
        except Exception as e:
            if attempt == max_retries - 1:
                raise
            logger.warning(f"Attempt {attempt+1} failed: {e}. Retrying in {delay}s...")
            time.sleep(delay)
            delay *= 2  # Exponential backoff

Logging Levels

Rule: Never use print() in production code. Always use the logging module.

Git is essential for tracking changes, collaborating, and deploying code.

Core Concepts

• Repository (repo): A project tracked by Git
• Commit: A snapshot of your changes
• Branch: An independent line of development
• Staging Area: Where you prepare changes before committing

Essential Commands

# Initialize a repo
git init

# Check status
git status

# Stage changes
git add filename.py        # Stage specific file
git add .                  # Stage all changes

# Commit
git commit -m "Add extraction script for user data"

# View history
git log --oneline

# Create and switch branches
git checkout -b feature/add-pipeline
git checkout main

# Merge
git merge feature/add-pipeline

Good Commit Messages

feat: add daily user extraction pipeline
fix: handle null values in transformation step
docs: update README with setup instructions
refactor: extract database connection to utility module

.gitignore (always set this up!)

.env
__pycache__/
*.pyc
.venv/
data/
*.csv

Rule: Commit early, commit often. Write meaningful messages.

GitHub is where you host your code, collaborate, and showcase your portfolio.

Initial Setup

1. Create account at github.com
2. Configure Git locally:

git config --global user.name "Your Name"
git config --global user.email "[email protected]"

Working with Remotes

# Clone a repo
git clone https://github.com/user/repo.git

# Add remote
git remote add origin https://github.com/user/repo.git

# Push changes
git push origin main
git push -u origin feature/my-branch

# Pull latest changes
git pull origin main

Pull Request Workflow

1. Create a branch: git checkout -b feature/add-pipeline
2. Make changes and commit
3. Push: git push -u origin feature/add-pipeline
4. Open a Pull Request on GitHub
5. Get code review
6. Merge into main

Repository Best Practices

• Always include a README.md
• Add a .gitignore for your language
• Never commit secrets or credentials
• Use branches for new features

Portfolio Tip: Pin your best data engineering projects on your GitHub profile. Recruiters check this.

Bash scripting automates repetitive tasks and ties your tools together.

Data-Relevant Commands

# Count rows in CSV (minus header)
wc -l data.csv | awk '{print $1 - 1}'

# Preview CSV columns
head -1 data.csv | tr ',' '\n'

# Find large files
find . -size +100M -type f

# Check disk usage
du -sh data/
df -h

# Download a file
curl -o data.csv https://example.com/data.csv
wget https://example.com/data.csv

Environment Variables

# Set
export DB_HOST=localhost
export DB_PASSWORD=secret

# Use
echo $DB_HOST
python pipeline.py  # Can read via os.environ

# Load from .env file
source .env

Simple Bash Script

#!/bin/bash
set -e  # Exit on error

echo "Starting pipeline at $(date)"
python extract.py
python transform.py
python load.py
echo "Pipeline completed at $(date)"

chmod +x run_pipeline.sh
./run_pipeline.sh

Tip: Even if you write pipelines in Python, you'll use bash for Docker, CI/CD, cron jobs, and quick data inspection.

SSH keys let you securely connect to GitHub and remote servers without typing passwords.

Generate an SSH Key

ssh-keygen -t ed25519 -C "[email protected]"
# Press Enter for default location (~/.ssh/id_ed25519)
# Enter a passphrase (recommended)

Add to SSH Agent

eval "$(ssh-agent -s)"
ssh-add ~/.ssh/id_ed25519

Add to GitHub

# Copy public key
cat ~/.ssh/id_ed25519.pub
# Go to GitHub → Settings → SSH Keys → New SSH Key → Paste

Test Connection

ssh -T [email protected]
# "Hi username! You've successfully authenticated..."

Switch Repo to SSH

git remote set-url origin [email protected]:username/repo.git

Why SSH? HTTPS requires entering credentials each time. SSH keys are more secure and convenient. You'll also use SSH to connect to cloud servers.

PostgreSQL is the industry-standard relational database for data engineering.

Installation Options

Option 1: Docker (recommended)

docker run --name postgres \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=analytics \
  -p 5432:5432 \
  -d postgres:16

Option 2: Native Install

# Mac
brew install postgresql@16
brew services start postgresql@16

# Ubuntu
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Connecting

# CLI
psql -h localhost -U admin -d analytics

# GUI options:
# - pgAdmin (free, full-featured)
# - DBeaver (free, multi-database)
# - TablePlus (paid, clean UI)

First Commands

-- Create a table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data
INSERT INTO users (name, email)
VALUES ('Alice', '[email protected]');

-- Query
SELECT * FROM users;

Recommendation: Use Docker for PostgreSQL. It keeps your machine clean and matches production environments.

Good table design is the foundation of efficient data storage and querying.

Key Concepts

• Table: A collection of related data organized in rows and columns
• Primary Key: Uniquely identifies each row (id SERIAL PRIMARY KEY)
• Foreign Key: References a primary key in another table
• Constraints: Rules that enforce data integrity

Common Data Types

Relationships

-- One-to-Many: One customer has many orders
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total NUMERIC(10,2),
  order_date TIMESTAMP DEFAULT NOW()
);

-- Many-to-Many: Orders contain many products, products in many orders
CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Indexes

-- Speed up lookups on frequently queried columns
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);

Tip: Think about how the data will be queried before designing your tables.

Normalization reduces data redundancy and improves integrity. Know the first three normal forms.

First Normal Form (1NF)

Each column contains only atomic (single) values. No repeating groups.

Bad: tools: "Python, SQL, Docker"

Good: Separate user_tools table with one row per tool.

Second Normal Form (2NF)

1NF + every non-key column depends on the entire primary key (not just part of it).

Bad (composite key order_id, product_id):

• customer_name depends only on order_id, not on product_id

Fix: Move customer_name to the orders table.

Third Normal Form (3NF)

2NF + no transitive dependencies (non-key columns depending on other non-key columns).

Bad: orders table has customer_id, customer_name, customer_email

Fix: Keep only customer_id in orders; name and email live in customers.

When to Denormalize

In analytical databases (data warehouses), denormalization is common for query performance:

• Star schema and snowflake schema
• Pre-joined dimension tables
• Materialized views

Key Insight: Normalize for OLTP (transactional systems). Denormalize for OLAP (analytics). Data engineers work with both.

Entity-Relationship diagrams visualize database structure before you write any SQL.

Components

• Entity: A table (rectangle) — e.g., customers, orders
• Attribute: A column (listed inside the entity)
• Relationship: A line connecting entities
• Cardinality: How many records relate to each other

Cardinality Notation

• 1:1 — One-to-one (user ↔ profile)
• 1:N — One-to-many (customer → orders)
• M:N — Many-to-many (orders ↔ products, via junction table)

Tools for Creating ER Diagrams

• dbdiagram.io: Free, text-based, great for quick diagrams
• Lucidchart: Visual drag-and-drop
• draw.io (diagrams.net): Free, flexible
• pgModeler: Specifically for PostgreSQL
• DBeaver: Auto-generates from existing database

dbdiagram.io Example

Table customers {
  id integer [primary key]
  name varchar
  email varchar
}

Table orders {
  id integer [primary key]
  customer_id integer [ref: > customers.id]
  total decimal
  created_at timestamp
}

Portfolio Tip: Include ER diagrams in your project READMEs. They show you think about data design.

Docker packages your application and its dependencies into lightweight, portable containers.

Containers vs VMs

Install Docker

• Mac/Windows: Docker Desktop
• Linux: sudo apt install docker.io

First Commands

# Verify installation
docker --version

# Run your first container
docker run hello-world

# Run PostgreSQL
docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16

# List running containers
docker ps

# Stop and remove
docker stop pg
docker rm pg

# List images
docker images

Why Docker for Data Engineers? Reproducible environments, easy database setup, consistent deployments. Most production pipelines run in containers.

A Dockerfile is a recipe for building a container image.

Basic Dockerfile

# Start from a base image
FROM python:3.11-slim

# Set working directory
WORKDIR /app

# Copy requirements and install dependencies
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

# Copy application code
COPY . .

# Set the command to run
CMD ["python", "pipeline.py"]

Key Instructions

Build and Run

# Build image
docker build -t my-pipeline:latest .

# Run container
docker run my-pipeline:latest

# Run with environment variables
docker run -e DB_HOST=host.docker.internal my-pipeline:latest

# Run interactively
docker run -it my-pipeline:latest bash

.dockerignore

.venv/
__pycache__/
.git/
.env
data/

Best Practice: Put COPY requirements.txt and RUN pip install before COPY . . to leverage Docker layer caching.

Docker Compose lets you define and run multi-container applications with a single file.

docker-compose.yml

version: '3.8'

services:
  postgres:
    image: postgres:16
    container_name: postgres
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: analytics
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "8080:80"
    depends_on:
      - postgres

volumes:
  postgres_data:

Commands

# Start all services
docker compose up -d

# View logs
docker compose logs -f postgres

# Stop all services
docker compose down

# Stop and remove volumes (reset data)
docker compose down -v

# Rebuild after changes
docker compose up -d --build

Connecting

• psql: psql -h localhost -U admin -d analytics
• pgAdmin: Open http://localhost:8080, add server with host postgres, port 5432
• Python: postgresql://admin:secret@localhost:5432/analytics

Why Compose? One command spins up your entire dev environment. Share the file with teammates for consistent setups.

Volumes persist data beyond the lifecycle of a container.

Why Volumes?

Containers are ephemeral — when you remove a container, its data is gone. Volumes solve this.

Types of Storage

Named Volumes (recommended for databases)

volumes:
  postgres_data:

services:
  postgres:
    volumes:
      - postgres_data:/var/lib/postgresql/data

Bind Mounts (map host directory)

services:
  pipeline:
    volumes:
      - ./data:/app/data        # Host ./data → Container /app/data
      - ./config:/app/config:ro  # Read-only mount

Volume Commands

# List volumes
docker volume ls

# Inspect a volume
docker volume inspect postgres_data

# Remove unused volumes
docker volume prune

# Remove specific volume
docker volume rm postgres_data

Common Patterns

# Mount local data for processing
docker run -v $(pwd)/data:/app/data my-pipeline

# Mount config files
docker run -v $(pwd)/config.yaml:/app/config.yaml:ro my-app

# Share data between containers
docker run -v shared_data:/data container1
docker run -v shared_data:/data container2

Key Rule: Always use volumes for database containers. Losing your database data because you forgot a volume is a painful lesson.

The first step of any ETL pipeline: extracting data from source systems.

Basic Extraction Script

import requests
import json
import logging
from datetime import datetime

logger = logging.getLogger(__name__)

def extract_from_api(url: str, params: dict = None) -> list[dict]:
    """Extract data from a REST API with pagination."""
    all_records = []
    page = 1
    
    while True:
        request_params = {**(params or {}), 'page': page, 'per_page': 100}
        response = requests.get(url, params=request_params, timeout=30)
        response.raise_for_status()
        
        data = response.json()
        if not data:
            break
        
        all_records.extend(data)
        logger.info(f"Extracted page {page}: {len(data)} records")
        page += 1
    
    logger.info(f"Total records extracted: {len(all_records)}")
    return all_records

# Usage
users = extract_from_api('https://api.example.com/users')

Good Public APIs to Practice With

• GitHub API: https://api.github.com/users/octocat/repos
• JSONPlaceholder: https://jsonplaceholder.typicode.com/posts
• Open-Meteo Weather: https://api.open-meteo.com/v1/forecast
• CoinGecko: https://api.coingecko.com/api/v3/coins/markets

Save Raw Data

def save_raw(data: list[dict], filename: str):
    """Save raw extracted data to JSON."""
    with open(filename, 'w') as f:
        json.dump(data, f, indent=2, default=str)
    logger.info(f"Saved {len(data)} records to {filename}")

Best Practice: Always save raw data before transforming. You can always re-transform, but you can't un-extract.

Transform raw data into a clean, structured format ready for loading.

Common Transformations

import json
from datetime import datetime

def transform_users(raw_data: list[dict]) -> list[dict]:
    """Clean and transform raw user data."""
    transformed = []
    
    for record in raw_data:
        transformed.append({
            'user_id': record['id'],
            'username': record['login'].lower().strip(),
            'email': record.get('email', '').lower().strip() or None,
            'account_type': record.get('type', 'unknown'),
            'created_at': parse_date(record.get('created_at')),
            'is_active': record.get('active', True),
            'extracted_at': datetime.utcnow().isoformat()
        })
    
    return transformed

def parse_date(date_str: str) -> str | None:
    """Parse various date formats to ISO format."""
    if not date_str:
        return None
    try:
        return datetime.fromisoformat(date_str.replace('Z', '+00:00')).isoformat()
    except ValueError:
        return None

Common Transformation Tasks

1. Rename fields: Map source names to target schema
2. Clean strings: Strip whitespace, normalize case
3. Handle nulls: Default values or explicit NULL
4. Parse dates: Convert to consistent format
5. Type casting: Ensure correct data types
6. Deduplication: Remove duplicate records
7. Filtering: Remove invalid or irrelevant records
8. Enrichment: Add computed fields (e.g., extracted_at)

Data Validation

def validate_record(record: dict) -> bool:
    """Validate a transformed record."""
    required = ['user_id', 'username']
    return all(record.get(field) is not None for field in required)

valid_records = [r for r in transformed if validate_record(r)]
logger.info(f"Valid: {len(valid_records)}, Dropped: {len(transformed) - len(valid_records)}")

Principle: Transformations should be pure functions — same input always produces same output. This makes debugging easy.

The final step: loading transformed data into your database.

Using psycopg2 (direct connection)

import psycopg2
from psycopg2.extras import execute_values

def load_to_postgres(records: list[dict], table: str):
    """Load records into PostgreSQL."""
    conn = psycopg2.connect(
        host='localhost', port=5432,
        dbname='analytics', user='admin', password='secret'
    )
    cursor = conn.cursor()
    
    columns = records[0].keys()
    query = f"""
        INSERT INTO {table} ({', '.join(columns)})
        VALUES %s
        ON CONFLICT (user_id) DO UPDATE SET
            username = EXCLUDED.username,
            email = EXCLUDED.email,
            extracted_at = EXCLUDED.extracted_at
    """
    values = [tuple(r.values()) for r in records]
    execute_values(cursor, query, values)
    
    conn.commit()
    print(f"Loaded {len(records)} records into {table}")
    cursor.close()
    conn.close()

Using SQLAlchemy

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('postgresql://admin:secret@localhost:5432/analytics')

# Load DataFrame to table
df = pd.DataFrame(records)
df.to_sql('users', engine, if_exists='append', index=False)

Create the Target Table

CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    account_type VARCHAR(50),
    created_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    extracted_at TIMESTAMP NOT NULL
);

Key Pattern: Use ON CONFLICT ... DO UPDATE (upsert) to make your loads idempotent — safe to re-run.

Production-grade pipelines need robust error handling and must be safe to re-run.

Idempotency

A pipeline is idempotent if running it multiple times produces the same result.

# BAD: Duplicates data on re-run
INSERT INTO users (id, name) VALUES (1, 'Alice');

# GOOD: Upsert pattern
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

# GOOD: Delete and reload
DELETE FROM users WHERE extract_date = '2024-01-15';
INSERT INTO users ... ;

Structured Pipeline with Error Handling

import logging
import sys
from datetime import datetime

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s'
)
logger = logging.getLogger('etl_pipeline')

def run_pipeline():
    start = datetime.utcnow()
    logger.info("Pipeline started")
    
    try:
        # Extract
        logger.info("Extracting data...")
        raw_data = extract()
        logger.info(f"Extracted {len(raw_data)} records")
        
        # Transform
        logger.info("Transforming data...")
        clean_data = transform(raw_data)
        logger.info(f"Transformed {len(clean_data)} records")
        
        # Load
        logger.info("Loading data...")
        load(clean_data)
        
        elapsed = (datetime.utcnow() - start).total_seconds()
        logger.info(f"Pipeline completed in {elapsed:.1f}s")
    except Exception as e:
        logger.error(f"Pipeline failed: {e}", exc_info=True)
        sys.exit(1)

if __name__ == '__main__':
    run_pipeline()

Golden Rule: If your pipeline fails halfway through, you should be able to re-run it safely from the beginning.

Once your pipeline works, schedule it to run automatically.

Option 1: Cron (simplest)

# Edit crontab
crontab -e

# Run every day at 6 AM
0 6 * * * cd /path/to/project && python pipeline.py >> /var/log/pipeline.log 2>&1

# Run every hour
0 * * * * cd /path/to/project && python pipeline.py

# Run every Monday at midnight
0 0 * * 1 cd /path/to/project && python pipeline.py

Cron Expression Format

┌──── minute (0-59)
│ ┌──── hour (0-23)
│ │ ┌──── day of month (1-31)
│ │ │ ┌──── month (1-12)
│ │ │ │ ┌──── day of week (0-7, 0 and 7 = Sunday)
│ │ │ │ │
* * * * *

Option 2: Python Schedule Library

import schedule
import time

schedule.every().day.at("06:00").do(run_pipeline)
schedule.every(1).hours.do(run_pipeline)

while True:
    schedule.run_pending()
    time.sleep(60)

Option 3: Docker + Cron

FROM python:3.11-slim
COPY crontab /etc/cron.d/pipeline-cron
RUN chmod 0644 /etc/cron.d/pipeline-cron
RUN crontab /etc/cron.d/pipeline-cron
CMD ["cron", "-f"]

Note: Cron works for simple cases. For production, you'll want an orchestrator like Airflow (covered in Step 8).

Cloud platforms are where production data engineering happens. Start with one provider.

Which Cloud to Choose?

Recommendation: Start with AWS (most in-demand) or GCP (best free data tools).

AWS Setup Checklist

1. Create account at aws.amazon.com
2. Enable MFA on root account
3. Create an IAM admin user (never use root for daily work)
4. Install AWS CLI: brew install awscli or pip install awscli
5. Configure: aws configure (enter access key, secret, region)

GCP Setup Checklist

1. Create account at cloud.google.com
2. Create a new project
3. Enable billing (free tier credits apply)
4. Install gcloud CLI: brew install google-cloud-sdk
5. Initialize: gcloud init

Cost Safety Tips

• Set up billing alerts immediately
• Use free tier resources only when learning
• Delete resources when done experimenting
• Check costs daily during your first month

Warning: Cloud bills can add up fast. Always set billing alerts before deploying anything.

Object storage (S3/GCS) is the backbone of modern data architectures.

Key Concepts

• Bucket: Top-level container (like a root folder)
• Object: A file stored in a bucket (any format, any size)
• Key: The full path to an object (e.g., raw/users/2024-01-15/data.parquet)
• Prefix: Virtual folder structure (no real directories)

AWS S3 with Python (boto3)

import boto3

s3 = boto3.client('s3')

# Upload a file
s3.upload_file('data.csv', 'my-bucket', 'raw/users/data.csv')

# Download a file
s3.download_file('my-bucket', 'raw/users/data.csv', 'local_data.csv')

# List objects
response = s3.list_objects_v2(Bucket='my-bucket', Prefix='raw/users/')
for obj in response.get('Contents', []):
    print(obj['Key'], obj['Size'])

Data Lake Organization Pattern

s3://my-data-lake/
├── raw/              # Untouched source data
│   ├── users/
│   └── orders/
├── staging/          # Cleaned, validated
│   ├── users/
│   └── orders/
└── curated/          # Business-ready
    ├── dim_users/
    └── fact_orders/

File Format Best Practices

• Raw layer: Keep original format (JSON, CSV)
• Staging/Curated: Use Parquet (columnar, compressed, fast)
• Partition by date: s3://bucket/table/year=2024/month=01/data.parquet

Key Insight: In modern data engineering, the data lake (S3/GCS) is often the central hub, not a database.

IAM (Identity and Access Management) controls who can access what in your cloud account.

Core Concepts

• User: A person or service that interacts with cloud resources
• Group: A collection of users with shared permissions
• Role: A set of permissions that can be assumed (by users, services, or applications)
• Policy: A JSON document defining specific permissions

Principle of Least Privilege

Only grant the minimum permissions needed.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject"
      ],
      "Resource": "arn:aws:s3:::my-data-bucket/*"
    }
  ]
}

Common Data Engineering Permissions

Best Practices

1. Never use root account for daily work
2. Enable MFA on all accounts
3. Use roles instead of long-lived access keys
4. Rotate credentials regularly
5. Use separate accounts for dev/staging/prod

Security Rule: Never commit AWS keys to Git. Use environment variables or IAM roles.

Cloud-managed databases handle backups, patches, and scaling so you can focus on data.

AWS RDS (Relational Database Service)

# Create a PostgreSQL instance via CLI
aws rds create-db-instance \
  --db-instance-identifier my-analytics-db \
  --db-instance-class db.t3.micro \
  --engine postgres \
  --master-username admin \
  --master-user-password secretpassword \
  --allocated-storage 20

Connecting from Python

import psycopg2

conn = psycopg2.connect(
    host='my-analytics-db.xxx.us-east-1.rds.amazonaws.com',
    port=5432,
    dbname='postgres',
    user='admin',
    password='secretpassword'
)

Managed vs Self-Hosted

GCP Alternative: Cloud SQL

gcloud sql instances create my-db \
  --database-version=POSTGRES_16 \
  --tier=db-f1-micro \
  --region=us-central1

Tip: Use db.t3.micro (AWS) or db-f1-micro (GCP) for learning — they're free tier eligible.

Orchestration is managing the execution of complex, interdependent data workflows.

Why Orchestration?

Real pipelines are not a single script. They have:

• Dependencies: Task B can't run until Task A succeeds
• Schedules: Run daily at 6 AM, hourly, on events
• Error Handling: Retry failed tasks, alert on failure
• Monitoring: Know which tasks ran, how long they took, what failed

Without Orchestration

# Fragile chain of scripts
python extract.py && python transform.py && python load.py
# What if transform fails? What if you need to re-run just one step?

With Orchestration (Airflow)

extract >> transform >> load  # Dependencies are explicit
# Automatic retries, logging, monitoring, backfills

Popular Orchestrators

Key Concepts

• DAG: Directed Acyclic Graph — a workflow with no circular dependencies
• Task: A single unit of work in a DAG
• Operator: The type of work a task performs (Python, Bash, SQL, etc.)
• Schedule: When the DAG runs
• Backfill: Running a DAG for past dates

Career Tip: Airflow is by far the most requested orchestration tool in job postings. Learn it first.

The recommended way to run Airflow locally is with Docker Compose.

Quick Setup

# Create project directory
mkdir airflow-project && cd airflow-project
mkdir -p dags logs plugins

# Download the official docker-compose
curl -LfO 'https://airflow.apache.org/docs/apache-airflow/stable/docker-compose.yaml'

# Set Airflow user ID
echo -e "AIRFLOW_UID=$(id -u)" > .env

# Initialize the database
docker compose up airflow-init

# Start Airflow
docker compose up -d

Access Airflow

• Web UI: http://localhost:8080
• Username: airflow
• Password: airflow

Folder Structure

airflow-project/
├── dags/           # Your DAG files go here
├── logs/           # Airflow logs
├── plugins/        # Custom plugins
├── docker-compose.yaml
└── .env

Lightweight Setup (for learning)

The full docker-compose includes Redis, Celery workers, etc. For learning, use the lite version:

# Override to use LocalExecutor (simpler)
environment:
  AIRFLOW__CORE__EXECUTOR: LocalExecutor

Useful Commands

docker compose logs -f airflow-webserver
docker compose down     # Stop
docker compose down -v  # Stop and reset

Tip: Don't try to install Airflow natively with pip — the Docker approach is much smoother.

A DAG (Directed Acyclic Graph) defines your workflow — tasks and their dependencies.

Minimal DAG

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'data-team',
    'retries': 2,
    'retry_delay': timedelta(minutes=5),
}

def extract():
    print("Extracting data from API...")
    return {"rows": 100}

def transform(**context):
    data = context['ti'].xcom_pull(task_ids='extract_task')
    print(f"Transforming {data['rows']} rows...")

def load():
    print("Loading data to PostgreSQL...")

with DAG(
    dag_id='my_first_etl',
    default_args=default_args,
    description='A simple ETL pipeline',
    schedule='@daily',
    start_date=datetime(2024, 1, 1),
    catchup=False,
    tags=['etl', 'tutorial'],
) as dag:

    extract_task = PythonOperator(
        task_id='extract_task',
        python_callable=extract,
    )

    transform_task = PythonOperator(
        task_id='transform_task',
        python_callable=transform,
    )

    load_task = PythonOperator(
        task_id='load_task',
        python_callable=load,
    )

    extract_task >> transform_task >> load_task

Key Parameters

• schedule: @daily, @hourly, 0 6 * * * (cron), None (manual only)
• start_date: When the DAG begins scheduling
• catchup: If True, Airflow runs for all past dates since start_date
• tags: For organizing DAGs in the UI

Save and Test

# Place in dags/ folder
cp my_dag.py airflow-project/dags/

# Test a task
docker compose exec airflow-webserver airflow tasks test my_first_etl extract_task 2024-01-01

Common Gotcha: catchup=False prevents Airflow from running your DAG for every day since start_date.

Operators define what each task actually does.

Most Used Operators

PythonOperator

from airflow.operators.python import PythonOperator

def my_function(name, **context):
    execution_date = context['ds']  # '2024-01-15'
    print(f"Hello {name}, running for {execution_date}")

task = PythonOperator(
    task_id='greet',
    python_callable=my_function,
    op_kwargs={'name': 'Airflow'},
)

BashOperator

from airflow.operators.bash import BashOperator

task = BashOperator(
    task_id='run_script',
    bash_command='python /opt/airflow/scripts/pipeline.py {{ ds }}',
)

PostgresOperator

from airflow.providers.postgres.operators.postgres import PostgresOperator

task = PostgresOperator(
    task_id='create_table',
    postgres_conn_id='my_postgres',
    sql='sql/create_tables.sql',
)

TaskFlow API (modern approach)

from airflow.decorators import dag, task
from datetime import datetime

@dag(schedule='@daily', start_date=datetime(2024, 1, 1), catchup=False)
def my_etl():

    @task
    def extract():
        return {"data": [1, 2, 3]}

    @task
    def transform(raw_data):
        return [x * 2 for x in raw_data["data"]]

    @task
    def load(transformed_data):
        print(f"Loading {len(transformed_data)} records")

    raw = extract()
    transformed = transform(raw)
    load(transformed)

my_etl()

Tip: The TaskFlow API (using @task decorators) is the modern Pythonic way to write DAGs. Use it for new projects.

Reliable pipelines need monitoring, alerting, and smart retry strategies.

Retry Configuration

default_args = {
    'retries': 3,
    'retry_delay': timedelta(minutes=5),
    'retry_exponential_backoff': True,
    'max_retry_delay': timedelta(minutes=30),
    'email_on_failure': True,
    'email': ['[email protected]'],
}

Task-Level Timeouts

task = PythonOperator(
    task_id='extract',
    python_callable=extract_data,
    execution_timeout=timedelta(minutes=30),  # Kill if exceeds 30 min
)

Callbacks for Alerting

def alert_on_failure(context):
    task_id = context['task_instance'].task_id
    dag_id = context['dag'].dag_id
    error = context['exception']
    # Send Slack/email notification
    print(f"ALERT: {dag_id}.{task_id} failed: {error}")

with DAG(
    dag_id='monitored_pipeline',
    on_failure_callback=alert_on_failure,
    ...
) as dag:
    ...

Airflow UI Monitoring

• Grid View: See task status over time (green/red/yellow)
• Graph View: Visualize DAG dependencies
• Gantt View: See task durations and parallelism
• Task Logs: Click any task to view detailed logs

SLAs

task = PythonOperator(
    task_id='critical_load',
    python_callable=load_data,
    sla=timedelta(hours=2),  # Alert if task takes longer
)

Production Tip: Always set execution_timeout on tasks that call external APIs. Without it, a hung API call can block your pipeline indefinitely.

Analytics engineering bridges the gap between data engineering and data analysis.

The Role

Analytics engineers transform raw data in the warehouse into clean, tested, documented datasets that analysts and stakeholders can trust.

Where It Fits

Data Sources → [Data Engineer] → Raw Data → [Analytics Engineer] → Clean Models → [Analyst] → Dashboards

Key Tool: dbt (data build tool)

dbt lets you:

• Write transformations as SQL SELECT statements
• Manage dependencies between models automatically
• Test your data with assertions
• Document every model and column
• Track changes with version control (Git)

ELT vs ETL

Analytics engineering follows the ELT pattern — load raw data first, then transform with dbt.

Why It Matters

• Every modern data team uses dbt or similar tools
• SQL skills from Step 1 directly apply here
• It's the fastest path from "data in a database" to "trusted dashboards"

Career Note: "Analytics Engineer" is one of the fastest-growing roles in data. Many data engineers also handle analytics engineering.

dbt Core is the open-source CLI version of dbt.

Install

pip install dbt-postgres   # For PostgreSQL
# or
pip install dbt-bigquery   # For BigQuery
pip install dbt-snowflake  # For Snowflake

Initialize a Project

dbt init my_analytics
cd my_analytics

Project Structure

my_analytics/
├── dbt_project.yml      # Project configuration
├── profiles.yml         # Connection settings (in ~/.dbt/)
├── models/
│   ├── staging/         # Clean raw data
│   └── marts/           # Business logic
├── tests/               # Custom tests
├── seeds/               # CSV lookup tables
├── macros/              # Reusable SQL functions
└── snapshots/           # Track slowly changing dimensions

Configure Connection (profiles.yml)

my_analytics:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: admin
      password: secret
      dbname: analytics
      schema: dbt_dev

Verify Setup

dbt debug       # Test connection
dbt run         # Run all models
dbt test        # Run all tests
dbt docs generate && dbt docs serve  # View documentation

Tip: Use dbt Core with PostgreSQL in Docker for a completely free local setup.

dbt models are SQL files that define transformations. Organize them in layers.

Layer Architecture

Sources (raw tables) → Staging → Intermediate (optional) → Marts

Sources (raw data)

# models/staging/sources.yml
version: 2
sources:
  - name: raw
    schema: public
    tables:
      - name: users
      - name: orders
      - name: products

Staging Models (clean raw data)

-- models/staging/stg_users.sql
WITH source AS (
    SELECT * FROM {{ source('raw', 'users') }}
),
renamed AS (
    SELECT
        id AS user_id,
        LOWER(TRIM(name)) AS user_name,
        LOWER(TRIM(email)) AS email,
        created_at,
        CASE WHEN active = 'Y' THEN TRUE ELSE FALSE END AS is_active
    FROM source
    WHERE id IS NOT NULL
)
SELECT * FROM renamed

Mart Models (business logic)

-- models/marts/fct_daily_revenue.sql
WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
order_items AS (
    SELECT * FROM {{ ref('stg_order_items') }}
)
SELECT
    orders.order_date,
    COUNT(DISTINCT orders.order_id) AS total_orders,
    COUNT(DISTINCT orders.user_id) AS unique_customers,
    SUM(order_items.quantity * order_items.unit_price) AS revenue
FROM orders
JOIN order_items USING (order_id)
GROUP BY 1

Model Configuration

-- At top of model file
{{ config(materialized='table') }}    -- Recreates table each run
{{ config(materialized='view') }}     -- Creates a view (default)
{{ config(materialized='incremental') }}  -- Appends new data only

Key Concept: {{ ref('model_name') }} creates dependencies. dbt automatically runs models in the right order.

dbt tests assert that your data meets expectations. If a test fails, you know something is wrong.

Built-in Tests (schema.yml)

# models/staging/schema.yml
version: 2
models:
  - name: stg_users
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: email
        tests:
          - unique
          - not_null
      - name: is_active
        tests:
          - accepted_values:
              values: [true, false]

Built-in Test Types

Relationship Test

- name: stg_orders
  columns:
    - name: user_id
      tests:
        - relationships:
            to: ref('stg_users')
            field: user_id

Custom Tests (SQL)

-- tests/assert_positive_revenue.sql
SELECT *
FROM {{ ref('fct_daily_revenue') }}
WHERE revenue < 0
-- If this returns rows, the test fails

Running Tests

dbt test                    # Run all tests
dbt test --select stg_users  # Test specific model
dbt build                    # Run models + tests together

Best Practice: Add tests to every model. At minimum: unique and not_null on primary keys. Run tests in CI/CD.

dbt auto-generates a documentation website from your project — a data catalog for your team.

Add Descriptions

# models/marts/schema.yml
version: 2
models:
  - name: fct_daily_revenue
    description: >
      Daily revenue aggregation including order counts
      and unique customer counts. Grain: one row per day.
    columns:
      - name: order_date
        description: The date the orders were placed
      - name: total_orders
        description: Count of distinct orders for the day
      - name: revenue
        description: Total revenue in USD (quantity * unit_price)
        tests:
          - not_null

Generate and Serve Docs

dbt docs generate   # Build the documentation site
dbt docs serve      # Serve at localhost:8080

What You Get

• Model catalog: Every model with descriptions
• Column details: Types, descriptions, tests
• Lineage graph: Visual DAG of all model dependencies
• Source freshness: When source data was last updated

Doc Blocks (reusable descriptions)

-- models/docs.md
{% docs user_id %}
The unique identifier for a user, sourced from the
application database. This is the primary key in dim_users.
{% enddocs %}

- name: user_id
  description: '{{ doc("user_id") }}'

Why Document? In 6 months, you won't remember why you wrote that transformation. Your teammates need documentation even sooner.

A strong portfolio demonstrates your skills better than any resume bullet point.

Portfolio Project Ideas

Beginner Portfolio (build all 3)

1. API-to-Database ETL Pipeline

   • Extract from a public API (GitHub, weather, crypto)
   • Transform with Python
   • Load to PostgreSQL
   • Schedule with cron or Airflow
   • Shows: ETL skills, Python, SQL, API handling

2. dbt Analytics Project

   • Take a public dataset (Kaggle, NYC taxi data)
   • Load raw data into PostgreSQL
   • Build staging and mart models with dbt
   • Add tests and documentation
   • Shows: SQL, data modeling, analytics engineering

3. End-to-End Data Pipeline

   • Combine extraction, transformation, loading, orchestration
   • Use Docker for all services
   • Deploy to cloud (even free tier)
   • Shows: Full pipeline skills, Docker, cloud basics

What Makes a Project Stand Out

• Clear README with architecture diagram
• Docker Compose for easy local setup
• Data quality tests
• CI/CD pipeline (GitHub Actions)
• Real-world data source (not just toy data)
• Proper error handling and logging

Tip: Quality over quantity. Three well-documented projects beat ten half-finished ones.

Your README is the first thing recruiters and hiring managers see.

README Template

# Project Name

One-line description of what this project does.

## Architecture

![Architecture Diagram](docs/architecture.png)

## Tech Stack
- Python 3.11
- PostgreSQL 16
- Apache Airflow 2.x
- Docker & Docker Compose
- dbt Core

## How It Works
1. **Extract**: Pulls data from [API name] every hour
2. **Transform**: Cleans and normalizes data with Python
3. **Load**: Upserts into PostgreSQL
4. **Transform (dbt)**: Builds analytical models
5. **Orchestrate**: Airflow manages the full workflow

## Quick Start
```bash
git clone https://github.com/you/project.git
cd project
docker compose up -d
# Pipeline runs automatically

Data Model

[ER diagram or description]

Lessons Learned

• [Something you learned building this]

## Architecture Diagram Tools

- **draw.io (diagrams.net)**: Free, web-based
- **Excalidraw**: Hand-drawn style, great for quick diagrams
- **Mermaid**: Text-based diagrams in Markdown
- **Lucidchart**: Professional diagrams

---

**Rule**: If someone can't understand your project from the README alone, it needs more work.

Your resume gets about 10 seconds of attention. Make every line count.

Resume Structure

1. Header: Name, email, LinkedIn, GitHub, portfolio site
2. Summary (optional): 2 lines max, mention key skills
3. Skills: Organized by category
4. Experience/Projects: 2-4 entries with impact metrics
5. Education: Keep brief

Skills Section Example

Languages:     Python, SQL, Bash
Databases:     PostgreSQL, MySQL
Tools:         Apache Airflow, dbt, Docker, Git
Cloud:         AWS (S3, RDS, IAM), GCP (BigQuery)
Concepts:      ETL/ELT, Data Modeling, Data Quality

Project Bullets (Action + Technology + Impact)

Good:

• Built Python ETL pipeline extracting data from 3 REST APIs into PostgreSQL, processing 50K+ records daily with 99.9% uptime
• Designed dbt transformation layer with 15 staging and 8 mart models, reducing analyst query time by 60%
• Containerized full data stack with Docker Compose (Airflow, PostgreSQL, dbt), enabling one-command local setup

Bad:

• Used Python and SQL
• Worked with databases
• Learned Airflow

Tips

• One page maximum
• Quantify everything possible
• Tailor skills to job description
• Link to GitHub projects
• Use a clean, ATS-friendly format (no fancy templates)

Reality Check: For entry-level roles, portfolio projects carry more weight than work experience. Invest time in them.

Data engineering interviews typically cover SQL, Python, system design, and behavioral questions.

Interview Format (typical)

1. Phone Screen (30 min): Behavioral + basic technical
2. Technical Screen (60 min): SQL + Python coding
3. System Design (60 min): Design a data pipeline/system
4. Onsite/Final (3-5 hours): Mix of all above

SQL Interview Topics

• JOINs (especially LEFT JOIN edge cases)
• Window functions (ROW_NUMBER, LAG/LEAD, running totals)
• CTEs and subqueries
• GROUP BY with HAVING
• Query optimization (explain plans, indexes)

Python Interview Topics

• Data structures (dicts, lists, sets)
• File I/O (CSV, JSON, Parquet)
• API calls and error handling
• Basic algorithms (deduplication, merging datasets)

System Design Topics

• Design a batch ETL pipeline
• Design a real-time data pipeline
• How would you handle late-arriving data?
• How would you ensure data quality?
• How would you scale a pipeline from 1GB to 1TB?

Common Behavioral Questions

• Tell me about a data pipeline you built
• How did you handle a data quality issue?
• Describe a time you had to learn a new technology quickly

Preparation Plan: Practice 2 SQL problems per day on LeetCode/HackerRank for 2-3 weeks before interviews.

Use these resources to prepare for data engineering interviews.

SQL Practice

• LeetCode Database: 200+ SQL problems by difficulty
• HackerRank SQL: Structured SQL challenges
• SQLZoo: Interactive SQL tutorial with exercises
• StrataScratch: Real interview questions from tech companies
• dataskew Interview Prep: Curated data engineering questions

System Design

• Designing Data-Intensive Applications (book by Martin Kleppmann): The definitive resource
• DataTalks.Club: Weekly events and community discussions

Mock Interviews

• Practice explaining your projects out loud (record yourself)
• Do mock interviews with friends or online platforms
• Time yourself on SQL problems (aim for 15-20 min per medium problem)

Interview Day Tips

1. Think out loud — explain your approach before coding
2. Ask clarifying questions (data volume, latency requirements, etc.)
3. Start with a simple solution, then optimize
4. Discuss trade-offs (cost vs performance, complexity vs simplicity)
5. Be honest about what you don't know

Final Advice: The best interview preparation is building real projects. Everything in this roadmap prepares you for interviews.