Warehouse Fillrate Pipeline

The Problem

Across 10 warehouse distribution centers, buyers and operations teams had no reliable way to answer a basic question: of everything we ordered, how much actually shipped — and why did we come up short?

The data to answer that question existed, but it was scattered across three disconnected systems — an Oracle order management database, a separate instock data warehouse, and a shipping records source. None of them talked to each other. Analysts were stitching it together manually in Excel, which meant the data was always stale, the methodology was inconsistent, and a full network-wide view across all 10 DCs was essentially impossible to produce on demand.

The Engineering Challenge

The goal was to automate this completely — run multiple times a day, pull from all three systems, apply a consistent calculation, and record the results with zero manual intervention. The challenge was that each step of that process depends on the previous one: you can't calculate what was lost until you know what was ordered, and you can't categorize a shortfall without knowing whether the item was actually in stock that day.

Each step depends on the one before it, and each step talks to a different system — Oracle, Athena, SQL Server. That's exactly the problem AWS Step Functions is built for. Each Lambda handles one thing, hands off to the next, and Step Functions takes care of retries and failure alerting automatically.

One of the trickier decisions was handling the case where the pipeline fires but there's nothing new to process. Running on a schedule doesn't mean new data shows up on schedule — and recalculating against the same data twice would create duplicate records. The first Lambda checks for new order activity against a last-run timestamp in Parameter Store and just bails out early if nothing has changed. The rest of the pipeline only kicks off when there's actually something new.

The business logic itself — four distinct calculation paths depending on item type and instock status, with 7-day rolling averages — was defined collaboratively with a 5-person cross-functional team. Translating that into clean, maintainable C# across three Lambda functions, with edge cases for flow items, missing items, and contract-committed vendors, was my work end to end.

Architecture

Built for Production

A scheduled pipeline that quietly fails is worse than no pipeline at all — stale data looks like fresh data until someone notices. Reliability was a first-class requirement.

• Automatic retries with backoff. Step Functions wraps each Lambda with configurable retry logic. Transient failures — a slow database response, a momentary Athena timeout — retry automatically with exponential backoff before the pipeline considers a step failed.
• Failure alerting via SNS. Any unrecovered failure triggers an SNS notification immediately. The ops team knows before anyone notices stale data in a dashboard.
• Safe to re-run. The watermark check means if the same run fires twice or a retry overlaps, the output is identical — no duplicate records, no double-counting.
• Infrastructure as code. The entire AWS environment — EventBridge rule, Step Functions state machine, all three Lambdas, Parameter Store entries, SNS topic, IAM roles — is defined in C# using AWS CDK. Reproducible, version-controlled, deployable in a single command.
• Historical backfill built in. At launch, a year of historical data needed to be calculated. Rather than a one-off script, I built a dedicated backfill Lambda that reuses the same core logic, runs in batches, and can be re-run safely without risk of corruption.

Scale

Outcome

Before this system, there was no consistent, reliable answer to a question that directly affected purchasing decisions, vendor contracts, and distribution operations across the entire network. The data existed — it just couldn't be assembled fast enough or consistently enough to be useful.

After deployment, buyers and distribution managers across all 10 centers had access to accurate, up-to-date fillrate data multiple times per day — with a consistent methodology applied uniformly, instock context factored in, and rolling trend data available for every item at every DC. The analytics team's downstream reporting now has a clean, reliable source of truth that didn't exist before.

What previously required hours of analyst effort — if it happened at all — runs automatically, around the clock, without anyone touching it.

My Role

My ownership of this project was the architecture and full technical implementation. A 5-person team worked extensively with cross-functional stakeholders — buyers, operations, and vendor management — to determine the correct business logic and calculation methodology. Once that logic was defined, I translated it into a production AWS system from scratch.

• Designed the 3-Lambda Step Functions architecture
• Built all infrastructure using AWS CDK in C#
• Implemented all three Lambda functions in .NET 8 / C#
• Designed the SQL Server schema for fillrate and lost cases records
• Integrated Oracle (LM), Athena, SQL Server, Parameter Store, and SNS
• Built a separate historical recalculation Lambda for backfilling a full year of data
• Debugged and resolved edge cases in lost cases logic across flow items, missing items, and FSA flag application
• Translated complex multi-team business requirements into a clean, maintainable, serverless architecture

Company and system names have been anonymized. Architecture, scale figures, and technical details are accurate.