{"id":52,"date":"2025-10-26T18:31:16","date_gmt":"2025-10-26T18:31:16","guid":{"rendered":"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/?page_id=52"},"modified":"2025-10-26T18:41:12","modified_gmt":"2025-10-26T18:41:12","slug":"etl-reading-control-students-worksheet","status":"publish","type":"page","link":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/etl-reading-control-students-worksheet\/","title":{"rendered":"ETL Reading Control: Students&#8217; Worksheet"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>Worksheet \u2014 ETL Chapters 1\u20132<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Name(s): ____________________&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Team: ______&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date: ____________<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Part A \u2014 Readiness (10 min)<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li>1) Batch \/ Micro-batch \/ Streaming: definitions + one use case each (\u22642 lines).<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>2) Two windowing strategies + when to use them (\u22642 lines).<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>3) CDC: when it helps \/ when it\u2019s overkill (one line each).<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>4) Bronze (what must not happen) vs Silver (what typically happens).<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>5) One transformation + one update pattern that pair well; why (\u22642 lines).<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Part B \u2014 Case Application (35\u201345 min)<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">Assigned case (A\/B\/C): ____________<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u2022 Ingestion Mode &amp; Windowing <br><br>\u2014 Mode: ______\u00a0\u00a0Window: ______\u00a0\u00a0Why: ______________________<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u2022 Medallion Map <br><br>\u2014 Bronze: ______\u00a0\u00a0Silver: ______\u00a0\u00a0Gold: ______<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u2022 Transformations (\u22652) <br><br>\u2014 T1: ______\u00a0\u00a0T2: ______\u00a0\u00a0\u00a0\u00a0Update pattern: ______\u00a0\u00a0Why: ______<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u2022 CDC \/ SCD &amp; Late Data <br><br>\u2014 CDC: ______ Where: ______\u00a0\u00a0SCD: ______ Where: ______\u00a0\u00a0Late data: ______<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u2022 Data Quality &amp; Observability <br><br>\u2014 DQ: ______, ______\u00a0\u00a0\u00a0Observability: ______, ______<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Part C \u2014 Teach-back (90s): One trade-off I learned <\/h1>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>ETL Ch.1\u20132 \u2014 Case Pack (A, B, C)<\/strong><br>Use this pack during Part B (Apply the Highlights). Choose or assign one case per team. Each case requires selecting ingestion mode, mapping Bronze\u2192Silver\u2192Gold, choosing transformations and update patterns, and defining DQ\/observability.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><br><strong>Case A \u2014 IoT Right\u2011Time Dashboard<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>Context<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">IoT sensors emit events ~every 200ms (temperature, vibration, on\/off).<br>Stakeholders accept a 3\u20135 minute visibility lag on the dashboard (right\u2011time, not ultra real\u2011time).<br>Team and budget are small; ops simplicity is valued.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>Objectives<\/em><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Pick an ingestion mode that balances latency and complexity (micro\u2011batch vs streaming).<\/li>\n\n\n\n<li>Choose an appropriate windowing strategy (if any) for KPIs.<\/li>\n\n\n\n<li>Define Bronze\u2192Silver\u2192Gold with at least two transformations.<\/li>\n\n\n\n<li>Select an update pattern aligned with your transformations.<\/li>\n\n\n\n<li>Specify two DQ checks and two observability metrics.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Constraints &amp; Risks<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Compute\/ops costs must remain low; avoid fragile architectures.<\/li>\n\n\n\n<li>Bursty traffic: some sensors may spike; others go silent.<\/li>\n\n\n\n<li>Late\/duplicate events can occur; some devices clock\u2011drift.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Required Outputs (team deliverable)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ingestion choice (+ rationale) &amp; windowing.<\/li>\n\n\n\n<li>Bronze\u2192Silver\u2192Gold (verbs\/actions per layer).<\/li>\n\n\n\n<li>Two transformations (e.g., dedup, resample, anomaly flags) + one update pattern (e.g., upsert\/append).<\/li>\n\n\n\n<li>DQ checks (\u22652) and Observability metrics (\u22652).<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Hints &amp; Reminders<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Right\u2011time often = micro\u2011batch (e.g., 1\u20132 min) instead of full streaming.<\/li>\n\n\n\n<li>Tumbling windows for fixed slices; sliding windows for rolling KPIs; session windows for device sessions.<\/li>\n\n\n\n<li>Bronze: land raw + metadata; Silver: clean\/dedup\/standardize\/join; Gold: aggregates for the dashboard.<\/li>\n\n\n\n<li>Pair dedup\/late\u2011data handling with MERGE\/UPSERT in Silver; aggregate + append\/overwrite partition in Gold.<\/li>\n\n\n\n<li>Observe freshness (watermarks), end\u2011to\u2011end latency, and error\/retry rates.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Case B \u2014 HR Nightly CSV \u2192 Morning KPI<\/h2>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Context<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Secure HR system exports a CSV at 02:00 daily (employees, departments, contracts).<br>Management needs HR KPIs by 08:00 (turnover, new hires, headcount by org).<br>Data includes PII; occasional schema drift in the CSV is reported.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Objectives<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Choose an ingestion mode consistent with a daily export (likely batch).<\/li>\n\n\n\n<li>Place CDC\/SCD decisions correctly (where to merge changes; what history to keep).<\/li>\n\n\n\n<li>Map Bronze\u2192Silver\u2192Gold, including masking\/anonymization where needed.<\/li>\n\n\n\n<li>Select an update pattern for Silver\/Gold tables.<\/li>\n\n\n\n<li>Define two DQ checks and two observability metrics relevant to HR KPIs.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Constraints &amp; Risks<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Privacy &amp; compliance: PII minimisation\/anonymisation required outside secure zones.<\/li>\n\n\n\n<li>Schema drift (new\/renamed columns) may break pipelines.<\/li>\n\n\n\n<li>Backfills for retro corrections happen occasionally.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Required Outputs (team deliverable)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ingestion choice (+ rationale).<\/li>\n\n\n\n<li>Bronze\u2192Silver\u2192Gold with PII handling and CDC\/SCD note (if any).<\/li>\n\n\n\n<li>Two transformations (e.g., standardize org tree, dedup employees) + one update pattern.<\/li>\n\n\n\n<li>Two DQ checks and two observability metrics.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Hints &amp; Reminders<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Batch is appropriate; micro\u2011batch not necessary unless HR drops mid\u2011day files.<\/li>\n\n\n\n<li>CDC is useful if HR logs changes; else use daily MERGE in Silver with SCD Type 2 in dimensions you must historicize.<\/li>\n\n\n\n<li>Masking\/tokenization for PII beyond secure layers; document data contracts with HR.<\/li>\n\n\n\n<li>Observability: schema\u2011drift alerts, freshness, % invalid emails\/IDs, join conformance.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Case C \u2014 Social Content Moderation (Federated)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Context<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Event stream (posts\/reports) ingested from a federated platform; manual triage by moderators.<br>Moderation targets: route risky items within minutes; allow later label corrections by moderators.<br>Data volume variable; some instances may be unreliable or rate\u2011limited.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Objectives<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Choose ingestion mode + windowing to meet minute\u2011level triage SLA.<\/li>\n\n\n\n<li>Define late\u2011data and label\u2011correction handling (reprocessing\/update strategy).<\/li>\n\n\n\n<li>Map Bronze\u2192Silver\u2192Gold with transformations for triage\/aggregation\/analytics.<\/li>\n\n\n\n<li>Decide on CDC usage; select an update pattern that supports corrections.<\/li>\n\n\n\n<li>List two DQ checks and two observability metrics.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Constraints &amp; Risks<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Class imbalance (few positives); potential abuse\/spam; content toxicity handling.<\/li>\n\n\n\n<li>Data may arrive late\/out\u2011of\u2011order; labels can be revised after human review.<\/li>\n\n\n\n<li>Compute must remain reasonable for volunteer\u2011run instances.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Required Outputs (team deliverable)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ingestion choice (+ rationale) &amp; windowing.<\/li>\n\n\n\n<li>Bronze\u2192Silver\u2192Gold with two transformations (e.g., dedup, route\/tag, text cleaning, risk scoring).<\/li>\n\n\n\n<li>Update pattern supporting late corrections (e.g., MERGE) and\/or partition overwrite for specific windows.<\/li>\n\n\n\n<li>Two DQ checks and two observability metrics.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\">Hints &amp; Reminders<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Micro\u2011batch or streaming with short windows; triage latency target drives the choice.<\/li>\n\n\n\n<li>Keep Bronze raw + metadata; in Silver, dedup\/normalise and maintain a corrections log for label updates.<\/li>\n\n\n\n<li>Gold provides queues\/summaries for dashboards; use MERGE for corrected labels; watermark for late data.<\/li>\n\n\n\n<li>Observe freshness, moderation queue latency, % duplicates, schema drift\/error rates.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Worksheet \u2014 ETL Chapters 1\u20132 Name(s): ____________________&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Team: ______&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date: ____________ Part A \u2014 Readiness (10 min) Part B \u2014 Case Application (35\u201345 min) Assigned case (A\/B\/C): ____________ \u2022 Ingestion Mode &amp; Windowing \u2014 Mode: ______\u00a0\u00a0Window: ______\u00a0\u00a0Why: ______________________ \u2022 Medallion Map \u2014 &hellip; <a href=\"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/etl-reading-control-students-worksheet\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":11,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-52","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/52","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/comments?post=52"}],"version-history":[{"count":3,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/52\/revisions"}],"predecessor-version":[{"id":57,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/52\/revisions\/57"}],"wp:attachment":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/media?parent=52"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}