6520_PLN_BAS_sku_launch_date Transformation

Target Database Table

drp.sku_launch_date.

Script Description and Logic

Pulls the first date an SKU was purchased or launched at the product and store level. Products from all integrations are included in the primary source table, drp.order_line_revenue, and reported by unique SKU launch id and shop. From the drp.order_line_revenue table, a join is made to drp.order_business unit variants to segment SKUs by unique stores and the earliest order date.

The resulting table is then staged for insertion. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.sku_launch_date table.

The records in staging are compared to those in the final table.

Any SKU launch ids that match are removed from the final table and updated by way of insertion from staging (upsert). The records that appear in staging, but not in the final table are inserted into drp.sku_launch_date.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_line_revenue database Primary
drp order_business_unit database Lookup
drp_staging sku_launch_date database Pre-insert

SQL Flow

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

6520_PLN_BAS_sku_launch_date Transformation