Product Schema

Entity Type: Product
Purpose: Represents a product with associated items, resources, and relationships
Format: JSONL (one product per line)
Sample Files: ecommerce_20260603222533716_001.jsonl (CEUS, new BUQ format)

Structure

Active Product

json

{
  "EntityId": number,
  "EntityTypeId": "Product",
  "FieldSetId": null,
  "Attributes": { ... },
  "Items": [ ... ],
  "Resources": [ ... ],
  "InboundLinks": [ ... ],
  "OutboundLinks": [ ... ]
}

Deleted Product

json

{
  "EntityId": number,
  "EntityTypeId": "Product",
  "Deleted": true
}

When Deleted: true, all other fields (Attributes, Items, Resources, InboundLinks, OutboundLinks) are null/empty. ETL should remove these products from the target system.

Top-Level Properties

EntityId (number) - Unique product identifier
EntityTypeId (string) - Always "Product"
FieldSetId (null) - Optional fieldset reference
Deleted (boolean) - Present and true for deletion records; absent for active products
Attributes (object) - Product-level attributes
Items (array) - Associated Item entities (SKUs)
Resources (array) - Product-level media/documents
InboundLinks (array) - Entities linking to this product
OutboundLinks (array) - Entities this product links to

Product-Level Attributes

Products have a small set of their own attributes (6-9 populated):

ProductId (string) - Product identifier (e.g., "P100982724")
ProductShortDescription (localized object) - {"en": "...", "es": "...", "fr-CA": "..."}
ProductProductFamily (string) - Product family code (e.g., "PML320")
ProductMainSupplier (string) - Primary supplier identifier
ProductReportingTaxonomyLevel3 (string) - Reporting taxonomy code
ProductWatscoTaxonomy1 (string) - Watsco structure code level 1
ProductWatscoTaxonomy2 (string) - Watsco structure code level 2
ProductWatscoTaxonomy3 (string) - Watsco structure code level 3

Items Array

Each product contains zero or more Item entities representing SKUs.

Single-item products: ~95% of catalog
Multi-item products: up to 59 items per product (configurable products with variants)
Zero items: only on Deleted: true records

json

{
  "EntityId": number,
  "EntityTypeId": "Item",
  "FieldSetId": null,
  "Attributes": { ... },
  "BusinessUnitQualifiers": [ ... ],
  "Resources": [ ... ],
  "InboundLinks": [ ... ],
  "OutboundLinks": [ ... ]
}

BusinessUnitQualifiers (June 2026+)

IBUQ (Business Unit Qualified) fields are now in a nested array on each Item rather than flat on Attributes. Each entry represents one BU or region's data for that item.

json

{
  "EntityId": 513622,
  "EntityTypeId": "IBUQ",
  "FieldSetId": "IBUQCEUS",
  "Attributes": {
    "IBUQBUName": "CEUS",
    "IBUQEffectiveBusinessUnit": "CEUS",
    "IBUQERPItemNumber": "EHK310B",
    "IBUQBUActiveinECommerce": true,
    ...
  }
}

Master BUQ: Where IBUQBUName == IBUQEffectiveBusinessUnit — contains full IBUQ data
Regional BUQs: Sparse overrides — inherit missing fields from master
FieldSetId: Identifies the BU/region (IBUQCEUS, IBUQCEFL, IBUQBAK, etc.)
BUQ presence: Indicates the item is carried by that region

See business-unit-qualifiers.md for full documentation.

Key Item Attributes

Identification:

ItemLegacyPIMItemNo - Legacy PIM identifier
ItemManufacturerItemNo - Manufacturer part number
ItemWatscoItemNo - Watsco internal item number
ItemUPCCode - UPC barcode
ItemEANCode - EAN barcode
ItemGTINCode - GTIN code

Supplier Information:

ItemMainSupplier - Primary supplier name
ItemManufacturer - Manufacturer name
ItemSupplierShortDescriptionEN - Short description
ItemSupplierLongDescriptionEN - Long description

Physical Attributes:

ItemItemImperialHeightinInches
ItemItemImperialLengthinInches
ItemItemImperialWidthinInches
ItemItemImperialWeightinPounds
ItemItemImperialCubicMeasurement
ItemItemImperialDimensionalWeight

Packaging:

ItemPackagedImperialHeightinInches
ItemPackagedImperialLengthinInches
ItemPackagedImperialWidthinInches
ItemPackagedImperialWeightinPounds
ItemItemQuantity
ItemItemQuantityType (e.g., "EA")
ItemItemPackagedQuantity
ItemItemMastCartonQuantity

Inventory & Status:

ItemOnHandQtyInAllWatscoSubs - Total on-hand quantity
ItemSupplierDiscontinuedFlag - Discontinuation status
ItemCreatedOn - Creation timestamp
ItemLastChangedon - Last modified timestamp

Business Flags:

ItemHydrosItem - Hydros system flag
ItemSupplySyncItem - Supply sync flag
ItemRebateItem - Rebate eligibility
ItemRebateCategory - Rebate category
ItemOEMPart - OEM part flag

Origin:

ItemCountryofOrigin - Array of country codes (e.g., ["USA"])

Resources

Both Product and Item entities can have associated resources:

json

{
  "ResourceURL": "https://watsco-pim.s3.us-east-1.amazonaws.com/InRiver/SANDBOX/ECOM/assets/...",
  "Attributes": {
    "ResourceFileId": number,
    "ResourceFilename": "string",
    "ResourceTitle": "string",
    "ResourcePublishedStatus": "NotPublished",
    "ResourceResourceType": "normal|ai1|ai2|ai3|ai4|logo|ibw",
    "ResourceImportProcessed": boolean,
    "ResourceMimeType": "jpeg|png|pdf",
    "ResourceContentHash": "SHA-256 hash string"
  }
}

Resource Types

normal - Standard product image (most common - 89%)
ai1-ai4 - Additional images (11%)
logo - Brand/manufacturer logo
ibw - Unknown type
cl - Cutsheet/catalog (legacy format only)
subs - Submittal sheet (legacy format only)

CDN Migration

Resources have moved from Aperture CDN to Watsco S3:

Legacy: https://asset.productmarketingcloud.com/api/assetstorage/...
Current: https://watsco-pim.s3.us-east-1.amazonaws.com/InRiver/SANDBOX/ECOM/assets/...

Resource Coverage

~81% of products have at least one resource
Products range from 0 to 76 resources
All current resources are images (jpeg/png); no PDFs in JSONL samples

Links

InboundLinks (Product-level)

Category assignments via ChannelNodeProduct link type. All active products have at least one.

json

{
  "LinkTypeId": "ChannelNodeProduct",
  "SourceEntityTypeId": "ChannelNode",
  "SourceEntityId": number
}

OutboundLinks (Product-level)

Always empty in observed samples.

Item-Level Links

Items have their own InboundLinks and OutboundLinks for product relationships:

json

{
  "LinkTypeId": "ItemAssociatedItem|ItemSubstituteItem|ItemUpsellingItem",
  "TargetEntityTypeId": "Item",
  "TargetEntityId": number
}

Link types observed:

ItemAssociatedItem - Related/associated products (most common)
ItemSubstituteItem - Substitute/replacement products
ItemUpsellingItem - Upselling recommendations

JSONL File Naming Conventions

ecommerce_{YYYYMMDDHHMMSSmmm}_{seq}.jsonl        — delta
ecommerce_{YYYYMMDDHHMMSSmmm}_full_{seq}.jsonl    — full load

Timestamp is the export generation time (UTC)
Sequence numbers (_001, _002, _003) indicate file chunking for large exports
_full_ marker indicates a complete snapshot (vs incremental delta)

S3 Paths

s3://watsco-pim/InRiver/SANDBOX/ECOM/{BU}/PRODUCT/YYYY/MM/  — Product JSONL
s3://watsco-pim/InRiver/SANDBOX/ECOM/{BU}/CHANNEL/           — ChannelStructure
s3://watsco-pim/InRiver/SANDBOX/ECOM/SHARED/                 — AttributeFile.json
s3://watsco-pim/InRiver/SANDBOX/ECOM/assets/                 — Media files (CDN)

BU codes: GEM, BAK, ECM, CEUS, CIAC, HOM

Example Hierarchy

Product 648070 (Active)
├── Attributes: ProductId, ProductShortDescription, ProductFamily, Taxonomy
├── InboundLinks: ChannelNodeProduct → ChannelNode 50714
├── Item 432829 - "PML320 4\" Brown Louvered Hood, Ring"
│   ├── OutboundLinks: ItemAssociatedItem → Item 431717
│   └── Resources: [normal.jpg]
├── Item 432838 - "PML320 4\" White Louvered Hood, 11\" Tail"
├── Item 432845 - "PML320 4\" White Louvered Hood, Ring"
├── Item 432840 - "PML320 4\" Brown Louvered Hood, 11\" Tail"
└── Item 442896 - "PML320 6\" White Louvered Hood, Ring"

Product 647401 (Deleted)
└── {"EntityTypeId": "Product", "EntityId": 647401, "Deleted": true}

Usage

Products are the primary entity for e-commerce integration:

Contains all SKU-level data via Items array
Links to media assets via Resources
Connects to categories via ChannelNode relationships
Provides complete product information for display and ordering

See business-unit-qualifiers for BUQ inheritance and regional data
See attribute-file for complete attribute definitions
See channel-node for category relationships

Undocumented S3 Exports

The following S3 paths exist but are not yet documented or consumed by the pipeline:

Path	Contents	Status
`ECOM/CIAC/PRODUCT/`	CIAC (Carrier InterAmerican Corp) product exports	Not documented
`ECOM/HOM/`	HOM business unit exports	Not documented
`ECOM/assets/`	Media/image files (CDN source)	Not documented
`EDW/brand/`	Brand reference data	Not documented
`EDW/channel/`	Channel structure (EDW format)	Not documented
`EDW/item/`	Item-level EDW export	Not documented
`EDW/supplier/`	Supplier reference data	Not documented
`Media/`	Media assets (legacy path?)	Not documented
`Supplier/`	Supplier data (legacy path?)	Not documented

Product Schema ​

Structure ​

Active Product ​

Deleted Product ​

Top-Level Properties ​

Product-Level Attributes ​

Items Array ​

BusinessUnitQualifiers (June 2026+) ​

Key Item Attributes ​

Resources ​

Resource Types ​

CDN Migration ​

Resource Coverage ​

Links ​

InboundLinks (Product-level) ​

OutboundLinks (Product-level) ​

Item-Level Links ​

JSONL File Naming Conventions ​

S3 Paths ​

Example Hierarchy ​

Usage ​

Related Documentation ​

Undocumented S3 Exports ​

Product Schema

Structure

Active Product

Deleted Product

Top-Level Properties

Product-Level Attributes

Items Array

BusinessUnitQualifiers (June 2026+)

Key Item Attributes

Resources

Resource Types

CDN Migration

Resource Coverage

Links

InboundLinks (Product-level)

OutboundLinks (Product-level)

Item-Level Links

JSONL File Naming Conventions

S3 Paths

Example Hierarchy

Usage

Related Documentation

Undocumented S3 Exports