Automation that does not break — Python, SQL views, Power Automate, emails, logs and refresh

What to automate

Automate tasks that are frequent, rule-based, error-prone and stable. A quick value check: (minutes per run × runs per year) vs build + maintenance time. A 30-minute weekly task = 26 hours/year. Also count the cost of errors avoided.

Five properties of reliable automation

1. Idempotent — running twice gives the same result (load with "delete month then insert", or upsert by key; never blind append).
2. Logged — every run records start, end, rows processed, warnings, errors.
3. Observable — failures alert a human (email/Teams), and silence is never success: alert when an expected run did not happen.
4. Validated — inputs checked (columns, types, row counts within expected ranges) before outputs are published.
5. Maintainable — configuration outside code, documented runbook, version control, named owner.

Python logging pattern

import logging, sys
from pathlib import Path

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(message)s",
    handlers=[logging.FileHandler("job.log"), logging.StreamHandler(sys.stdout)],
)
log = logging.getLogger("store_files")

def run():
    files = sorted(Path("incoming").glob("stock_*.xlsx"))
    log.info("found %d files", len(files))
    if not files:
        raise RuntimeError("No input files — upstream delivery missing")
    ...

if __name__ == "__main__":
    try:
        run()
        log.info("success")
    except Exception:
        log.exception("job failed")   # full traceback in the log
        sys.exit(1)                   # non-zero exit lets the scheduler flag failure

Excel files to SQL views

Pattern (Project 28):

1. Staging table mirrors the file: stg_stock(store_id, month, product_id, qty, source_file, loaded_at).
2. Loader validates each file (required columns, numeric qty ≥ 0, month matches filename), deletes existing rows for that store-month, inserts new rows (idempotent), logs counts.
3. Views apply business logic once for every consumer:

CREATE VIEW v_stock_latest AS
SELECT s.store_id, s.product_id, s.qty, s.month
FROM stg_stock s
JOIN (SELECT store_id, MAX(month) AS month FROM stg_stock GROUP BY store_id) m
  ON m.store_id = s.store_id AND m.month = s.month;

Power BI and Excel read the views — logic lives in one governed place.

Python to Power BI

Python writes outputs (scores, cleaned tables) to a database table or a shared Parquet/CSV location on a schedule; Power BI imports on its own refresh schedule after the Python job. Sequence the schedules and alert if the input is stale (e.g. a loaded_at check measure on the report).

Power Automate: emails and logs

Power Automate (Microsoft 365) automates flows between apps with triggers and actions:

• Trigger: scheduled (daily 07:00), file created in SharePoint, new email, Power BI data alert.
• Actions: get files, condition, send email (Outlook), post to Teams, add row to a log list, refresh a Power BI semantic model.
• Error handling: group actions in Scopes (Try / Catch / Finally) and use "Configure run after" (has failed / timed out) to send failure alerts and write a log row with the error.

Mail-based reporting flow: at 07:00 → check each store's file exists → if missing, email the store manager and log "missing" → otherwise refresh the dataset → email a summary with the report link → log "sent". Avoid emailing attachments of sensitive data; send links to access-controlled reports.

Report refresh

Safe file operations (desktop cleaning)

Rules for any script that moves files (Project 32): dry-run mode first; never delete (move to an archive); never overwrite (add a suffix); skip locked files; log every action; test on a sample folder.

from pathlib import Path
import shutil, time

def archive(src: Path, root: Path, dry_run=True, min_age_days=30):
    if (time.time() - src.stat().st_mtime) < min_age_days * 86400:
        return None
    month = time.strftime("%Y-%m", time.localtime(src.stat().st_mtime))
    dest_dir = root / month / (src.suffix.lstrip(".").lower() or "other")
    dest = dest_dir / src.name
    i = 1
    while dest.exists():                          # never overwrite
        dest = dest_dir / f"{src.stem}_{i}{src.suffix}"; i += 1
    if not dry_run:
        dest_dir.mkdir(parents=True, exist_ok=True)
        shutil.move(str(src), dest)
    return dest

Commission management through Streamlit

Project 34 combines everything: validated uploads, a rule engine with unit tests, review and approval in a Streamlit app, an audit trail (who approved which run, when) and an exported payout file. Treat payout calculations as financial controls: two-person approval, immutable history, reconciliation of totals to sales.

Practice

Summary

• Automate frequent, stable, rule-based work; estimate value including errors avoided.
• Reliable jobs are idempotent, logged, observable, validated and maintainable.
• Put logic in SQL views; sequence Python → database → BI refresh with staleness checks.
• Power Automate flows need Try/Catch scopes and run-after alerts; file scripts need dry-run and no-delete rules.