# Physical Reporting Flow

## Overview

The `physical_reporting` flow ingests physical product inventory and sales data from various warehouse partners. This flow processes daily inventory snapshots, tracking stock levels, shipments, and product metadata for physical media products.

## Reports

### DP World (dpworld)

DP World is a logistics and warehouse partner that provides daily inventory reports for physical products.

**Drop Path:** `s3://prod-orcd-ftp/ftp/dpworld/`  
**File Pattern:** `dpwstock_orchard_YYYYMMDD.csv`  
**Example File:** `dpwstock_orchard_20260223.csv`

## Configuration

- **Flow Name:** `physical_reporting`
- **Secrets Path:** `swf-physical-reporting`
- **Snowflake Account:** `TheOrchard`
- **Snowflake Database:** `PHYSICAL_REPORTING` ⚠️
- **Snowflake Schema:** `PROD`
- **Staging Table:** `PHYSICAL_REPORTING.PROD.staging_raw_physical_daily_dpw`
- **Archive Path:** `s3://cucumbers/physical-warehouse-reports/dpworld/archives/{date}/`



### Column Specifications

| Column Name | Column Type | Example Value | Description |
|-------------|-------------|---------------|-------------|
| `report_date` | `varchar` | `2026-02-23` | Context date of an ETL |
| `file_name` | `varchar` | `dpwstock_orchard_20260223.csv` | The source file name processed |
| `processed_datetime` | `timestamp_tz` | `2026-02-24 14:36:32` | Timestamp of when the data was loaded into staging raw |
| `cat_no` | `varchar` | `00572CD` | Physical product code / Catalogue Number |
| `status` | `varchar` | `32` | Physical Stock Status Key |
| `barcode` | `varchar` | `793888100572` | UPC |
| `no_of_discs` | `number` | `1` | No of discs |
| `title` | `varchar` | `DARK BLACK COAL` | Product title |
| `artist` | `varchar` | `LOGAN HALSTEAD` | Primary artist |
| `additional_info_1` | `varchar` | `STANDARD` | Version |
| `label_code` | `varchar` | `21989` | Label ID |
| `label` | `varchar` | `THIRTY TIGERS (NEW)` | Label name |
| `sub_label` | `varchar` | `LOGAN HALSTEAD RECORDS` | Imprint |
| `format` | `varchar` | `CD ALBUM` | Product type format |
| `dealer_price` | `float` | `6.99` | Dealer price |
| `release_date` | `date` | `2023-05-05` | Sales Start Date |
| `lost_rights_date` | `date` | | Lost rights date |
| `embargo_date` | `date` | `2023-03-02` | Embargo date |
| `carton_qty` | `float` | `45.0` | Carton qty / Box Lot |
| `week_1_ship` | `float` | `-1.0` | Last Weeks ship Qty |
| `week_2_ship` | `float` | `-2.0` | Last Weeks ship Qty - 1 |
| `week_3_ship` | `float` | `-1.0` | Last Weeks ship Qty - 2 |
| `week_4_ship` | `float` | `0.0` | Last Weeks ship Qty - 3 |
| `last_month_ship` | `float` | `0.0` | Last month ship Qty |
| `3_month_ship` | `float` | `0.0` | Previous 3 months ship Qty (excludes curr month) |
| `6_month_ship` | `float` | `0.0` | Previous 6 months ship Qty (excludes curr month) |
| `rolling_12_month_ship` | `float` | `0.0` | Rolling 12 month ship Qty (excludes curr month) |
| `last_year_year` | `float` | `0.0` | Last Calendar Year Ship Qty |
| `curr_year_year` | `float` | `-4.0` | Curr Calendar Year Ship Qty |
| `dpw_on_hand` | `float` | `140.0` | DPW on hand qty |
| `dpw_open_orders` | `float` | `1.0` | DPW open orders qty |
| `dpw_free_stock` | `float` | `137.0` | DPW free stock qty |
| `dpw_tot_physical_stock` | `float` | `137.0` | DPW tot physical stock qty |
| `offsite_storage` | `float` | `0.0` | Offsite storage qty |
| `open_mfg_inc_await_strg` | `float` | `2.0` | Inbound Stock Qty |
| `returns_receipted` | `float` | `0.0` | Returns receipted qty |
| `returns_in_prog` | `float` | `0.0` | Returns in prog qty |
| `frozen` | `float` | `0.0` | Frozen qty |
| `4_week_avg` | `float` | `-1` | 4 weeks avg ship qty |
| `8_week_avg` | `float` | `-2` | 8 weeks avg ship qty |
| `min_stock` | `float` | `0.0` | Min stock qty |
| `max_stock` | `float` | `0.0` | Max stock qty |
| `mfg_lead_time` | `float` | `0.0` | Manufacturing lead time |
| `amazon_asin` | `varchar` | | Amazon ASIN |
| `asda_line_cd` | `varchar` | | ASDA line code |
| `sains_line_cd` | `varchar` | | Sainsbury's line code |
| `tesco_line_cd` | `varchar` | | Tesco line code |
| `year_4_ship` | `float` | `0` | Qty shipped year-4 |
| `year_3_ship` | `float` | `0` | Qty shipped year-3 |
| `year_2_ship` | `float` | `0` | Qty shipped year-2 |
| `lifetime_returns` | `float` | `4.0` | Lifetime returns qty |
| `lifetime_net_ship` | `float` | `-4` | Lifetime net ship |
| `returns_disp` | `varchar` | `Keep` | Returns disposition |
| `returns_rule` | `varchar` | | Returns rule |
| `cert_gb` | `varchar` | `TBC` | Cert GB |
| `cert_ie` | `varchar` | `TBC` | Cert IE |
| `commodity_code` | `varchar` | `85234920` | Commodity code |
| `country_of_origin` | `varchar` | `GB` | Product country of origin |

## Data Flow

1. **Drop** - Partner uploads daily inventory file to S3 drop location:
   - `s3://prod-orcd-ftp/ftp/dpworld/`
   - File naming: `dpwstock_orchard_YYYYMMDD.csv`

2. **Download** - Flow detects and downloads the file from drop location to archive
   - `s3://cucumbers/physical-warehouse-reports/dpworld/archives/{date}/`

3. **Stage** - Raw data loaded into Snowflake staging table with metadata:
   - **Database:** `PHYSICAL_REPORTING`
   - **Table:** `PHYSICAL_REPORTING.PROD.staging_raw_physical_daily_dpw`
   - **Account:** TheOrchard