Untitled

Hi Krishna,

I hope you're doing well. I wanted to provide you with some information regarding the CVS front store products hierarchy and how it relates to a real-time example of Quaker Oats. Please refer to the image below for a visual representation.

[Insert image here]

It's important to note that in this context, SKU's and Products are used interchangeably. At the highest level, we have the MDSE GROUP or BU (Business Units). There are 7 MDSE GROUPS: GENERAL MERCHANDISE, CONSUMER HEALTH CARE, PERSONAL CARE, EDIBLES, GREETING CARDS, BEAUTY CARE, and PHOTO. Each MDSE GROUP has different Categories, and each Category has different Sub-Categories, and so on.

To access this information in Snowflake, most of the product-related data is stored in the PROD_COMM_DB.CDP_APP.SKU and PROD_COMM_DB.CDP_APP.CAT_SUBCAT tables. You can use the following logic to join these two tables:

SELECT *
FROM PROD_COMM_DB.CDP_APP.SKU SK
LEFT JOIN PROD_COMM_DB.CDP_APP.CAT_SUBCAT ST
USING (SUBCAT_NBR, SUBCAT_DSC, SEG_NBR, SEG_DSC, CAT_DSC, CAT_NBR)

Now, let's talk about inactive SKU's. The SKU's or products have four statuses: System Inactive, Active, Pending Disco, and Discontinued. If you want to filter out the non-active SKU's, you can use the following code:

SELECT *
FROM PROD_COMM_DB.CDP_APP.SKU
WHERE SKU_STATUS != 'Active'

I hope this information clarifies the hierarchy and provides you with the necessary insights. If you have any further questions or need additional assistance, please don't hesitate to let me know.

Best regards,