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.