Documentation: Daily Retail Transactions & Inventory Movements

Overview and Metadata

Purpose:
This query consolidates daily retail transactions and inventory movements into a unified dataset. It captures sales, returns, store–warehouse transfers, supplier transactions, and stockist shipments. The output standardizes sources/targets and ensures consistent transaction classification.
Project Context:
Part of the retail supply chain harmonization project on Snowflake.
The resulting dataset is shared with a third-party partner to support inventory visibility, demand planning, and operational reconciliation.
Source Tables:
DW_LOADTARGET.DWH.TBDWFT_SALES
DW_LOADTARGET.DWH.TBDWMD_REASON
DW_LOADTARGET.DWH.TBDWMD_ARTICLE
DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_NO_PACK
DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_PACK_BY_SIZE
DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT
Output:
Produces a final dataset of transactions (as a table or view) to be consumed for reporting, stock reconciliation, and partner integration.

Executive Summary & Business Logic

Business Goal:
Create a single, daily feed of standardized transactions across sales, returns, warehouse movements, supplier receipts/shipments, and stockist dispatches. This ensures the third party has a complete and consistent view of stock flows.
Key Business Rules:
All records are restricted to Italian companies (170R, 170V, 170F) and sales year ≥ 2023.
Returns are detected either by reason code classification (CL0004) or when all movement signs are inverted.
Warehouses are grouped under a single virtual warehouse (WH1), eliminating internal warehouse-to-warehouse movements.

Data Flow Diagram

flowchart LR
    subgraph Sources
        SALES[TBDWFT_SALES]:::src
        REASON[TBDWMD_REASON]:::src
        ARTICLE[TBDWMD_ARTICLE]:::src
        NO_PACK[VWDWFT_RABT_MOV_NO_PACK]:::src
        PACK[VWDWFT_RABT_MOV_PACK_BY_SIZE]:::src
        WHS_REF[WHS_ENABLED_ONEBEAT]:::src
    end

    SALES --> SALES_RETURNS
    REASON --> SALES_RETURNS
    ARTICLE --> SALES_RETURNS

    NO_PACK --> TRANSACTIONS_ALL
    PACK --> TRANSACTIONS_ALL
    REASON --> TRANSACTIONS_ALL
    ARTICLE --> TRANSACTIONS_ALL
    WHS_REF --> TRANSACTIONS_ALL

    TRANSACTIONS_ALL --> FORMATTED_TRANSACTIONS
    FORMATTED_TRANSACTIONS --> WHS_TO_STORE
    FORMATTED_TRANSACTIONS --> STORE_TO_WHS
    FORMATTED_TRANSACTIONS --> STORE_TO_STORE
    FORMATTED_TRANSACTIONS --> DSS_STORE_TO_STORE
    FORMATTED_TRANSACTIONS --> EXT_STORE
    FORMATTED_TRANSACTIONS --> EXT_WHS
    FORMATTED_TRANSACTIONS --> STOCKIST

    WHS_TO_STORE --> TRANSACTIONS_FINAL
    STORE_TO_WHS --> TRANSACTIONS_FINAL
    STORE_TO_STORE --> TRANSACTIONS_FINAL
    DSS_STORE_TO_STORE --> TRANSACTIONS_FINAL
    EXT_STORE --> TRANSACTIONS_FINAL
    EXT_WHS --> TRANSACTIONS_FINAL
    STOCKIST --> TRANSACTIONS_FINAL
    SALES_RETURNS --> TRANSACTIONS_FINAL

    TRANSACTIONS_FINAL --> FIX_DELETION_TRANSACTIONS
    FIX_DELETION_TRANSACTIONS --> FINAL[Final Output Dataset]:::final

    classDef src fill:#f9f,stroke:#333,stroke-width:1px
    classDef cte fill:#bbf,stroke:#333,stroke-width:1px
    classDef final fill:#bfb,stroke:#333,stroke-width:2px

Execution Parameters and Pilot Conditions

Transactions are filtered by:
SALES.posting_dat_num = TARGET_DATE (runs daily for a single date).
country_cod = 'ITA'.
company_cod IN ('170R','170V','170F').
Excludes non-selling articles (business_cod <> 'C').
Excludes dss channel sales (channel_cod <> '40').
Pilot scope: Currently restricted to Italian stores and warehouses.
These conditions may expand later to include other countries and business units.

Final Columns Data Dictionary

Column Name	Data Type	Description	Notes
`id`	String	Unique identifier of the aggregated transaction	Based on movement codes, may be derived from multiple raw rows
`quantity`	Number	Net quantity of items involved in the transaction	Sign convention adjusted for consistency (in/out/sale/return)
`sale_price`	Number	Net monetary value of the transaction	Includes sign adjustments for returns and outflows
`sku_id`	String	Unique identifier of article + measure	Format: `COUNTRY_COMPANY_ARTICLE_MEASURE`
`source_location_id`	String	Location where stock is moving from	Can be store, warehouse (`WH1`), client, supplier, or stockist
`target_location_id`	String	Location where stock is moving to	Standardized: non italian stores are mapped into `Store_No_Italy`
`transaction_date`	Timestamp	Date of the transaction	Converted from `yyyymmdd` format
`type`	String	Transaction type	One of: `sale`, `return`, `in`, `out`
`currency`	String	Currency code used in the transaction	Typically `EUR`, occasionally `CZK`

Data Flow Logic

SALES_RETURNS

Purpose: Captures sales and returns directly from the sales fact table.
Inputs: TBDWFT_SALES, TBDWMD_REASON, TBDWMD_ARTICLE.
Main Logic:
Identifies returns by reason code CL0004 or if all line items are marked as inverted.
Normalizes sales/returns into a source (store or client) and target (store or client) structure.
Aggregates values per transaction, applying correct sign conventions.
Assumptions: Every return is either reason-coded or has all movements inverted.

TRANSACTIONS_ALL

Purpose: Combines warehouse and store movement records.
Inputs: VWDWFT_RABT_MOV_NO_PACK, VWDWFT_RABT_MOV_PACK_BY_SIZE, plus article and reason lookups.
Main Logic:
Standardizes packed/unpacked movements into a common structure.
Adds warehouse flag via WHS_ENABLED_ONEBEAT.
Filters out in-store-app compensations to avoid double-counting.
Assumptions: Articles flagged as non-selling are excluded.

FORMATTED_TRANSACTIONS

Purpose: Normalizes transaction fields and location IDs.
Inputs: TRANSACTIONS_ALL.
Main Logic:
Builds sku_id and enforces consistent warehouse coding (WH1).
Prepares clean transaction records for later aggregation.

WHS_TO_STORE

Purpose: Formats warehouse-to-store transfers.
Inputs: FORMATTED_TRANSACTIONS.
Main Logic:
Identifies CL0113 (outgoing) vs CL0020 (incoming).
Calculates net quantity/value with correct sign convention.
Assumptions: Transactions are paired (warehouse send, store receive).

STORE_TO_WHS

Purpose: Formats store-to-warehouse returns.
Inputs: FORMATTED_TRANSACTIONS.
Main Logic:
Identifies CL0120 (outgoing) vs CL0013 (incoming).

STORE_TO_STORE

Purpose: Handles store-to-store transfers.
Inputs: FORMATTED_TRANSACTIONS.
Main Logic:
Uses CL0121 (sending) and CL0021 (receiving).
Excludes reason codes like %407% to avoid DSS sales overlap.

DSS_STORE_TO_STORE

Purpose: Captures DSS-driven store–client sales/returns.
Inputs: FORMATTED_TRANSACTIONS.
Main Logic:
Treats transactions as sales/returns instead of internal transfers.

EXT_STORE

Purpose: External supplier to store flows.
Inputs: FORMATTED_TRANSACTIONS.
Main Logic:
Uses CL0002 (supplier sending) and CL0102 (store sending back).
Applies only to store-type records.

EXT_WHS

Purpose: External supplier to warehouse flows.
Inputs: FORMATTED_TRANSACTIONS.
Main Logic: Same as EXT_STORE, but restricted to warehouse-type records.

STOCKIST

Purpose: Captures shipments from stores to stockists.
Inputs: FORMATTED_TRANSACTIONS.
Main Logic: Uses CL0131 as outflow movement.

TRANSACTIONS_FINAL

Purpose: Consolidates all movements into one unified dataset.
Inputs: All CTEs above.
Main Logic:
Standardizes location IDs (e.g., mapping 5% stores to Store_No_Italy).
Excludes warehouse-to-warehouse movements.

FIX_DELETION_TRANSACTIONS

Purpose: Corrects deletion/mis-signed transactions.
Inputs: TRANSACTIONS_FINAL.
Main Logic:
Reverses negative quantities.
Swaps source/target and type when necessary.
Assumptions: Deletion movements are rare but must be normalized.

Final SELECT

Purpose: Produces the daily aggregated dataset.
Main Logic: Groups by all relevant dimensions, summing quantity and value.
Assumptions: Duplicates from sign-corrections are resolved by aggregation.