SQL: Grouping Experience Events into arrays for Profile dataset in AEP

[SamyPe1]

Hi everyone,

I’m facing a challenge I haven’t been able to solve yet.

I have an Experience Event dataset that contains multiple rows per ID, and I need to transform it into a Profile dataset by aggregating all rows for a given ID into a single row, using arrays to store the values.

Here’s an example of the schema:

Experience Event Dataset

_entity.fg.ID  
_entity.fg.name  
_entity.fg.score  
_entity.fg.normalizedScore  
_entity.fg.version  
Timestamp  

Target Profile Dataset

_entity.ID  
_entity.fg[].name  
_entity.fg[].score  
_entity.fg[].normalizedScore  
_entity.fg[].version  
extSourceSystemAudit.lastUpdatedDate  

I’m trying to write a SQL query that would group all rows by ID and transform the values into arrays, but I haven’t found a working solution yet.

Has anyone faced a similar use case or could share an example of how to achieve this transformation using SQL in AEP?

Thanks in advance,

Regards,

Samy

[Devyendar]

@SamyPe1 

you can achieve this at high level in Query service by first group by ID (use partition function or subquery with group by) + build a struct per row + collect into an array.

Refer to details query examples here https://experienceleague.adobe.com/en/docs/experience-platform/query/key-concepts/nested-data-struct... 

[KevinFosterDX]

How will you prevent your arrays and the resulting profile records from growing too large? There are multiple limits applicable here:

https://experienceleague.adobe.com/en/docs/experience-platform/profile/guardrails#data-model-limits

Would you be better served with custom aggregates instead, e.g. first, latest, count, min, max, etc.?