3020_CHN_BAS_discount_code_mapping Transformation

Target Database Table

drp.discount_code_mapping.

Script Description and Logic

Pulls order-related fields and discount codes for all orders with discount codes and checks against the BSD to check which channel the discount code is mapped to.

All discount codes in the primary source table, bsd.discount_code_mapping, are included and reported by unique order id and discount code combination. From the bsd.discount_code_mapping table a lookup is performed to see if any discount codes use a wildcard identifier, this list is stored as wildcard_codes.

Next a list of orders is created from uos.order_discount_codes and then joined to wildcard_codes, orders and integration_mapping to map the wildcard codes to orders that had discount codes. The resulting table is stored as wildcard_mapping. Next a list of direct discount codes is generated and duplicates are removed, the resulting table is stored as direct_codes. Direct_codes is then joined to orders, integration_mapping and order_discount_codes to map direct discount codes in the BSD to orders that had discount codes. The resulting table is stored as direct_mapping.

Wildcard_mapping and direct_mapping are then combined and inserted into drp_staging.discount_code_mapping. All records are removed from the final table,drp.discount_code_mapping, and updated by way of insertion from the query (upsert). An additional insert into drp_staging.order_channel_source is then performed for use in later processes.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
bsd discount_code_mapping database Primary
uos order_discount_codes database Lookup
drp integration_mapping database Ensure unique records
drp_staging discount_code_mapping database Pre-insert
N/A wildcard_codes derived Pulls a list of wildcard discount codes from BSD
N/A wildcard_mapping derived Map wildcard codes from the BSD to orders with discount codes
N/A dedup_direct_codes derived Pulls a list of all discount codes from the BSD, duplicates are row ordered
N/A direct_codes derived Pulls a unique list of discount codes from dedup_direct_codes
N/A direct_mapping derived Maps the direct codes from the BSD to orders that had discount codes attached

SQL Flow

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

 3020_CHN_BAS_discount_code_mapping Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
order_channel_id CHAR uos.order_discount_codes order_id, __shop_id Combination of __shop_id, order_id and a text label of discount code mapping to identify records


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