# Analyzing LabelProfile vs vend_contact

Analyzing the differences between roles in LabelProfile and vend_contact.
The data is from Snowflake as of 2024-08-22.

## Steps
1. Run the Snowflake query..
2. Export the results to a CSV file.
3. name the file `snowflake-lp-vc.csv`.
4. run `bun dev`

## Snowflake Query

```sql
-- LabelProfile versus vend_contact
SELECT 
    i.email,
    lp.profile_id,
    -- Sort and lowercase the existing lp.roles string
    ARRAY_TO_STRING(ARRAY_SORT(SPLIT(LOWER(lp.roles), ',')), ',') as lp_roles,
    LISTAGG(DISTINCT LOWER(REPLACE(vr.role, ' ', '_')), ',') WITHIN GROUP (ORDER BY LOWER(REPLACE(vr.role, ' ', '_')) ASC) as vendor_contact_roles
FROM FACTS.PROD.PROFILE lp
INNER JOIN FACTS.PROD.HAS_PROFILE hp ON hp.profile_uuid = lp.uuid
INNER JOIN FACTS.PROD.IDENTITY i ON i.id = hp.identity_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VEND_CONTACT vc ON vc.id = lp.profile_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VEND_CONTACT_ROLES vcr ON vcr.vend_contact_id = vc.id
INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VENDOR_ROLES vr ON vr.id = vcr.role_id
WHERE profile_type = 'LabelProfile' 
AND i.active = 'Y'
GROUP BY i.email, lp.profile_id, lp.roles
ORDER BY i.email, lp.profile_id;
```

## Results Summary
Total records: 149,553
Matches: 148,708
LP has fewer roles: 507
LP has more roles: 338
Total mismatches: 845