---
schema_version: "1"
id: 8d159451467d1627f8b944b579b7f580fd0f67e18d3043fb906cb085a1561584
title: Normalize Data with UNION Use Case
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/data/264-0-0/c360_a_streaming_transform_normalization.md
canonical_url: https://help.salesforce.com/s/articleView?id=data.c360_a_streaming_transform_normalization.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: 60296b47539ca2e8e74f8a0f37c9e71dd6fde7677dcd35a8a333e1e9a4189272
description: Normalize data ingested into Data 360 with streaming data transforms.
products:
  - Data 360
release: "264"
---
# Normalize Data with `UNION` Use Case

Normalize data ingested into Data 360 with streaming data transforms.

Let’s look at an example. The source data lake object CRMContacts contains up to three phone numbers for each customer.

    
| CustomerId | Name | MobilePhone | HomePhone | WorkPhone |
| --- | --- | --- | --- | --- |
| C1 | John | 408-512-2234 |   | 650-342-4612 |
| C2 | Peter |   | 310-453-3421 | 915-870-9900 |

If the data lake object CRMContacts is directly mapped to the data model object Contact Point Phone, only one contact point—MobilePhone or HomePhone or WorkPhone—can be mapped. As a result, only one of the three phone numbers is available in product areas that consume the data model object, such as calculated insights, identity resolution, and segmentation.

Here’s how to use a streaming data transform to make all three phone numbers available.

```
SELECT CONCAT(CRMContacts.CustomerId, "_Mobile") as PhoneId, 
    CRMContacts.CustomerId as CustomerId, 
    CRMContacts.MobilePhone as PhoneNumber,
    "Mobile" as PhoneType
FROM CRMContacts
WHERE ISNOTNULL(CRMContacts.MobilePhone) AND CRMContacts.MobilePhone <> ""
UNION
SELECT CONCAT(CRMContacts.CustomerId, "_Home") as PhoneId,
    CRMContacts.CustomerId as CustomerId, CRMContacts.HomePhone as PhoneNumber,
    "Home" as PhoneType
FROM CRMContacts
WHERE ISNOTNULL(CRMContacts.HomePhone) AND CRMContacts.HomePhone <> ""
UNION
SELECT CONCAT(CRMContacts.CustomerId, "_Work") as PhoneId,
    CRMContacts.CustomerId as CustomerId,
    CRMContacts.WorkPhone as PhoneNumber, 
    "Work" as PhoneType
FROM CRMContacts
WHERE ISNOTNULL(CRMContacts.WorkPhone) AND CRMContacts.WorkPhone <> ""
```

After the data transform runs, the target DLO contains one record for each type of phone number.

   
| PhoneId | CustomerId | PhoneNumber | PhoneType |
| --- | --- | --- | --- |
| C1\_Mobile | C1 | 408-512-2234 | Mobile |
| C1\_Work | C1 | 650-342-4612 | Work |
| C2\_Home | C2 | 310-453-3421 | Home |
| C2\_Work | C2 | 915-870-9900 | Work |

Then, you can map the target Phones DLO to the Contact Point Phone DMO. The Contact Point Phone DMO now contains all three phone numbers for each customer.

To use the `UNION` operator in a streaming data transform, each `SELECT` statement must:

- Reference the same source data lake object. In the example above, all three `SELECT` statements use the same source data lake object CRMContacts.
- Map to the same fields for the target DLO. Each `SELECT` statement must have the same schema. In our example, all three `SELECT` statements map to the same set of fields: `PhoneId`, `CustomerId`, `PhoneNumber`, and `PhoneType`.
- Generate a unique value for the primary key field. If non-unique values are generated for the primary key field, one of the non-unique values is selected randomly. In our example, the `CONCAT()` function generates unique values for the primary key field on the target DLO.
- Have a unique field mapped to a primary key, or a set of fields mapped to a primary key in an expression.

`UNION` can be used with a maximum of five `SELECT` statements.

#### See Also

- [_Salesforce Help_: Normalized and Denormalized Data](https://help.salesforce.com/s/articleView?id=data.c360_a_normalized_and_denormalized_data.htm&release=264.0.0&type=5 "Data originates from multiple sources and can be normalized or denormalized. The Data 360 standard data model is normalized, so incoming data must be normalized before it can be mapped to the data model. Because not all source systems provide normalized export options or normalize data, assess your field-level data to establish how to transform and map the source data to the standard data model.")