# IDR 200: Extracting Identity Graph from Profile Attribute Snapshot Data with Data Distiller

## **Prerequisites**

You need to get familiar with Profile attribute snapshot dataset explorations. Please complete or browse the following sections before proceeding:

{% content-ref url="../unit-6-data-distiller-audiences/dda-300-audience-overlaps-with-data-distiller" %}
[dda-300-audience-overlaps-with-data-distiller](https://data-distilller.gitbook.io/adobe-data-distiller-guide/unit-6-data-distiller-audiences/dda-300-audience-overlaps-with-data-distiller)
{% endcontent-ref %}

## Scenario

The profile attribute snapshot dataset is a daily export from the Real-Time Customer Profile. This dataset also contains the identities for the profile data. The identities are encapsulated in a map data structure with identity types (called namespaces in AEP parlance) and identity values. There can be multiple identity types (email, cookie, cross-device such as CRM, etc.) and there can be multiple values within each. You are tasked with transforming this data in the map structure to a relational structure that can function as a lookup table. This lookup table will serve as the foundation of all analytics you will do on Real-Time Customer Profile data and beyond. Whether it is Customer 360, SQL Traits, or ML workflows, this lookup table will be invaluable for those use cases.

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FBRPpwCCbyu2PN9ASe7sV%2FScreen%20Shot%202023-08-04%20at%204.49.53%20PM.png?alt=media&#x26;token=ff815157-2091-41aa-b46e-350a1c6c9367" alt=""><figcaption></figcaption></figure>

## **Exploring the Map Structure using Data Distiller: The Identity Map**

Before you extract the identity information, you need to understand the map structure. Use the to\_json feature in Data Distiller to get in place information about the schema structure without having to go to the Schemas UI screen. This is an extremely important feature with deeply nested data structures and maps.&#x20;

{% code overflow="wrap" %}

```sql
SELECT to_json(identityMap) FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903;
```

{% endcode %}

If you execute the following, you will see the following:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FBZTmanH1RLmpMqkg3API%2FScreen%20Shot%202023-08-02%20at%202.54.10%20PM.png?alt=media&#x26;token=b848e8d4-c350-4d89-be86-aa04ba0f59cd" alt=""><figcaption><p>Identity maps structure</p></figcaption></figure>

What you see above is the identity map structure. The map has the identity namespace (`email, crmid`) as the unique key to index the values. Note that this is an array of values. If you need to extract the identities, you will need to use the following code:

{% code overflow="wrap" %}

```sql
SELECT identityMap.email.id FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903;
```

{% endcode %}

The results look like the following:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2Fv1OZKOpuVED3kQxb8x8z%2FScreen%20Shot%202023-08-02%20at%202.58.30%20PM.png?alt=media&#x26;token=3231131a-1b5f-4098-b65d-66ab8ab473ce" alt=""><figcaption><p>Array of identity values</p></figcaption></figure>

Typically we would explode this array

{% code overflow="wrap" %}

```sql
SELECT explode(identityMap.email.id) FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903;
```

{% endcode %}

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FbSKQ4LSHukbf7s5dStng%2FScreen%20Shot%202023-08-02%20at%203.01.49%20PM.png?alt=media&#x26;token=5c35769a-d6c8-4e00-ba2d-ce6d84392891" alt=""><figcaption><p>Exploding the array helps you extract the identities.</p></figcaption></figure>

{% hint style="info" %}
`identityMap.email.id` gives you the array of email identity values and not just an email identity value and these arrays are great for explode functions.
{% endhint %}

But there is a problem - plain vanilla "explode" will get rid of rows that do not have any identity values. That is a problem because the absence of an identity is a signal and is possibly associated with other identities that the person may have. Let us fix the issue by using *explode\_outer* from Data Distiller:

{% code overflow="wrap" %}

```sql
SELECT explode_outer(identityMap.email.id) AS email  FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903
```

{% endcode %}

<div data-full-width="false"><figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FFcnnrAfBdDC5KiUgvNw6%2FScreen%20Shot%202023-08-02%20at%203.35.11%20PM.png?alt=media&#x26;token=4b49ca41-d864-4199-bd68-73f86f56e108" alt=""><figcaption><p>explode_outer retains the nulls for the identity namespaces</p></figcaption></figure></div>

## **Extract Identities from the Map Structure to Create an Identity Lookup Table**

But just doing an `explode_outer` on a single identity namespace is of no use. We have destroyed the association between identities within that same namespace. Let us generate a synthetic UUID for each profile row to keep track of our identities as we explode them. If we do this right, we will generate a table of UUIDs and related identities as a columnar table that gives us leverage to use it for a variety of operations.&#x20;

If we take the first element of the email namespace and concatenate that with the first element of the `crmid` namespace, we are guaranteed a unique identifier. If not, it would mean that two rows in the profile export dataset have that identity in common. The identity stitching in Real-Time Customer Profile should have taken care of it and merged it into a single row.&#x20;

Let us now generate the unique UUIDs along with the schema-friendly identity map structure

{% code overflow="wrap" %}

```sql
SELECT concat(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,to_json(identityMap) from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903
```

{% endcode %}

The results will look like:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FTSoaqdb6Ah8Yj1wwvPEk%2FScreen%20Shot%202023-08-02%20at%204.45.26%20PM.png?alt=media&#x26;token=006f1a4a-8359-4609-98d4-43594f873d25" alt=""><figcaption><p>Generate the unique ID without llosing identity associations.</p></figcaption></figure>

But `concat` string function does a poor job of concatenating NULL values. We have to remedy that with `COALESCE`:

{% code overflow="wrap" %}

```sql
SELECT COALESCE(identityMap.email.id[0],identityMap.crmid.id[0]) as unique_id,to_json(identityMap) from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903
```

{% endcode %}

This works and you will get:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2F31C9bFnSnanJKBUruoab%2FScreen%20Shot%202023-08-02%20at%205.43.41%20PM.png?alt=media&#x26;token=8b91a45c-8c13-4641-b72b-37b819f3f964" alt=""><figcaption><p>COALESCE function retrieves the first non-zero value in a list and is a good choice for a unique ID for the profile in our example.</p></figcaption></figure>

If there are two identity namespaces, then the `explode_outer` operator works on each, one at a time. Make sure you remove the `to_json` as we do not need it anymore:

{% code overflow="wrap" %}

```sql
SELECT unique_id, explode_outer(identityMap.email.id) AS email, identityMap.crmid.id AS crmid FROM
(SELECT coalesce(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,identityMap from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903)
```

{% endcode %}

You will get:&#x20;

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FBuUGe9lINqOhxnSEHmC1%2FScreen%20Shot%202023-08-02%20at%205.50.28%20PM.png?alt=media&#x26;token=8fea26fb-3780-47fc-b7a7-ea5459925286" alt=""><figcaption><p><em>email</em> identities have been separated into separate rows without breaking profile association.</p></figcaption></figure>

We need to explode this one more time for `crmid` and we should get:

{% code overflow="wrap" %}

```sql
SELECT unique_id, email, explode_outer(crmid) as crmid FROM (
SELECT unique_id, explode_outer(identityMap.email.id) AS email, identityMap.crmid.id AS crmid FROM
(SELECT coalesce(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,identityMap from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903)
)
```

{% endcode %}

The results would be:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FqAmU3Rg71BaukshNhP0R%2FScreen%20Shot%202023-08-02%20at%205.51.35%20PM.png?alt=media&#x26;token=8ee6ddda-ae7e-461a-a69f-964ba76b3730" alt=""><figcaption><p>Identity lookup table in relational form</p></figcaption></figure>

Using this table, I can do some very interesting analysis. For example, we can look at the histogram of `email` IDs in the system:

{% code overflow="wrap" %}

```sql
SELECT bucket, count(unique_id) FROM(
SELECT unique_id, count(CASE WHEN email IS NULL THEN 0 ELSE email END) as bucket FROM(
SELECT unique_id, email, explode_outer(crmid) as crmid FROM (
SELECT unique_id, explode_outer(identityMap.email.id) AS email, identityMap.crmid.id AS crmid FROM
(SELECT coalesce(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,identityMap from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903)
))
GROUP BY unique_id 
ORDER BY bucket ASC)
GROUP BY bucket
```

{% endcode %}

The answer looks like this:

<figure><img src="https://1899859430-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FEhcgqFIfGdE0GXJzi5yR%2Fuploads%2FGP0TYnsEQvntnY2FhxAd%2FScreen%20Shot%202023-08-02%20at%206.25.16%20PM.png?alt=media&#x26;token=83d851d5-b565-4511-9d5d-c1fc8b972a9c" alt=""><figcaption></figcaption></figure>

Why are we seeing 976 as a bucket and 0 counts for unique\_ids? The sum of 8225 and 976 adds up to the number of profiles. Why did we even get this result in the first place? Is there something wrong with the way the SQL is written or is there something more profound happening in the identity graph?&#x20;

Still cannot figure it out? What if the *email* and *crmid i*dentities were non-existent? You have null rows in the table that you should clean before you do any analytics. For the identity lookup use case which is the goal of this section, it would not matter,