Building an Order Fulfillment Optimization Pipeline: From Orders to Delivery

Order fulfillment is inefficient. Teams can't optimize shipping routes, warehouse locations, or delivery times. High shipping costs, delayed deliveries, and poor customer experience result from manual processes. The result: customers receive orders late, shipping costs eat into margins, and fulfillment teams are overwhelmed.

The problem: An order comes in from a customer in New York. The team ships from a warehouse in California. Shipping costs $25 and takes 5 days. A warehouse in New Jersey has the same product in stock, costs $8 to ship, and takes 2 days. The team doesn't know, so they ship from California. The customer is unhappy, and the company loses $17 in margin.

The solution: An automated fulfillment pipeline that analyzes order data, inventory locations, shipping costs, and delivery networks—optimizing fulfillment decisions and reducing costs. This post walks through building a repeatable architecture that transforms manual fulfillment into intelligent optimization.

The Problem with Manual Order Fulfillment

Manual fulfillment breaks down at scale. Here's what e-commerce teams face:

The Warehouse Selection Problem

Orders are fulfilled from the wrong warehouse:

• Default to primary warehouse regardless of customer location
• Don't consider inventory availability across warehouses
• Ignore shipping costs and delivery times
• Can't optimize for multi-item orders

The math: If 30% of orders are fulfilled from suboptimal warehouses and each costs $10 more in shipping, that's $3,000 per 100 orders. For 10,000 orders per month, that's $300,000 in unnecessary shipping costs annually.

The Shipping Cost Problem

Shipping costs aren't optimized:

• Always use the same carrier regardless of cost
• Don't compare shipping options
• Can't negotiate better rates with volume
• Don't factor in packaging costs

The impact: A 2-day shipping option costs $15, but a 3-day option costs $8. Teams default to 2-day, paying $7 extra per order. For 1,000 orders per day, that's $7,000 per day in unnecessary costs.

The Delivery Time Problem

Delivery times aren't optimized:

• Don't consider customer location relative to warehouses
• Can't predict delivery times accurately
• Don't factor in carrier performance
• Can't offer expedited shipping when needed

The cost: Customers expect 2-day delivery but receive orders in 5 days. Customer satisfaction drops, leading to returns, refunds, and lost future sales.

The Multi-Item Order Problem

Multi-item orders are split inefficiently:

• Items shipped from different warehouses
• Multiple shipments increase costs
• Customers receive partial orders
• Tracking becomes complicated

The missed opportunity: A 3-item order could ship from one warehouse for $12 total, but ships from 3 warehouses for $36 total. Optimizing could save $24 per order.

A Repeatable Fulfillment Architecture

The solution is a pipeline architecture that analyzes orders, evaluates fulfillment options, optimizes decisions, and routes orders to optimal warehouses.

Architecture Components

1. Order Analysis Layer

• Parses order details (items, quantities, customer location)
• Identifies order characteristics (single-item, multi-item, expedited)
• Extracts customer preferences (shipping speed, carrier preference)
• Calculates order value and priority

2. Inventory Lookup System

• Queries inventory across all warehouses
• Checks stock availability for each item
• Identifies warehouse locations
• Tracks inventory levels and replenishment schedules

3. Warehouse Selection Engine

• Evaluates all warehouses with inventory
• Calculates distance to customer
• Considers warehouse capacity and workload
• Factors in warehouse capabilities (packaging, special handling)

4. Shipping Cost Calculator

• Queries carrier APIs for shipping rates
• Calculates packaging costs
• Factors in handling fees
• Applies volume discounts

5. Delivery Time Estimator

• Calculates transit times by carrier and service level
• Factors in warehouse processing time
• Considers carrier performance history
• Estimates delivery dates

6. Optimization Algorithm

• Combines cost, time, and service factors
• Scores fulfillment options
• Selects optimal warehouse and shipping method
• Handles multi-item order optimization

7. Order Routing System

• Sends orders to selected warehouse
• Creates shipping labels
• Updates order status
• Tracks fulfillment performance

Aggregating Order and Inventory Data

The first stage is building unified views of orders and inventory across warehouses.

Building the Order Fulfillment View

Create a comprehensive view that joins order data with inventory and customer context:

CREATE MATERIALIZED VIEW order_fulfillment_comprehensive AS
SELECT 
    -- Order Info
    o.order_id,
    o.order_date,
    o.order_status,
    o.order_value,
    o.customer_id,
    
    -- Order Items
    oi.product_id,
    oi.product_sku,
    oi.quantity,
    oi.unit_price,
    oi.total_price,
    
    -- Customer Location
    c.shipping_address,
    c.shipping_city,
    c.shipping_state,
    c.shipping_zip,
    c.shipping_country,
    c.latitude as customer_latitude,
    c.longitude as customer_longitude,
    
    -- Shipping Preferences
    o.shipping_method_requested, -- 'standard', 'expedited', 'overnight'
    o.delivery_date_requested,
    
    -- Inventory Availability
    i.warehouse_id,
    i.warehouse_name,
    i.warehouse_city,
    i.warehouse_state,
    i.warehouse_latitude,
    i.warehouse_longitude,
    i.stock_quantity,
    i.available_quantity,
    i.reserved_quantity,
    
    -- Calculated Fields
    -- Distance from warehouse to customer (simplified - use proper geodistance in production)
    SQRT(
        POW(69.1 * (i.warehouse_latitude - c.customer_latitude), 2) +
        POW(69.1 * (c.customer_longitude - i.warehouse_longitude) * COS(c.customer_latitude / 57.3), 2)
    ) as distance_miles,
    
    -- Can Fulfill (has enough stock)
    CASE 
        WHEN i.available_quantity >= oi.quantity THEN true
        ELSE false
    END as can_fulfill
    
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN inventory_by_warehouse i ON oi.product_id = i.product_id
WHERE o.order_status = 'pending_fulfillment';

Warehouse Capabilities View

CREATE MATERIALIZED VIEW warehouse_capabilities AS
SELECT 
    w.warehouse_id,
    w.warehouse_name,
    w.warehouse_city,
    w.warehouse_state,
    w.latitude,
    w.longitude,
    
    -- Capacity Metrics
    w.total_capacity,
    w.current_utilization,
    w.available_capacity,
    w.daily_order_capacity,
    w.current_daily_orders,
    
    -- Capabilities
    w.supports_expedited_shipping,
    w.supports_international,
    w.supports_special_handling,
    w.packaging_capabilities, -- JSON array of capabilities
    
    -- Performance Metrics
    w.avg_processing_time_hours,
    w.on_time_fulfillment_rate,
    w.accuracy_rate,
    
    -- Cost Factors
    w.handling_cost_per_order,
    w.packaging_cost_per_order
    
FROM warehouses w;

Shipping Cost Optimization

Calculate shipping costs across carriers and service levels to find the optimal option.

Shipping Cost Calculation

CREATE MATERIALIZED VIEW shipping_cost_options AS
SELECT 
    ofc.order_id,
    ofc.product_id,
    ofc.warehouse_id,
    ofc.warehouse_name,
    ofc.customer_latitude,
    ofc.customer_longitude,
    ofc.warehouse_latitude,
    ofc.warehouse_longitude,
    ofc.distance_miles,
    ofc.quantity,
    ofc.total_price as item_value,
    
    -- Shipping Options
    so.carrier, -- 'ups', 'fedex', 'usps', 'dhl'
    so.service_level, -- 'ground', '2day', 'overnight'
    so.shipping_cost,
    so.handling_fee,
    so.packaging_cost,
    so.total_shipping_cost,
    
    -- Delivery Time
    so.estimated_delivery_days,
    so.estimated_delivery_date,
    
    -- Cost per Mile
    so.total_shipping_cost / NULLIF(ofc.distance_miles, 0) as cost_per_mile,
    
    -- Value-to-Cost Ratio
    ofc.total_price / NULLIF(so.total_shipping_cost, 0) as value_to_cost_ratio
    
FROM order_fulfillment_comprehensive ofc
CROSS JOIN LATERAL (
    -- Query shipping options for this order-warehouse combination
    SELECT 
        carrier,
        service_level,
        shipping_cost,
        handling_fee,
        packaging_cost,
        shipping_cost + handling_fee + packaging_cost as total_shipping_cost,
        estimated_delivery_days,
        CURRENT_DATE + (estimated_delivery_days || ' days')::interval as estimated_delivery_date
    FROM shipping_rates
    WHERE origin_zip = ofc.warehouse_zip
      AND destination_zip = ofc.shipping_zip
      AND weight = ofc.package_weight
) so
WHERE ofc.can_fulfill = true;

Optimal Shipping Selection

CREATE MATERIALIZED VIEW optimal_shipping_selection AS
SELECT 
    order_id,
    product_id,
    warehouse_id,
    carrier,
    service_level,
    total_shipping_cost,
    estimated_delivery_days,
    estimated_delivery_date,
    
    -- Selection Score (lower cost + faster delivery = better)
    (
        -- Cost component (normalized, lower is better)
        (1.0 - (total_shipping_cost / MAX(total_shipping_cost) OVER (PARTITION BY order_id, product_id))) * 0.6 +
        -- Speed component (normalized, lower days is better)
        (1.0 - (estimated_delivery_days / MAX(estimated_delivery_days) OVER (PARTITION BY order_id, product_id))) * 0.4
    ) as selection_score,
    
    -- Rank by score
    ROW_NUMBER() OVER (
        PARTITION BY order_id, product_id 
        ORDER BY 
            (1.0 - (total_shipping_cost / MAX(total_shipping_cost) OVER (PARTITION BY order_id, product_id))) * 0.6 +
            (1.0 - (estimated_delivery_days / MAX(estimated_delivery_days) OVER (PARTITION BY order_id, product_id))) * 0.4 DESC
    ) as shipping_option_rank
    
FROM shipping_cost_options;

Warehouse Selection Logic

Select the optimal warehouse for each order based on cost, time, and availability.

Warehouse Selection Algorithm

CREATE MATERIALIZED VIEW optimal_warehouse_selection AS
SELECT 
    oss.order_id,
    oss.product_id,
    oss.warehouse_id,
    oss.warehouse_name,
    oss.carrier,
    oss.service_level,
    oss.total_shipping_cost,
    oss.estimated_delivery_days,
    oss.estimated_delivery_date,
    oss.selection_score,
    
    -- Warehouse Factors
    wc.available_capacity,
    wc.current_daily_orders,
    wc.avg_processing_time_hours,
    wc.on_time_fulfillment_rate,
    
    -- Combined Selection Score
    (
        oss.selection_score * 0.5 + -- Shipping cost and speed
        (wc.available_capacity / NULLIF(wc.total_capacity, 0)) * 0.2 + -- Capacity availability
        wc.on_time_fulfillment_rate * 0.2 + -- Reliability
        (1.0 - (wc.current_daily_orders / NULLIF(wc.daily_order_capacity, 0))) * 0.1 -- Workload balance
    ) as combined_selection_score,
    
    -- Final Rank
    ROW_NUMBER() OVER (
        PARTITION BY oss.order_id, oss.product_id
        ORDER BY 
            (oss.selection_score * 0.5 + 
             (wc.available_capacity / NULLIF(wc.total_capacity, 0)) * 0.2 + 
             wc.on_time_fulfillment_rate * 0.2 + 
             (1.0 - (wc.current_daily_orders / NULLIF(wc.daily_order_capacity, 0))) * 0.1) DESC
    ) as warehouse_rank
    
FROM optimal_shipping_selection oss
JOIN warehouse_capabilities wc ON oss.warehouse_id = wc.warehouse_id
WHERE oss.shipping_option_rank = 1; -- Best shipping option per warehouse

Multi-Item Order Optimization

For orders with multiple items, optimize to minimize total cost and shipments:

CREATE MATERIALIZED VIEW multi_item_order_optimization AS
WITH order_items_by_warehouse AS (
    SELECT 
        ows.order_id,
        ows.warehouse_id,
        COUNT(DISTINCT ows.product_id) as items_available,
        SUM(ows.total_shipping_cost) as total_shipping_cost,
        MAX(ows.estimated_delivery_days) as max_delivery_days,
        SUM(ows.combined_selection_score) as total_selection_score
    FROM optimal_warehouse_selection ows
    WHERE ows.warehouse_rank = 1
    GROUP BY ows.order_id, ows.warehouse_id
),
order_total_items AS (
    SELECT 
        order_id,
        COUNT(DISTINCT product_id) as total_items
    FROM order_items
    GROUP BY order_id
)
SELECT 
    oibw.order_id,
    oibw.warehouse_id,
    oibw.items_available,
    otl.total_items,
    oibw.total_shipping_cost,
    oibw.max_delivery_days,
    
    -- Can fulfill all items from this warehouse?
    CASE 
        WHEN oibw.items_available = otl.total_items THEN true
        ELSE false
    END as can_fulfill_all_items,
    
    -- Optimization Score (prefer warehouses that can fulfill all items)
    CASE 
        WHEN oibw.items_available = otl.total_items 
        THEN oibw.total_selection_score * 1.5 -- Bonus for single-warehouse fulfillment
        ELSE oibw.total_selection_score
    END as optimization_score,
    
    ROW_NUMBER() OVER (
        PARTITION BY oibw.order_id
        ORDER BY 
            CASE 
                WHEN oibw.items_available = otl.total_items THEN oibw.total_selection_score * 1.5
                ELSE oibw.total_selection_score
            END DESC
    ) as fulfillment_rank
    
FROM order_items_by_warehouse oibw
JOIN order_total_items otl ON oibw.order_id = otl.order_id;

Delivery Time Prediction

Predict delivery times accurately based on warehouse location, carrier performance, and historical data.

Delivery Time Estimation

CREATE MATERIALIZED VIEW delivery_time_predictions AS
SELECT 
    ows.order_id,
    ows.warehouse_id,
    ows.carrier,
    ows.service_level,
    ows.estimated_delivery_days,
    
    -- Historical Performance
    cp.avg_actual_delivery_days,
    cp.on_time_percentage,
    cp.early_delivery_percentage,
    cp.late_delivery_percentage,
    
    -- Adjusted Delivery Time (based on historical performance)
    CASE 
        WHEN cp.avg_actual_delivery_days > ows.estimated_delivery_days * 1.2 
        THEN cp.avg_actual_delivery_days
        ELSE ows.estimated_delivery_days
    END as adjusted_delivery_days,
    
    -- Confidence Score
    CASE 
        WHEN cp.on_time_percentage > 0.9 THEN 0.95
        WHEN cp.on_time_percentage > 0.8 THEN 0.85
        WHEN cp.on_time_percentage > 0.7 THEN 0.75
        ELSE 0.65
    END as delivery_confidence
    
FROM optimal_warehouse_selection ows
LEFT JOIN carrier_performance cp 
    ON ows.carrier = cp.carrier 
    AND ows.service_level = cp.service_level
    AND ows.warehouse_state = cp.origin_state
WHERE ows.warehouse_rank = 1;

Schema Overview

The fulfillment pipeline uses a hierarchical schema that traces from orders to optimal fulfillment decisions.

Key relationships:

• orders → order_fulfillment_comprehensive (enriched with inventory and customer data)
• order_fulfillment_comprehensive → shipping_cost_options (evaluates shipping costs)
• shipping_cost_options → optimal_warehouse_selection (selects best warehouse)
• optimal_warehouse_selection → multi_item_order_optimization (optimizes multi-item orders)

Traceability: Every fulfillment decision can be traced back to order details, inventory availability, shipping costs, and warehouse capabilities.

Closing Thoughts

Building an order fulfillment optimization pipeline transforms fulfillment from cost center to competitive advantage. The key is treating fulfillment as an optimization problem—not a manual process.

Key takeaways:

1. Unified data enables optimization: Join order, inventory, and customer data. Optimization requires complete context.

2. Multiple factors matter: Consider cost, time, capacity, and reliability. No single factor determines optimal fulfillment.

3. Multi-item orders need special handling: Optimize to minimize shipments. Single-warehouse fulfillment reduces costs and complexity.

4. Historical performance improves predictions: Use carrier and warehouse performance data. Adjust estimates based on actual delivery times.

5. Real-time optimization enables agility: Recalculate as conditions change. Inventory levels, warehouse capacity, and shipping rates change constantly.

The architecture described here is repeatable. Start with basic warehouse selection, add shipping cost optimization, then implement multi-item optimization and delivery time prediction. Each stage builds on the previous one.

Most importantly: this isn't about replacing fulfillment teams—it's about empowering them. Teams still handle exceptions and special cases. The optimization pipeline provides data-driven recommendations, reducing manual work and improving efficiency.

This architecture pattern is implemented in Pylar, but the concepts apply to any system optimizing order fulfillment. The key is the order analysis → inventory lookup → cost calculation → optimization → routing pipeline.