Profile Snapshot and segment qualification troubleshooting

[#1]

What is it?
A profile snapshot is system generated dataset which is generated daily in AEP, providing information about the unified profile.
A profile snapshot in Adobe Experience Platform refers to a snapshot of the attribute (record) data that your organization has within the Profile store. This snapshot provides a static view of the data at a specific point in time and does not include any event (time series) data.

How to access it
To access it, navigate to the list of datasets in AEP, where you'll find the profile snapshot.
Simply filter to 'Show System Datasets' and search for 'snapshot'
You can then go to the dataset page where you'll find the exact table name which you can then copy to use in Query Service: Ie: profile_snapshot_export_fea1xxf3_0edb_xx74_b964_8a63d48xxxxx

For instance this can be used in Query Service to better understand what profiles might be part of a particular segment following the latest daily segment evaluation taking place in AEP.

Counting records:

SELECT count()
FROM profile_snapshot_export_ID -- replace export id
WHERE segmentMembership['ups']['segment ID'] IS NOT NULL
AND segmentMembership['ups']['segment ID']['status'] IN ('realized')

Listing records:

SELECT *
FROM profile_snapshot_export_ID -- replace export id
WHERE segmentMembership['ups']['segment ID'] IS NOT NULL
AND segmentMembership['ups']['segment ID']['status'] IN ('realized')
LIMIT 10

Narrowing down to a specific userID:

SELECT *
FROM profile_snapshot_export_ID -- replace export id
WHERE _tenant.CustomID = 'User123'
AND segmentMembership['ups']['segment ID'] IS NOT NULL
AND segmentMembership['ups']['segment ID']['status'] IN ('realized', 'exited')

Now say you'd also like to understand if the segment membership was different with the previous daily segmentation, there is a way to query previous versions of the snapshot, each one corresponding to a specific date. Note we usually keep from 7 to 10 days in our logs.

select history_meta('profile_snapshot_export_fea1xxf3_0edb_xx74_b964_8a63d48xxxxx');

You can then target a specific version of the Profile Snapshot:

SELECT *
FROM profile_snapshot_export_ID SNAPSHOT AS OF 34
WHERE _tenant.CustomID = 'User123'
AND segmentMembership['ups']['segment ID'] IS NOT NULL
AND segmentMembership['ups']['segment ID']['status'] IN ('realized', 'exited')

The above is just an example, there are obviously so many queries you could make depending on your objective.

[#2]

Hi @Tof_Jossic  :  Thanks for providing more insight and way to query Segment Membership (from profile snapshot).

I tried using following query that you provided but that it does NOT work for my sandbox. it always gives NULL/ empty results (& does NOT error out).  Is this query syntactically correct?  or were you trying to explain just logical representation?  

Listing records:

SELECT * FROM profile_snapshot_export_ID -- replace export id WHERE segmentMembership['ups']['segment ID'] IS NOT NULL AND segmentMembership['ups']['segment ID']['status'] IN ('realized')  LIMIT 10

As of now, we been using explode() function to unpack Nested JSON object (nested key->value pairs).  we been writing nested SQL query to explode JSON objects at multiple levels.  

Thanks,

Nimesh

[#3]

@Nimesh11 I tested again and it does work with:

SELECT *
FROM profile_snapshot_export_xxxxxxxxxxxx
WHERE segmentMembership['ups']['973xxxxx-xxx-40xx-9xxx-78587xxxxxxx'] IS NOT NULL
AND segmentMembership['ups']['973xxxxx-xxx-40xx-9xxx-78587xxxxxxx']['status'] IN ('realized')
LIMIT 10

However, I believe you'll have to target the correct profile_snapshot_export as you may have several ones depending on the merge policies in that sandbox.

ie: based on the segment I am querying, I'm using the profile_snapshot_export using Merge Policy ID Stitching: Private Graph

(same for me: I don't get any results if I use the wrong Snapshot)

If unsure, you can use this from the Catalog Service API (GET https://platform.adobe.io/data/foundation/catalog/dataSets) to see the Snapshots relationship with the Merge Policies and identify the correct one