4150_CST_BAS_order_item_fulfillment_cost Transformation

Target Database Table


Script Description and Logic

Pulls order line id, fulfillment cost and shipping cost at the item fulfillment level. Fulfillments from all integrations are included in the primary source table, uos.order_item_fulfillments, and reported by unique order line id, shop id and integration id combination.

A lookup is performed against uos.order_item_fulfillments to return a list of order item fulfillments that have been updated in the last seven days. The order item fulfillments to update are then held in a staging table. From the uos.order_item_fulfillments table a join is made to the order_item_fulfillments_to_update table to lookup what order line fulfillments are pending update. Then a join is made to drp.fulfillment_cost to pull in fulfillment cost, number of items and shipping cost.

The fulfillment cost and shipping cost are calculated by dividing the cost by the number of items. The amounts are summed up to the order line level in case the order line was split shipped. The unique combinations of order line id, shop id, fulfillment cost, shipping cost and number of items are then placed in a staging table in preparation for incremental functionality. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.order_item_fulfillment_cost table. The records in staging are compared to those in the final table.

Any fulfillments that match are removed from the final table and updated by way of insertion from staging. The records that appear in staging, but not in the final table are inserted into drp.order_item_fulfillment_cost.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
uos order_item_fulfillments database Primary Source
drp fulfillment_cost database Obtain fulfillment and shipping cost for each fulfillment
drp_staging order_item_fulfillment_cost database Pre-insert
drp_staging order_item_fulfillments_to_update database Lookup
N/A last_sync_date derived Obtain last sync date for each record

SQL Flow

To View Larger Image, Right Click and Choose Open Image In New Tab.

 4150_CST_BAS_order_item_fulfillment_cost Transformation
Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
fulfillment_cost DECIMAL drp.fulfillment_cost fulfillment_cost Sum(fulfillment cost/num_items) per order_line and __shop_id
shipping_cost DECIMAL drp.fulfillment_cost shipping_cost Sum(shipping cost/num_items) per order_line and __shop_id

All content © Daasity 2021. Do not copy, share or distribute.