Case Study 22-1: Priya's Monday Report Goes Fully Automated

The Situation

It is Sunday evening at Acme Corp. Priya Okonkwo's laptop is closed. She is not thinking about work.

At 7:45 AM Monday morning, her Python pipeline will wake up, pull this week's sales data, calculate every metric Sandra Chen uses in the 9 AM staff meeting, generate a formatted Excel workbook with four properly formatted sheets, and deliver it to Sandra's inbox — complete with a summary in the email body.

This does not happen because Priya remembered to run a script. It happens because six weeks ago, she built a system that remembers for her.

This is how that system was built.

Background

By the end of Chapter 21, Priya had a working report_pipeline.py that could run manually. Sandra had asked her to automate it. Marcus Webb, Acme's IT manager, had given Priya a dedicated Windows server (a modest VM on Acme's internal infrastructure) to deploy production scripts.

Before tackling scheduling, Priya did three things:

  1. Made the pipeline robust — added comprehensive error handling, logging to a file, and a failure notification function
  2. Moved credentials to environment variables — the SMTP password and any API keys now live in the server's system environment, not in the code
  3. Tested the pipeline manually three times — once with the real data, once with a missing data file (to verify the error handling), once with the SMTP server disconnected (to verify the report saves locally even when email fails)

Only after all three passed did she start thinking about scheduling.

The Decision: Which Scheduling Method?

Priya evaluated her options:

Option A: schedule library in a long-running Python process - Pros: Simple, pure Python, easy to understand - Cons: Requires a Python process to be continuously running. If the server restarts, the process doesn't come back until someone logs in and starts it

Option B: APScheduler with a Windows service - Pros: More robust, survives reboots via Windows service management - Cons: More setup complexity, Marcus would need to help configure the Windows service

Option C: Windows Task Scheduler (OS-level) - Pros: Built into Windows, no additional Python process required, survives reboots, IT already knows how to manage it, provides task history in the Windows Event Log - Cons: Slightly more setup for the first task

Marcus recommends Option C. "The Task Scheduler is already monitored by our systems management tooling," he explains. "If a task fails or doesn't run, we'll see it in the dashboard. That's not true for a custom Python process."

Priya chooses Windows Task Scheduler.

The Implementation

Step 1: Preparing the Script

Priya makes one important change before scheduling: the script needs to work correctly when invoked by Task Scheduler, which runs processes with a different working directory and environment than a human terminal session.

She adds this at the top of monday_report_pipeline.py:

"""
monday_report_pipeline.py
Entry point for the scheduled Monday report.
Designed to be called by Windows Task Scheduler or cron.
"""

import os
import sys
import logging
import logging.handlers
from pathlib import Path
from datetime import datetime

# ---- Critical: Set the working directory to the script's directory ----
# When Task Scheduler runs this script, the working directory may not be
# what we expect. This ensures relative paths work correctly.
SCRIPT_DIR = Path(__file__).parent.resolve()
os.chdir(SCRIPT_DIR)

# ---- Load environment variables from .env file ----
# Task Scheduler doesn't inherit user environment variables.
# The .env file provides credentials regardless of how the script is invoked.
from dotenv import load_dotenv
dotenv_path = SCRIPT_DIR / ".env"
load_dotenv(dotenv_path=dotenv_path)

# ---- Configure file logging ----
# Task Scheduler may suppress stdout. File logging ensures we always have a record.
LOG_DIR = SCRIPT_DIR / "logs"
LOG_DIR.mkdir(exist_ok=True)

log_file = LOG_DIR / "monday_report.log"
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s  %(levelname)-8s  %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
    handlers=[
        logging.StreamHandler(sys.stdout),
        logging.handlers.TimedRotatingFileHandler(
            filename=str(log_file),
            when="W0",  # Rotate weekly on Monday
            backupCount=12,  # Keep 12 weeks of logs
            encoding="utf-8",
        ),
    ]
)

logger = logging.getLogger(__name__)

Step 2: The Script's Exit Code

Windows Task Scheduler uses the program's exit code to determine success or failure. Priya ensures her script exits with code 0 on success and code 1 on failure:

from report_pipeline import run_monday_report_pipeline

if __name__ == "__main__":
    logger.info("Monday Report Pipeline — invoked by Task Scheduler")
    success = run_monday_report_pipeline()

    exit_code = 0 if success else 1
    logger.info(f"Pipeline finished. Exit code: {exit_code}")
    sys.exit(exit_code)

Step 3: Creating the Task Scheduler Entry

Priya creates the scheduled task using PowerShell (so Marcus can review and reproduce it):

# Create the Acme Monday Report scheduled task
# Run this in PowerShell as Administrator

$PythonPath = "C:\Python311\python.exe"
$ScriptPath = "C:\AcmeAnalytics\reports\monday_report_pipeline.py"
$WorkingDir = "C:\AcmeAnalytics\reports\"
$LogPath = "C:\AcmeAnalytics\reports\logs\task_scheduler_output.log"

# Action: run the Python script
$TaskAction = New-ScheduledTaskAction `
    -Execute $PythonPath `
    -Argument $ScriptPath `
    -WorkingDirectory $WorkingDir

# Trigger: every Monday at 7:45 AM
$TaskTrigger = New-ScheduledTaskTrigger `
    -Weekly `
    -DaysOfWeek Monday `
    -At "7:45AM"

# Settings: allow running when not logged in, restart on failure
$TaskSettings = New-ScheduledTaskSettingsSet `
    -RunOnlyIfNetworkAvailable $true `
    -ExecutionTimeLimit (New-TimeSpan -Hours 1) `
    -RestartCount 2 `
    -RestartInterval (New-TimeSpan -Minutes 10) `
    -StartWhenAvailable $true

# Register the task
Register-ScheduledTask `
    -TaskName "Acme Monday Sales Report" `
    -TaskPath "\AcmeAnalytics\" `
    -Action $TaskAction `
    -Trigger $TaskTrigger `
    -Settings $TaskSettings `
    -RunLevel Highest `
    -Description "Weekly Monday morning sales report pipeline. Generates Excel report and emails Sandra Chen."

Write-Host "Task registered successfully."

The -StartWhenAvailable setting is important: if the server was offline at 7:45 AM (for example, after a weekend maintenance window), it will run the task as soon as the server is available, rather than skipping it entirely.

Step 4: Testing Before Going Live

Before trusting the task to Monday morning, Priya runs it manually from Task Scheduler:

  1. Open Task Scheduler
  2. Navigate to Task Scheduler Library → AcmeAnalytics
  3. Right-click "Acme Monday Sales Report" → Run

She watches the log file:

tail -f C:\AcmeAnalytics\reports\logs\monday_report.log

The run completes in 23 seconds. The Excel file appears in the reports folder. The email arrives in her own test inbox (she temporarily set the recipient to herself).

Marcus reviews the task settings and the log output. He approves it for production.

The Full Pipeline Flow (Monday at 7:45 AM)

07:45:00  Windows Task Scheduler triggers the task
07:45:01  monday_report_pipeline.py starts
07:45:01  Working directory set to C:\AcmeAnalytics\reports\
07:45:01  .env loaded, credentials available
07:45:01  Logging initialized: logs/monday_report.log
07:45:01  Step 1/4: Loading weekly sales data...
07:45:03  Loaded 1,247 sales records from acme_sales_2024_W43.csv
07:45:03  Step 2/4: Calculating business metrics...
07:45:03  Revenue: $847,320.00 | Margin: 33.2% | WoW: +3.2%
07:45:03  Step 3/4: Generating Excel report...
07:45:05  Report saved: reports/acme_weekly_report_20241028.xlsx
07:45:05  Step 4/4: Sending report to sandra.chen@acme.com...
07:45:07  Email sent | attachment: acme_weekly_report_20241028.xlsx
07:45:07  Pipeline COMPLETE — 6.2s total runtime
07:45:07  Exit code: 0

Sandra Chen receives the email at 7:45 AM, reviews the report over her coffee, and arrives at the 9 AM meeting with the week's numbers already loaded in her notes.

What Happens When It Fails

Priya designed the failure path as carefully as the success path.

Scenario 1: Sales data file wasn't deposited

The sales team has a process where they upload the weekly CSV by Sunday at 11 PM. If they forget, Priya's pipeline will fail on Monday morning. The error handling:

except FileNotFoundError as e:
    logger.error(f"Pipeline FAILED: Missing data file — {e}")

    # Send failure alert to Marcus and Priya
    send_failure_email(
        to="priya.okonkwo@acme.com; marcus.webb@acme.com",
        subject="ALERT: Monday Report Failed — Missing Data File",
        body=(
            f"The Monday report pipeline failed at {datetime.now().strftime('%H:%M')} "
            f"because the weekly sales CSV was not found.\n\n"
            f"Error: {e}\n\n"
            f"Expected file location: {DATA_DIR}\n"
            f"Action required: Upload the weekly sales CSV and re-run the pipeline."
        ),
    )
    sys.exit(1)

Scenario 2: SMTP server is down

The pipeline builds the Excel report successfully but cannot send the email. The code:

  1. Saves the report to the reports folder
  2. Logs the failure with the report path
  3. Sends a failure notification (which may also fail if SMTP is down, so it writes to a local alert file too)

Scenario 3: The server crashes and restarts between 7:45 and 8:00 AM

Task Scheduler's -StartWhenAvailable setting handles this: when the server comes back up, it sees the Monday task was missed and runs it as soon as the network is available. The ExecutionTimeLimit of 1 hour gives it plenty of time even if the server restarts mid-pipeline.

Marcus's Review Notes

When Marcus reviews the implementation before approving it for production, he makes three suggestions:

1. Add a success marker file

"If the task succeeds, write a timestamp to a 'last_success.txt' file. We can add a monitoring check to alert us if that file is older than 10 days — which would mean the report has been failing silently."

Priya adds:

# At the end of a successful pipeline run
success_marker = SCRIPT_DIR / "logs" / "last_success.txt"
with open(success_marker, "w") as f:
    f.write(datetime.now().isoformat())

2. Log the Task Scheduler task history

Windows Task Scheduler maintains its own task history log. Marcus shows Priya how to enable it: - In Task Scheduler, select the task - In the Actions panel, click "Enable All Tasks History" - View history in the History tab of the task

"When something goes wrong on a Monday morning, the first place to check is this history log. If the task shows 'Completed' with exit code 0, the problem is in your code's own log. If the task shows 'Failed to start', the problem is in how the task is configured — probably the Python path or working directory."

3. Document the recovery procedure

Marcus requests a simple runbook: what to do if the report doesn't arrive. Priya writes a one-page document covering: - How to check the Task Scheduler history - How to check the pipeline log file - How to run the pipeline manually if the scheduled run failed - Who to contact if the data file is missing (the sales operations team)

This document lives in C:\AcmeAnalytics\docs\monday_report_runbook.md.

The Outcome

Six weeks after deployment, the Monday report has run successfully every Monday without Priya's involvement. It has failed twice — once due to a missing data file (the sales team forgot to upload), once due to a network outage at 7:45 AM that delayed the run to 8:12 AM.

Both failures were detected within minutes. Both were recovered within the hour. Sandra did not notice either incident — the report arrived before her 9 AM meeting both times.

Marcus tells his weekly IT operations review: "The Analytics team has deployed their first automated pipeline. It runs clean. Let's use this as the template for the next three automation projects."

Priya starts planning what to automate next.

Discussion Questions

  1. Marcus insisted on Windows Task Scheduler rather than a Python-based scheduler. What are the tradeoffs? When would you argue for APScheduler instead?

  2. The pipeline exits with code 0 (success) or code 1 (failure). Why does this matter for Windows Task Scheduler? What happens in the Task Scheduler UI when a task exits with code 1?

  3. The -StartWhenAvailable Task Scheduler option runs a missed task as soon as the machine is available. For some types of jobs, running a missed task automatically is desirable. For others, it could cause problems. What kinds of jobs would be problematic if run late?

  4. Priya tested the failure scenarios (missing data file, SMTP down) before deploying. Why is failure-path testing as important as success-path testing for scheduled automation? What could go wrong if failure paths are not tested?

  5. The sales team uploads the weekly CSV manually. This is now a dependency of an automated pipeline. How would you design a monitoring system to detect when the CSV hasn't arrived by its expected time (Sunday 11 PM)?