Build the AdventureWorks DW ETL Worker Service with .NET 10 + GitHub Copilot
Replace legacy SSIS with a .NET 10 Worker Service — BackgroundService + change-tracking + DQ rules + Copilot Agent-mode loop.
About This Course
Replace legacy SSIS packages with a modern .NET 10 Worker Service that incrementally loads AdventureWorksDW from AdventureWorks. Learn the BackgroundService lifecycle + graceful shutdown + cancellation-token discipline, the SQL Server change-tracking + watermark + idempotent-upsert pattern for incremental loads, data-quality rule design + dead-letter routing without a BI tool, and the Copilot Agent-mode "run-fix-rerun" loop that turns flaky-pipeline debugging from a half-day job into a fifteen-minute conversation. By the end you will have a production-shaped Worker Service with structured logs, correlation IDs, DQ rules, dead-letter handling, and a tested recovery path.
Course Curriculum
6 Lessons
Worker services + hosted services in .NET 10
Learn the .NET 10 Worker Service pattern: BackgroundService + IHostedService, the host's startup + shutdown lifecycle, graceful-shutdown windows, the StopToken that signals orderly cancellation, and the cancellation-token discipline that keeps long-running loops responsive to shutdown. By the end of this lesson you will be able to explain the four BackgroundService lifecycle events, name the cancellation-token mistakes that produce force-killed processes, and judge when to reach for Worker Service over a Timer Trigger function or a scheduled job.
Scaffold the AdventureWorks DW ETL shell with Copilot Agent mode - Lab Exercises
Note: This lab pre-provisions a Windows VM and the Halcyon.Adventure.Etl starter files at start — allow up to 10 minutes for the environment to become ready before beginning the exercises.
Scaffold a .NET 10 Worker Service from the Microsoft worker template, wire the Options pattern + Serilog + ASP.NET Core health checks, prompt Copilot Agent mode to lay down the ETL shell structure, and verify the service runs with cancellation-token discipline from the first commit. By the end you will have an idle-but-correct ETL host ready for the incremental-load logic to land in Lesson 3.
Incremental load with SQL change tracking + watermark + idempotent upserts - Lab Exercises
Note: This lab pre-provisions a Windows VM and the Halcyon.Adventure.Etl starter files at start — allow up to 10 minutes for the environment to become ready before beginning the exercises.
Replace a full-table reload with an incremental load. Enable SQL Server change tracking on the source Sales.SalesOrderHeader + Sales.Customer tables, query CHANGETABLE(CHANGES, ...) for since-last-run deltas, persist a watermark table in the DW to track the last-processed version, and land idempotent upsert SQL that is safe to re-run on partial failure. By the end you will have an ETL leg that moves only the deltas each run + survives mid-run crashes without duplicates.
Data-quality rules + dead-letter patterns for ETL pipelines
Learn the data-quality pattern for ETL pipelines without a BI tool: rule-as-code (FluentValidation style) vs rule-as-DSL, where to run rules in the pipeline (staging layer before fact write), the fail-row vs fail-batch decision, and the dead-letter table + alerting hook pattern that preserves bad data for triage instead of silently discarding it. By the end of this lesson you will be able to design a DQ rule set that distinguishes shape errors from data errors, choose rule placement based on the pipeline stage, and judge when to fail-row vs fail-batch.
Add FluentValidation DQ rules + a dead-letter queue to the ETL - Lab Exercises
Note: This lab pre-provisions a Windows VM and the Halcyon.Adventure.Etl starter files at start — allow up to 10 minutes for the environment to become ready before beginning the exercises.
Add FluentValidation-style data-quality rules at the ETL's staging layer, route failing rows to a dead-letter SQL table with the original payload + the rule that failed + the timestamp, wire an alerting hook that posts a summary line to the structured log when the dead-letter table grows past a threshold per run. By the end you will have DQ-protected loads where bad rows preserve their payload for triage instead of poisoning the warehouse.
Observability + the Copilot Agent-mode run-fix-rerun loop on a flaky load - Lab Exercises
Note: This lab pre-provisions a Windows VM and the Halcyon.Adventure.Etl starter files at start — allow up to 10 minutes for the environment to become ready before beginning the exercises.
Add structured logging + correlation IDs that trace a single ETL run across all log lines. Reproduce a pre-staged flaky load that fails on roughly 1 in 3 runs for a specific root cause (a race between two parallel load legs on a shared staging table). Use the Copilot Agent-mode "see failure, propose fix, re-run" loop to iterate against the structured logs until the loop lands on the root cause + the targeted fix. By the end you will have the observability baseline + a tested recovery path that transfers to production flakiness debugging.