Data Engineering and Automation
Open in the Padhaiko app6.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.
| Aspect | Data Engineering | Applied Data Science / Analytics |
|---|---|---|
| Core Focus | Reliable ingestion, schemas, ETL/ELT pipelines, storage, SLAs, automation | Statistical inference, EDA, feature engineering, predictive modeling, storytelling |
| Primary Artifacts | Production pipelines, partitioned warehouses/lakes, schedulers, alerts | Notebooks, trained models, evaluation metrics, dashboards, reports |
| Key Quality Goals | Throughput, fault tolerance, idempotency, data lineage, freshness | Predictive 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.)
Three Phases of an ETL Pipeline
- 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. - Transform: Clean missing values, enforce schema types, deduplicate primary keys, join reference dimensions, and compute derived aggregates or business KPIs.
- 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.
| Aspect | ETL (Extract–Transform–Load) | ELT (Extract–Load–Transform) |
|---|---|---|
| Where Transformation Runs | Python / Spark worker before loading target DB | Inside target Data Warehouse / Lakehouse SQL engine after loading |
| Raw Data Retention | Target stores cleaned data only (unless Bronze archive kept) | Raw data lands immediately in target for flexible future transforms |
| Best Fit | Sensitive PII scrubbing before storage, moderate Python workloads | Large-scale cloud warehouses (BigQuery, Snowflake, DuckDB/Parquet) |
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:
* * * * * /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 AMCrontab 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:
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)| Aspect | OS Cron (crontab / systemd timers) | Python schedule Library |
|---|---|---|
| Process Lifecycle | OS spawns fresh Python process per trigger; zero RAM used between runs | Requires a continuously running Python loop (while True) |
| Crash Recovery | OS daemon keeps running even if one Python run crashes | Unhandled exception stops loop unless wrapped in try/except |
| Best Use Case | Production batch jobs, daily/hourly ETL on Linux servers | Simple 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
- 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. - Error Handling and Retries with Exponential Backoff: Transient network or database lock errors (
Timeout,ConnectionError, HTTP429/503) should retry with increasing delays (wait = base * 2^attemptplus jitter), whereas permanent schema/validation errors must fail fast and alert operators. - 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).
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.
| Storage Option | Structure / Engine | Strengths | Best Pipeline Role |
|---|---|---|---|
| CSV / JSONL | Row-oriented text | Human-readable, universal interchange | Raw Bronze ingestion landing or small exports |
| Apache Parquet | Columnar compressed binary | Preserves dtypes, 5–10x smaller, column pruning & row-group min/max stats | Silver/Gold data lakes, analytical scans (Pandas, Polars, DuckDB) |
| SQLite / PostgreSQL | Indexed relational B-tree tables | ACID transactions, concurrent point lookups, JOINs, UPSERT | Operational state, watermarks, serving dashboards/APIs |
| Redis / In-Memory Cache | Key-value RAM store | Sub-millisecond reads, TTL expiration | Real-time ML feature lookup, API response caching |
Key Retrieval Optimization Strategies
- 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. - Indexing & Clustering: In SQL databases, create
INDEXes on frequentWHERE,JOIN, andORDER BYkeys (customer_id,created_at). - 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.
- 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. - Automated HTML Reports (
jinja2+ Plotly / Matplotlib): Render a clean Jinja2 HTML template injecting KPI tables, base64-encoded PNG plots, or interactive Plotlydivfragments (fig.to_html(full_html=False)), producing a self-contained email- or browser-ready report. - Automated PDF Reports (
weasyprintorreportlab): Convert styled HTML+CSS Jinja2 templates into crisp paginated PDFs viaweasyprint.HTML(string=html).write_pdf("weekly_report.pdf")(ideal for printable invoices/board decks) or construct programmatic PDF layouts withreportlab.
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.
[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
- Trigger (
cronwithflock):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. - Extract with Watermark & Retry: Read
last_tsfrom SQLiteetl_state, fetch new orders from REST API with exponential backoff (requests+timeout), and save raw JSONL tobronze/dt=YYYY-MM-DD/. - Validate & Transform: Separate malformed records (negative prices, missing
order_id) intoquarantine/dlq.jsonl; deduplicate, downcast dtypes (category,float32), and compute daily/regional KPIs. - Atomic Storage: Write
silver/orders/dt=YYYY-MM-DD/data.parquetatomically (temp file → rename), upsert aggregated KPIs into Golddaily_kpistable, and advancelast_tsonly after writes succeed. - 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 ifrows_in == 0or any unhandled exception occurs.
Worked Examples
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.
- Compute the delay after each of the 4 failed attempts (
attempt = 0, 1, 2, 3). - 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 sattempt = 1:1.5 * 2^1 = 1.5 * 2 = 3.0 sattempt = 2:1.5 * 2^2 = 1.5 * 4 = 6.0 sattempt = 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).
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.
6.2
A daily analytics pipeline stores 365 daily Parquet partitions, each 40 MB (dt=YYYY-MM-DD/part.parquet).
- 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).
- Write the
cronexpression 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 only7 * 40 MB = 280 MB = 0.28 GB. - Speedup / I/O reduction factor =
14,600 / 280 = 365 / 7 ≈ 52.14xless 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
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.
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 orUPSERTso re-runs never duplicate records. - Scheduling:
cronexpression with virtual-env absolute paths andflocklockfile (orschedulefor daemon loops). - Observability:
RotatingFileHandlerstructured 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.
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
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.
- Partition Overwrite: Write atomically to
orders/dt=2026-11-18/(temp path → replace), cleanly overwriting any partial prior run. - SQL
UPSERT(INSERT ... ON CONFLICT DO UPDATE) orDELETE + INSERTinside one transaction: Guarantees duplicate primary keys update cleanly instead of creating double rows.
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.
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 = 72runs 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.
*/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.
Explain the role of a Dead-Letter Queue (DLQ), RotatingFileHandler, and data quality assertions in a production data pipeline.
Three Pipeline Reliability Mechanisms:
- 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.
RotatingFileHandler: Caps log file size (maxBytes) and keeps a bounded count of archives (backupCount) so unattended pipeline logs never fill the server disk.- 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.
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.
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,500rows. - Percentage reduction =
(487,500 / 500,000) * 100 = 97.5%.
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.
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:
- Excel (
.xlsx): Useopenpyxl(orxlsxwriter) withpd.ExcelWriterto write multi-tab KPI sheets, freeze headers (ws.freeze_panes), format currency/percent columns, and add charts. - HTML: Combine
Jinja2templates with Matplotlib base64 images or Plotlyfig.to_html(full_html=False)to build self-contained interactive browser/email reports. - PDF: Render styled Jinja2 HTML+CSS directly into paginated PDFs using
WeasyPrint(HTML(string=...).write_pdf(...)) or build programmatic vector layouts withReportLab.
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).