HPSCAT vendor data ingestion pipeline

Automated data pipeline that pulls vendor volume data from the HPSCAT API and uploads it to SharePoint via Microsoft Graph. Replaced a manual monthly process across 11 manufacturer vendors.

What it pulls

Vendor volume data for 11 manufacturers across the company's foodservice portfolio.

Why PowerShell, not Power Automate

HPSCAT requires static-IP whitelisting. Power Automate's egress IPs are dynamic and can't be reliably whitelisted, so the pipeline runs as PowerShell from office-network sources whose IP ranges are whitelisted by HPSCAT.

Variants

• HPSCAT-SingleVendor One-vendor refresh, used for ad hoc pulls.
• HPSCAT-MultiVendor The standard monthly run across all 11 vendors.
• HPSCAT-MultiDay Multi-day windows when a single day is short.
• HPSCAT-MultiVendor-Backfill Historical backfill across vendors and date ranges.

SharePoint integration

Uploads land in document libraries via Microsoft Graph with structured folder layout. Permissions and folder structure are managed automatically so downstream BI work doesn't need to chase access.

Operational shape

• Scheduled runs with structured logging and alerting on failure.
• Idempotent retries: rerunning the same window produces the same result.
• Field mapping and validation to handle vendor-specific schema quirks.