PadhaikoAll subjects

Data Engineering and Automation

Advanced Python Programming for Data Science (ENCT 325)

Open in the Padhaiko app

6.1 Overview of data engineering in applied data science

In real-world organizations, machine learning models and dashboards are only as reliable as the upstream infrastructure feeding them clean, timely data. Data engineering builds, tests, schedules, and monitors the automated systems that collect, transform, validate, and deliver data reliably to data scientists and analysts.

AspectData EngineeringApplied Data Science / Analytics
Core FocusReliable ingestion, schemas, ETL/ELT pipelines, storage, SLAs, automationStatistical inference, EDA, feature engineering, predictive modeling, storytelling
Primary ArtifactsProduction pipelines, partitioned warehouses/lakes, schedulers, alertsNotebooks, trained models, evaluation metrics, dashboards, reports
Key Quality GoalsThroughput, fault tolerance, idempotency, data lineage, freshnessPredictive accuracy, causal insight, interpretability, business impact

Without sound data engineering, data scientists spend 80% of their time manually fixing broken CSVs, fighting silent schema changes, and re-running fragile ad-hoc scripts.


6.2 Designing and implementing ETL pipelines

An ETL (Extract, Transform, Load) pipeline pulls raw data from operational sources, cleans and conforms it in memory or a staging engine, and writes curated tables into an analytical destination. (By contrast, ELT loads raw data into a cloud data warehouse first and executes SQL transformations inside the warehouse.)

Definition

Three Phases of an ETL Pipeline

  1. Extract: Pull data from CSV/JSON files, REST APIs, or OLTP databases—preferably incrementally using a high-watermark timestamp or ID (WHERE updated_at > last_run) rather than re-reading historical tables every hour.
  2. Transform: Clean missing values, enforce schema types, deduplicate primary keys, join reference dimensions, and compute derived aggregates or business KPIs.
  3. Load: Persist curated output into target storage (Parquet partitions, PostgreSQL/SQLite tables, or a data warehouse) using atomic or idempotent writes (staging table swap, UPSERT, or partition overwrite) so re-running a failed job never duplicates rows.
AspectETL (Extract–Transform–Load)ELT (Extract–Load–Transform)
Where Transformation RunsPython / Spark worker before loading target DBInside target Data Warehouse / Lakehouse SQL engine after loading
Raw Data RetentionTarget stores cleaned data only (unless Bronze archive kept)Raw data lands immediately in target for flexible future transforms
Best FitSensitive PII scrubbing before storage, moderate Python workloadsLarge-scale cloud warehouses (BigQuery, Snowflake, DuckDB/Parquet)
Python
import sqlite3
import pandas as pd


def extract_orders(csv_path: str) -> pd.DataFrame:
    return pd.read_csv(csv_path, parse_dates=["order_date"])


def transform_orders(df: pd.DataFrame) -> pd.DataFrame:
    out = df.dropna(subset=["order_id", "amount"]).drop_duplicates(subset=["order_id"]).copy()
    out = out[out["amount"] > 0]
    out["order_day"] = out["order_date"].dt.strftime("%Y-%m-%d")
    return out.groupby("order_day", as_index=False).agg(
        orders=("order_id", "count"),
        revenue=("amount", "sum"),
    )


def load_daily_summary(summary_df: pd.DataFrame, db_path: str) -> None:
    with sqlite3.connect(db_path) as conn:
        summary_df.to_sql("daily_sales", conn, if_exists="replace", index=False)

6.3 Automating workflows with schedulers (CRON, schedule)

Manual script execution does not scale. Schedulers run Python pipelines automatically on fixed calendars, intervals, or dependency graphs.

System-Level Scheduling with Linux cron

A crontab entry defines when the OS cron daemon runs a command using five time fields:

Crontab Five-Field SyntaxText
*  *  *  *  *   /path/to/.venv/bin/python /opt/pipelines/run_etl.py >> /var/log/etl.log 2>&1
|  |  |  |  |
|  |  |  |  +---- Day of week (0-7, Sunday=0 or 7)
|  |  |  +------- Month (1-12)
|  |  +---------- Day of month (1-31)
|  +------------- Hour (0-23)
+---------------- Minute (0-59)

Common examples:
  0 2 * * *      -> Every day at 02:00 AM
  */15 * * * *   -> Every 15 minutes
  30 8 * * 1-5   -> Weekdays (Mon-Fri) at 08:30 AM

Crontab Best Practices: Always specify absolute paths to both the virtual environment Python binary and the script, redirect stdout/stderr (>> logfile 2>&1), and guard against overlapping runs with flock if a job might exceed its interval.

In-Process Python Scheduling with schedule

The lightweight third-party schedule library expresses readable job cadences inside a long-running Python worker process:

Python
import time
import schedule


def run_hourly_ingest():
    print("Running incremental API ingest...")


schedule.every(1).hours.do(run_hourly_ingest)
schedule.every().day.at("06:30").do(run_hourly_ingest)

# Event loop inside a worker daemon
while True:
    schedule.run_pending()
    time.sleep(30)
AspectOS Cron (crontab / systemd timers)Python schedule Library
Process LifecycleOS spawns fresh Python process per trigger; zero RAM used between runsRequires a continuously running Python loop (while True)
Crash RecoveryOS daemon keeps running even if one Python run crashesUnhandled exception stops loop unless wrapped in try/except
Best Use CaseProduction batch jobs, daily/hourly ETL on Linux serversSimple local workers, prototypes, or single-container microservices

For complex multi-task DAGs (Directed Acyclic Graphs with retries, backfills, and task dependencies), production teams step up to workflow orchestrators such as Apache Airflow, Prefect, or Dagster.


6.4 Logging, monitoring, and error handling in pipelines

Automated jobs fail at 3:00 AM without anyone watching the terminal. Pipelines require structured observability and defensive recovery so failures are diagnosed in minutes—not discovered days later by angry stakeholders.

Pipeline Observability Pillars

  1. Structured Logging (logging + RotatingFileHandler): Record run ID, stage name, row counts read/dropped/written, and elapsed duration in machine-parseable log lines; rotate log files so disk space never fills up.
  2. Error Handling and Retries with Exponential Backoff: Transient network or database lock errors (Timeout, ConnectionError, HTTP 429/503) should retry with increasing delays (wait = base * 2^attempt plus jitter), whereas permanent schema/validation errors must fail fast and alert operators.
  3. Monitoring, SLAs, and Dead-Letter Queues (DLQ):
    • Data Quality Checks: Assert row counts > 0, null percentages below threshold, and primary-key uniqueness before overwriting Gold tables.
    • Dead-Letter Queue (DLQ): Quarantine malformed rows to an errors/ table or JSONL file instead of crashing an entire 1-million-row batch over 3 corrupt records.
    • Alerting: Emit webhooks (Slack/Teams/email) or exit codes on failure and track freshness SLAs (e.g., flag if today's partition did not arrive by 07:00).
Python
import logging
import time
from logging.handlers import RotatingFileHandler

logger = logging.getLogger("pipeline")
logger.setLevel(logging.INFO)
handler = RotatingFileHandler("pipeline.log", maxBytes=5_000_000, backupCount=3)
handler.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(message)s"))
logger.addHandler(handler)


def fetch_with_retry(fn, max_retries: int = 3, base_delay: float = 1.0):
    for attempt in range(max_retries):
        try:
            return fn()
        except ConnectionError as exc:
            delay = base_delay * (2 ** attempt)
            logger.warning("Attempt %d failed (%s); retrying in %.1fs", attempt + 1, exc, delay)
            time.sleep(delay)
    logger.error("All %d attempts failed", max_retries)
    raise RuntimeError("Upstream fetch exhausted retries")

6.5 Data storage and retrieval strategies for pipelines

Choosing the right storage format and layout determines query latency, cloud/disk cost, and pipeline reliability.

Pipeline Storage Formats and Retrieval Strategies
Storage OptionStructure / EngineStrengthsBest Pipeline Role
CSV / JSONLRow-oriented textHuman-readable, universal interchangeRaw Bronze ingestion landing or small exports
Apache ParquetColumnar compressed binaryPreserves dtypes, 5–10x smaller, column pruning & row-group min/max statsSilver/Gold data lakes, analytical scans (Pandas, Polars, DuckDB)
SQLite / PostgreSQLIndexed relational B-tree tablesACID transactions, concurrent point lookups, JOINs, UPSERTOperational state, watermarks, serving dashboards/APIs
Redis / In-Memory CacheKey-value RAM storeSub-millisecond reads, TTL expirationReal-time ML feature lookup, API response caching

Key Retrieval Optimization Strategies

  1. Partitioning (dt=2026-11-18/): Storing Parquet files in date- or region-partitioned directories enables partition pruning—a query for November skips scanning January–October folders entirely.
  2. Indexing & Clustering: In SQL databases, create INDEXes on frequent WHERE, JOIN, and ORDER BY keys (customer_id, created_at).
  3. Incremental Watermarks & Idempotent Upserts: Maintain a pipeline_runs(job_name, last_watermark) metadata table so each run retrieves only new/updated records since the last successful commit.

6.6 Automated report generation (Excel, HTML, PDF)

Pipelines often conclude by delivering formatted executive reports directly to non-technical recipients.

  1. Automated Excel Reports (openpyxl / xlsxwriter + pandas.ExcelWriter): Write multi-sheet workbooks (Summary, Regional_Breakdown, Anomalies), freeze header panes (ws.freeze_panes = "A2"), apply currency/percentage number formats, auto-fit column widths, and embed native Excel charts or conditional formatting.
  2. Automated HTML Reports (jinja2 + Plotly / Matplotlib): Render a clean Jinja2 HTML template injecting KPI tables, base64-encoded PNG plots, or interactive Plotly div fragments (fig.to_html(full_html=False)), producing a self-contained email- or browser-ready report.
  3. Automated PDF Reports (weasyprint or reportlab): Convert styled HTML+CSS Jinja2 templates into crisp paginated PDFs via weasyprint.HTML(string=html).write_pdf("weekly_report.pdf") (ideal for printable invoices/board decks) or construct programmatic PDF layouts with reportlab.
Python
import pandas as pd

summary = pd.DataFrame({"region": ["East", "West"], "revenue": [125000, 184000]})
with pd.ExcelWriter("weekly_report.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="KPI_Summary", index=False)
    ws = writer.sheets["KPI_Summary"]
    ws.freeze_panes = "A2"

6.7 Case study: End-to-end automated analytics pipeline

Bringing Chapters 1–6 together, consider an automated Daily Retail Analytics Pipeline that runs unattended every morning at 06:00 AM.

End-to-End Automated Daily Analytics PipelineText
[06:00 cron] ---> [1. Extract (API/DB)] ---> [2. Validate & Quarantine DLQ]
                        |                               |
                        v                               v
             [3. Transform (Pandas)] ---> [4. Store Silver Parquet + SQLite Gold]
                                                        |
                                                        v
             [6. Log Metrics + Alert] <--- [5. Generate Excel & HTML/PDF Report]

Pipeline Stages in Production

  1. Trigger (cron with flock): 0 6 * * * /usr/bin/flock -n /tmp/retail_etl.lock /opt/retail/.venv/bin/python -m retail_etl.run >> /var/log/retail_etl.log 2>&1.
  2. Extract with Watermark & Retry: Read last_ts from SQLite etl_state, fetch new orders from REST API with exponential backoff (requests + timeout), and save raw JSONL to bronze/dt=YYYY-MM-DD/.
  3. Validate & Transform: Separate malformed records (negative prices, missing order_id) into quarantine/dlq.jsonl; deduplicate, downcast dtypes (category, float32), and compute daily/regional KPIs.
  4. Atomic Storage: Write silver/orders/dt=YYYY-MM-DD/data.parquet atomically (temp file → rename), upsert aggregated KPIs into Gold daily_kpis table, and advance last_ts only after writes succeed.
  5. Automated Reporting & Observability: Generate daily_report_YYYY-MM-DD.xlsx + daily_report.html (embedded Seaborn/Plotly visuals), log "SUCCESS rows_in=... rows_dlq=... duration_s=...", and send an alert notification if rows_in == 0 or any unhandled exception occurs.

Worked Examples

Example 6.1

6.1

An API ingestion step uses exponential backoff with base_delay = 1.5 s (delay = base_delay * 2^attempt for attempt = 0, 1, 2, 3) before failing after 4 failed retries.

  1. Compute the delay after each of the 4 failed attempts (attempt = 0, 1, 2, 3).
  2. Compute the total cumulative wait time across all 4 backoff sleeps.

Computing Individual Backoff Delays:

  • attempt = 0: 1.5 * 2^0 = 1.5 * 1 = 1.5 s
  • attempt = 1: 1.5 * 2^1 = 1.5 * 2 = 3.0 s
  • attempt = 2: 1.5 * 2^2 = 1.5 * 4 = 6.0 s
  • attempt = 3: 1.5 * 2^3 = 1.5 * 8 = 12.0 s

Summing Total Wait Time: total_wait = 1.5 + 3.0 + 6.0 + 12.0 = 22.5 s (equivalent to 1.5 * (2^4 - 1) = 1.5 * 15 = 22.5 s).

Answer:

Individual delays are 1.5 s, 3.0 s, 6.0 s, and 12.0 s, and the total cumulative backoff wait time is 22.5 s.

Example 6.2

6.2

A daily analytics pipeline stores 365 daily Parquet partitions, each 40 MB (dt=YYYY-MM-DD/part.parquet).

  1. Compare the bytes scanned to answer a 7-day report query without partitioning (scanning all 365 files) versus with date partition pruning (reading only 7 daily folders).
  2. Write the cron expression that runs this weekly report every Monday at 07:30 AM.

Comparing Data Scanned (Full Scan vs. Partition Pruning):

  • Without partitioning: Must scan all 365 * 40 MB = 14,600 MB = 14.6 GB.
  • With date partition pruning (dt=...): Reads only 7 * 40 MB = 280 MB = 0.28 GB.
  • Speedup / I/O reduction factor = 14,600 / 280 = 365 / 7 ≈ 52.14x less data read from disk.

Writing the Crontab Schedule: Minute = 30, Hour = 7, Day-of-month = *, Month = *, Day-of-week = 1 (Monday): 30 7 * * 1 /opt/etl/.venv/bin/python /opt/etl/weekly_report.py >> /var/log/weekly_report.log 2>&1

Answer:

Partition pruning cuts data scanned from 14.6 GB (14,600 MB) to 0.28 GB (280 MB), a 52.14x I/O reduction. The Monday 07:30 AM cron schedule is 30 7 * * 1.

Example 6.3

6.3

How should a student answer an exam question asking: "Design an end-to-end automated Python analytics pipeline, covering ETL design, scheduling, logging/error handling, storage, and automated report delivery"?

Drawing the Pipeline Architecture Diagram: Open with a clear 5-stage ASCII block diagram: Scheduler (cron/flock) → Extract (incremental watermark + retries) → Transform & Validate (DLQ + Pandas) → Atomic Storage (Bronze JSONL / Silver Parquet / Gold SQLite) → Automated Reporting (openpyxl/Jinja2+Plotly PDF) + Logging/Alerts.

Detailing Each Syllabus Requirement Concisely:

  • ETL & Idempotency: Incremental extraction by updated_at, partition overwrites or UPSERT so re-runs never duplicate records.
  • Scheduling: cron expression with virtual-env absolute paths and flock lockfile (or schedule for daemon loops).
  • Observability: RotatingFileHandler structured logs, exponential backoff for transient network failures, DLQ quarantine for bad rows, and webhook alerting on SLA/quality failure.
  • Reporting: Export formatted multi-sheet Excel (openpyxl) and standalone HTML/PDF executive summaries automatically.
Answer:

Present the 5-stage pipeline diagram and systematically cover incremental/idempotent ETL, cron + flock scheduling, logging + exponential-backoff retries + DLQ error handling, partitioned Parquet + SQL storage, and automated openpyxl/HTML/PDF report generation.


Exercises

Practice Exercises — Data Engineering and Automation

Question 1

Distinguish between ETL and ELT, and explain what idempotency means in an ETL Load stage with two concrete implementation techniques.

ETL vs. ELT:

  • ETL (Extract–Transform–Load): Transforms and cleans data in Python/Spark workers before writing curated tables to target storage.
  • ELT (Extract–Load–Transform): Lands raw data directly into a warehouse/lakehouse first and performs transformations inside the target engine using SQL.

Idempotency and Two Techniques: A pipeline stage is idempotent if running it multiple times for the same batch or date produces the exact same target state without duplicating rows.

  1. Partition Overwrite: Write atomically to orders/dt=2026-11-18/ (temp path → replace), cleanly overwriting any partial prior run.
  2. SQL UPSERT (INSERT ... ON CONFLICT DO UPDATE) or DELETE + INSERT inside one transaction: Guarantees duplicate primary keys update cleanly instead of creating double rows.
Answer:

ETL transforms data before loading while ELT loads raw data first and transforms inside the warehouse. An idempotent Load produces identical state on re-runs, achieved via atomic partition overwrites or transactional SQL UPSERT / DELETE + INSERT.

Question 2

Translate the cron expression */20 * * * 1-5 into plain English, compute how many times the job runs in a single active 24-hour weekday, and compare Linux cron with Python's schedule library.

Decoding */20 * * * 1-5 and Run Count:

  • */20 = every 20 minutes (at minutes :00, :20, :40 — 3 runs per hour).
  • * * = every hour of every day of month / month.
  • 1-5 = Monday through Friday only.
  • Runs per active weekday = 24 hours * (60 / 20) runs/hour = 24 * 3 = 72 runs per weekday.

cron vs. schedule: cron is OS-managed, starts a fresh process per run (zero idle RAM, survives individual script crashes), and pairs cleanly with flock. Python's schedule runs inside an always-on while True: Python loop, making it easy for prototypes but requiring external process supervision (systemd/Docker) and exception handling so a single crash does not stop future runs.

Answer:

*/20 * * * 1-5 runs every 20 minutes on weekdays (Monday–Friday), executing 72 times per weekday. cron launches isolated OS-scheduled processes, whereas schedule relies on an active Python event loop.

Question 3

Explain the role of a Dead-Letter Queue (DLQ), RotatingFileHandler, and data quality assertions in a production data pipeline.

Three Pipeline Reliability Mechanisms:

  1. Dead-Letter Queue (DLQ): Routes individual corrupt or schema-violating rows (e.g., invalid date string, negative quantity) into a separate quarantine file/table with error reasons, letting valid rows finish processing instead of failing an entire batch.
  2. RotatingFileHandler: Caps log file size (maxBytes) and keeps a bounded count of archives (backupCount) so unattended pipeline logs never fill the server disk.
  3. Data Quality Assertions: Pre-load checks verifying len(df) > 0, null rate thresholds, and unique primary keys so corrupted upstream feeds never overwrite clean Gold tables.
Answer:

A DLQ isolates malformed records without halting valid rows; RotatingFileHandler prevents logs from exhausting disk space via size-capped rotation; and data quality assertions block empty or corrupted batches from polluting Gold storage.

Question 4

Compare Apache Parquet, CSV, and SQLite/PostgreSQL for pipeline storage. If an incremental watermark query fetches only 12,500 newly updated rows instead of re-ingesting the full 500,000-row table, compute the percentage reduction in rows transferred.

Storage Comparison:

  • CSV: Text-based, slow to parse, loses dtypes; best only for raw exchange.
  • Apache Parquet: Columnar compressed binary preserving dtypes with column/partition pruning; best for analytical Silver/Gold lakes.
  • SQLite/PostgreSQL: Indexed ACID row stores supporting JOINs, UPSERTs, and concurrent lookups; best for pipeline state and application serving.

Incremental Watermark Reduction:

  • Rows avoided = 500,000 - 12,500 = 487,500 rows.
  • Percentage reduction = (487,500 / 500,000) * 100 = 97.5%.
Answer:

CSV suits simple text interchange, Parquet suits compressed columnar analytical storage, and SQLite/PostgreSQL suit indexed ACID queries and pipeline state. Incremental extraction reduces rows transferred from 500,000 to 12,500, a 97.5% reduction.

Question 5

Describe how Python automates Excel, HTML, and PDF report generation at the end of a data pipeline, naming at least one Python library for each format.

Automated Reporting Libraries and Workflow:

  1. Excel (.xlsx): Use openpyxl (or xlsxwriter) with pd.ExcelWriter to write multi-tab KPI sheets, freeze headers (ws.freeze_panes), format currency/percent columns, and add charts.
  2. HTML: Combine Jinja2 templates with Matplotlib base64 images or Plotly fig.to_html(full_html=False) to build self-contained interactive browser/email reports.
  3. PDF: Render styled Jinja2 HTML+CSS directly into paginated PDFs using WeasyPrint (HTML(string=...).write_pdf(...)) or build programmatic vector layouts with ReportLab.
Answer:

Automate Excel reports with openpyxl/xlsxwriter via pd.ExcelWriter; generate HTML reports by injecting KPIs and Matplotlib/Plotly charts into Jinja2 templates; and produce PDF reports from HTML+CSS via WeasyPrint (or programmatically via ReportLab).

← Data Visualization and Storytelling