Processing 51K Daily Queries to Drive Supply Chain Excellence for Amazon.com using Amazon Redshift

In 2012, Amazon.com launched Amazon Logistics to handle direct-to-customer deliveries, expanding the company’s previous focus, which was solely on logistics in Amazon fulfillment centers. To support this new service in a data-driven manner and to help derive insights from all package touch points while improving supply chain metrics, Amazon created the PerfectMile team within the finance department. Initially, Amazon Logistics was growing 115 percent every year. For the first 5 years after its launch, the team changed its technology every 6 months, looking for a relational database solution that could scale to accommodate this rapid growth and that had the compute power to handle enormous volumes of data.

Following Amazon’s company-wide principle—customer obsession—and to fulfill delivery promises, the team wanted to provide a single view of truth for packages in the supply chain. Hundreds of Amazon teams needed access to information that was consumable, regularly available, and optimized to facilitate near-real-time decisions for billions of packages. The solution needed to connect a complex network of supply chain actors and activities from volumes of data across driver information, package scans, truck routes, touch points at fulfillment and sort centers, and many more. “At the beginning, we were strongly constrained in terms of resources,” says Lionel Abderemane, senior technical program manager at Amazon. “We had to find ways to automate the production of key performance indicators and facilitate broad access to this information.”

The team turned to Amazon Redshift in 2017 and built a website that secured and centralized data insights for more than 200 Amazon analysts and developers at that time. At the end of 2022, more than 40,000 operators were using the website daily, including those on the front line, senior leaders monitoring transportation networks, and business intelligence engineers and analysts. They query data at a granular level—down to the shipment ID—to track deliveries and gain operational insights. “We couldn’t find anything equivalent to Amazon Redshift that had the power and capacity to transform and process high volumes of data with the timing that we needed,” Abderemane says.

Using Amazon Redshift, the PerfectMile team improved the accuracy and speed of issue identification in a supply chain that handled billions of items shipped in 2021, about double the number of shipments from 2020. The team optimized its architecture by using different Amazon Redshift clusters, which offered faster query performance using the same SQL-based tools that the team had already been using. Using the Amazon Redshift Data Sharing feature, the solution does not need to migrate data physically among clusters, which improves speed while scaling to handle more than 500 sources of near-real-time data from at least 100 different teams. The solution ingests data from more than 30 upstream technical services in transportation and last mile, adds business rules for measurements and targets, and generates metrics and reports. Using Amazon Redshift Federated Query, the PerfectMile team consolidates data from its data lake as well as its operational stores to help enhance decision-making and operational insights. When internal Amazon teams identify delivery issues and find solutions quickly, Amazon customers benefit from a more efficient package delivery service.

Rather than the limit of 45 concurrent queries under the previous solution, 51,000 queries per day run using Amazon Redshift. The team ingests data from more than 100 data sources and processes hundreds of MB per second using Amazon Redshift. This helps to analyze errors, optimize forecasting, and proactively address potential issues. More than 500 internal teams at Amazon.com are able to build their own sales and financial models. Teams identify and act on gaps in the supply chain quicker by independently collecting and consolidating the data, applying logic to identify exactly where a problem occurred, and publishing the analysis for leadership. “Amazon Redshift provides the scale that we as a company need to get an overview of each and every package’s life cycle,” says Arnaud Colin, founder of the PerfectMile team and senior manager of data engineering at Amazon.com. The insights that the team generates drive operational excellence during supply chain strain during the holiday season or supply delivery challenges during a crisis.

Additionally, Amazon.com can act as a carrier for shipments that are sold outside Amazon, and the solution is expanding vertically to accommodate third-party seller and fulfillment data. The team also uses Amazon Redshift to drive horizontal scaling as it launches in new countries, including Brazil, Singapore, Australia, Saudi Arabia, and the United Arab Emirates.

“You cannot imagine the savings that we realize by staying in the same environment, with the stability of data that persists over time, which we can still use after 5 years,” Colin says.

The ability to streamline processes internally has helped to optimize and simplify Amazon deliveries, providing a better customer experience. After working primarily with historical data, the team will use the new data pipelines to feed models to forecast peaks and other potential delivery challenges. Using Amazon Redshift, the team will help Amazon Robotics and other internal teams use machine learning to optimize operations. “We will continue to partner and innovate with Perfect Mile team so they keep achieving their business outcomes faster and at low cost using Amazon Redshift” says Pradeep Misra, Analytics specialist architect at AWS.