Source Database Table
Script that Populates Database Table
Script Description and Logic
Pulls the first date a 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|
Calculated and Derived Fields
|Target Column||Target Column Data Type||Source schema.table||Source Column||Transformation/Logic|
|sku_launch_id||CHAR||drp.order_line_revenue, drp.order_business_unit||store_name, sku||store_name + sku|
|sku_launch_date||TIMESTAMP WITHOUT TIME ZONE||drp.order_line_revenue||order_date||MIN(order_date) of a product by unique store and sku|
All content © Daasity 2021. Do not copy, share or distribute.