6010_INV_BAS_inventory_history Transformation

Target Database Table

drp.inventory_level_history.

Script Description and Logic

Pulls a snapshot of inventory and product information at the daily inventory level. Products and inventory from all integrations are included in the primary source table, uos.inventory_level, and reported by unique inventory level id, shop id and location.

From the uos.inventory_level table, a join is made to uos.product variants to combine inventory levels during the snapshot with product sku cost and price. The code performs a check to see if a daily snapshot of today’s inventory was loaded previous, if no inventory records for today exist then all records in the daily snapshot are recorded by way of insertion from the query into drp.inventory_level_history.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
uos inventory_levels database Primary
drp product_variants database Lookup

SQL Flow

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

6010_INV_BAS_inventory_history TransformationCalculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
inventory_history_id CHAR uos.inventory_level_history inventory_level_id, __loaded_at inventory_level_id + date of inventory


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