Worksheet — ETL Chapters 1–2

Name(s): ____________________     Team: ______     Date: ____________

Part A — Readiness (10 min)

• 1) Batch / Micro-batch / Streaming: definitions + one use case each (≤2 lines).

• 2) Two windowing strategies + when to use them (≤2 lines).

• 3) CDC: when it helps / when it’s overkill (one line each).

• 4) Bronze (what must not happen) vs Silver (what typically happens).

• 5) One transformation + one update pattern that pair well; why (≤2 lines).

Part B — Case Application (35–45 min)

Assigned case (A/B/C): ____________

• Ingestion Mode & Windowing

— Mode: ______  Window: ______  Why: ______________________

• Medallion Map

— Bronze: ______  Silver: ______  Gold: ______

• Transformations (≥2)

— T1: ______  T2: ______    Update pattern: ______  Why: ______

• CDC / SCD & Late Data

— CDC: ______ Where: ______  SCD: ______ Where: ______  Late data: ______

• Data Quality & Observability

— DQ: ______, ______   Observability: ______, ______

Part C — Teach-back (90s): One trade-off I learned

ETL Ch.1–2 — Case Pack (A, B, C)
Use this pack during Part B (Apply the Highlights). Choose or assign one case per team. Each case requires selecting ingestion mode, mapping Bronze→Silver→Gold, choosing transformations and update patterns, and defining DQ/observability.

Case A — IoT Right‑Time Dashboard

Context

IoT sensors emit events ~every 200ms (temperature, vibration, on/off).
Stakeholders accept a 3–5 minute visibility lag on the dashboard (right‑time, not ultra real‑time).
Team and budget are small; ops simplicity is valued.

Objectives

• Pick an ingestion mode that balances latency and complexity (micro‑batch vs streaming).
• Choose an appropriate windowing strategy (if any) for KPIs.
• Define Bronze→Silver→Gold with at least two transformations.
• Select an update pattern aligned with your transformations.
• Specify two DQ checks and two observability metrics.

Constraints & Risks

• Compute/ops costs must remain low; avoid fragile architectures.
• Bursty traffic: some sensors may spike; others go silent.
• Late/duplicate events can occur; some devices clock‑drift.

Required Outputs (team deliverable)

• Ingestion choice (+ rationale) & windowing.
• Bronze→Silver→Gold (verbs/actions per layer).
• Two transformations (e.g., dedup, resample, anomaly flags) + one update pattern (e.g., upsert/append).
• DQ checks (≥2) and Observability metrics (≥2).

Hints & Reminders

• Right‑time often = micro‑batch (e.g., 1–2 min) instead of full streaming.
• Tumbling windows for fixed slices; sliding windows for rolling KPIs; session windows for device sessions.
• Bronze: land raw + metadata; Silver: clean/dedup/standardize/join; Gold: aggregates for the dashboard.
• Pair dedup/late‑data handling with MERGE/UPSERT in Silver; aggregate + append/overwrite partition in Gold.
• Observe freshness (watermarks), end‑to‑end latency, and error/retry rates.

Case B — HR Nightly CSV → Morning KPI

Context

Secure HR system exports a CSV at 02:00 daily (employees, departments, contracts).
Management needs HR KPIs by 08:00 (turnover, new hires, headcount by org).
Data includes PII; occasional schema drift in the CSV is reported.

Objectives

• Choose an ingestion mode consistent with a daily export (likely batch).
• Place CDC/SCD decisions correctly (where to merge changes; what history to keep).
• Map Bronze→Silver→Gold, including masking/anonymization where needed.
• Select an update pattern for Silver/Gold tables.
• Define two DQ checks and two observability metrics relevant to HR KPIs.

Constraints & Risks

• Privacy & compliance: PII minimisation/anonymisation required outside secure zones.
• Schema drift (new/renamed columns) may break pipelines.
• Backfills for retro corrections happen occasionally.

Required Outputs (team deliverable)

• Ingestion choice (+ rationale).
• Bronze→Silver→Gold with PII handling and CDC/SCD note (if any).
• Two transformations (e.g., standardize org tree, dedup employees) + one update pattern.
• Two DQ checks and two observability metrics.

Hints & Reminders

• Batch is appropriate; micro‑batch not necessary unless HR drops mid‑day files.
• CDC is useful if HR logs changes; else use daily MERGE in Silver with SCD Type 2 in dimensions you must historicize.
• Masking/tokenization for PII beyond secure layers; document data contracts with HR.
• Observability: schema‑drift alerts, freshness, % invalid emails/IDs, join conformance.

Case C — Social Content Moderation (Federated)

Context

Event stream (posts/reports) ingested from a federated platform; manual triage by moderators.
Moderation targets: route risky items within minutes; allow later label corrections by moderators.
Data volume variable; some instances may be unreliable or rate‑limited.

Objectives

• Choose ingestion mode + windowing to meet minute‑level triage SLA.
• Define late‑data and label‑correction handling (reprocessing/update strategy).
• Map Bronze→Silver→Gold with transformations for triage/aggregation/analytics.
• Decide on CDC usage; select an update pattern that supports corrections.
• List two DQ checks and two observability metrics.

Constraints & Risks

• Class imbalance (few positives); potential abuse/spam; content toxicity handling.
• Data may arrive late/out‑of‑order; labels can be revised after human review.
• Compute must remain reasonable for volunteer‑run instances.

Required Outputs (team deliverable)

• Ingestion choice (+ rationale) & windowing.
• Bronze→Silver→Gold with two transformations (e.g., dedup, route/tag, text cleaning, risk scoring).
• Update pattern supporting late corrections (e.g., MERGE) and/or partition overwrite for specific windows.
• Two DQ checks and two observability metrics.

Hints & Reminders

• Micro‑batch or streaming with short windows; triage latency target drives the choice.
• Keep Bronze raw + metadata; in Silver, dedup/normalise and maintain a corrections log for label updates.
• Gold provides queues/summaries for dashboards; use MERGE for corrected labels; watermark for late data.
• Observe freshness, moderation queue latency, % duplicates, schema drift/error rates.