Case study

ETL Tracking Dashboard

Built a Python ETL workflow with a Streamlit tracking dashboard to monitor data freshness, load reliability, and pipeline run health.

Challenge

Business users needed trustworthy daily data, but failures and stale extracts were hard to detect quickly across multiple source feeds.

Solution

Designed modular ETL jobs in Python (extract, transform, load) with validation checkpoints, retry-safe processing, and run metadata captured for each stage. Exposed pipeline status, latency, and error signals in a Streamlit dashboard so teams could diagnose issues fast without digging through logs.

Impact

Reduced manual pipeline triage time, increased confidence in daily reporting, and made ETL reliability visible to both technical and non-technical stakeholders.

Stack

PythonETLPandasSQLStreamlit

ETL Overview

High-level control flow across sources, ordered execution, processing, outbound routing, and observability.

Job 1 — Student Career Coaches

Row flow: RAW → PROCESS → OUTBOUND with job-local supporting details beneath.

Career coaches ordered extract + join

for source in ["SF_Org1_Legacy", "SF_Org2_EdCloud", "Central_Campus_Data"]:
    extract_to_parquet(source=source, auth="jwt", out_dir="data/raw")

run_duckdb_join_plan(job="career_coaches", schema_dir="sql/schema")

Job 2 — Student Majors and Minors

Row flow: RAW → PROCESS → OUTBOUND with per-row PII and enrollment details.

Majors/minors validation + routing

df = run_duckdb_join_plan(job="majors_minors")
df = apply_pii_rules(df)
route_to_targets(df, targets=["SF_Org1_Legacy", "SF_Org2_EdCloud"], allow_csv_fallback=True)

Job 3 — Slate Grad Application CRM

Row flow: RAW → PROCESS → OUTBOUND for high-field mapping payloads.

Field coverage + replay contract

mapped = map_slate_payload(raw_df, spec="salesforce/grad_app_required_fields.json")
coverage = compute_field_coverage(mapped)
logger.event("field_map_coverage", coverage)
if coverage["required_missing"] > 0:
    queue_replay(target="SF_Org2_EdCloud")